Analiza trasowania przestrzennego z pgRouting w ServBay

pgRouting to potężny moduł rozszerzający bazy danych PostgreSQL i PostGIS, oferujący bogaty zestaw funkcji do analizy trasowania i sieci przestrzennych. Dzięki pgRouting deweloperzy mogą wykonywać zaawansowane zapytania na danych sieciowych, takie jak wyznaczanie najkrótszej ścieżki między punktami, rozwiązywanie problemu komiwojażera (TSP) czy analizę obszarów dostępnych w zadanym czasie lub odległości. Tego typu funkcjonalności są niezwykle przydatne przy budowie aplikacji mapowych, planowaniu logistyki czy analizie ruchu i transportu.

W tym artykule omawiamy krok po kroku, jak łatwo włączyć rozszerzenie pgRouting dla bazy PostgreSQL w lokalnym środowisku webowym ServBay, przeprowadzić podstawową konfigurację i skorzystać z analiz trasowania.

Przegląd

ServBay to wygodne, kompleksowe lokalne środowisko programistyczne na macOS, które integruje różnorodne serwery www, bazy danych i języki programowania. Pakiet PostgreSQL dostarczany z ServBay posiada już preinstalowane rozszerzenia pgRouting i PostGIS. Oznacza to, że nie trzeba ich ręcznie pobierać i kompilować – wystarczy aktywować rozszerzenie w wybranej bazie.

Wymagania wstępne

Przed rozpoczęciem korzystania z pgRouting, upewnij się, że spełniasz poniższe warunki:

1. ServBay jest zainstalowany i uruchomiony: Jeśli jeszcze tego nie zrobiłeś, pobierz i zainstaluj najnowszą wersję z oficjalnej strony ServBay.
2. Pakiet PostgreSQL w ServBay jest zainstalowany i uruchomiony: Sprawdź w interfejsie ServBay, czy pakiet PostgreSQL jest aktywny i wyświetla się jako „running”.
3. Podstawowa znajomość SQL i PostgreSQL: Ten przewodnik zakłada, że rozumiesz podstawy pracy z bazą PostgreSQL i językiem SQL.
4. Znajomość PostGIS: pgRouting opiera swoje działanie na rozszerzeniu PostGIS do obsługi typów i funkcji przestrzennych. Upewnij się, że rozszerzenie PostGIS jest aktywne w Twojej bazie. Pakiet PostgreSQL w ServBay domyślnie je zawiera.

Instalacja i aktywacja rozszerzenia pgRouting

ServBay dostarcza wymagane pliki rozszerzenia pgRouting – jedyne, co musisz zrobić, to aktywować je w wybranej bazie danych.

1. Połącz się z bazą PostgreSQL:

   Istnieje wiele sposobów połączenia z bazą PostgreSQL, np. przez narzędzie wiersza poleceń psql, graficzne aplikacje (jak pgAdmin, DBeaver), czy biblioteki w wybranym języku programowania.

   Popularną, bezpośrednią metodą jest użycie narzędzia psql, które ServBay udostępnia wraz z prawidłową konfiguracją zmiennych środowiskowych (Terminal z poziomu aplikacji ServBay) lub po ręcznym dodaniu katalogu bin do systemowej ścieżki.

   W terminalu połącz się ze swoją bazą (np. servbay_geo_db, użytkownik servbay) poleceniem:

   psql -U servbay -d servbay_geo_db

   Jeśli korzystasz z innych nazw bazy, użytkownika czy hasła – dostosuj komendę do swojej konfiguracji.

2. Sprawdź i aktywuj rozszerzenie PostGIS (jeśli nie jest jeszcze aktywne):

   pgRouting wymaga działania PostGIS. Przed aktywacją pgRouting upewnij się, że PostGIS jest dostępny – wpisz w psql:

   CREATE EXTENSION IF NOT EXISTS postgis;

   Polecenie utworzy, bądź pominie tworzenie rozszerzenia, jeśli już istnieje.

