Extension Installation

pgRouting requires PostGIS as a prerequisite dependency.

Cloud Hosting

For systems with existing PostgreSQL and PostGIS installations:

sudo apt install postgresql-9.3-pgrouting

Windows Systems

Download from: http://download.osgeo.org/postgis/windows/

Creating Extensions

Enable the required extensions in your PostgreSQL database:

CREATE EXTENSION PostGIS;
CREATE EXTENSION pgRouting;

Practical Example

Creating Network Edge Table

CREATE TABLE network_edges (
    edge_id BIGSERIAL PRIMARY KEY,
    direction VARCHAR,
    start_node BIGINT,
    end_node BIGINT,
    forward_cost FLOAT,
    backward_cost FLOAT,
    capacity BIGINT,
    reverse_capacity BIGINT,
    category INTEGER,
    reverse_category INTEGER,
    x_start FLOAT,
    y_start FLOAT,
    x_end FLOAT,
    y_end FLOAT,
    geom geometry
);

Populating Edge Data

INSERT INTO network_edges (
    category, reverse_category,
    forward_cost, backward_cost,
    capacity, reverse_capacity,
    x_start, y_start,
    x_end, y_end) VALUES
(3, 1, 1, 1, 80, 130, 2, 0, 2, 1),
(3, 2, -1, 1, -1, 100, 2, 1, 3, 1),
(2, 1, -1, 1, -1, 130, 3, 1, 4, 1);

Generating Geometry and Connectivity

UPDATE network_edges SET 
    geom = ST_MakeLine(ST_Point(x_start, y_start), ST_Point(x_end, y_end)),
    direction = CASE 
        WHEN (forward_cost > 0 AND backward_cost > 0) THEN 'Bidirectional'
        WHEN (forward_cost > 0 AND backward_cost < 0) THEN 'Forward_Only'
        WHEN (forward_cost < 0 AND backward_cost > 0) THEN 'Reverse_Only'
        ELSE 'Invalid' 
    END;

Building Network Topology

SELECT pgr_createTopology('network_edges', 0.001);

Shortest Path Query

SELECT * FROM pgr_dijkstra(
    'SELECT edge_id, start_node, end_node, forward_cost, backward_cost FROM network_edges',
    4, 8
);

pgRouting Function Usage

Function Signatures

pgr_dijkstra(edges_sql, start_vertex, end_vertex [, directed])
pgr_dijkstra(edges_sql, start_vertex, end_vertices [, directed])
pgr_dijkstra(edges_sql, start_vertices, end_vertex [, directed])
pgr_dijkstra(edges_sql, start_vertices, end_vertices [, directed])

Parameters

Edge SQL Requirements

Result Columns

Implementation Example

Topology Creation

SELECT pgr_createTopology(
    'pipeline_network', 
    0.001, 
    'geometry_column', 
    'edge_id', 
    'source_node', 
    'target_node'
);

Path Query

SELECT * FROM pgr_dijkstra(
    'SELECT edge_id, source_node, target_node, length AS forward_cost FROM pipeline_network',
    2010002150, 2010002551
);