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:

    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.

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.

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.

Dodaj komentarz

This site uses Akismet to reduce spam. Learn how your comment data is processed.