3. Aktywuj pgRouting:

   W tej samej sesji wykonaj SQL:

   CREATE EXTENSION pgrouting;

   Po sukcesie powinien pojawić się komunikat w stylu CREATE EXTENSION.

4. Weryfikacja instalacji:

   Aby potwierdzić aktywację rozszerzenia, wyświetl listę zainstalowanych rozszerzeń:

   \dx

   Wśród pozycji na liście zobaczysz postgis i pgrouting wraz ze szczegółami.

Konfiguracja pgRouting: Przygotowanie danych sieciowych i tworzenie topologii

Algorytmy pgRouting działają na tabelach reprezentujących strukturę sieci – najczęściej są to odcinki dróg (linie), każdy z określonym początkiem, końcem i wagą (np. dystans, czas). Dla efektywnych obliczeń trasowania konieczna jest budowa „topologii”, opisującej powiązania (węzły) między odcinkami.

Tworzenie tabeli sieci drogowej

Poniżej przykład prostej tabeli ways służącej do przechowywania sieci drogowej: każdy odcinek ma unikalne ID, węzły początkowy i końcowy, koszt przejazdu w obu kierunkach oraz geometrię linii w formacie PostGIS.

-- Tworzy tabelę do przechowywania danych sieci drogowej
CREATE TABLE ways (
    id SERIAL PRIMARY KEY, -- Unikalny identyfikator odcinka
    source INTEGER,        -- ID węzła początkowego
    target INTEGER,        -- ID węzła końcowego
    cost DOUBLE PRECISION, -- Koszt pokonania odcinka (np. dystans, czas)
    reverse_cost DOUBLE PRECISION, -- Koszt przejazdu w przeciwnym kierunku
    geom GEOMETRY(LineString, 4326) -- Geometria odcinka jako LineString w układzie WGS 84 (SRID 4326)
);

Wyjaśnienie:

• SERIAL PRIMARY KEY: Automatyczne generowanie unikalnego ID dla odcinka.
• source, target: ID węzłów początek/koniec odcinka. Wartości są generowane/uzupełniane podczas tworzenia topologii.
• cost, reverse_cost: Wagi przejazdu. Jeśli droga jest jednokierunkowa, reverse_cost powinien być równy NULL lub bardzo dużej wartości.
• geom: Kolumna z geometrią linii wg standardu PostGIS. Typ LineString, układ współrzędnych 4326 (GPS).

Wstawianie przykładowych danych

Do tabeli ways można wstawić przykładowe odcinki:

-- Wstawianie przykładowych odcinków sieci
INSERT INTO ways (source, target, cost, reverse_cost, geom) VALUES
(1, 2, 1.0, 1.0, ST_SetSRID(ST_MakeLine(ST_MakePoint(116.4074, 39.9042), ST_MakePoint(116.4084, 39.9052)), 4326)),
(2, 3, 1.0, 1.0, ST_SetSRID(ST_MakeLine(ST_MakePoint(116.4084, 39.9052), ST_MakePoint(116.4094, 39.9062)), 4326)),
(3, 4, 1.0, 1.0, ST_SetSRID(ST_MakeLine(ST_MakePoint(116.4094, 39.9062), ST_MakePoint(116.4104, 39.9072)), 4326));

Wyjaśnienie:

• ST_MakePoint(x, y): Tworzy punkt o zadanych współrzędnych geograficznych.
• ST_MakeLine(point1, point2): Tworzy linię łączącą dwa punkty.
• ST_SetSRID(geometry, srid): Ustawia identyfikator układu współrzędnych dla geometrii.
• Przykład tworzy trzy odcinki w przykładowym fragmencie Pekinu, każdy o koszcie 1.0.

Tworzenie topologii

Po wstawieniu danych należy wygenerować topologię funkcją pgr_createTopology. Bada ona geometrie odcinków, identyfikuje węzły (punkty przecięć) oraz uzupełnia kolumny source i target w tabeli – dodatkowo tworzy tabelę węzłów ([nazwa_tabeli]_vertices_pgr).

