Georäumliche Routing-Analysen mit pgRouting in ServBay

pgRouting ist ein leistungsstarkes Erweiterungsmodul für PostgreSQL und PostGIS-Datenbanken, das speziell für die Analyse und das Routing von Netzwerk- und Geodaten entwickelt wurde. Mit pgRouting können Entwickler komplexe Abfragen auf Netzwerkdaten durchführen – beispielsweise das Finden des kürzesten Wegs zwischen zwei Punkten, das Lösen des Traveling Salesman Problem (TSP) oder die Einzugsgebietsanalyse („Service Areas“). Diese Funktionen sind sehr hilfreich beim Bau von Kartenanwendungen, der Routen- und Logistikplanung oder der Verkehrsflussanalyse.

In diesem Beitrag zeigen wir, wie Sie in Ihrer lokalen ServBay-Webentwicklungsumgebung die pgRouting-Erweiterung für Ihre PostgreSQL-Datenbank aktivieren und grundlegende Konfigurationen sowie Routing-Analysen vornehmen.

Überblick

ServBay bietet Entwicklern auf macOS eine praktische lokale Entwicklungsumgebung mit vorintegrierten Webservern, Datenbanken und Programmiersprachen. Das mitgelieferte PostgreSQL-Paket enthält bereits die Erweiterungen pgRouting und PostGIS. Sie müssen also diese Erweiterungen nicht manuell herunterladen oder kompilieren – eine einfache Aktivierung über SQL genügt.

Voraussetzungen

Stellen Sie vor dem Einsatz von pgRouting sicher, dass folgende Voraussetzungen erfüllt sind:

1. ServBay ist installiert und wird ausgeführt: Falls Sie ServBay noch nicht installiert haben, besuchen Sie die offizielle Webseite von ServBay und laden Sie die aktuelle Version herunter.
2. PostgreSQL-Paket in ServBay ist installiert und gestartet: Prüfen Sie in der ServBay-Benutzeroberfläche, ob das PostgreSQL-Paket installiert und aktiv ist.
3. Grundkenntnisse in SQL und PostgreSQL: Dieser Leitfaden setzt ein Verständnis für grundlegende SQL- und PostgreSQL-Konzepte voraus.
4. Grundlagen von PostGIS: pgRouting setzt auf PostGIS zur Verarbeitung georäumlicher Datentypen und Funktionen. Stellen Sie sicher, dass die PostGIS-Erweiterung in Ihrer Datenbank aktiviert ist – das ServBay-PostgreSQL-Paket bringt PostGIS standardmäßig mit.

Installation und Aktivierung der pgRouting-Erweiterung

ServBay stellt Ihnen die erforderlichen Dateien für pgRouting bereit. Sie müssen die Erweiterung lediglich in der gewünschten Datenbank aktivieren.

1. Verbindung zur PostgreSQL-Datenbank herstellen:

   Sie können auf verschiedene Arten eine Verbindung zu Ihrer PostgreSQL-Datenbank aufbauen – z.B. via Kommandozeilen-Tool psql, mittels grafischer Tools (wie pgAdmin, DBeaver) oder über Bibliotheken in Ihrer Programmiersprache.

   Die Nutzung von psql, das ServBay mitliefert, ist einfach und effizient. Sie können über die Schaltfläche „Terminal“ in der ServBay-Oberfläche direkt ein Terminalfenster mit den nötigen Umgebungsvariablen öffnen oder das bin-Verzeichnis von ServBay manuell zum System-PATH hinzufügen.

   Im Terminal verbinden Sie sich zu Ihrer Zieldatenbank (zum Beispiel eine Datenbank namens servbay_geo_db mit dem Standardbenutzer servbay) so:

   psql -U servbay -d servbay_geo_db

   Anpassungen sind nötig, wenn Datenbankname, Nutzer oder Passwort abweichen.

