5

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

8
  • 4
    It's been a while since "Beginners Guide to pgRouting" and I have learned a lot of new things since then. The information is spread over my follow-up posts on pgRouting: underdark.wordpress.com/tag/pgrouting. I'm planning to update the beginners guide with what I've learned in the close future.
    – underdark
    Commented Oct 12, 2011 at 10:38
  • Yes, I've been following all the things you've covered with interest - you're doing almost exactly what I want to do with routing, catchment areas and so on. I just can't find a machine powerful enough to build a network for the whole UK in order to use your stuff in a production environment. I either need more patience, more time or a pre-built network!
    – Michael
    Commented Oct 12, 2011 at 12:56
  • Which OS (Opendata) dataset are you using Merdian2? or Vector District?
    – Mapperz
    Commented Oct 12, 2011 at 14:43
  • 1
    In passing, I think that the OS VectorMap District roads layers are intended for mapping, not routing. They have gaps in the lines, for example where one road crosses under another one. N.
    – nhopton
    Commented Feb 21, 2013 at 9:15
  • 1
    Did you ever resolve this? I'm also trying to create the network table (12 days and counting) from the VectorMap District road layer. I'm beginning to conclude that OS doesn't have an OpenData product which lends itself to routing
    – Adrian
    Commented Oct 21, 2013 at 10:38

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.