-- Tworzy topologię sieci drogowej
-- Parametry:
-- 'ways': Nazwa tabeli z siecią
-- 0.00001: Tolerancja (jak blisko muszą być punkty, by uznać je za ten sam węzeł)
-- 'geom': Kolumna z geometrią
-- 'id': Kolumna z ID odcinka
SELECT pgr_createTopology('ways', 0.00001, 'geom', 'id');

Wyjaśnienie:

• pgr_createTopology: Kluczowa funkcja pgRouting budująca model sieci.
• Tolerancja określa maksymalny dystans, w jakim dwa węzły będą traktowane jako ten sam. Dostosuj do dokładności swoich danych.

W wyniku działania funkcji kolumny source i target w tabeli ways zostaną uzupełnione, a powstanie tabela ways_vertices_pgr z identyfikatorami i współrzędnymi wszystkich węzłów.

Analizy trasowania z pgRouting

Po utworzeniu topologii można korzystać ze standardowych algorytmów trasowania oferowanych przez pgRouting. Oto najpopularniejsze przykłady:

Analiza najkrótszej ścieżki (Dijkstra)

Najczęstsze zadanie trasowania to wyznaczenie najkrótszej drogi pomiędzy dwoma węzłami. pgRouting udostępnia algorytm Dijkstry.

-- Zapytanie o najkrótszą ścieżkę od węzła 1 do 4
-- Parametry:
-- 'SELECT id, source, target, cost FROM ways': Zapytanie opisujące sieć (musi zwracać kolumny id, source, target i cost)
-- 1: ID węzła początkowego
-- 4: ID węzła końcowego
-- directed := true: Czy graf sieci jest skierowany?
SELECT seq, id1 AS node, id2 AS edge, cost, geom
FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM ways',
    1, -- Węzeł początkowy
    4, -- Węzeł końcowy
    directed := true
)
JOIN ways ON edge = ways.id; -- Połączenie wyników z oryginalną tabelą w celu uzyskania geometrii

Wyjaśnienie:

• pgr_dijkstra: Wykonuje algorytm wyznaczania najkrótszej ścieżki.
• Pierwszy parametr to zapytanie SQL ze strukturą sieci.
• Kolejne – ID początkowego i końcowego węzła.
• Połączenie wyników z tabelą ways pozwala pobrać geometrię potrzebną do wizualizacji.

Problem komiwojażera (Traveling Salesperson Problem – TSP)

Algorytm TSP pozwala wyznaczyć najkrótszą trasę odwiedzającą wskazaną listę węzłów, wracając do punktu startowego.

-- Rozwiązanie TSP dla węzłów 1, 2, 3, 4, start z węzła 1
-- Parametry:
-- 'SELECT id, x::float8 AS x, y::float8 AS y FROM ways_vertices_pgr': Zapytanie zwracające ID i współrzędne węzłów
-- start_id := 1: Punkt startowy
SELECT seq, node, edge, cost
FROM pgr_tsp(
    'SELECT id, ST_X(the_geom)::float8 AS x, ST_Y(the_geom)::float8 AS y FROM ways_vertices_pgr WHERE id IN (1, 2, 3, 4)',
    start_id := 1
);

Wyjaśnienie:

• pgr_tsp: Rozwiązuje problem komiwojażera.
• W pierwszym parametrze podajemy węzły oraz ich współrzędne (ID, x, y).
• start_id określa punkt początkowy podróży.

Analiza obszaru obsługi (Driving Distance / Driving Time)

Analiza obszaru obsługi pokazuje, które fragmenty sieci (odcinki) można osiągnąć z danego węzła przy założonym limicie kosztu (np. dystansu lub czasu).

-- Znajdź wszystkie odcinki osiągalne w koszcie <= 2 z węzła 1
-- Parametry:
-- 'SELECT id, source, target, cost FROM ways': Zapytanie opisujące sieć
-- 1: Węzeł początkowy
-- 2: Maksymalny koszt
-- directed := true: Czy graf jest skierowany?
SELECT seq, id1 AS node, id2 AS edge, cost, geom
FROM pgr_drivingDistance(
    'SELECT id, source, target, cost FROM ways',
    1, -- Węzeł początkowy
    2, -- Maksymalny koszt
    directed := true
)
JOIN ways ON edge = ways.id;

