[GIS] How to create a routable road network from Ordnance Survey data

Question

I've been following the "Beginners Guide to pgRouting" in an attempt to create an in-house application that provides drive-time isochrones.
I have a server, postgreSQL and PostGIS and pgRouting – no problem. I have drivers to I can talk to the database from ASP.Net pages – easy again.
I've got the data from OS too – imported shapefiles into the database – all easy.

Now I've run the bits of code in that article and the first two are easy again (and quick) so I've got my Node table and the road_ext view OK. However the third statement that creates the "Network" table is killing me, or more specifically the server. I have been trying with just the OS Grid tile "SO" which is not the most remote part of the UK by any means but still not going to have anything like as many roads as the London / Birmingham / Manchester area tiles surely. I'm running it on a quad-core box with a decent chunk of RAM, it's been running for 15 hours and not done yet. Box is not doing anything else, it's dedicated to the task. Even a little tile like the tip of Scotland (John O'Groats) took about 3 hours.
At this rate I'm looking at over a week to build a network which is ridiculous. Am I doing anything obviously wrong here or is it just going to take that long and I have to deal with it.

Ultimately I want to produce these polygons and overlay them on Bing Maps. If I have gone off-course above, could anyone please suggest an alternate dataset like OSM or something that's ready made I can use?

Thanks,
Michael

Answer 1

I agree that @nhopton's answer solves the OP's problem as VMD is a cartographic product and not a routing one.

For what it's worth though, I have created two routeable networks using PgRouting and OS datasets. I have a full UK "quick and dirty" network made using the OS Strategi shapefiles and a more detailed network of Scottish roads from ITN (Integrated Transport Network). ITN was loaded using Snowflake's GoLoader to import the GML into PostGIS.

For the Strategi network I had it up and running in less than 30 minutes. Download the shapefiles from OS (https://www.ordnancesurvey.co.uk/opendatadownload/products.html) and extract the shapefiles from the zip file. Using QGIS I merged all the roads shapefiles into one large shapefile and then imported this into the PostGIS database (almost 500000 records for the whole UK). I then followed the pgrouting tutorial (http://docs.pgrouting.org/2.0/en/doc/src/tutorial/index.html) to build the network using pgrouting version 2.0. Version 2.0, I found, is a lot quicker than old version 1.0.x in building the topology.

For the ITN network I had to do a bit more work once the data was loaded into PostGIS. I have a couple of functions that generate the grade separation and one way street tables and then another that generates a network table combining the line segments and grade separation / one way tables. Using this table I build the topology and vertices table. This gives me a table I can route across but with errors. There are two areas that need attention after the topology has been built: line segments making up bridges need to be merged together to create a single segment and one way streets need to be checked for correctly digitised line direction.

Solving the first problem, I used an FME LineJoiner transformer to merge all the elevated sections into distinct segments (so up+across+down becomes just one line). The same thing could be done using PostGIS ST_LineMerge, I imagine. These new segments are then inserted into the network replacing the original segments. You can build the network topology again to check.

To fix the problem of one way streets going the wrong way (pgrouting uses digitised line direction) there are some helpful attributes in the ITN tables. The road links that are one way have a line orientation attribute so you can see if the one way goes with or against the digitised direction. It is easy then to select the one way streets that go against the flow. In QGIS I used the Swap Line Direction plugin to flip all the edges in one click (is there a PostGIS function to do this?). Save the table and rebuild the network topology.

Now, you should have bridges of single segments, one way streets all going the correct way so you just need to set up some costs. I set up distance and time costs on the segments and set very high reverse costs on one way streets to help ensure correct routing. You should now be good to go.

ITN has a heap of attribution that my method doesn't use. There are additional turn restrictions and road routing information that can be built in to create a very realistic model. Check the spec here.