Geospatiale routeringsanalyse met pgRouting in ServBay

pgRouting is een krachtige uitbreiding voor de PostgreSQL- en PostGIS-databases, speciaal ontworpen om uitgebreide routerings- en netwerkanalysefuncties te bieden voor geospatiale data. Met pgRouting kunnen ontwikkelaars complexe queries uitvoeren op wegenkaarten, zoals het vinden van het kortste pad tussen twee punten, het oplossen van het handelsreizigersprobleem (TSP), of het uitvoeren van service area-analyses. Deze functies zijn bijzonder nuttig bij het bouwen van kaartapplicaties, logistieke planning, verkeersanalyse en meer.

In dit artikel leggen we stap voor stap uit hoe je binnen de lokale ontwikkelomgeving ServBay eenvoudig de pgRouting-extensie aan je PostgreSQL-database toevoegt, en hoe je basisconfiguratie en routeringsanalyses uitvoert.

Overzicht

ServBay biedt ontwikkelaars op macOS een handige alles-in-één lokale ontwikkelomgeving, voorgeïntegreerd met diverse webservers, databases en programmeertalen. Het PostgreSQL-pakket van ServBay is reeds voorzien van de uitbreidingen pgRouting en PostGIS. Dat betekent dat je deze extensies niet handmatig hoeft te downloaden en compileren—je hoeft ze enkel in je database te activeren.

Vereisten

Voordat je aan de slag gaat met pgRouting, zorg ervoor dat je aan de volgende voorwaarden voldoet:

1. ServBay is geïnstalleerd en draait: Ga naar de officiële ServBay website om de nieuwste versie te downloaden en installeren als je ServBay nog niet hebt.
2. Het PostgreSQL-pakket binnen ServBay is geïnstalleerd en gestart: Controleer in het ServBay-dashboard of het PostgreSQL-pakket is geïnstalleerd en “loopt”.
3. Basiskennis van SQL en PostgreSQL: Deze handleiding gaat ervan uit dat je bekend bent met de basis van PostgreSQL en SQL-queries.
4. Bekendheid met PostGIS: pgRouting is afhankelijk van PostGIS om geospatiale datatypes en functies te verwerken. Zorg dat de PostGIS-extensie geactiveerd is binnen je database. Het vooraf geïnstalleerde ServBay PostgreSQL-pakket bevat doorgaans al PostGIS.

pgRouting-extensie installeren en activeren

ServBay maakt het eenvoudig: de benodigde pgRouting-bestanden zijn al aanwezig, je hoeft de extensie alleen nog per gewenste database te activeren.

1. Verbind met de PostgreSQL-database:

   Je kunt op meerdere manieren verbinding maken, zoals met de commandline-tool psql, een grafische tool (zoals pgAdmin of DBeaver), of via client-bibliotheken in programmeertalen.

   Gebruikmaken van de via ServBay geleverde psql-CLI is een directe en veelgebruikte methode. Je kunt vanuit het ServBay-dashboard eenvoudig een terminal openen (vooraf goed ingesteld voor PostgreSQL), of zelf het ServBay bin-pad aan de systeem-PATH toevoegen.

   Vanuit de terminal maak je bijvoorbeeld verbinding met een database servbay_geo_db als ServBay-gebruiker met het volgende commando:

   psql -U servbay -d servbay_geo_db

   Heb je een andere database, gebruikersnaam of wachtwoord? Pas het commando dan aan.

2. Check en activeer de PostGIS-extensie (indien nog niet geactiveerd):

   pgRouting vereist PostGIS. Voer in de psql-prompt het volgende uit:

   CREATE EXTENSION IF NOT EXISTS postgis;

   Dit activeert de PostGIS-extensie of slaat deze stap over als hij al actief is.

3. Installeer de pgRouting-extensie:

   Geef in dezelfde sessie het volgende SQL-commando om pgRouting te activeren:

   CREATE EXTENSION pgrouting;

   Is dit succesvol? Dan zie je een melding zoals CREATE EXTENSION.

4. Controleer de installatie:

   Je kan de actieve extensies opvragen:

   \dx

   Je zou nu postgis en pgrouting (en hun bijbehorende info) in het overzicht moeten zien.

pgRouting configureren: Wegenkaart en topologie aanmaken

De algoritmes van pgRouting werken op een tabel die de wegen en hun onderlinge verbindingen beschrijft. Meestal gebruik je daarvoor een tabel met lijnen (wegen), waarbij elk segment een beginpunt, eindpunt en gewichten (zoals afstand of tijd) heeft. Om efficient te routeren, moet je een topologiestructuur creëren die de connecties (knooppunten) tussen segmenten registreert.