Wyjaśnienie:

• pgr_drivingDistance: Wyznacza obszar obsługi dla wybranego węzła.
• Podobnie jak w innych funkcjach, można zestawić wyniki z tabelą ways, by uzyskać geometrię do wyświetlania.

Wizualizacja wyników trasowania

Wizualizacja wyników pgRouting ułatwia interpretację i prezentację tras. Do tego celu możesz skorzystać z narzędzi GIS na komputerze lub osadzić interaktywne mapy w aplikacjach webowych.

Użycie desktopowego oprogramowania GIS (np. QGIS)

QGIS to darmowa i otwartoźródłowa aplikacja GIS, którą można bezpośrednio połączyć z bazą PostgreSQL/PostGIS i ładować na mapę tabele z wynikami analizy trasowania.

1. Uruchom QGIS.
2. Wybierz Warstwa (Layer) > Menedżer źródeł danych (Data Source Manager).
3. W menu po lewej wybierz PostGIS.
4. Kliknij Nowy (New), by dodać połączenie do bazy.
5. Wprowadź dane połączenia (np. Host: localhost, Port: 5432, Database: servbay_geo_db, User: servbay, Password: Twoje hasło do PostgreSQL) i przetestuj połączenie.
6. Po udanym połączeniu zobaczysz w bazie swoją strukturę, m.in. tabelę ways i ways_vertices_pgr.
7. Zaznacz tabele/widoki do załadowania (np. z wynikami trasy) i kliknij Dodaj (Add), by wyświetlić je na mapie.

Wizualizacja w aplikacjach WWW (Leaflet lub OpenLayers)

W aplikacjach webowych potrzebny jest backend (np. PHP, Node.js, Python uruchamiany w ServBay) wykonujący zapytania pgRouting i zwracający wyniki w formacie GeoJSON dla frontendu. Frontend, używając bibliotek takich jak Leaflet lub OpenLayers, wyświetla te dane na mapie.

Poniżej przykład prostej strony HTML, która wczytuje statyczny obiekt GeoJSON. W aplikacji dynamicznej:

1. Backend wykonuje zapytania pgRouting.
2. Wyniki zamienia na format GeoJSON.
3. Frontend pobiera GeoJSON przez API.
4. Frontend ładuje i wyświetla dane używając np. metody L.geoJSON z Leaflet.

<!DOCTYPE html>
<html>
<head>
    <title>Przykład wizualizacji pgRouting w ServBay</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="https://unpkg.com/leaflet/dist/leaflet.css" />
    <script src="https://unpkg.com/leaflet/dist/leaflet.js"></script>
    <style>
        #map { height: 600px; width: 100%; } /* Ustaw rozmiar mapy */
    </style>
</head>
<body>
    <h1>Wizualizacja wyników pgRouting z ServBay</h1>
    <div id="map"></div>

    <script>
        // Inicjalizacja mapy, środek i poziom powiększenia
        var map = L.map('map').setView([39.906, 116.409], 14); // Centrum przykładowych danych

        // Dodaj podkład mapowy OpenStreetMap
        L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
            attribution: '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
        }).addTo(map);

        // Przykład: Dodanie GeoJSON z backendu
        // W docelowej aplikacji geojsonData pobierz przez AJAX z backendu
        var geojsonData = {
            "type": "FeatureCollection",
            "features": [
                {
                    "type": "Feature",
                    "properties": {
                        "id": 1,
                        "cost": 1.0
                    },
                    "geometry": {
                        "type": "LineString",
                        "coordinates": [
                            [116.4074, 39.9042],
                            [116.4084, 39.9052]
                        ]
                    }
                },
                {
                    "type": "Feature",
                    "properties": {
                        "id": 2,
                        "cost": 1.0
                    },
                    "geometry": {
                        "type": "LineString",
                        "coordinates": [
                            [116.4084, 39.9052],
                            [116.4094, 39.9062]
                        ]
                    }
                },
                {
                    "type": "Feature",
                    "properties": {
                        "id": 3,
                        "cost": 1.0
                    },
                    "geometry": {
                        "type": "LineString",
                        "coordinates": [
                            [116.4094, 39.9062],
                            [116.4104, 39.9072]
                        ]
                    }
                }
            ]
        };

        // Dodaj dane GeoJSON do mapy
        L.geoJSON(geojsonData, {
            style: function (feature) {
                return {color: "#ff0000", weight: 4}; // Czerwona, gruba linia trasy
            }
        }).addTo(map);

        // Dopasuj widok mapy do wszystkich elementów
        if (L.geoJSON(geojsonData).getBounds().isValid()) {
             map.fitBounds(L.geoJSON(geojsonData).getBounds());
        }
    </script>