2. PostGIS-Erweiterung prüfen und aktivieren (falls nicht bereits geschehen):

   Da pgRouting auf PostGIS basiert, stellen Sie sicher, dass PostGIS aktiviert ist. Im psql-Prompt führen Sie einfach aus:

   CREATE EXTENSION IF NOT EXISTS postgis;

   Dieser Befehl erstellt die PostGIS-Erweiterung oder überspringt sie, falls bereits vorhanden.

3. pgRouting-Erweiterung aktivieren:

   Im Anschluss daran aktivieren Sie pgRouting mit folgendem SQL-Befehl:

   CREATE EXTENSION pgrouting;

   Bei Erfolg erscheint eine Meldung wie CREATE EXTENSION.

4. Installation prüfen:

   Die aktivierten Erweiterungen listen Sie mit:

   \dx

   In der Liste sollten sowohl postgis als auch pgrouting samt zugehöriger Informationen erscheinen.

Grundkonfiguration: Netzwerkdaten und Topologie

pgRouting-Algorithmen benötigen eine Tabellenstruktur, die Ihr Netzwerk (z.B. Straßensegmentdaten) beschreibt. Dazu gehören u.a. Start- und Zielpunkte sowie ein Gewicht (z.B. Distanz oder Zeit). Für effiziente Berechnungen wird eine sogenannte „Topologie“ gebraucht, die die Knoten (Netzwerkpunkte) und deren Verknüpfungen bestimmt.

Erstellen einer Netzwerktabelle

Hier ein einfaches Beispiel, wie Sie eine Tabelle namens ways für Ihre Netzwerkdaten anlegen. Die Tabelle enthält eine ID, Quell- und Ziel-Knoten-IDs, Kosten für Hin- und Rückrichtung sowie eine mit PostGIS GEOMETRY gespeicherte Liniengeometrie.

-- Tabelle zum Speichern von Netzwerkdaten anlegen
CREATE TABLE ways (
    id SERIAL PRIMARY KEY, -- Eindeutiger Bezeichner für das Straßensegment
    source INTEGER,        -- Startknoten-ID
    target INTEGER,        -- Zielknoten-ID
    cost DOUBLE PRECISION, -- Kosten für die Strecke in Richtung (z. B. Distanz, Zeit)
    reverse_cost DOUBLE PRECISION, -- Rückwegskosten
    geom GEOMETRY(LineString, 4326) -- Geometrie des Segments, Typ LineString, SRID 4326 (WGS 84)
);

Erläuterung:

• SERIAL PRIMARY KEY: Automatisch generierte, eindeutige ID pro Straßensegment.
• source, target: IDs für Start- und Zielknoten in der Topologie. Werden beim Topologieaufbau generiert oder zugewiesen.
• cost, reverse_cost: Gewicht für Durchlaufkosten in beide Richtungen, z. B. für Einbahnstraßen. Setzen Sie reverse_cost auf NULL oder einen hohen Wert, um das Rückwärtsrouting zu unterbinden.
• geom: Speichert die Liniengeometrie. LineString steht für Liniensegmente und 4326 ist das CRS WGS 84 (GPS-Standard).

Beispieldaten einfügen

Sie können der Tabelle ways einige Beispielsegmente hinzufügen:

-- Beispiel-Strecken in die Netzwerktabelle einfügen
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));

Erläuterung:

• ST_MakePoint(x, y): Erstellt einen PostGIS-Punkt aus Längen- und Breitengrad.
• ST_MakeLine(point1, point2): Erstellt ein Liniensegment aus zwei Punkten.
• ST_SetSRID(geometry, srid): Weist der Geometrie das Koordinatensystem zu.
• Die Beispielkoordinaten liegen im Bereich von Peking.

Topologie erstellen

Wenn die Netzwerktabelle bereit ist, erzeugen Sie mit der Funktion pgr_createTopology die Topologie. Das Tool analysiert die Geometrien, erkennt Netzwerkknoten und füllt die Spalten source und target automatisch aus – zudem wird eine separate Knoten-Tabelle generiert (üblicherweise [Tabellenname]_vertices_pgr).