Wegenkaart-tabel aanmaken

Hier is een voorbeeld van een eenvoudige ways-tabel waarin we segment-id's, bron- en doelknopen, kosten, teruggaande kosten en de lijn-geometrie (PostGIS) opslaan.

-- Maak een tabel voor het opslaan van wegenkaartgegevens
CREATE TABLE ways (
    id SERIAL PRIMARY KEY, -- Unieke wegsegment-id
    source INTEGER,        -- Startknooppunt-id
    target INTEGER,        -- Eindknooppunt-id
    cost DOUBLE PRECISION, -- Kosten in de richting van begin->einde (bijv. afstand, tijd)
    reverse_cost DOUBLE PRECISION, -- Kosten in de omgekeerde richting
    geom GEOMETRY(LineString, 4326) -- Lijn-geometrie van het segment, SRID 4326 (WGS84 lat/lon)
);

Toelichting:

• SERIAL PRIMARY KEY: genereert een unieke id per segment.
• source, target: verwijzen naar het begin- en eindknooppunt binnen de topologie.
• cost, reverse_cost: gewichten voor heen- en terugrichting (voor eenrichtingswegen reverse_cost op NULL of erg hoog zetten).
• geom: lijn-geometry met het PostGIS-LineString-type en SRID 4326 (WGS 84, GPS-coördinaten).

Voorbeelddata toevoegen

Voeg enkele voorbeeldsegmenten toe aan de tabel ways:

-- Voorbeeld wegsegmenten toevoegen
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));

Toelichting:

• ST_MakePoint(x, y): maakt een puntobject van opgegeven coördinaten.
• ST_MakeLine(punt1, punt2): maakt een lijn tussen twee punten.
• ST_SetSRID(geometry, srid): stelt de ruimtelijke referentie (SRID) in voor het object.
• De voorbeelddata vormen drie aaneengeschakelde segmenten, alle met een kost van 1.0. De coördinaten bevinden zich in het centrum van Beijing.

Topologie aanmaken

Wanneer de wegenkaart-tabel gereed is, maak je met de functie pgr_createTopology de topologie aan. Deze functie analyseert de lijnen, herkent knooppunten, vult de source- en target-velden, en creëert een nieuwe knooppuntentabel (meestal [tabelnaam]_vertices_pgr).

-- Topologie opbouwen voor de wegenkaart
-- Parameters:
-- 'ways': tabelnaam voor wegenkaart
-- 0.00001: tolerantie om te bepalen wanneer punten als dezelfde knoop tellen
-- 'geom': naam van de geometriemkolom
-- 'id': id-kolom van de wegsegmenten
SELECT pgr_createTopology('ways', 0.00001, 'geom', 'id');

Toelichting:

• pgr_createTopology: Kerndoelfunctie van pgRouting om een netwerk van knopen en segmenten op te bouwen.
• De tolerantie is belangrijk: die bepaalt binnen welke afstand aparte punten als identiek gelden (voor je knoopstructuur). Afhankelijk van je datanauwkeurigheid kies je de juiste waarde.

Na deze functie zijn de source- en target-kolommen van ways ingevuld en is de tabel ways_vertices_pgr aangemaakt met alle knopen en hun coördinaten.

Routeringsanalyse met pgRouting

Nu de topologie is aangemaakt, kun je verschillende algoritmen van pgRouting toepassen. Hieronder vind je basisvoorbeelden.

Kortste pad-analyse (Dijkstra)

Het bepalen van het kortste pad tussen twee knopen is één van de belangrijkste analyses; pgRouting biedt hier een implementatie van Dijkstra’s algoritme voor.

-- Kortste pad opvragen van knoop 1 naar knoop 4
-- Parameters:
-- 'SELECT id, source, target, cost FROM ways': query die het netwerk beschrijft; moet id, source, target en cost bevatten
-- 1: startknooppunt-id
-- 4: eindknooppunt-id
-- directed := true: geef aan of het netwerk gericht is (kosten heen en terug kunnen verschillen)
SELECT seq, id1 AS node, id2 AS edge, cost, geom
FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM ways',
    1, -- startknoop
    4, -- eindknoop
    directed := true -- bij eenrichtingsverkeer anders instellen of weglaten
)
JOIN ways ON edge = ways.id; -- om geometrie op te halen voor visualisatie

