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:
- 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.
- PostgreSQL-Paket in ServBay ist installiert und gestartet: Prüfen Sie in der ServBay-Benutzeroberfläche, ob das PostgreSQL-Paket installiert und aktiv ist.
- Grundkenntnisse in SQL und PostgreSQL: Dieser Leitfaden setzt ein Verständnis für grundlegende SQL- und PostgreSQL-Konzepte voraus.
- Grundlagen von PostGIS:
pgRouting
setzt aufPostGIS
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.
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 Standardbenutzerservbay
) so:bashpsql -U servbay -d servbay_geo_db
1Anpassungen sind nötig, wenn Datenbankname, Nutzer oder Passwort abweichen.
PostGIS-Erweiterung prüfen und aktivieren (falls nicht bereits geschehen):
Da
pgRouting
aufPostGIS
basiert, stellen Sie sicher, dassPostGIS
aktiviert ist. Impsql
-Prompt führen Sie einfach aus:sqlCREATE EXTENSION IF NOT EXISTS postgis;
1Dieser Befehl erstellt die PostGIS-Erweiterung oder überspringt sie, falls bereits vorhanden.
pgRouting-Erweiterung aktivieren:
Im Anschluss daran aktivieren Sie pgRouting mit folgendem SQL-Befehl:
sqlCREATE EXTENSION pgrouting;
1Bei Erfolg erscheint eine Meldung wie
CREATE EXTENSION
.Installation prüfen:
Die aktivierten Erweiterungen listen Sie mit:
sql\dx
1In der Liste sollten sowohl
postgis
als auchpgrouting
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)
);
2
3
4
5
6
7
8
9
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 Siereverse_cost
aufNULL
oder einen hohen Wert, um das Rückwärtsrouting zu unterbinden.geom
: Speichert die Liniengeometrie.LineString
steht für Liniensegmente und4326
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));
2
3
4
5
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');
2
3
4
5
6
7
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
2
3
4
5
6
7
8
9
10
11
12
13
14
Erläuterung:
pgr_dijkstra
: Führt die Berechnung des kürzesten Wegs aus.- Die Netzwerkanweisung muss mindestens
id
,source
,target
undcost
enthalten. Für bidirektionale Modelle ggf. auchreverse_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
);
2
3
4
5
6
7
8
9
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
2
3
4
5
6
7
8
9
10
11
12
13
14
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.
- Öffnen Sie QGIS.
- Gehen Sie zu Layer > Datenquellenverwaltung.
- Wählen Sie im Menü PostGIS.
- Klicken Sie auf Neu, um eine neue Datenbankverbindung einzurichten.
- 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. - Nach dem Aufbau sehen Sie in Ihrer Datenbank die Tabellen, darunter
ways
undways_vertices_pgr
. - 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: '© <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>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
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.