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

问题描述

我有一个数据库，目前填充了大约 10k 行.它们都有一个基于邮政编码中心的经度/纬度.我发现了一个我现在正在尝试扩展的查询，但总而言之，它在一定程度上运行良好.然而，在我下面的例子中，我试图在 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 = San Jose, CA我得到的结果如下: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.

我当前的查询看起来像:

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"

推荐答案

这是我在我使用的商店定位器上使用的查询:

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屋！