Эффективное нахождение расстояния между 2 латами / лонгами в SQL

Question

Я работаю с миллиардами строк данных, и каждая строка имеет начальную широту / долготу и конечную широту / долготу. Мне нужно рассчитать расстояние между каждой начальной / конечной точкой - но это занимает очень много времени. Мне действительно нужно сделать то, что я делаю, более эффективным.

В настоящее время я использую функцию (ниже) для вычисления гипотенузы между точками. Есть ли способ сделать это более эффективным? Я должен сказать, что я уже пробовал приводить широту / долготу в качестве пространственной географии и использовать встроенные в SQL функции STDistance() (не проиндексированные), но это было еще медленнее.

Любая помощь приветствуется. Я надеюсь, что есть какой-то способ ускорить функцию, даже если она немного ухудшает точность (вероятно, с точностью до 100 м). Заранее спасибо!

DECLARE @l_distance_m float
, @l_long_start FLOAT
, @l_long_end FLOAT
, @l_lat_start FLOAT
, @l_lat_end FLOAT
, @l_x_diff FLOAT
, @l_y_diff FLOAT

SET @l_lat_start = @lat_start 
SET @l_long_start = @long_start
SET @l_lat_end = @lat_end
SET @l_long_end = @long_end 
-- NOTE 2 x PI() x (radius of earth) / 360 = 111 
SET @l_y_diff = 111 * (@l_lat_end - @l_lat_start)
SET @l_x_diff = 111 * (@l_long_end - @l_long_start) * COS(RADIANS((@l_lat_end + @l_lat_start) / 2))
SET @l_distance_m = 1000 * SQRT(@l_x_diff * @l_x_diff + @l_y_diff * @l_y_diff)
RETURN @l_distance_m

Answer 1

Я не занимался программированием на SQL с 1994 года, однако сделаю следующие наблюдения:

1. Используемая вами формула - это формула, которая работает до тех пор, пока расстояния между вашими координатами не становятся слишком большими. Это будет иметь большие ошибки при расчете расстояния между Нью-Йорком и Сингапуром, но при расчете расстояния между Нью-Йорком и Бостоном должно быть в пределах 100 метров.
2. Я не думаю, что есть какая-либо формула аппроксимации, которая была бы быстрее, однако я вижу некоторые незначительные улучшения реализации, которые могут ускорить ее, такие как (1), почему вы удосуживаетесь назначить @l_lat_start из @lat_start, вы не можете просто использовать @lat_start напрямую (и то же самое для @long_start, @lat_end, @long_end), (2) Вместо того, чтобы иметь 111 в формулах для @l_y_diff и @l_x_diff, вы могли бы избавиться от него, следовательно, сохранив умножение, и вместо 1000 в формуле для @l_distance_m вы могли бы иметь 111000, (3) используя COS(RADIANS(@l_lat_end)) или COS(RADIANS(@l_lat_start)), не снизив точность, пока точки не слишком далеко, или если все точки находятся в одном городе, вы можете просто определить косинус любой точки в городе

Кроме того, я думаю, что вам нужно рассмотреть другие идеи, такие как создание таблицы с результатами и всякий раз, когда точки добавляются / удаляются из таблицы, обновляя таблицу результатов в это время.