PostgreSQL / pgRouting

Important

The content of this page is outdated and is for archiving purposes only.

Create a routing database

We will create a database “routing” and load the PostGIS and pgRouting functions.

createdb -U postgres -E UNICODE routing
createlang -U postgres plpgsql routing
cd /usr/share/postgresql/8.2/contrib/
psql -U postgres -f postgis-1.2.1/lwpostgis.sql routing
psql -U postgres -f postgis-1.2.1/spatial_ref_sys.sql routing

The sql-data will probably be in /usr/share/postlbs/

psql -U postgres -f routing_core.sql routing
psql -U postgres -f routing_core_wrappers.sql routing
psql -U postgres -f routing_topology.sql routing
psql -U postgres -f matching.sql routing

Load routing data

The data we use for routing is the Geobase “National Road Network” dataset for British Columbia (nrn_rrn_bc_shp_en.zip). It is available in shape file format and can be loaded into PostgreSQL using “shp2pgsql”. A long list of attributes comes with the road network data, but most of them do not contain data. For this routing workshop we will only keep “gid”, “id” and “the_geom”.

To overlay the route with Google Maps the data has been reprojected to EPSG 54004 meanwhile you better use: EPSG 900913)

        Column    |       Type       | Modifiers
--------------+------------------+-----------
 gid          | integer          |
 id           | integer          |
 the_geom     | geometry         |

Import a clip (Victoria Downtown area) of the British Columbia road network data.

A table named “victoria” will be created automatically.

psql -U postgres routing
\i /home/pgrouting/RoutingData/victoria.sql

(The network extent is “-13737893, 6141906, -13728396, 6151394”)

Add the geometry column:

ALTER TABLE victoria RENAME COLUMN the_geom TO geom;
SELECT AddGeometryColumn('victoria','the_geom',54004,'MULTILINESTRING',2);
UPDATE victoria SET the_geom=geom;
ALTER TABLE victoria DROP COLUMN geom;

Prepare routing table for Dijkstra

Add source, target and length column

ALTER TABLE victoria ADD COLUMN source integer;
ALTER TABLE victoria ADD COLUMN target integer;
ALTER TABLE victoria ADD COLUMN length double precision;

Create network topology

SELECT assign_vertex_id('victoria', 0.001, 'the_geom', 'gid');
UPDATE victoria SET length = length(the_geom);

Create indexes for source, target and geometry column

CREATE INDEX source_idx ON victoria(source);
CREATE INDEX target_idx ON victoria(target);
CREATE INDEX geom_idx ON victoria USING GIST(the_geom GIST_GEOMETRY_OPS);

Run Shortest Path Dijkstra query

shortest_path( sql text,
                   source_id integer,
                   target_id integer,
                   directed boolean,
                   has_reverse_cost boolean )

(Source and target IDs are node IDs.)

Shortest path core function

SELECT * FROM shortest_path('
                SELECT gid as id,
                         source::integer,
                         target::integer,
                         length::double precision as cost
                        FROM victoria',
                238, 1455, false, false);
 vertex_id | edge_id |       cost
-----------+---------+------------------
           238 |   76619 | 172.172139617447
          1051 |   80792 | 309.209732132692
           632 |   22142 | 275.695065878201
           ... |     ... | ...

Wrapper function without bounding box

SELECT gid, AsText(the_geom) AS the_geom
        FROM dijkstra_sp('victoria', 238, 1455);
  gid   |                              the_geom
--------+---------------------------------------------------------------
        484 | MULTILINESTRING((-13735834.0196717 6151280.78177026, ... ))
  13944 | MULTILINESTRING((-13734179.5114759 6150720.27269911, ... ))
  22142 | MULTILINESTRING((-13733851.6421797 6149933.91231484, ... ))
        ... | ...

Wrapper function with bounding box

SELECT gid, AsText(the_geom) AS the_geom
        FROM dijkstra_sp_delta('victoria', 238, 1455, 3000);

Prepare routing table for A-Star

Add x1, y1 and x2, y2 column

ALTER TABLE victoria ADD COLUMN x1 double precision;
ALTER TABLE victoria ADD COLUMN y1 double precision;
ALTER TABLE victoria ADD COLUMN x2 double precision;
ALTER TABLE victoria ADD COLUMN y2 double precision;
UPDATE victoria SET x1 = x(startpoint(the_geom));
UPDATE victoria SET y1 = y(startpoint(the_geom));
UPDATE victoria SET x2 = x(endpoint(the_geom));
UPDATE victoria SET y2 = y(endpoint(the_geom));

Note

“endpoint()” function fails for some versions of PostgreSQL (ie. 8.2.5, 8.1.9). A workaround for that problem is using the “PointN()” function instead:

UPDATE victoria SET x1 = x(PointN(the_geom, 1));
UPDATE victoria SET y1 = y(PointN(the_geom, 1));
UPDATE victoria SET x2 = x(PointN(the_geom, NumPoints(the_geom)));
UPDATE victoria SET y2 = y(PointN(the_geom, NumPoints(the_geom)));

Run Shortest Path A-Star query

shortest_path_astar( sql text,
                   source_id integer,
                   target_id integer,
                   directed boolean,
                   has_reverse_cost boolean )

(Source and target IDs are node IDs.)

A-Star core function

SELECT * FROM shortest_path_astar('
                SELECT gid as id,
                         source::integer,
                         target::integer,
                         length::double precision as cost,
                         x1, y1, x2, y2
                        FROM victoria',
                238, 1455, false, false);

Wrapper function with bounding box

SELECT gid, AsText(the_geom) AS the_geom
        FROM astar_sp_delta('victoria', 238, 1455, 3000);

Prepare routing table for Shooting-Star

Add x1, y1 and x2, y2 column

ALTER TABLE victoria ADD COLUMN reverse_cost double precision;
UPDATE victoria SET reverse_cost = length;
ALTER TABLE victoria ADD COLUMN to_cost double precision;
ALTER TABLE victoria ADD COLUMN rule text;

Run Shortest Path Shooting-Star query

shortest_path_shooting_star( sql text,
                   source_id integer,
                   target_id integer,
                   directed boolean,
                   has_reverse_cost boolean )

(Source and target IDs are edge IDs.)

Shooting-Star core function

SELECT * FROM shortest_path_shooting_star('
                SELECT gid as id,
                         source::integer,
                         target::integer,
                         length::double precision as cost,
                         x1, y1, x2, y2,
                         rule, to_cost
                        FROM victoria',
                36339, 22921, false, false);

Wrapper function with bounding box

SELECT gid, AsText(the_geom) AS the_geom
        FROM shootingstar_sp('victoria', 36339, 22921, 5000,
                                 'length',true,true);