-- Netzwerktopologie erstellen
-- Parameter:
-- 'ways': Tabellenname
-- 0.00001: Toleranz zur Knoten-Erkennung (in Grad, für WGS 84)
-- 'geom': Geometriespalte
-- 'id': ID-Spalte
SELECT pgr_createTopology('ways', 0.00001, 'geom', 'id');

Erläuterung:

• pgr_createTopology: Zentrales Tool zum Erstellen des Knoten-Kanten-Modells für das Netzwerk.
• Die Toleranz definiert, wie nahe beieinander Punkte liegen müssen, um als gleicher Knoten zu gelten; abhängig von der Präzision Ihrer Daten.

Nach der Ausführung sind die Spalten source und target gefüllt, und Ihnen steht eine Tabelle ways_vertices_pgr mit allen erkannten Knoten und ihren Koordinaten zur Verfügung.

Routing-Analysen mit pgRouting

Nachdem die Topologie steht, können Sie die verschiedenen Algorithmen von pgRouting nutzen. Im Folgenden einige typische Anwendungsbeispiele.

Kürzeste-Wege-Analyse (Dijkstra)

Die Suche nach dem kürzesten Pfad zwischen zwei Knoten ist eine Standardanwendung – dafür implementiert pgRouting den Dijkstra-Algorithmus.

-- Kürzeste Route von Knoten 1 zu 4 finden
-- Parameter:
-- 'SELECT id, source, target, cost FROM ways': Netzwerkdefinition (enthält id, source, target, cost)
-- 1: Startknoten-ID
-- 4: Zielknoten-ID
-- directed := true: Ist das Netzwerk gerichtet?
SELECT seq, id1 AS node, id2 AS edge, cost, geom
FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM ways',
    1, -- Startknoten-ID
    4, -- Zielknoten-ID
    directed := true -- Wenn Sie Rückwärtskosten einbeziehen wollen, auf false setzen bzw. weglassen
)
JOIN ways ON edge = ways.id; -- Verknüpfung mit Original-Tabelle für Geometrie

Erläuterung:

• pgr_dijkstra: Führt die Berechnung des kürzesten Wegs aus.
• Die Netzwerkanweisung muss mindestens id, source, target und cost enthalten. Für bidirektionale Modelle ggf. auch reverse_cost.
• Die JOIN-Anweisung holt für jede Kante die Geometrie – nützlich für eine spätere Visualisierung.

Traveling Salesman Problem (TSP)

Der TSP-Algorithmus ermittelt den kürzesten Rundkurs, der eine gegebene Liste von Punkten einmalig besucht.

-- TSP für die Knoten 1, 2, 3, 4, Start bei Knoten 1
-- Parameter:
-- 'SELECT id, x::float8 AS x, y::float8 AS y FROM ways_vertices_pgr': Abzufahrende Knoten
-- start_id := 1: Startknoten-ID
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)', -- Zu besuchende Knoten
    start_id := 1 -- Startknoten-ID
);

Erläuterung:

• pgr_tsp: Löser für das Traveling Salesman Problem.
• Erster Parameter: Knoten mit Koordinaten – meist aus der [ways]_vertices_pgr-Tabelle.
• start_id: Startpunkt der TSP-Rundfahrt.

Einzugsgebietsanalyse (Driving Distance / Time)

Die Service-Area-Analyse identifiziert alle Segmente eines Netzwerks, die innerhalb eines gegebenen Kostenlimits (z.B. Distanz oder Zeit) von einem Startpunkt erreichbar sind.

