mySQL 经纬度查询 x 英里半径内的其他行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8994718/
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
mySQL longitude and latitude query for other rows within x mile radius
提问by chris
I have a database populated with roughly 10k rows currently. All of them have a longitude/latitude based on the center of a zipcode. I found a query that I am now attempting to expand on, but all in all it works well to a point. However, in my example below I am trying to find things within a 25 mile radius, which in all seems to work for the most part. Most of my results do yield within the 25 mile criteria, however I am getting a handful that are way off anything from 86 miles to 800 miles off the mark.
我有一个数据库，目前填充了大约 10k 行。它们都有一个基于邮政编码中心的经度/纬度。我发现了一个我现在正在尝试扩展的查询，但总而言之，它在一定程度上运行良好。然而，在我下面的例子中，我试图在 25 英里半径内找到东西，这似乎在大多数情况下都有效。我的大部分结果确实在 25 英里的标准内产生，但是我得到了少数结果，偏离了 86 英里到 800 英里的范围。
Example: This is my center lat/lon: 37.2790669,-121.874722 = San Jose, CA Im getting results like: 33.016928,-116.846046 = San Diego, CA which is about 355 miles from San Jose.
示例：这是我的中心纬度/经度：37.2790669,-121.874722 = San Jose, CA 我得到的结果如下：33.016928,-116.846046 = San Diego, CA，距圣何塞约 355 英里。
my current query looks like:
我当前的查询看起来像：
SELECT *,(((acos(sin(($lat*pi()/180)) * sin((`latitude`*pi()/180))+cos(($lat*pi()/180))
* cos((`latitude`*pi()/180)) * cos((($lon - `longitude`)*pi()/180))))*180/pi())*60*1.1515)
AS `distance` FROM `geo_locations` HAVING `distance` <= 25 ORDER BY `distance` ASC"
回答by Gordnfreeman
Here is the query I use on the store locator I work with:
这是我在使用的商店定位器上使用的查询：
SELECT
`id`,
(
6371 *
acos(
cos( radians( :lat ) ) *
cos( radians( `lat` ) ) *
cos(
radians( `long` ) - radians( :long )
) +
sin(radians(:lat)) *
sin(radians(`lat`))
)
) `distance`
FROM
`location`
HAVING
`distance` < :distance
ORDER BY
`distance`
LIMIT
25
:latand :longare the points the passed by the user where latand longare the points stored in the database.
:lat和:long是用户通过的点，其中lat和long是存储在数据库中的点。
The :distance is measured in miles, in the working version of the code the :distance is actually pulled from a drop down ranging from 10-50 miles
:distance 以英里为单位，在代码的工作版本中， :distance 实际上是从 10-50 英里的下拉菜单中拉出来的
Changing the code to work with kilometers can be accomplished by changing 3959 (the distance from the center of the earth to its surface in miles) to 6371 (3959 miles converted to kilometers) thanks to joshhendo for that solution.
由于 joshhendo 的解决方案，可以通过将 3959（从地球中心到地表的距离以英里为单位）更改为 6371（3959 英里转换为公里）来更改代码以使用公里。
