Geospatial Routing Analysis with pgRouting in ServBay

pgRouting is a powerful extension module for PostgreSQL and PostGIS databases, designed to provide rich routing and network analysis capabilities for geospatial data. With pgRouting, developers can execute complex queries on road network data, such as finding the shortest path between two points, solving the Traveling Salesperson Problem (TSP), or conducting service area analysis. These features are invaluable when building map applications, logistics planning systems, transportation analytics, and more.

This article provides a detailed walkthrough on how to easily enable the pgRouting extension for your PostgreSQL database in the ServBay local web development environment, as well as how to perform basic configuration and routing analysis.

Overview

ServBay provides macOS developers with a convenient, all-in-one local development environment, pre-integrated with various web servers, databases, and programming languages. The PostgreSQL package provided by ServBay already includes both pgRouting and PostGIS extensions. This means you don’t need to manually download or compile these extensions—just enable them in your database and you're ready to go.

Prerequisites

Before you get started with pgRouting, make sure you have the following prerequisites:

1. ServBay Installed and Running: If you haven’t installed ServBay, visit the official ServBay website to download and install the latest version.
2. PostgreSQL Package Installed and Started in ServBay: In the ServBay app interface, confirm that the PostgreSQL package is installed and running.
3. Basic Knowledge of SQL and PostgreSQL: This guide assumes you are familiar with basic PostgreSQL database operations and SQL queries.
4. Understanding of PostGIS Basics: pgRouting relies on PostGIS for handling geospatial data types and functions. Ensure the PostGIS extension is enabled in your database. The PostgreSQL package from ServBay typically includes PostGIS by default.

Installing and Enabling the pgRouting Extension

ServBay has already included the pgRouting extension files for you. All you need to do is enable the extension in the database where you intend to use it.

1. Connect to the PostgreSQL Database:

   There are several ways to connect to your PostgreSQL database, such as using the command-line tool psql, graphical tools (like pgAdmin, DBeaver), or client libraries in programming languages.

   The command-line psql tool provided by ServBay is a common and straightforward option. You can quickly open a terminal window with the necessary environment variables by clicking the “Terminal” button in the ServBay app, or manually add ServBay’s bin directory to your system PATH.

   In the terminal, use the following command to connect to your target database (for example, a database named servbay_geo_db with the default servbay user):

   psql -U servbay -d servbay_geo_db

   Adjust the command if your database name, username, or password differs.

2. Check and Enable the PostGIS Extension (if not already enabled):

   pgRouting depends on PostGIS. Make sure PostGIS is enabled before enabling pgRouting. At the psql prompt, enter:

   CREATE EXTENSION IF NOT EXISTS postgis;

   This command creates the PostGIS extension or skips creation if it already exists.

3. Create the pgRouting Extension:

   In the same database connection, execute the following SQL command to create the pgRouting extension:

   CREATE EXTENSION pgrouting;

   If successful, you'll see output similar to CREATE EXTENSION.

4. Verify Installation:

   You can list the installed extensions to verify that pgRouting has been enabled:

   \dx

   You should see both postgis and pgrouting listed with their respective details.

Configuring pgRouting: Preparing Network Data and Building Topology

pgRouting algorithms operate on tables representing road network structures. Typically, this involves a table of line segments (roads), each with a start node, end node, and a weight (such as distance or travel time). For efficient routing calculations, you also need to turn these segments into a “topology” that indicates how road segments connect at nodes.

Creating a Road Network Table

Below is a simple example demonstrating how to create a table named ways for storing road network data. The table includes a unique road segment ID, source node ID, target node ID, forward and reverse travel costs (cost, reverse_cost), and the road’s geometry using the PostGIS GEOMETRY type.

-- Create a table to store road network data
CREATE TABLE ways (
    id SERIAL PRIMARY KEY, -- Unique identifier for the road segment
    source INTEGER,        -- Start node ID
    target INTEGER,        -- End node ID
    cost DOUBLE PRECISION, -- Cost to traverse this segment in the forward direction (e.g., distance, time)
    reverse_cost DOUBLE PRECISION, -- Cost to traverse this segment in reverse
    geom GEOMETRY(LineString, 4326) -- Road geometry, using LineString and SRID 4326 (WGS 84 coordinates)
);

Explanation:

• SERIAL PRIMARY KEY: Automatically generates a unique ID for each road segment.
• source, target: Indicate the start and end node IDs in the topology. These will be populated or associated automatically when building topology.
• cost, reverse_cost: Define the weight for traversing the segment. For one-way streets, set reverse_cost to NULL or a very large value.
• geom: Uses the GEOMETRY type from PostGIS to store spatial data. LineString indicates a line; 4326 is a common Spatial Reference ID (SRID) representing the WGS 84 coordinate system (used by GPS).

Inserting Example Data

You can insert some example road segments into the ways table:

