MySQL在范围内选择坐标 [英] MySQL select coordinates within range
问题描述
我在我的数据库中有100 000个地址(即记录)。他们每个人都有自己的坐标(经度和纬度)。
现在,考虑到地理位置(经纬度),我想在地图上只显示5英里范围内的地址(使用谷歌地图v3 API)。
这意味着通常只有5或6个地址必须在100 000个地址中显示。
一个解决方案可以检索所有记录并应用Java中的公式计算距离只有当它在范围内时才显示它。
这会浪费处理能力,因为我只需要检索所有记录需要在地图上显示其中的5个或6个。
如何在数据库端(MySQL)解决这个问题,以便仅返回5英里范围内?
您可以使用称为 Haversine公式。
$ sql =SELECT *,(3959 * acos(cos(radians(。 $ lat。 ))* cos(弧度(lat))* cos(弧度(lng) - 弧度(。$ lng。))+ sin(弧度(。$ lat。))* sin(弧度(lat)) ))AS距离FROM your_table HAVING距离< 5;
其中 $ lat
和 $ lng
是你点的坐标,lat / lng是你的表格列。以上将列出5 nm范围内的位置。将 3959
替换为 6371
以更改为公里。
此链接可能很有用: https://developers.google.com/maps/articles/phpsqlsearch_v3
编辑:我没有看到你提到Java。这个例子在PHP中,但查询仍然是你需要的。
I've in my database 100 000 addresses (that is records).
Each one of them has its own coordinates (latitude and longitude).
Now, given the geo location of the user (latitude and longitude), I want to show on a map only the addresses inside the 5 miles range (using Google maps v3 APIs).
This means that usually only 5 or 6 addresses have to be shown out of the 100 000 addresses.
One solution could be retrieving all the records and apply a formula in Java to calculate the distance of each address and show it only if it's inside the range.
That would be a waste of processing power, because I would need to retrieve all the records, when I only need to show 5 or 6 of them on the map.
How can I solve this problem on the database side (MySQL), in order to return only the addresses in the 5 miles range?
You can use what is called the Haversine formula.
$sql = "SELECT *, ( 3959 * acos( cos( radians(" . $lat . ") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(" . $lng . ") ) + sin( radians(" . $lat . ") ) * sin( radians( lat ) ) ) ) AS distance FROM your_table HAVING distance < 5";
Where $lat
and $lng
are the coordinates of your point, and lat/lng are your table columns. The above will list the locations within a 5 nm range. Replace 3959
by 6371
to change to kilometers.
This link could be useful: https://developers.google.com/maps/articles/phpsqlsearch_v3
Edit: I didn't see you mentioned Java. This example is in PHP but the query is still what you need.
这篇关于MySQL在范围内选择坐标的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!