</body>
</html>

Zapisz powyższy plik HTML w katalogu strony w ServBay (np. /Applications/ServBay/www/pgrouting-demo/index.html) i otwórz w przeglądarce przez lokalny adres (np. http://pgrouting-demo.servbay.demo). Przykład prezentuje statyczną trasę – dynamiczne wyświetlanie tras wymaga integracji z backendem i przekazywania danych z PostgreSQL.

Wskazówki i dobre praktyki

• Jakość danych: Wyniki analiz pgRouting są ściśle zależne od jakości sieci wejściowej. Upewnij się, że dane są kompletne, poprawne i mają prawidłową topologię.
• Wydajność: Dla dużych sieci obliczenia trasowania mogą być czasochłonne. Warto zadbać o indeksy oraz stosować uproszczone wersje sieci do analizy.
• Pamięć: Duża liczba węzłów może generować spore zapotrzebowanie na RAM serwera PostgreSQL.

Najczęściej zadawane pytania (FAQ)

Q: Otrzymuję błąd „rozszerzenie nie istnieje” przy CREATE EXTENSION pgrouting;. Co robić?
A: Upewnij się, że pakiet PostgreSQL jest zainstalowany i aktywny w ServBay oraz że wersja bazy obsługuje pgRouting (powinna być domyślnie dostępna w ServBay). Jeśli błąd występuje nadal, sprawdź logi ServBay/PostgreSQL, by poznać szczegóły, i potwierdź, że masz odpowiednie uprawnienia (np. łączysz się jako użytkownik servbay).

Q: Jak dobrać wartość tolerancji tolerance w funkcji pgr_createTopology?
A: Dopasuj wartość do dokładności danych geograficznych. Tolerancja definiuje dystans, w jakim dwa wierzchołki uznawane są za ten sam węzeł. Dla dokładnych danych GPS stosuj wartości rzędu 0.000001 lub mniejsze. Im mniej dokładne dane, tym tolerancja powinna być większa – aby uniknąć sztucznych przerw w sieci. Zbyt wysoka tolerancja grozi łączeniem niepowiązanych odcinków.

Q: Jak obsłużyć drogi jednokierunkowe lub zakazy zawracania?
A: Za pomocą kolumn cost i reverse_cost w tabeli ways. Jeśli odcinek jest jednokierunkowy, ustaw reverse_cost na NULL lub bardzo wysoką wartość (symbolizuje brak przejazdu). Zakazy zawracania to temat bardziej złożony – mogą wymagać rozszerzonego modelu lub przetwarzania wyników. Zaawansowane funkcje pgRouting także pozwalają to obsłużyć.

Podsumowanie

Dzięki ServBay postawienie lokalnego środowiska PostgreSQL z włączonym pgRouting staje się proste i szybkie. Wystarczy w kilku krokach aktywować rozszerzenia SQL, przygotować dane sieci i utworzyć topologię, by korzystać z zaawansowanych analiz trasowania. Połącz wyniki z narzędziami GIS lub bibliotekami webowymi, a otrzymasz atrakcyjne wizualizacje i silne wsparcie analityczne dla projektu geoinformatycznego czy aplikacji webowej. ServBay minimalizuje bariery konfiguracyjne, pozwalając skupić się na funkcjach i kodzie Twojego systemu!