MySQL - 选择彼此距离接近的记录

Question

我有一个包含位置信息的MySQL表，例如：

ID  latitude  longitude  value
1   11.11111  22.22222   1
2   33.33333  44.44444   2
3   11.11112  22.22223   5

我想选择彼此靠近的记录（例如，在上面的示例中，第1行和第3行），以便我可以将它们作为一条记录插入到新表中。所谓的“靠近”，假设是100米。我希望新表格如下：

ID  latitude  longitude  value   
1   11.11111  22.22222   3
2   33.33333  44.44444   2

如您所见，我希望保留第一条记录的坐标，并在“value”列中插入两条记录的平均值。

我用于距离计算的公式如下：

R*SQRT(((lon2*c-lon1*c)*cos(0.5*(lat2*c+lat1*c)))^2 + (lat2*c-lat1*c)^2)

在哪里

[lat1, lon1] the coordinates of the first location,
[lat2, lon2] the coordinates of the second location,
[R] the average radius of Earth in meters,
[c] a number to convert degrees to radians.

这个公式对于短距离计算效果很好。

我的问题不在于将经纬度转换为距离，而在于我的SQL语句。我知道如何选择与特定经纬度坐标的最大距离为100米的记录，但我不知道如何选择彼此之间最大距离的记录。

我采用的一种方法是——它有效——通过PHP逐个循环记录，并为每个记录制作一个SQL查询以选择附近的记录。但是这样做，我在循环中执行了一次SQL查询，据我所知，这是一种不好的做法，尤其是如果记录数将达到数千条时。

我希望我表述清楚了。如果没有，我很乐意提供更多信息。

谢谢你的帮助。

- treecon

---

你能制作 SQL 字段吗？ - Hacker

Answer 1

以下是获取在范围内的所有地点的SQL语句：

SELECT 
    ID,
    latitude,
    longitude,
    (6371 * acos (cos( radians(origin.latitude)) * cos( radians( destination.latitude )) 
        * cos( radians(destination.longitude) - radians(origin.longitude)) + sin(radians(origin.latitude)) 
        * sin( radians(destination.latitude)))) AS distance
FROM myTable as destination, myTable as origin
WHERE destination.id = myId
HAVING distance < 100 --some value in kilometers

6371是千米的常量。 3959是英里的常量。

此主题有更多答案：MySQL大圆距离（Haversine公式）

- Felippe Duarte