Расчёт расстояния между точками по координатам в SQL

Быстрый ответ

Используйте следующий SQL-запрос для расчёта расстояния между двумя точками на поверхности Земли с применением формулы гаверсинуса:

SELECT (6371 * ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * 
       COS(RADIANS(lon2) – RADIANS(lon1)) + 
       SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)))) AS distance_in_km

Значения lat1, lat2, lon1, lon2 необходимо заменить на желаемые географические координаты. Если результаты хочется увидеть в милях, используйте коэффициент 3959 вместо 6371.

Применение формулы гаверсинуса на практике

Разберем, как вычислить расстояние между Лондоном (51.5074, -0.1278) и Эдинбургом (55.9533, -3.1883), используя формулу гаверсинуса:

SELECT (6371 * ACOS(COS(RADIANS(51.5074)) * COS(RADIANS(55.9533)) * 
       COS(RADIANS(-3.1883) – RADIANS(-0.1278)) + 
       SIN(RADIANS(51.5074)) * SIN(RADIANS(55.9533)))) AS distance_in_km

Таким образом, мы получим расстояние около 534 км.

Открываем новые горизонты – использование типа данных geography

Для ещё большей точности можно использовать тип данных geography, доступный в SQL Server 2008, который позволяет легко измерять расстояния методом STDistance:

DECLARE @Point1 geography = geography::Point(lat1, lon1, 4326)
DECLARE @Point2 geography = geography::Point(lat2, lon2, 4326)

SELECT @Point1.STDistance(@Point2) as distance_in_meters

Снова заменим lat1, lon1, lat2, lon2 на выбранные координаты, чтобы получить расстояние в метрах.

Визуализация

Для наглядности представьте следующую картинку:

🌍 Расстояние между (LatA, LongA) и (LatB, LongB)

# При помощи следующей SQL-формулы мы можем вычислить это расстояние:
(
  acos(
    sin(LatA) * sin(LatB) + 
    cos(LatA) * cos(LatB) * 
    cos(LongB – LongA)
  ) * 6371 # Радиус Земли в км
)

Это можно представить как измерение расстояния на искривлённой поверхности Земли:

(LatA, LongA) 📍
    📏----------------------📏
                                📍 (LatB, LongB)

Таким образом, мы получаем расстояние в километрах.

Практические применения – универсальность единиц измерения и большие расстояния

Адаптация под свои нужды

Вы можете создать собственную функцию для переключения между различными единицами измерения, будь то мили, километры, световые годы и прочее.

Подробнее об этом расскажет наш спикер на видео

Прохождение больших расстояний

Тип данных geography позволяет учитывать кривизну Земли, что очень ценно при расчёте больших расстояний, например, между Лондоном и Эдинбургом — примерно 538 км.

Полезные материалы

1. Описание функции ST_Distance_Sphere в MySQL 8.0.
2. Введение в PostGIS: Geography.
3. Реализация формулы гаверсинуса в JavaScript.
4. Тип данных geography в SQL Server.
5. Расстояние по большому кругу в Википедии.
6. Модуль R*Tree в SQLite.
7. Функция ST_Distance в PostGIS.