T-SQL i Spatial – Obliczanie dystansu

W ostatnim czasie zająłem się projektem polegającym na pobieraniu lokalizacji geograficznej z czujników umieszczonych w różnych „urządzeniach mobilnych”, będę przedstawiał tutaj co ciekawsze jego elementy.
Na rozgrzewkę – obliczanie dystansu pomiędzy dwoma punktami za pomocą Funkcji geograficznych MS SQL Servera.

Wymagania:

  • Zapisujemy w bazie długość i szerokość geograficzną (LAT, LNG)
  • Podczas zapisu następuje konwersja LAT i LNG do obiektu SqlGeography
  • Obliczamy odległość pomiędzy dwoma sąsiadującymi punktami
  • Obliczamy kumulatywną (narastającą) odległość pokonają przez obiekt w danym okresie czasu (z pominięciem niedokładnych odczytów GPS).

Realizacja:

  1. TRIGER do konwersji zwyczajnych liczb reprezentujących długość i szerokość geograficzną na typ Geography:
    CREATE TRIGGER autoConvTrg ON [gps].[Reads]
    AFTER INSERT
    AS
    UPDATE gps.Reads SET Geo=geography::Point(inserted.Lat, inserted.Lng, 4326)
    FROM INSERTED
    WHERE inserted.Id=gps.Reads.Id

    Powyższy kod załatwia nam konwersję podczas wstawiania rekordów (jeśli potrzebujemy robić to także podczas aktualizacji (update), dokonujemy stosownej modyfikacji). Układ 4326 to WGS84 , czyli popularny GPS.

  2. Obliczanie odległości pomiędzy dwoma sąsiadującymi punktami.
SELECT *,
(LAG(p.Geo) OVER(ORDER BY p.Id)).STDistance(p.Geo)/1000 Distance
FROM [gps].[Reads]
WHERE DateTime BETWEEN @StartDate AND @EndDate AND SensorId=@SensorId

Co tutaj się dzieje? Otóż  korzystamy za bardzo pomocnej funkcji SQLa (od wersji 2014): LAG, która pozwala nam bez zbędnych kombinacji dostać się do poprzedniego rekordu na podstawie zadanego kryterium uszeregowania (tutaj sortujemy rekordy po Id – Identity w tabeli). Przeciwną funkcją jest LEAD, która znajduje kolejny rekord.
Skoro mamy już poprzednie rekord to korzystamy z funkcji STDistance,  która oblicza dystans; wywołujemy ją na rzecz danego rekordu, jako parametr podając kolejny odczyt.
Powyższe do każdego rekordu doda nam dynamicznie wyliczony atrybut z odległością od poprzedniego punktu. Dystans podawany jest w metrach, dlatego dzielimy przez 1000 aby otrzymać wynik w kilometrach.
3. Ostatni krok to obliczenie sumy narastająco. Przy każdym rekordzie chcę mieć informacje jaki dystans przebył śledzony obiekt do tej pory. Innymi słowy ma to być suma wszystkich „dystansów” poniżej danego rekordu.

SELECT SUM(Distance) over(order by Id rows unbounded preceding) as CumulativeDist,
*
FROM
(
   SELECT *,
   (LAG(p.Geo) OVER(ORDER BY p.Id)).STDistance(p.Geo)/1000   Distance
   FROM [gps].[Reads] p
   WHERE DateTime BETWEEN @StartDate AND @EndDate AND   SensorId=@SensorId
   ) x
WHERE Distance<50 --integrity check: Dist can't be more than 50km

W efekcie uzyskamy bardzo przystępną tabelę zawierającą kumulatywnie policzony dystans. Celem odfiltrowania rażąco niedokładnych lokalizacji ograniczam analizowany zbiór tylko do punktów dla, których odległość pomiędzy dwoma kolejnymi odczytami jest mniejsza niż 50km.
Skorzystaliśmy tutaj z kolejnej, pomocnej nowości w SQL 2014 – rozszerzonej klauzuli OVER, którą można teraz stosować także do funkcji SUM(!).
W kolejnych wpisach przybliżę dalsze składowe projektu, m.in. GUI (ASP MVC+VUE), Backend (.NET + IoC + kilka ciekawostek) oraz obsługę chińskich odbiorników GPS.

Leave a Reply