-- Alle Segmente finden, die von Knoten 1 aus mit Kosten <= 2 erreicht werden können
-- Parameter:
-- 'SELECT id, source, target, cost FROM ways': Netzwerkdefinition
-- 1: Startknoten-ID
-- 2: Maximale Kosten (Distanz, Zeit)
-- directed := true: Gerichtet?
SELECT seq, id1 AS node, id2 AS edge, cost, geom
FROM pgr_drivingDistance(
    'SELECT id, source, target, cost FROM ways',
    1, -- Startknoten-ID
    2, -- maximales Kostenlimit
    directed := true
)
JOIN ways ON edge = ways.id; -- Für Geometriedaten

Erläuterung:

• pgr_drivingDistance: Ermittelt das Einzugsgebiet ab Startknoten bis max. Kosten.
• Über JOIN können die Geometriedaten für Visualisierungen genutzt werden.

Visualisierung der Routing-Ergebnisse

Für die Auswertung und Präsentation ist es oft hilfreich, die Analyseergebnisse visuell darzustellen. Hierzu können Desktop-GIS-Software oder Web-Mapping-Bibliotheken genutzt werden.

Visualisierung in Desktop-GIS (z.B. QGIS)

QGIS ist eine weit verbreitete, freie Desktop-GIS-Lösung. Sie kann direkt auf PostgreSQL/PostGIS-Instanzen zugreifen und deren Geodaten – auch Ergebnisse von pgRouting – laden und visualisieren.

1. Öffnen Sie QGIS.
2. Gehen Sie zu Layer > Datenquellenverwaltung.
3. Wählen Sie im Menü PostGIS.
4. Klicken Sie auf Neu, um eine neue Datenbankverbindung einzurichten.
5. Tragen Sie die Verbindungsdaten Ihrer ServBay-PostgreSQL-Datenbank ein (z.B.: Host: localhost, Port: 5432, Datenbank: servbay_geo_db, Benutzer: servbay, Passwort: Ihr Passwort). Mit „Verbindung testen“ prüfen Sie Ihre Angaben.
6. Nach dem Aufbau sehen Sie in Ihrer Datenbank die Tabellen, darunter ways und ways_vertices_pgr.
7. Wählen Sie die gewünschten Tabellen oder Abfragen (z.B. ein View mit kürzester Route) und fügen Sie diese Ihrem QGIS-Projekt hinzu.

Webmapping-Visualisierung (Leaflet, OpenLayers)

Für Webanwendungen benötigen Sie einen Backend-Service (z.B. PHP, Node.js, Python in ServBay), der Routenergebnisse von pgRouting abfragt und als GeoJSON an das Frontend liefert. Die Darstellung übernimmt dann eine JavaScript-Bibliothek wie Leaflet oder OpenLayers.

Hier ein einfaches HTML-Beispiel mit einer statischen Route; für echte Routing-Ergebnisse ersetzen Sie das GeoJSON durch dynamische Datenübertragung vom Backend:

<!DOCTYPE html>
<html>
<head>
    <title>ServBay pgRouting Web-Visualisierungsbeispiel</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%; } /* Kartengröße festlegen */
    </style>
</head>
<body>
    <h1>ServBay pgRouting Ergebnis-Visualisierung</h1>
    <div id="map"></div>

    <script>
        // Karte initialisieren, Zentrum und Zoom-Level festlegen
        var map = L.map('map').setView([39.906, 116.409], 14); // Beispiel: Zentrum des Demo-Datensatzes

        // OpenStreetMap als Basiskarte hinzufügen
        L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
            attribution: '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a>-Mitwirkende'
        }).addTo(map);

        // Beispiel: statisches GeoJSON aus pgRouting-Ergebnis
        // In der Praxis die GeoJSON-Daten per AJAX vom Backend laden
        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]
                        ]
                    }
                }
            ]
        };

        // GeoJSON-Objekt auf der Karte anzeigen
        L.geoJSON(geojsonData, {
            style: function (feature) {
                return {color: "#ff0000", weight: 4}; // Rote, breite Route
            }
        }).addTo(map);

        // Kartenansicht an Route anpassen
        if (L.geoJSON(geojsonData).getBounds().isValid()) {
             map.fitBounds(L.geoJSON(geojsonData).getBounds());
        }


    </script>