Toelichting:

• pgr_dijkstra: voert het Dijkstra-algoritme uit.
• De eerste parameter is een SQL-query waarin elk segment zijn id, bron, doel en kosten vermeldt. Voor heen/terug-gedrag kun je reverse_cost meenemen.
• Tweede en derde parameter zijn de knoop-id’s voor start en einde.
• JOIN ways ON edge = ways.id: hiermee koppel je de resultaten aan de oorspronkelijke geometrietabel voor weergave.

Handelsreizigersprobleem (Traveling Salesperson Problem, TSP)

Met de TSP-algoritme zoek je het efficiëntste rondje om een set knopen te bezoeken.

-- TSP oplossen voor bezoek aan knopen 1, 2, 3, 4 (startend bij 1)
-- Parameters:
-- 'SELECT id, x::float8 AS x, y::float8 AS y FROM ways_vertices_pgr': lijst met te bezoeken knopen, met hun coördinaten
-- start_id := 1: startpunt voor de route
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
);

Toelichting:

• pgr_tsp: lost het handelsreizigersprobleem op.
• De eerste parameter is een SQL-selectie met per knoop id, x, y. Kies de gewenste knopen uit ways_vertices_pgr.
• Start-id bepaalt waar je begint.

Service area-analyse (Drivings Distance / Drivings Time)

Deze analyse brengt alle knopen binnen een bepaalde afstand of reistijd vanaf een of meerdere beginpunten in kaart.

-- Vanuit knoop 1 alle segmenten binnen 2 kosten-eenheden opvragen
-- Parameters:
-- 'SELECT id, source, target, cost FROM ways': query die het netwerk beschrijft
-- 1: startknooppunt
-- 2: maximaal toegestane kosten
-- directed := true: of het netwerk gericht is
SELECT seq, id1 AS node, id2 AS edge, cost, geom
FROM pgr_drivingDistance(
    'SELECT id, source, target, cost FROM ways',
    1, -- begin
    2, -- maximum kosten
    directed := true
)
JOIN ways ON edge = ways.id;

Toelichting:

• pgr_drivingDistance: voert de service area (bereikbaarheid) analyse uit.
• Eerste parameter: SQL-query die id, source, target en cost levert.
• Tweede parameter: startknoop.
• Derde parameter: maximaal toelaatbare kosten.
• Door de JOIN koppel je de geometrie weer voor visualisatie.

Visualisatie van routeringsresultaten

Resultaten van pgRouting-analyses inzichtelijk maken is essentieel. Dit kan via desktop-GIS software of door webkaarten in je applicatie te integreren.

Desktop GIS (zoals QGIS) gebruiken

QGIS is een gratis desktop GIS-programma waarmee je direct kan verbinden met PostgreSQL/PostGIS, en ruimtelijke data (zoals pgRouting-resultaten) kan bekijken.

1. Start QGIS.
2. Kies Laag (Layer) > Databronbeheer (Data Source Manager).
3. Selecteer PostGIS in het linkermenu.
4. Klik op Nieuw (New) om een databaseverbinding toe te voegen.
5. Vul je ServBay PostgreSQL-inloggegevens in (bijvoorbeeld: Host: localhost, Port: 5432, Database: servbay_geo_db, Gebruiker: servbay, Wachtwoord: jouw PostgreSQL-wachtwoord). Met 'Test Verbindings' kun je checken of het werkt.
6. Als de verbinding lukt, zie je links in QGIS je database, met o.a. tabellen als ways en ways_vertices_pgr.
7. Selecteer de tabel of view die je wil inladen (bijv. een query met de kortste route) en klik op Toevoegen (Add) om de laag te visualiseren.

Visualiseren in een webapplicatie (Leaflet of OpenLayers)

Voor webapps heb je een backend nodig (bijvoorbeeld PHP, Node.js, Python draaiend in ServBay) die de pgRouting-query's draait, en resultaten (idealiter als GeoJSON) aanlevert aan de frontend. De frontend laat deze data zien met een kaartbibliotheek zoals Leaflet of OpenLayers.

Hieronder zie je de basisstructuur van een HTML-bestand dat met Leaflet een statische lijn visualiseert. Voor dynamische routeresultaten:

1. Draai je de pgRouting-query op de backend.
2. Converteer je de geometrie naar GeoJSON.
3. Stel je een API-endpoint beschikbaar die GeoJSON levert.
4. Gebruik je aan de frontend Leaflet’s L.geoJSON om de data weer te geven.

