ДИАГНОСТИЧЕСКИЙ ПРИСОЕДИНЕНИЕ задано Широта / Долгота

Question

ДИАГНОСТИЧЕСКИЙ ПРИСОЕДИНЕНИЕ задано Широта / Долгота

Учитывая следующие таблицы:

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

Как построить эффективный запрос T-SQL, который связывает каждую строку в A с ближайшей строкой в B?

ResultSet должен содержать все строки в A и связывать их с 1 и только 1 элементом в B. Формат, который я ищу, следующий:

(A.id, B.id, distanceAB)

У меня есть функция, которая рассчитывает расстояние с учетом 2 пар широты и долготы. Я пробовал что-то используя order by ... limit 1 и / или rank() over (partition by ...) as rowCount ... where rowCount = 1 но результат либо не совсем то, что мне нужно, либо возвращение занимает слишком много времени.

Я что-то пропустил?

6

sql t-sql join distance haversine

Источник

user807231 20 янв '12 в 21:20

3 ответа

Другие вопросы по тегам sql t-sql join distance haversine

user1121833 21 янв '12 в 02:14 2012-01-21 02:14 · Answer 1 · 2012-01-21 02:14

Невозможно обойти тот факт, что вам придется сравнивать каждую запись в 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

Это вернется через разумное количество времени, если ваши наборы не слишком велики. С 3 местоположениями в A и 130 000 или около того в B, это займет около одной секунды на моей машине. 1000 записей в каждой занимает около 40 с. Как я уже сказал, он плохо масштабируется.

Следует отметить, что ответ Спарки может давать неверные результаты при определенных обстоятельствах. Предположим, ваше местоположение А находится на +40,+100. +40,+111 не будут возвращены, даже если это ближе, чем +49,+109.

user210908 20 янв '12 в 23:23 2012-01-20 23:23 · Answer 2 · 2012-01-20 23:23

Это один из подходов, который должен иметь низкую производительность, но большое предостережение заключается в том, что он может не найти никаких результатов.

    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

Что вы в основном делаете, так это ищите любую строку B в радиусе примерно 20 единиц от A, а затем сортируете ее по вашей функции, чтобы определить ближайшую. Вы можете настроить радиус устройства по мере необходимости. Хотя он не является точным, он должен уменьшить размер набора результатов и дать вам достойные результаты производительности.

user1153919 20 янв '12 в 23:43 2012-01-20 23:43 · Answer 3 · 2012-01-20 23:43

Это возможно с объединением двух подзапросов. Первый содержит все расстояния между местоположениями A и B, второй содержит только минимальное расстояние местоположений B от местоположений A.

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