-- Insert sample road segments
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));

Explanation:

• ST_MakePoint(x, y): Creates a PostGIS point object from given longitude and latitude.
• ST_MakeLine(point1, point2): Creates a PostGIS line object from two points.
• ST_SetSRID(geometry, srid): Assigns a spatial reference identifier to the geometry.
• The sample data represents three connected road segments, each with a cost of 1.0. The coordinates represent an example area in downtown Beijing.

Building Topology

Once your road network table is ready, use the pgr_createTopology function to create topology. This function analyzes the geometry of each road segment, detects connection points (nodes), and populates the source and target columns in your table. It also creates a new node table (usually named [your_table_name]_vertices_pgr).

-- Build topology for the road network data
-- Parameters:
-- 'ways': Name of the table to create topology on
-- 0.00001: Tolerance value, used to determine if two points are close enough to be regarded as the same node
-- 'geom': Name of the geometry column
-- 'id': Name of the road segment ID column
SELECT pgr_createTopology('ways', 0.00001, 'geom', 'id');

Explanation:

• pgr_createTopology: Core function from pgRouting to build node-edge network models.
• The tolerance value is very important—it determines how close points must be to be treated as the same intersection or node. Select a value appropriate for your data’s accuracy and scale.

After running this function, the source and target columns in the ways table will be populated, and a new table named ways_vertices_pgr will be created containing all identified nodes and their coordinates.

Routing Analysis with pgRouting

Once topology has been built, you can start using the various algorithm functions provided by pgRouting for routing analysis. Here are examples of some common algorithms.

Shortest Path Analysis (Dijkstra)

Finding the shortest path between two nodes is a common routing requirement. pgRouting provides an implementation of the Dijkstra algorithm.

-- Query the shortest route from node 1 to node 4
-- Parameters:
-- 'SELECT id, source, target, cost FROM ways': Query to construct the graph; must include id, source, target, and cost columns
-- 1: Start node ID
-- 4: End node ID
-- directed := true: Specifies if the graph is directed (i.e., cost and reverse_cost differ)
SELECT seq, id1 AS node, id2 AS edge, cost, geom
FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM ways',
    1, -- Start node ID
    4, -- End node ID
    directed := true -- Set to false or omit if you need to consider reverse costs
)
JOIN ways ON edge = ways.id; -- Join results with the original ways table to get geometry

Explanation:

• pgr_dijkstra: Runs the Dijkstra shortest path algorithm.
• The first parameter is a SQL query returning the network edges, with at least id, source, target, and cost columns. If bi-directional paths are needed, include reverse_cost and set directed to false.
• The second and third parameters are the starting and ending node IDs.
• JOIN ways ON edge = ways.id: Enriches results with the original geometry data for visualization.

Traveling Salesperson Problem (TSP)

The TSP algorithm finds the shortest or lowest-cost circuit visiting a specified set of nodes.

-- Solve the TSP for visiting nodes 1, 2, 3, 4, starting from node 1
-- Parameters:
-- 'SELECT id, x::float8 AS x, y::float8 AS y FROM ways_vertices_pgr': Query of nodes to visit and their coordinates
-- start_id := 1: Specify the starting node 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)', -- Nodes and coordinates to visit
    start_id := 1 -- Starting node ID
);

Explanation:

• pgr_tsp: Runs the TSP algorithm.
• The first parameter is a SQL query returning the nodes to visit, with at least id, x, y (coordinates), normally queried from ways_vertices_pgr.
• start_id: The node at which the trip begins.

Service Area Analysis (Driving Distance / Driving Time)

Service area analysis identifies all areas or road segments reachable within a given cost (distance or time) from one or more starting points.

-- From node 1, find all road segments reachable within a cost of 2 units
-- Parameters:
-- 'SELECT id, source, target, cost FROM ways': Query to build the graph
-- 1: Start node ID
-- 2: Maximum cost allowed
-- directed := true: Whether the graph is directed
SELECT seq, id1 AS node, id2 AS edge, cost, geom
FROM pgr_drivingDistance(
    'SELECT id, source, target, cost FROM ways',
    1, -- Start node ID
    2, -- Maximum cost
    directed := true
)
JOIN ways ON edge = ways.id; -- Join with the original ways table to get geometry

Explanation:

• pgr_drivingDistance: Executes a service area analysis algorithm.
• The first parameter is the query to build the graph, same as for pgr_dijkstra.
• The second parameter is the source node ID.
• The third parameter is the maximum allowed cost (distance/time).
• Enrich results via JOIN for visualization.

Visualizing Routing Results

Visualizing analysis results from pgRouting is crucial for understanding and demonstrating routing outcomes. You can use desktop GIS software or integrate JavaScript mapping libraries into your web app.

Using Desktop GIS Tools (e.g., QGIS)

