Road Network Building with PostGIS and pgRouting

Building a directed road graph in PostgreSQL for shortest paths and time-budget reachability, demonstrated with synthetic data.

My contribution

My original project explored road-network preparation and shortest-path queries in PostgreSQL, alongside traffic collection in Python. A directed graph makes connectivity, one-way travel, and travel-time assumptions explicit. The subsequent review added input rules, rebuild checks, and a repeatable synthetic demonstration to make those decisions testable.

Result: The synthetic example shows why travel direction matters: the outward route costs 18.691 modeled seconds, while the return takes a different path at 40.871 seconds. A 20-second budget reaches four vertices, including the origin.

Tools

PostgreSQL
PostGIS
pgRouting
SQL
Python

Tested prototype using synthetic data. Traffic observations do not currently change routing costs, and isochrone polygons are not implemented.

Project gallery

Portfolio presentations of synthetic results and documented behavior. These are not screenshots of a deployed operational application. 5 images. Select an image to view it full size.

Synthetic road network showing graph connectivity, direction rules, and a disconnected component. — Eight synthetic road segments become a directed graph with explicit direction permissions, geodesic lengths, and travel costs in seconds.

Forward and return routes on the synthetic graph, illustrating the effect of one-way travel on the selected path. — The A-to-C route uses edges 1 and 2 at 18.691 seconds. The return uses edges 3, 4, and 5 at 40.871 seconds under the fixture's modeled base costs.

Four synthetic graph vertices reachable within a 20-second budget using the current modeled base costs. — A 20-second query returns four reachable vertices using current modeled base costs. This output represents network vertices, not an isochrone polygon.

Traffic observation workflow covering bounded collection, validation, atomic storage, and freshness filtering, separate from routing costs. — The separate Python collector supports bounded requests, validated observations, atomic writes, and freshness filtering. Observations do not currently change route costs.

Recorded local validation: 21 mocked Python tests, synthetic SQL assertions, and database integration checks on PostgreSQL, PostGIS, and pgRouting. — The October 6, 2026 local record includes 21 mocked Python tests, synthetic SQL assertions, and database integration checks on PostgreSQL 18.4, PostGIS 3.6.2, and pgRouting 4.0.1.

From road segments to routing decisions

The workflow turns normalized road segments into a graph of junctions and directed connections. PostGIS measures segment length; modeled speed converts that length into travel time. pgRouting then finds the lowest-cost permitted path, with route geometry returned in travel order.

Explicit direction and input rules matter because a connected line is not necessarily a road that can be driven both ways. The model checks speeds, closures, and stale graph builds. Roads must already be split at junctions; crossings and bridge levels are not resolved automatically.

A separate Python collector handles traffic flow and incident observations through bounded requests, validation, atomic writes, and freshness filtering. Matching those observations to the correct road and direction remains future work, so they do not change the routing costs.

What the synthetic demonstration shows

Eight invented road segments include one-way travel, a closed connection, and a disconnected component. A direct trip from A to C uses two edges. The return uses three different edges because the direct connection permits travel in only one direction.

The time-budget query returns four reachable vertices within 20 modeled seconds. It identifies reachable network points, including the origin; it does not produce an area boundary or represent partially reachable road segments. These values describe the synthetic example, not observed journey times.

Validation and remaining work

The recorded validation includes 21 passing mocked Python tests, synthetic SQL checks, and local database integration checks. PostgreSQL 18.4, PostGIS 3.6.2, and pgRouting 4.0.1 were exercised on Windows, including observation writes, rollback, and reader consistency during a graph rebuild.

Hosted Python checks also passed on Windows and Linux with Python 3.11 and 3.13. Those jobs used mocked services and a dry run; they did not validate a live traffic API or database compatibility on Linux.

Real-road accuracy, traffic matching, and large-network performance remain unverified. Turn restrictions, departure-time routing, partial-edge reachability, and isochrone polygons are not implemented. No production deployment or adoption is claimed.

The original project was supplied by its author. Review revisions, test fixtures, and documentation were developed with Codex assistance.

Technical details

Network analysis · Tested synthetic prototype

Road lines alone cannot answer routing questions. They need connected junctions, permitted travel directions, and consistent costs. Traffic observations also need a verified match to local roads before they can inform route choices.

Require normalized line segments with a known coordinate system, positive modeled speeds, and explicit direction codes. Inputs must already be split at intended junctions. Exact shared endpoints connect; interior crossings, near misses, and bridge or tunnel separation require upstream preparation.
Derive vertices from segment endpoints and compute geodesic lengths in meters. Convert length and modeled speed to seconds, assigning a prohibited cost to closed or disallowed directions. Graph rebuild checks prevent routing against stale input.
Use directed Dijkstra routing and preserve the order and orientation of route geometry. Handle unreachable trips explicitly, bound vertex snapping by distance, and query vertices reachable within a modeled time budget.
Keep the Python traffic collector separate from the graph. It bounds requests and retries, validates flow and incident responses, writes a batch atomically, and filters observations by freshness. The default dry run makes no database connection or API request.
Recorded local checks cover direction rules, closures, geometry order, invalid inputs, stale graphs, observation writes and rollback, and consistent reads during a concurrent rebuild. These checks used an isolated database and synthetic data.
Hosted Python checks passed on Windows and Linux with Python 3.11 and 3.13. They exercised mocked HTTP/database calls and the collector dry run; database integration was checked separately on Windows.
The original project was supplied by its author. Review revisions, test fixtures, and documentation were developed with Codex assistance.

← Back to projects