mySQL经度和纬度查询x英里半径内的其他行 [英] mySQL longitude and latitude query for other rows within x mile radius

问题描述

我有一个目前约有1万行的数据库.它们都基于邮政编码的中心具有经度/纬度.我发现了一个我现在试图扩展的查询，但是总的来说它在某种程度上运行良好.但是，在下面的示例中，我试图找到半径25英里以内的事物，这似乎在大多数情况下都是可行的.我的大部分结果都在25英里标准范围内，但是我得到的结果却很少，偏离了86英里到800英里.

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.

示例: 这是我的中心纬度/经度:37.2790669，-121.874722 =加利福尼亚圣何塞 我得到的结果如下:33.016928，-116.846046 =加利福尼亚州圣地亚哥，距离圣何塞约355英里.

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.

我当前的查询如下:

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"

推荐答案

以下是我在与之合作的商店定位器上使用的查询:

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

:lat和:long是用户传递的点，其中lat和long是存储在数据库中的点.

:lat and :long are the points the passed by the user where lat and long are the points stored in the database.

:distance以英里为单位，在代码的工作版本中，:distance实际上是从10到50英里范围内的下拉列表中拉出的.

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

在joshhendo提供的解决方案的帮助下，可以将3959(从地球中心到地面的距离，以英里为单位)更改为6371(将3959英里转换为公里)，从而更改以千米为单位的代码.

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.

这篇关于mySQL经度和纬度查询x英里半径内的其他行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！