MySQL MySQL中经纬度范围内查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4687312/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Querying within longitude and latitude in MySQL
提问by Genadinik
Before asking for specific code examples, I just wanted to ask whether it is possible to make a query something like this pseudo code:
在询问具体的代码示例之前，我只是想问问是否可以进行类似以下伪代码的查询：
select items from table where lat/lon = -within x miles of a certain lat/lon point-
从表中选择项目，其中纬度/经度 = - 在某个纬度/经度点的 x 英里内 -
Is that doable? Or do I have to jump through some hoops? Any good approaches that could be recommended would be great!
那可行吗？还是我必须跳过一些箍？任何可以推荐的好方法都会很棒！
回答by daroczig
You should search for the Haversine formula, but a good start could be:
您应该搜索Haversine 公式，但一个好的开始可能是：
- Creating a Store Locator with PHP, MySQL & Google Maps- See Section 'Finding Locations with MySQL'
- Geo/Spatial Search with MySQL
- 使用 PHP、MySQL 和 Google 地图创建商店定位器- 请参阅“使用 MySQL 查找位置”部分
- 使用 MySQL 进行地理/空间搜索
Citing from the first url:
从第一个网址引用：
Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.
这是将查找距离 37, -122 坐标 25 英里半径内最近的 20 个位置的 SQL 语句。它根据该行的纬度/经度和目标纬度/经度计算距离，然后仅询问距离值小于25的行，按距离对整个查询进行排序，并将其限制为20个结果。要按公里而不是英里搜索，请将 3959 替换为 6371。
SELECT
id,
( 3959
* acos( cos( radians(37) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(-122) )
+ sin( radians(37) )
* sin( radians( lat ) )
)
)
AS distance
FROM markers
HAVING distance < 25
ORDER BY distance
LIMIT 0 , 20;
回答by TheSteve0
I would highly recommend using either SpatiaLite or PostGIS for this kind of operation. They have built in the kind of functions you are trying to hand code. They also have proper support for spatial data which doesn't really exist in MySQL.
我强烈建议使用 SpatiaLite 或 PostGIS 进行此类操作。他们内置了您尝试编写代码的那种功能。它们还对 MySQL 中并不真正存在的空间数据提供了适当的支持。
Not exactly a solution in MySQL but a better solution if you want to keep doing spatial requests in the future.
不完全是 MySQL 中的解决方案，但如果您想在将来继续进行空间请求，则是更好的解决方案。
