/ ordnance survey

pgRouting and OS Open Roads

Get the data you need from OS: http://www.ordnancesurvey.co.uk/business-and-government/products/os-open-roads.html

Load the data into PostGIS using QGIS or pgAdmin or ogr2ogr - your choice. This takes some time to process as there are a large number links making up the network: 3.6 million across the UK. I am loading into a local database with a schema called routing.

Once the data has been loaded into the database add some extra columns to the table to hold the routing information. I use PgAdminIII as it has a nice, friendly interface.

ALTER TABLE routing.openroads
  ADD COLUMN source integer,
  ADD COLUMN target integer,
  ADD COLUMN speed_km integer,
  ADD COLUMN cost_len double precision,
  ADD COLUMN rcost_len double precision,
  ADD COLUMN cost_time double precision,
  ADD COLUMN rcost_time double precision,
  ADD COLUMN x1 double precision,
  ADD COLUMN y1 double precision,
  ADD COLUMN x2 double precision,
  ADD COLUMN y2 double precision,
  ADD COLUMN to_cost double precision,
  ADD COLUMN rule text,
  ADD COLUMN isolated integer;

Create the required indices on the source and target fields for the fast finding of the start and end of the route and, optionally, on the class and formofway fields for classification.

CREATE INDEX os_om_openroads_source_idx ON os_om.openroads USING btree(source);
CREATE INDEX os_om_openroads_target_idx ON os_om.openroads USING btree(target);
-- takes a few minutes

CREATE INDEX os_om_openroads_class_idx ON os_om.openroads USING btree(class);
CREATE INDEX os_om_openroads_formofway_idx ON os_om.openroads USING btree(formofway);
-- takes a few more minutes

Populate the line end coordinate fields. This took about an hour on my database.

UPDATE routing.openroads 
SET x1 = st_x(st_startpoint(geometry)),
    y1 = st_y(st_startpoint(geometry)),
    x2 = st_x(st_endpoint(geometry)),
    y2 = st_y(st_endpoint(geometry));

Use the length of the road as the distance cost. This took about an hour to update every link. As I tweak the network I'll probably update the "Not classified" links to have a higher distance cost to force the routing algorithms to use other links.

UPDATE routing.openroads
  SET cost_len = ST_Length(geometry),
    rcost_len = ST_Length(geometry);

Set the average speed depending on road class and the nature of the road using the class and formofway fields. I have set the "Not Classified" links to have a speed of 1km/h which increases the cost of traversing that link. Most "not classified" are paths and private roads and so I have elected to make them less desirable to travel on. Adjust the speeds here as you see fit and note that I have used kilometres per hour and not miles per hour. This took just under an hour to process.

UPDATE routing.openroads SET speed_km = 
  CASE WHEN class = 'A Road' AND formofway = 'Roundabout' THEN 20
  WHEN class = 'A Road' AND formofway = 'Collapsed Dual Carriageway' THEN 60
  WHEN class = 'A Road' AND formofway = 'Dual Carriageway' THEN 60
  WHEN class = 'A Road' AND formofway = 'Single Carriageway' THEN 55
  WHEN class = 'A Road' AND formofway = 'Slip Road' THEN 30
  WHEN class = 'B Road' AND formofway = 'Single Carriageway' THEN 50
  WHEN class = 'B Road' AND formofway = 'Collapsed Dual Carriageway' THEN 55
  WHEN class = 'B Road' AND formofway = 'Slip Road' THEN 30
  WHEN class = 'B Road' AND formofway = 'Roundabout' THEN 20
  WHEN class = 'B Road' AND formofway = 'Dual Carriageway' THEN 55
  WHEN class = 'Motorway' AND formofway = 'Collapsed Dual Carriageway' THEN 70
  WHEN class = 'Motorway' AND formofway = 'Dual Carriageway' THEN 70
  WHEN class = 'Motorway' AND formofway = 'Roundabout' THEN 20
  WHEN class = 'Motorway' AND formofway = 'Slip Road' THEN 30
  WHEN class = 'Motorway' AND formofway = 'Single Carriageway' THEN 60
  WHEN class = 'Not Classified' AND formofway = 'Roundabout' THEN 1
  WHEN class = 'Not Classified' AND formofway = 'Single Carriageway' THEN 1
  WHEN class = 'Not Classified' AND formofway = 'Slip Road' THEN 1
  WHEN class = 'Not Classified' AND formofway = 'Dual Carriageway' THEN 1
  WHEN class = 'Not Classified' AND formofway = 'Collapsed Dual Carriageway' THEN 1
  WHEN class = 'Unclassified' AND formofway = 'Single Carriageway' THEN 30
  WHEN class = 'Unclassified' AND formofway = 'Dual Carriageway' THEN 40
  WHEN class = 'Unclassified' AND formofway = 'Roundabout' THEN 20
  WHEN class = 'Unclassified' AND formofway = 'Slip Road' THEN 30
  WHEN class = 'Unclassified' AND formofway = 'Collapsed Dual Carriageway' THEN 40
  ELSE 1 END;

Then use the speed and length to calulate road link travel time. Another hour to crunch.

UPDATE routing.openroads
  SET cost_time = ST_Length(geometry)/1000.0/speed_km::numeric*3600.0,
  rcost_time = ST_Length(geometry)/1000.0/speed_km::numeric*3600.0;

Once you've done that you can build your network using:

SELECT routing.pgr_createTopology('routing.openroads', 0.001, 'geometry', 'gid', 'source', 'target');

NOTE: This takes a LONG time to run. 19h25m on my reasonably specced machine.

It is always a good idea to analyse your network graph as it will highlight potential errors:

SELECT pgr_analyzegraph('routing.openroads', 0.001, 'geometry', 'gid', 'source', 'target');

And clean up the table after all the changes and updates to it

VACUUM ANALYZE VERBOSE routing.openroads;

OS Open Roads at 1:3000

Then, using QGIS and the PgRouting Layer plugin, load and configure your network and get routing. Do bear in mind thought that this network ignores trivialities such as over- and underpasses, one way streets and no entry restrictions. But, for a relatively detailed and free dataset, it is going to give pretty good ball park results.
pgRouting Layer plugin
Using a time cost
A first route
Using a distance cost
A second route