Table of Contents

Network Routing Optimization with PostGIS, pgRouting, and OpenStreetMap Data

Introduction

When configuring PostGIS pgRouting network routing, raw road geometries imported from OpenStreetMap require meticulous attribute engineering to calculate realistic routing costs. In modern geographic information systems (GIS), spatial analytics often extend beyond static map rendering and spatial overlays into dynamic network analysis. Desktop GIS applications (such as QGIS or ArcGIS Pro) excel at interactive editing and exploratory spatial data analysis, but they frequently struggle with large-scale network routing queries across regional or national extents. When dealing with millions of road segments, concurrent user requests, or complex multi-stop optimization problems, memory bottlenecks and client-side processing limitations render desktop environments inefficient.

Database-level routing via PostGIS and pgRouting resolves these challenges by pushing computation directly into the relational database management system (RDBMS). By leveraging PostgreSQL’s cost-based query planner, robust indexing structures (GiST and SP-GiST), and parallel execution workers, enterprise routing architectures can process shortest-path calculations in milliseconds. This tutorial provides a comprehensive guide for GIS database engineers setting up custom routing engines using open-source tools, focusing on OpenStreetMap (OSM) data ingestion, topology creation, attribute weighting, and advanced algorithmic execution in SQL (PostGIS pgRouting OpenStreetMap Network Routing).

Step-by-Step Implementation Guide

Setting up an enterprise-grade routing engine requires a disciplined, four-stage workflow: data acquisition, topology cleansing and importation, cost attribute modeling, and executing graph algorithms.

1. Downloading Regional Road Networks via OpenStreetMap Extracts

The foundation of any open-source routing engine is high-quality topological network data. OpenStreetMap provides global vector data containing detailed road classifications, one-way restrictions, speed limits, and bridge/tunnel attributes. Rather than downloading planetary datasets, engineers typically extract regional .osm.pbf files from reliable providers like Geofabrik or Protomaps.

For programmatic extraction or automated data pipelines, you can also query Overpass API or clip regional bounding boxes using osmium-tool:

Bash

# Install osmium command-line tool
sudo apt-get install osmium-tool

# Extract a specific bounding box or regional sub-network from a country PBF file
osmium extract --bbox 36.6,-1.4,37.1,-1.1 kenya-latest.osm.pbf -o nairobi_region.osm.pbf

2. Importing and Topology Cleansing Road Networks

PostGIS pgRouting network routing OpenStreetMap topology cleansing and edge generation

Once the regional .osm.pbf extract is secured, the data must be imported into a PostgreSQL database equipped with PostGIS and pgRouting extensions. While ogr2ogr can import raw line geometries, osm2pgrouting is specifically designed to parse OSM highway tags, extract nodes and edges, and construct a routing-ready topology out of the box.

First, enable the required extensions in your PostgreSQL database:

SQL

-- Enable PostGIS and pgRouting extensions
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pgrouting;

Next, use osm2pgrouting to parse the .osm file, utilizing a configuration file (mapconfig.xml) that defines which highway types (e.g., motorways, trunk roads, primary roads, residential streets) should be included as drivable edges:

Bash

osm2pgrouting \
  -f nairobi_region.osm.pbf \
  -d routing_db \
  -U postgres \
  -h localhost \
  -p 5432 \
  --conf mapconfig.xml

This utility automatically generates four core tables in your database:

ways: The edge table containing road geometries, length, speed, and cost attributes.
way_nodes: Junction reference lookup tables.
osm_nodes: Original OSM nodes with geographic coordinates.
classes: Highway classification lookup tables.

If your workflow requires custom ogr2ogr ingestion instead of osm2pgrouting, you must manually build topological nodes and edges. For deep database performance tuning during large imports, review our guide on spatial index tuning and configuration best practices.

3. Building Network Node and Edge Tables with Valid Cost Attributes

Raw road geometries imported from OpenStreetMap require meticulous attribute engineering to calculate realistic routing costs. A naive shortest-path analysis based strictly on geometric length (ST_Length) will incorrectly route delivery trucks down narrow residential alleys or pedestrian footpaths instead of high-capacity arterial highways.

To build a robust road network database (Build road network database PostGIS), we must compute accurate cost metrics—such as physical distance, maximum speed limits, and estimated travel time—while accounting for one-way streets, turn restrictions, and vehicle class limitations.

