How to Install and Configure pgRouting on PostgreSQL: Build Your Own Shortest Path Routing Engine - ITFROMZERO - Share tobe shared! A hands-on guide to resolving logistics dispatch bottlenecks: installing pgRouting on PostgreSQL, building graph topologies, and computing optimal shortest paths.

Table of Contents

A 2 AM Incident: When the Logistics Routing API Froze

At 2 AM, the on-call PagerDuty alert buzzed relentlessly. The driver-dispatch microservice cluster crashed with 504 Gateway Timeout errors. On the dashboard, thousands of fare-calculation queries froze mid-flight during a massive flash sale. Until then, the system had been calculating straight-line distances and aggressively hammering third-party mapping APIs. As a result, the monthly API bill hit $3,000, while the 100 req/s rate limit throttled traffic right at peak hours.

Staring at the sea of red on Grafana, the team decided to fix this mess once and for all. Instead of burning cash on external APIs, we moved the entire routing workload into our internal database. By loading road network data into PostgreSQL and enabling pgRouting, all shortest path algorithms now run directly on a highly optimized C engine.

The Anatomy of Graph Networks in PostgreSQL

A database cannot inherently understand how two GPS coordinates connect if you only store flat point coordinates. To find routes, the data must be modeled as a graph:

1. Network Topology Structure

Every road segment is an Edge, while three-way junctions or intersections act as intersection vertices (Nodes or Vertices). A standard data table for pgRouting requires at least the following fields:

id: Unique identifier for the road segment.
source: Starting junction ID.
target: Ending junction ID.
cost: Forward cost (length in meters or travel time in seconds).
reverse_cost: Reverse cost. For two-way streets, this equals cost; for one-way streets where traveling in reverse is prohibited, set a negative value (typically -1).
geom: Spatial geometric object (LineString) managed by PostGIS, using the standard WGS 84 coordinate reference system (EPSG:4326).

2. How Do PostGIS and pgRouting Divide Responsibilities?

PostGIS handles the geometry: storing latitude and longitude, transforming coordinate systems, and computing real-world distances. pgRouting acts as an algorithmic library built on top of PostGIS. It provides out-of-the-box implementations of Dijkstra, A*, the Travelling Salesperson Problem (TSP), and Driving Distance (isochrones / service areas by travel time) without requiring you to write routing logic from scratch.

Step-by-Step Installation and Real-World Implementation

Here are the steps to build a routing cluster directly on an Ubuntu 22.04 LTS environment running PostgreSQL 16.

Step 1: Install PostGIS and pgRouting

Open your terminal and install the packages corresponding to your PostgreSQL version:

# Update package lists
sudo apt update

# Install PostGIS 3 and pgRouting for PostgreSQL 16
sudo apt install -y postgresql-16-postgis-3 postgresql-16-pgrouting

Once installed, log into PostgreSQL to enable the extensions on your project database:

sudo -u postgres psql -d transport_db

-- PostGIS must be enabled first because pgRouting depends on it
CREATE EXTENSION IF NOT EXISTS postgis;

-- Enable pgRouting
CREATE EXTENSION IF NOT EXISTS pgrouting;

-- Verify successful installation
SELECT pgr_version();

Step 2: Design the Schema and Import Road Network Data

The roads table is created to model urban street segments:

CREATE TABLE roads (
    id SERIAL PRIMARY KEY,
    osm_id BIGINT,
    name VARCHAR(100),
    source INTEGER,
    target INTEGER,
    cost DOUBLE PRECISION,
    reverse_cost DOUBLE PRECISION,
    geom GEOMETRY(LineString, 4326)
);

Real-world data is typically extracted from OpenStreetMap (OSM) or logistics partner Shapefiles in CSV or GeoJSON format. If you need to quickly convert a coordinate CSV file to JSON for script ingestion, check out toolcraft.app/en/tools/data/csv-to-json — it parses files directly in your browser, ensuring complete privacy for sensitive client data.

Now, let’s insert 4 sample road segments forming a closed road loop in downtown Hanoi (around coordinates 105.85, 21.02):

INSERT INTO roads (id, name, geom)
VALUES
    (1, 'Trang Tien Segment A', ST_GeomFromText('LINESTRING(105.850 21.020, 105.855 21.020)', 4326)),
    (2, 'Trang Tien Segment B', ST_GeomFromText('LINESTRING(105.855 21.020, 105.860 21.020)', 4326)),
    (3, 'Phan Chu Trinh Segment', ST_GeomFromText('LINESTRING(105.855 21.020, 105.855 21.025)', 4326)),
    (4, 'Hai Ba Trung Segment', ST_GeomFromText('LINESTRING(105.855 21.025, 105.860 21.020)', 4326));

Step 3: Automatically Build Network Topology with pgr_createTopology

At this point, the source and target columns are still empty. Instead of manually inspecting every intersection, pgRouting provides the pgr_createTopology function to automatically detect intersecting endpoints:

-- Syntax: table name, snapping tolerance, geometry column name, primary key column name
SELECT pgr_createTopology('roads', 0.00001, 'geom', 'id');

A tolerance of 0.00001 degrees corresponds to roughly 1.1 meters on the ground. This threshold is ideal for snapping together endpoints with slight GPS drift. Once executed, pgRouting automatically generates the auxiliary roads_vertices_pgr table containing the vertex list, while populating the source and target columns in the roads table.

Next, we calculate the travel costs. We use ST_Length on the geography data type to obtain real-world distances in meters:

-- Cast to geography so PostGIS accurately computes distance in meters on the ellipsoid
UPDATE roads
SET cost = ST_Length(geom::geography),
    reverse_cost = ST_Length(geom::geography);

-- Suppose Phan Chu Trinh is a one-way street; set reverse_cost = -1 to disallow reverse traversal
UPDATE roads SET reverse_cost = -1 WHERE id = 3;

-- Create B-tree indexes on source and target to speed up graph queries
CREATE INDEX idx_roads_source ON roads(source);
CREATE INDEX idx_roads_target ON roads(target);

Step 4: Run Dijkstra’s Algorithm to Find the Shortest Path

Everything is set. You can now execute pgr_dijkstra to compute the optimal route from junction 1 to junction 3:

SELECT 
    path.seq,
    path.node,
    path.edge,
    path.cost AS step_cost,
    path.agg_cost AS total_cost,
    r.name AS road_name,
    ST_AsGeoJSON(r.geom) AS geojson
FROM pgr_dijkstra(
    'SELECT id, source, target, cost, reverse_cost FROM roads',
    1, -- Start node
    3, -- Target node
    directed := true
) AS path
LEFT JOIN roads r ON path.edge = r.id
ORDER BY path.seq;

The query returns a list of path segments along with cumulative costs and their corresponding GeoJSON strings. The backend simply serializes this GeoJSON array and returns it to the driver app to render the route polyline instantly.

Real-World Results After Migration

After two weeks in production, the migration yielded dramatic improvements:

Backend API cluster CPU utilization dropped sharply from 92% down to 14%.
Route calculation p99 latency plummeted from 420ms (external API calls) to just 8-12ms over the local network.
Saved nearly $3,000 per month on commercial map API fees.
Completely eliminated rate-limit bottlenecks during flash sale spikes.

If your logistics platform is scaling up and hitting walls with API costs and latency, mastering PostGIS and pgRouting directly inside your database is an engineering investment well worth making.