Using pgRouting with OS Meridian2

A quick guide to building a pgRouting network from Ordnance Survey's Meridian2 dataset. It assumes you have a working PostgreSQL/PostGIS database. The most current information will be on Github: https://github.com/mixedbredie/meridian2-for-pgrouting

Get the data

Download the Meridian2 shapefiles for the UK from https://www.ordnancesurvey.co.uk/opendatadownload/products.html

Merge the roads

• unzip the downloaded Meridian2 data
• using QGIS you can merge all the roads layers to make one layer.
• Vector Menu > Data Management > Merge Shapefiles To One
• Choose all roads layers
• create merged layer
• Add the new layer to QGIS

Load to the database

• Use the DB Manager in QGIS to load the data to PostGIS. This takes a wee while as there are 1.25 million roads features.
• Optionally create schema: os_m2 (or use public)
• Create table: m2_roads
• Set primary key field: gid
• Set geometry field: geometry
• Set target SRID: 27700
• Check the box to create single part features rather than multipart.
• Check box to create spatial index.

Create a network table

Add some fields that pgRouting needs

ALTER TABLE os_m2.m2_roads
	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;

Build the indices on the source and target fields to speed things up

CREATE INDEX strat_rds_source_idx ON os_m2.m2_roads USING btree(source);
CREATE INDEX strat_rds_target_idx ON os_m2.m2_roads USING btree(target);

Populate the network table

Calculate coordinates for start and end points of lines

UPDATE os_m2.m2_roads 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));

Update the length fields with length of the links

UPDATE os_m2.m2_roads SET
	cost_len = ST_Length(geometry),
	rcost_len = ST_Length(geometry);

Set some average speeds used to calculate travel time. Adjust as required.

UPDATE os_m2.m2_roads SET speed_km =
	CASE WHEN number LIKE 'M%' THEN 60
	WHEN number LIKE 'A%' THEN 50
	WHEN number LIKE 'B%' THEN 40
	ELSE 30 END;

Calculate the travel time for each link

UPDATE os_m2.m2_roads SET
	cost_time = cost_len/1000.0/speed_km::numeric*3600.0,
	rcost_time = cost_len/1000.0/speed_km::numeric*3600.0; 

Update the statistics on the table and clear out the cruft

VACUUM ANALYZE VERBOSE os_m2.m2_roads;

Build the topology

Build your network (took 45 min on my quad core processor)

SELECT pgr_createtopology('os_m2.m2_roads',0.001,'geometry','gid','source','target');

Analyse the network

Analyse your network for errors (took another 45 min). You may get some complaints about the geometry being MULTILINESTRING rather than LINESTRING.

SELECT pgr_analyzegraph('os_m2.m2_roads',0.001,'geometry','gid','source','target');

Get lost

Use the pgRouting Layer plugin in QGIS to load your network table and do some routing.