mysql - SQL:根据纬度/经度查找最近的邻居

Question

我有一张包含咖啡馆纬度/经度的表格。我想做一个 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 |
+-------------------------+----------------------+----------------------+

非常感谢任何帮助!

Answer 1

您可以使用 Spherical Law of Cosines得到用 earth-radii 表示的距离.

有些人更喜欢 Haversine formula因为它给出了更好的精度，但考虑到 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。