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);