</body>
</html>

Speichern Sie diese HTML-Datei im Webverzeichnis Ihres ServBay-Projektes (z.B. /Applications/ServBay/www/pgrouting-demo/index.html) und rufen Sie sie im Browser via ServBay auf (z.B. http://pgrouting-demo.servbay.demo), und Sie sehen eine Karte mit der Beispielroute. Für dynamische Ergebnisse muss das GeoJSON per Backend-API generiert und ersetzt werden.

Hinweise und Best Practices

• Datenqualität: Die Genauigkeit Ihrer Routing-Ergebnisse hängt maßgeblich von der Qualität der Netzwerkdaten ab. Achten Sie auf vollständige, gut strukturierte Geometrien und eine saubere Topologie.
• Performance: Große Netzwerke können zu längeren Berechnungszeiten führen. Nutzen Sie gegebenenfalls Indizes, vereinfachen Sie das Netzwerk oder nutzen Sie effizientere Algorithmen.
• Arbeitsspeicher: Komplexe Topologien können viel RAM beanspruchen – stellen Sie sicher, dass Ihr Datenbankserver ausreichend Ressourcen hat.

FAQ – Häufige Fragen

F: Beim Befehl CREATE EXTENSION pgrouting; erhalte ich eine Fehlermeldung, dass die Erweiterung nicht existiert. Was tun?
A: Prüfen Sie zunächst, ob das ServBay-PostgreSQL-Paket installiert und aktiv ist. Kontrollieren Sie, dass Ihre Datenbankversion pgRouting unterstützt und die Erweiterung im entsprechenden Verzeichnis verfügbar ist (bei ServBay normalerweise Standard). Schauen Sie, falls nötig, auch in die ServBay- oder PostgreSQL-Logdateien für Details. Sie sollten außerdem mit ausreichenden Berechtigungen (z.B. Benutzer servbay) verbunden sein.

F: Wie wähle ich den richtigen Toleranzwert bei der Funktion pgr_createTopology?
A: Die Toleranz hängt von der Präzision Ihrer räumlichen Daten ab. Sie bestimmt, ab welchem Mindestabstand zwei Punkte als identisch gelten. Bei sehr präzisen (z.B. GPS-) Daten ist ein Wert wie 0.000001 denkbar, bei niedrigeren Genauigkeiten entsprechend mehr. Zu große Werte können unbeabsichtigt Segmente verbinden, die nicht zusammengehören.

F: Wie lassen sich Einbahnstraßen oder Abbiegeregeln modellieren?
A: Dies erfolgt über die Spalten cost und reverse_cost in Ihrer Netzwerk-Tabelle. Für reine Einbahnstraßen setzen Sie die Rückwärtskosten (reverse_cost) auf NULL oder einen sehr hohen Wert, um die Richtung zu sperren. Komplexere Verkehrsregeln (z.B. Abbiegeverbote) erfordern fortgeschrittenes Routing oder eine Nachbearbeitung der Ergebnisse. pgRouting stellt dafür teils spezielle Funktionen zur Verfügung.

Fazit

Mit ServBay gelingt der Aufbau einer lokalen PostgreSQL-Datenbank mit pgRouting-Support schnell und unkompliziert. Mittels weniger SQL-Befehle aktivieren Sie die nötigen Erweiterungen, bereiten Ihre Netzwerkdaten vor und erstellen die Topologie – und können danach mit mächtigen Algorithmen georäumliche Analysen durchführen. Dank der Integration mit GIS-Desktops oder Web-Mapping-Bibliotheken lassen sich Ergebnisse einfach visualisieren und in eigene Projekte einbinden. ServBay nimmt Ihnen die Komplexität der Systemeinrichtung ab, sodass Sie sich ganz auf Ihre Anwendung und deren Logik konzentrieren können.