A work in progress

I threw together some notes on installing PgRouting on Ubuntu last year sometime but I haven’t really had chance to come back to it and do anything meaningful, until a chance conversation with a client got me thinking about trying again with some Ordnance Survey ITN data. If you do a google search on actually doing anything with ITN data you’ll quickly find out that most people are using ESRI Productivity Suite, or various other components, even if the end result is data in PostgreSQL. So, I thought, how hard can it be? The answer- not that difficult.

The first thing you need to do is get PgRouting installed and configured. If you’re on Ubuntu then the instructions in my post linked to above should get you there- yes there are some command line sections but the code is provided. The instructions work on PostgreSQL 8.4, 9.0 and 9.1 to the best of my knowledge. I’m using 9.1. Pay attention to the link in the update at the top of the page- if you get an error loading the driving distances sql then you do need to manually edit the source code for PgRouting but the updated link shows you the changes you need.

You can download a sample dataset of ITN data from the Ordnance Survey website, and that’s the dataset that I have used here.

To load the data into PostgreSQL, I’m using the extremely cool Loader, which just happens to be an Astun Technology open source script. Grab it and fill in the config file loader.config (in the python folder) to match your environment and your database. For loading itn files you’ll need to adjust the prep_cmd and gfs_file to work with itn data:

prep_cmd=python prepgml4ogr.py $file_path prep_osgml.prep_osmm_itn
gfs_file=../gfs/osmm_itn_postgres.gfs

Don’t forget to set your GDAL_DATA environment variable to point at your install (on my pc this is /usr/share/gdal/1.9 ymmv). Then run loader and you should end up with a nice database with ITN data loaded into it.

From here on in, we’re following the great PgRouting tutorial from here. You’ll need to do a bit of tweaking to get a table in the format that PgRouting is expecting- namely with road names, links, length, class or type, and geometry. I got around this by creating a new table called “ways” (so it matches the examples in the tutorial) in PgAdmin3:

create table ways as select l.*, r.roadname, row_number() over() as uid from roadlink l left join road r on (l.fid = any(r.networkmember_ref))

Then you need to add a couple of additional columns for creating your network topology, use the assign_vertex_id function from pgrouting to fill in these sources and targets for each road link, and for luck, create some indices on the source and target columns:

ALTER TABLE ways ADD COLUMN "source" integer;
ALTER TABLE ways ADD COLUMN "target" integer;
SELECT assign_vertex_id('ways', 0.00001, 'wkb_geometry', 'uid');
CREATE INDEX source_idx ON ways("source");
CREATE INDEX target_idx ON ways("target");

You can then manually run the simplest of the PgRouting shortest path algorithms (Dijkstra) in PgAdmin3 to check it’s all working (substitute in values for your source and target UID):

SELECT * FROM shortest_path('SELECT uid as id,source::integer,target::integer,
   length::double precision as cost FROM ways', uid1, uid2, false, false);

This should return you some data but unless you’re superhuman it won’t mean much until you can visualise it on a map. Fortunately Underdark comes to the rescue again with her PgRouting Layer plugin for QGIS. Hurrah!

To get this to work, you’ll need to ensure that psycopg2 is installed for your version of python. Then clone the git repository linked to above, or download it as a zip file, extract and place it in ~/.qgis/python/plugins. When you start QGIS you should see it as an available plugin.

You need to connect to your database, in the normal way (as if you were going to load a postgis layer) and then the database will be available in the connection dropdown in PgRouting Layer. Fill in the options for your Network table (=ways in my example), Edge id (=uid), From node (=source), To node (=target), Edge costs (=length) and Geometry (=wkb_geometry). Don’t tick the boxes for “directed graph” or “has reverse costs” at the moment, then you can ignore the “Reverse costs” box too. Choose a node to route from, and another to route to, click “Route!” and off you go.

There are many other options in PgRouting, and it should just be a case of figuring out how to join the ITN data together to meet the requirements. That’s next on the list so I’ll post again when I make more progress, but have fun!