<!DOCTYPE html>
<html>
<head>
    <title>ServBay pgRouting Web Visualisatie Demo</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%; } /* Grootte van de kaartcontainer */
    </style>
</head>
<body>
    <h1>ServBay pgRouting Resultaten Visualiseren</h1>
    <div id="map"></div>

    <script>
        // Initialiseer de kaart met een centraal punt en zoomniveau
        var map = L.map('map').setView([39.906, 116.409], 14); // Midden van het voorbeeldgebied

        // Voeg de OpenStreetMap-basemap toe
        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);

        // Voorbeeld: voeg GeoJSON toe die afkomstig zou kunnen zijn van een pgRouting-query
        // In de praktijk haal je geojsonData via een AJAX-call uit je backend
        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]
                        ]
                    }
                }
            ]
        };

        // Voeg de GeoJSON-data toe aan de kaart met rode lijnen
        L.geoJSON(geojsonData, {
            style: function (feature) {
                return {color: "#ff0000", weight: 4}; // Rode, dikke lijn voor routes
            }
        }).addTo(map);

        // Zoom automatisch zodat alle features zichtbaar zijn
        if (L.geoJSON(geojsonData).getBounds().isValid()) {
             map.fitBounds(L.geoJSON(geojsonData).getBounds());
        }


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

Sla deze HTML op in de rootmap van je ServBay-site (bijvoorbeeld /Applications/ServBay/www/pgrouting-demo/index.html), en bezoek hem via ServBay (bijv. http://pgrouting-demo.servbay.demo) om je voorbeeldresultaat te bekijken. Dit is een statisch voorbeeld, bedoeld om te laten zien hoe je GeoJSON inlaadt met Leaflet.

Belangrijke aandachtspunten

• Datakwaliteit: Het succes van pgRouting hangt sterk af van de correctheid en volledigheid van je wegenkaart: zorg voor nauwkeurige en goed getopologiseerde data.
• Prestaties: Routing op grote netwerken kan traag zijn. Maak gebruik van indexen, vereenvoudig je netwerk, of gebruik snellere algoritmes waar mogelijk.
• Geheugenverbruik: Uitgebreide topologieën vragen veel RAM. Zorg dat je servers voldoende resources hebben.

Veelgestelde vragen (FAQ)

V: Ik krijg een fout bij CREATE EXTENSION pgrouting;, dat de extensie niet gevonden kan worden. Wat nu?
A: Controleer allereerst of de PostgreSQL binnen ServBay geïnstalleerd en gestart is. Vervolgens: is het databaseversienummer geschikt en zit de pgRouting-extensie echt in jouw versie van ServBay? (meestal wel). Check anders de ServBay- en PostgreSQL-logbestanden voor meer informatie. Zorg dat je ingelogd bent als een gebruiker met voldoende rechten (bijv. de servbay-gebruiker).

V: Hoe kies ik de juiste tolerantie (tolerance) bij pgr_createTopology?
A: Die hangt af van de ruimtelijke nauwkeurigheid van je data. De tolerantie geeft aan binnen welke afstand knooppunten als "samenvallend" worden gezien. Bij heel nauwkeurige (GPS-)data is een kleine waarde (bijvoorbeeld 0.000001) zinvol, bij minder nauwkeurige data kun je iets hoger gaan. Te hoge waarden leiden tot foutief verbonden wegen.

V: Hoe ga ik om met eenrichtingsverkeer of verboden afslaan?
A: De velden cost en reverse_cost in de ways-tabel regelen dit. Voor eenrichtingswegen zet je reverse_cost op NULL of een zeer hoge waarde (onbereikbaar). Complexere verkeersregels, zoals niet-keren, dienen vaak gemodelleerd te worden in een geavanceerder netwerk of via post-processing. pgRouting biedt extra tools voor geavanceerde beperkingen.

Samenvatting

Met ServBay kun je eenvoudig een lokale PostgreSQL-database opzetten die pgRouting ondersteunt. Met enkele SQL-commando’s activeer je de extensies, laad je je wegenkaart in, en maak je een topologie aan, waarna je krachtige routingsalgoritmes tot je beschikking hebt. Combineer dit met desktop-GIS of webkaarten om professionele visualisaties te bouwen ter ondersteuning van webapps of geospatiale projecten. Dankzij ServBay wordt het beheer van je ontwikkelomgeving sterk vereenvoudigd, zodat je je kunt focussen op de eigenlijke toepassing en functionaliteit.