SQL

-- Add cost and reverse_cost columns to theways table
ALTER TABLE ways ADD COLUMN IF NOT EXISTS cost double precision;
ALTER TABLE ways ADD COLUMN IF NOT EXISTS reverse_cost double precision;

-- Populate cost attributes based on length (in meters) and speed limit (km/h)
-- Travel time cost formula: (Length in meters / (Speed in km/h * 1000 / 3600))
UPDATE ways
SET 
  cost = CASE 
           WHEN max_speed > 0 THEN (length / (max_speed * 0.27778))
           ELSE (length / (50 * 0.27778)) -- Default 50 km/h urban speed
         END,
  reverse_cost = CASE 
                   -- Handle one-way streets designated by OSM tags (oneway = '-1' or 'yes')
                   WHEN oneway = 'yes' THEN -1
                   WHEN oneway = '-1' THEN CASE 
                     WHEN max_speed > 0 THEN (length / (max_speed * 0.27778))
                     ELSE (length / (50 * 0.27778))
                   END
                   ELSE CASE 
                     WHEN max_speed > 0 THEN (length / (max_speed * 0.27778))
                     ELSE (length / (50 * 0.27778))
                   END
                 END;

To optimize analytical queries that group or aggregate network metrics across administrative zones, you can combine these network attributes with spatial window functions in PostGIS.

4. Executing Advanced Routing Algorithms in SQL

PostGIS pgRouting network routing Dijkstra shortest path query execution

With a topologically clean edge table containing explicit source, target, cost, and reverse_cost attributes, you can execute graph traversal algorithms directly in SQL (Shortest path analysis PostgreSQL).

Dijkstra’s Algorithm (`pgr_dijkstra`)

Dijkstra’s algorithm is the foundational method for exact shortest-path finding in weighted graphs (pgRouting tutorial Dijkstra A star). It explores all viable paths outward from the source node until the destination node is reached.

SQL

SELECT 
    seq,
    node,
    edge,
    cost,
    agg_cost,
    geom
FROM pgr_dijkstra(
    'SELECT gid AS id, source, target, cost, reverse_cost FROM ways',
    12450, -- Source node ID
    89321, -- Target node ID
    directed := true
) AS pt
JOIN ways AS w ON pt.edge = w.gid;

A* Search Algorithm (`pgr_aStar`)

For large regional networks, standard Dijkstra can evaluate an excessive number of unpromising nodes. The A* algorithm accelerates shortest-path calculations by incorporating a heuristic function (Euclidean or Haversine distance to the target) to guide the search direction.

SQL

SELECT 
    pt.seq,
    pt.node,
    pt.edge,
    pt.cost,
    pt.agg_cost
FROM pgr_aStar(
    'SELECT gid AS id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM ways',
    12450, 
    89321, 
    directed := true
) AS pt;

Traveling Salesperson Problem (TSP) Solver

PostGIS pgRouting network routing Traveling Salesperson Problem TSP optimization

Commercial logistics often require visiting multiple stops in optimal sequence (e.g., delivery fleets). The pgRouting pgr_tsp function solves the classic Traveling Salesperson Problem using heuristic optimization over a distance matrix generated by pgr_dijkstraCost.

SQL

-- Compute optimal visiting sequence for a depot and multiple delivery nodes
SELECT * FROM pgr_tsp(
    $$
    SELECT * FROM pgr_dijkstraCost(
        'SELECT gid AS id, source, target, cost, reverse_cost FROM ways',
        ARRAY[12450, 4521, 89321, 33102],
        directed := true
    )
    $$,
    start_id := 12450
);

When integrating complex multi-modal transit networks (combining road routing with scheduled public transport timetables), database engineers can bridge road graphs with GTFS feeds. Learn more about advanced transit integration in our guide on multimodal accessibility modeling with GTFS.

Conclusion

Migrating network routing workflows from client-side desktop GIS to an enterprise database architecture running PostGIS and pgRouting transforms spatial capability. By leveraging OpenStreetMap data, rigorous topology cleansing, dynamic cost modeling, and optimized SQL routing functions like pgr_dijkstra and pgr_aStar, organizations can build scalable, high-performance routing engines capable of handling complex spatial logistics with ease.