QGIS is a free, open-source desktop GIS application that can connect directly to PostgreSQL/PostGIS databases, allowing you to load and display spatial data—including results from pgRouting.

1. Open QGIS.
2. Go to Layer > Data Source Manager.
3. Select PostGIS from the left menu.
4. Click New to create a new database connection.
5. Fill in your ServBay PostgreSQL connection details (e.g., Host: localhost, Port: 5432, Database: servbay_geo_db, User: servbay, Password: your PostgreSQL password). Click “Test Connection” to verify.
6. Once connected, expand your database to see its tables, including ways and ways_vertices_pgr.
7. Choose the table or view you'd like to load (e.g., a view containing shortest path results), and click Add to display it in QGIS.

Visualizing in a Web Application (Leaflet or OpenLayers)

For web apps, you'll need a backend service (for example, using PHP, Node.js, or Python in ServBay) to execute pgRouting queries and return the results (typically in GeoJSON format) to the frontend. On the frontend, use a mapping library like Leaflet or OpenLayers to display the spatial data.

Here is a basic HTML structure showing how to add a static polyline with Leaflet. To display dynamic results:

1. Execute pgRouting queries on the backend.
2. Convert the results’ geometry into GeoJSON (or another frontend-compatible format).
3. Provide GeoJSON data via an API endpoint.
4. Use Leaflet’s L.geoJSON (or equivalent) in frontend JavaScript to load and display the data.

<!DOCTYPE html>
<html>
<head>
    <title>ServBay pgRouting Web Visualization Example</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%; } /* Set the size of the map container */
    </style>
</head>
<body>
    <h1>ServBay pgRouting Result Visualization</h1>
    <div id="map"></div>

    <script>
        // Initialize map, set center and zoom
        var map = L.map('map').setView([39.906, 116.409], 14); // Centered on sample data area

        // Add OpenStreetMap basemap
        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);

        // Example: Add GeoJSON data from pgRouting query results
        // In production, geojsonData would be queried from the backend via AJAX
        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]
                        ]
                    }
                }
            ]
        };

        // Add GeoJSON to the map with L.geoJSON
        L.geoJSON(geojsonData, {
            style: function (feature) {
                return {color: "#ff0000", weight: 4}; // Show routes as bold red lines
            }
        }).addTo(map);

        // Adjust map view to fit all features
        if (L.geoJSON(geojsonData).getBounds().isValid()) {
             map.fitBounds(L.geoJSON(geojsonData).getBounds());
        }


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

Save the above HTML file in your ServBay website root directory (e.g., /Applications/ServBay/www/pgrouting-demo/index.html) and access it via ServBay (such as http://pgrouting-demo.servbay.demo) to see a map with the sample route. Note that this is a static example, demonstrating how to load GeoJSON in Leaflet.

Notes

• Data Quality: The quality of pgRouting results highly depends on the input road network data. Ensure that your data is accurate, complete, and topologically correct.
• Performance: Routing calculations on large networks can be time-consuming. Consider using indexes, simplifying your network, or more efficient algorithms for performance.
• Memory Usage: Large topologies may require substantial memory. Make sure your database server is sufficiently resourced.

Frequently Asked Questions (FAQ)

Q: What should I do if I get an error about the extension not existing when running CREATE EXTENSION pgrouting;? A: First, verify that the PostgreSQL package is installed and running in ServBay. Then, check that your database version supports pgRouting, and that the ServBay-provided PostgreSQL includes the extension (which is usually the case). If the problem persists, check the ServBay or PostgreSQL log files for more detailed errors. Make sure you're connecting with a user who has sufficient privileges (such as the servbay user).

Q: How should I choose the tolerance value in the pgr_createTopology function? A: The tolerance value depends on the precision of your geospatial data. It defines the minimum distance for two vertices to be considered the same node. For high-precision data (such as GPS coordinates), use a small value (e.g., 0.000001 or smaller). For lower-precision or multi-source data, a higher value may ensure proper network connectivity. Setting the tolerance too high may cause unrelated segments to become connected incorrectly.

Q: How can I handle one-way streets or traffic restrictions like U-turn bans? A: Use the cost and reverse_cost columns in your ways table for this purpose. For one-way streets, set reverse_cost to NULL or a very large value to make the reverse direction non-traversable. For U-turn bans, you may need a more complex network model or post-process routing results; pgRouting provides advanced features for handling such cases.

Summary

With ServBay, setting up a PostgreSQL database that supports pgRouting in your local development environment is straightforward. With just a few SQL commands, you can enable extensions, prepare your road network data, and build topology, unlocking the full power of pgRouting geospatial routing algorithms. Combined with desktop GIS tools or web mapping libraries, you can easily visualize routing results, providing robust data support for your web apps or geospatial projects. ServBay streamlines environment setup, allowing developers to focus on application logic and feature implementation.