The aim of this post is to describe the steps necessary to calculate routes with pgRouting. In the end, we’ll visualize the results in QGIS.
This guide assumes that you have the following installed and running:
- Postgres with PostGIS and pgAdmin
- QGIS with PostGIS Manager and RT Sql Layer plugins
Installing pgRouting
pgRouting can be downloaded from www.pgrouting.org.
Building from source is covered by pgRouting documentation. If you’re using Windows, download the binaries and copy the .dlls into PostGIS’ lib folder, e.g. C:\Program Files (x86)\PostgreSQL\8.4\lib.
Start pgAdmin and create a new database based on your PostGIS template. (I called mine ‘routing_template’.) Open a Query dialog, load and execute the three .sql files located in your pgRouting download (routing_core.sql, routing_core_wrappers.sql, routing_topology.sql). Congratulations, you now have a pgRouting-enabled database.
Creating a routable road network
The following description is based on the free road network published by National Land Survey of Finland (NLS). All you get is one Shapefile containing line geometries, a road type attribute and further attributes unrelated to routing.
pgRouting requires each road entry to have a start and an end node id. We’ll create those now:
First step is to load roads.shp into PostGIS. This is easy using PostGIS Manager – Data – Load Data from Shapefile.
Next, we create start and end point geometries. I used a view:
CREATE OR REPLACE VIEW road_ext AS
SELECT *, startpoint(the_geom), endpoint(the_geom)
FROM road;
Now, we create a table containing all the unique network nodes (start and end points) and we’ll also give them an id:
CREATE TABLE node AS
SELECT row_number() OVER (ORDER BY foo.p)::integer AS id,
foo.p AS the_geom
FROM (
SELECT DISTINCT road_ext.startpoint AS p FROM road_ext
UNION
SELECT DISTINCT road_ext.endpoint AS p FROM road_ext
) foo
GROUP BY foo.p;
Finally, we can combine our road_ext view and node table to create the routable network table:
CREATE TABLE network AS
SELECT a.*, b.id as start_id, c.id as end_id
FROM road_ext AS a
JOIN node AS b ON a.startpoint = b.the_geom
JOIN node AS c ON a.endpoint = c.the_geom
(This can take a while.)
I recommend adding a spatial index to the resulting table.
Calculating shortest routes
Let’s try pgRouting’s Shortest Path Dijkstra method. The following query returns the route from node #1 to node #5110:
SELECT * FROM shortest_path('
SELECT gid AS id,
start_id::int4 AS source,
end_id::int4 AS target,
shape_leng::float8 AS cost
FROM network',
1,
5110,
false,
false)
Final step: Visualization
With RT Sql Layer plugin, we can visualize the results of a query. The results will be loaded as a new layer. The query has to contain both geometry and a unique id. Therefore, we’ll join the results of the previous query with the network table containing the necessary geometries.
SELECT *
FROM network
JOIN
(SELECT * FROM shortest_path('
SELECT gid AS id,
start_id::int4 AS source,
end_id::int4 AS target,
shape_leng::float8 AS cost
FROM network',
1,
5110,
false,
false)) AS route
ON
network.gid = route.edge_id
In my case, this is how the result looks like:
Route from node #1 to node #5110
