April 27, 2015 2 minutes minutes read Marcus

Preparing the OS Open Roads dataset for use with pgRouting

Advice on preparing the OS Open Roads dataset for use with pgRouting and a SQL script to automate the process

See update

The Ordnance Survey has recently released a new open data roads layer, called "Open Roads". It is currently still described as a beta product but is available for download. It is higher resolution than Meridian 2 (approx scale 1:25,000) and has a network topology and is pretty much routable "out of the box".

Advantages over Meridian 2 and Strategi

There is greater detail in urban areas with more streets represented. This may be useful for urban accessibility studies, giving more accurate road distance measures.

Disadvantages over Meridian 2 and Strategi

It is a very large dataset, with some 3.2 million records, and therefore more difficult to work with - the main problems being query speed. It is possible to download smaller portions of the dataset (25km2 areas), but only in GML format. It has a basic road classification scheme: Motorways, A-roads, B-roads, Unclassified and Not Classified. The final version will include Classified Unnumbered roads which are known unofficially as C-roads. There is a 'formofway' column which identifies the physical form of the road: single carriageway, dual carriageway, roundabout and slip road. The lack of distinction between different types of unclassified road makes the dataset less than ideal for drive time analysis, despite OS stating that it is suitable for "simple drive time analysis". More details are available in the OS guide to the dataset.

Preparing the database for pgRouting

The Open Maps dataset can be downloaded from:

http://www.ordnancesurvey.co.uk/opendatadownload/products.html

The download contains three shapefiles: road nodes; motorway nodes; and road links. Only the road links shapefile is needed to use with pgRouting.

I imported the shapefile into a new PostgreSQL schema (called openroads) using the PostGIS Shapefile and DBF Loader 2.1 within pgAdmin III, using the following settings:

  • encoding set to LATIN1
  • set to generate simple geometries
  • SRID set to 27700

Further information, and a PostgreSQL script with all the queries needed to prepare the database for use with pgRouting can be found in my GitHub repository.