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-
从表中选择项目，其中lat/lon = -在某个lat/lon点的x英里范围内-
Is that doable? Or do I have to jump through some hoops? Any good approaches that could be recommended would be great!
这是可行的吗?还是我必须跳过一些圈?任何可能被推荐的好方法都是很棒的!
2 个解决方案
#1
51
You should search for the Haversine formula, but a good start could be:
你应该搜索“哈夫斯公式”，但一个好的开始可能是:
- Creating a Store Locator with PHP, MySQL & Google Maps - See Section 'Finding Locations with MySQL'
- 使用PHP、MySQL和谷歌地图创建一个存储定位器——参见“使用MySQL查找位置”一节
- Geo/Spatial Search with MySQL
- 地理与MySQL /空间搜索
Citing from the first url:
引用第一个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.
下面是SQL语句，它将找到距离37，- 122坐标25英里范围内最近的20个位置。它根据行的纬度/经度和目标纬度/经度计算距离，然后只要求距离值小于25的行，按距离对整个查询进行排序，并将结果限制为20个。用千米代替英里搜索，用6371代替3959。
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;
#2
5
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.
我强烈推荐使用spatial或PostGIS来进行这种操作。它们构建了你想要的那种函数。他们也有适当的空间数据支持，这在MySQL中并不存在。
Not exactly a solution in MySQL but a better solution if you want to keep doing spatial requests in the future.
这不是MySQL中的解决方案，但如果您希望在未来继续执行空间请求，这是一个更好的解决方案。
#1
51
You should search for the Haversine formula, but a good start could be:
你应该搜索“哈夫斯公式”，但一个好的开始可能是:
- Creating a Store Locator with PHP, MySQL & Google Maps - See Section 'Finding Locations with MySQL'
- 使用PHP、MySQL和谷歌地图创建一个存储定位器——参见“使用MySQL查找位置”一节
- Geo/Spatial Search with MySQL
- 地理与MySQL /空间搜索
Citing from the first url:
引用第一个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.
下面是SQL语句，它将找到距离37，- 122坐标25英里范围内最近的20个位置。它根据行的纬度/经度和目标纬度/经度计算距离，然后只要求距离值小于25的行，按距离对整个查询进行排序，并将结果限制为20个。用千米代替英里搜索，用6371代替3959。
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;
#2
5
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.
我强烈推荐使用spatial或PostGIS来进行这种操作。它们构建了你想要的那种函数。他们也有适当的空间数据支持，这在MySQL中并不存在。
Not exactly a solution in MySQL but a better solution if you want to keep doing spatial requests in the future.
这不是MySQL中的解决方案，但如果您希望在未来继续执行空间请求，这是一个更好的解决方案。