SQL：基于经纬度查找最近的邻居

Question

SQL：基于经纬度查找最近的邻居

3

我有一个包含咖啡馆经纬度的表格。我想编写一个SQL查询，以便找到每个咖啡馆最近的咖啡馆。有人可以帮忙提供如何实现这个功能的建议吗？

表格基本上长这样：

咖啡馆ID

+-------------------------+----------------------+----------------------+
| cafe_id                 | gps_latitude         | gps_longitude        |
+-------------------------+----------------------+----------------------+
| 011-1003                | 55.86649500000000000 |  8.16856200000000000 |
| 192-143                 | 57.04419159749860000 | 10.36447024359820000 |
| 037-0233                | 55.08773849210000000 |  8.56101036070000000 |
| 121-934                 | 56.89120900000000000 |  9.16818100000000000 |
+-------------------------+----------------------+----------------------+

非常感谢您的帮助！

- Wessi

如果性能成为问题，请在WHERE中添加“边界框”。如果这还不够好，请参阅我的博客。 - Rick James

3个回答

2

试试这个：

SELECT zip, primary_city, latitude, longitude,
      111.045* DEGREES(ACOS(COS(RADIANS(latpoint))
                 * COS(RADIANS(latitude))
                 * COS(RADIANS(longpoint) - RADIANS(longitude))
                 + SIN(RADIANS(latpoint))
                 * SIN(RADIANS(latitude)))) AS distance_in_km
 FROM zip
 JOIN (
     SELECT  42.81  AS latpoint,  -70.81 AS longpoint
   ) AS p ON 1=1
 ORDER BY distance_in_km;

可以在此阅读有关此主题的更多信息： http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

- Petar Ivanov

1

下面的查询可以帮助找到两个（相邻的）最近位置。

在MySQL中设置变量，使用当前的纬度和经度值。

SET @lat=55.866495, @lng=8.168562;

执行查询

(SELECT tbl.cafe_id , tbl.gps_latitude, tbl.gps_longitude, 111.045 * DEGREES(ACOS(COS(RADIANS(@lat))
 * COS(RADIANS(gps_latitude))
 * COS(RADIANS(gps_longitude) - RADIANS(@lng))
 + SIN(RADIANS(@lat))
 * SIN(RADIANS(gps_latitude))))
 AS distance_in_km
FROM table_name as tbl ORDER BY distance_in_km ASC LIMIT 0,1)
UNION ALL
(SELECT tbl.cafe_id , tbl.gps_latitude, tbl.gps_longitude, 111.045 * DEGREES(ACOS(COS(RADIANS(@lat))
 * COS(RADIANS(gps_latitude))
 * COS(RADIANS(gps_longitude) - RADIANS(@lng))
 + SIN(RADIANS(@lat))
 * SIN(RADIANS(gps_latitude))))
 AS distance_in_km
FROM table_name as tbl ORDER BY distance_in_km DESC LIMIT 0,1);

- Haresh Vidja

嗯...看起来第一个 SELECT 找到了“最近的”，但第二个找到了“最远的”。 - Rick James

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- trincot · Accepted Answer

你可以使用球面余弦定理来得到以地球半径为单位表示的距离。

有些人更喜欢Haversine公式，因为它给出的精度更高，但是考虑到MySql的浮点精度已经足够高，两者之间的差异可以忽略不计。第一个公式实现起来更简单：

select     c1.cafe_id,
           substring_index (
             group_concat( c2.cafe_id order by 
               acos(  sin(radians(c1.gps_latitude)) * sin(radians(c2.gps_latitude)) 
                    + cos(radians(c1.gps_latitude)) * cos(radians(c2.gps_latitude)) 
                      * cos(radians(c2.gps_longitude-c1.gps_longitude)) ) ),
             ',', 1) nearest
from       cafe c1
inner join cafe c2 on c1.cafe_id <> c2.cafe_id
group by   c1.cafe_id

样例数据的输出结果为：

|  cafe_id |  nearest |
|----------|----------|
| 011-1003 | 037-0233 |
| 037-0233 | 011-1003 |
| 121-934  | 192-143  |
| 192-143  | 121-934  |

这里有一个MySql fiddle。

解释

距离计算用于group_concat聚合函数的order by子句中，其结果是按照到所分组咖啡店的距离排序的逗号分隔的cafe_id值列表。 substring_index函数从该列表中提取第一项。

联接条件很重要，因为如果没有它，您将得到咖啡店本身作为最近的邻居（它的距离显然为0）。

设置距离限制

在评论中，您要求只在特定半径内包括邻居的可能性。

在这种情况下，您可以输出转换为公里的“距离”（英里将是不同的因素）：

select     c1.cafe_id,
           substring_index (
             group_concat( c2.cafe_id order by 
               acos(  sin(radians(c1.gps_latitude)) * sin(radians(c2.gps_latitude)) 
                    + cos(radians(c1.gps_latitude)) * cos(radians(c2.gps_latitude)) 
                      * cos(radians(c2.gps_longitude-c1.gps_longitude)) ) ),
             ',', 1) nearest,
           min(
               acos(  sin(radians(c1.gps_latitude)) * sin(radians(c2.gps_latitude)) 
                    + cos(radians(c1.gps_latitude)) * cos(radians(c2.gps_latitude)) 
                      * cos(radians(c2.gps_longitude-c1.gps_longitude)) ) )
             * 6371 km
from       cafe c1
inner join cafe c2 on c1.cafe_id <> c2.cafe_id
group by   c1.cafe_id

现在，您可以根据距离决定是否要忽略邻居。如果您真的想排除最近邻居太远的咖啡馆，则在末尾添加一个having子句：

having     km < 5

如果您更喜欢使用英里作为单位，那么在SQL中使用3959作为乘法因子，而不是6371。