基于距离的JOIN，给定纬度/经度

Question

给定以下表格：

table A (id, latitude, longitude)
table B (id, latitude, longitude)

如何构建一个高效的T-SQL查询，将A中的每一行与B中最接近的一行关联起来？

结果集应该包含A中的所有行，并将它们与B中的1个且仅有1个元素关联起来。我要求的格式如下：

(A.id, B.id, distanceAB)

我有一个函数，可以计算给定两个纬度和经度对之间的距离。我尝试使用order by ... limit 1和/或rank() over (partition by ...) as rowCount ... where rowCount = 1，但结果要么不是我需要的，要么返回时间太长。

我是否遗漏了什么？

- Marsellus Wallace

Answer 1

无论如何，您都需要将A中的每个记录与B中的每个记录进行比较，如果A和B都包含大量记录，则这显然会扩展得很糟糕。

话虽如此，以下方法将返回正确的结果：

SELECT aid, bid, distanceAB
FROM (
  SELECT aid, bid, distanceAB,
    dense_rank() over (partition by aid order by distanceAB) as n
  FROM (
    SELECT a.id as aid, B.id as bid,
      acos(sin(radians(A.lat)) * sin(radians(B.lat)) +
        cos(radians(A.lat)) * cos(radians(B.lat)) *
        cos(radians(A.lon - B.lon))) * 6372.8 as distanceAB
    FROM A cross join B
  ) C
) D
WHERE n = 1

如果您的数据集不太大，这将在合理的时间内返回。对于A中的3个位置和B中的约130,000个位置，在我的机器上大约需要1秒钟。每个1000条记录大约需要40秒钟。正如我所说，它的可扩展性很差。

值得注意的是，在某些情况下，Sparky的答案可能会返回不正确的结果。假设您的A位置位于+40，+100。尽管+40，+111比+49，+109更接近，但不会被返回。

- Chad

Answer 2

这是一种应该具有不错性能的方法，但需要注意的一个大问题是它可能找不到任何结果。

    select top 1 a.id,b.id,dbo.yourFunction() as DistanceAB
    from a 
    join b on b.latitude between a.latitude-10 and a.latitude+10 and
              b.longititude between a.longitude-10 and b.longittude+10
    order by 3

你所做的基本上是在 A 周围大约 20 个单位半径内寻找任何 B 行，然后按照你的函数进行排序以确定最近的行。你可以根据需要调整单位半径。虽然不是完全精确的，但它应该能减少结果集的大小，并且应该能给出不错的性能结果。

- Sparky

Answer 3

通过两个子查询的联接是可能的。第一个包含A和B位置之间的所有距离，第二个仅包含从A位置到B位置的最小距离。

SELECT x.aid, x.bid, x.distance
FROM
(SELECT A.ID AS aid, 
        B.ID AS bid, 
        SQRT(A.Latitude * A.Latitude + B.Longitude * B.Longitude) AS Distance
     FROM LocationsA AS A 
     CROSS JOIN LocationsB AS B) x JOIN
(SELECT A.ID AS aid, 
        MIN(SQRT(A.Latitude * A.Latitude + B.Longitude * B.Longitude)) AS Distance
     FROM LocationsA AS A 
     CROSS JOIN LocationsB AS B
     GROUP BY A.ID) y ON x.aid = y.aid AND x.Distance = y.Distance

- tpolyak