Getting started with OS MasterMap Highways Network - Paths and pgRouting

OS MasterMap Highways network is a detailed and accurate network data set for Great Britian. It can be used in a variety of routing applications and these notes help get started with using the data for walk time in the open source application pgRouting.

PgRouting extends the spatial capabilities of the PostGIS extension to the PostgreSQL database to provide routing functions. These notes are based on the work of Tim Manners and Ross MacDonald which has been shared on GitHub and provide an overview of how to create a network data set for use with pgRouting.

Software Requirements

PostgreSQL with the PostGIS and pgRouting extensions (pgRouting is packaged with PostGIS from version 2.1)

Data Requirements

OS MasterMap Highways Road and Asset Management Information (RAMI) – this gives the additional features required for routing although it is possible to build a basic network without these

Tables required:

Base data

• RoadLink – network line geometry and attribution describing the link.

• PathLink – network line geometry and attribution describing the link.

• ConnectingLink – line segmentation enabling the connection between road and path; it does not represent a real world feature.

Depending on how the data has been translated from the GML this may be one table for each of these or separate tables for the different elements of each. The instructions work best if you have separated the network references into separate tables.

Before beginning you should also consider how much data you need, the bigger the area then the longer it will take to build and perform analysis.

Preparing the data

Before building the network the 3 sets of links need to be combined into 1 layer, ensuring that they all have the same attribution. To keep this simple we have used the minimum attributes required from each. However, these need to be added to the connecting link as described in the following table:

Name	Type	Length	Precision	Default Value
endnode	Character	20
startnode	Character	20
endgradeseparation	Int4		0
startgradeseparation	Int4		0
formofway	Character	42		'Connecting link'

These layers need to be populated, setting the grade separation as 0 and the connecting node and path node values can be used as the end and start node. As the default value for Formofway is set to ‘Connecting link’ this should already be populated.

-- grade separation is set to 0
update schema.connectinglink set endgradeseparation = 0;
update schema.connectinglink set startgradeseparation = 0;
-- start node to equal pathnode and end connecting node
update schema.connectinglink set endnode = connectingnode ;
update schema.connectinglink set startnode = pathnode;

Once the attribution is populated the 3 layers can be merged into a new layer.

CREATE TABLE schema.roads_paths AS
SELECT a.geom, a.TOID, a.endnode, a.startnode, a.endgradeseparation, a.startgradeseparation, a.formofway 
FROM schema.roadlink  a
UNION ALL
SELECT b.geom, b.TOID, b.endnode, b.startnode, b.endgradeseparation, b.startgradeseparation, b.formofway 
FROM schema.pathlink  b
UNION ALL
SELECT c.geom, c.TOID,  c.endnode, c.startnode, c.endgradeseparation, c.startgradeseparation, c.formofway
FROM schema.connectinglink c;

Add a geometry index to the table

--Geometry index
CREATE INDEX roads_paths_geom_idx
  ON schema.roads_paths 
  USING gist
  (geom);

Building the network

These notes are for guidance, table and field names should be checked to ensure they match.

The first statement generates a node lookup using the startNode and endNode references.

Where appropriate, it introduces additional nodes using the grade separation to ensure the real-world physical separations between RoadLinks are handled accordingly.

CREATE TABLE schema.node_table AS
  SELECT row_number() OVER (ORDER BY foo.p) AS id,
         foo.p AS node
  FROM (
    SELECT DISTINCT CONCAT(a.startnode, a.startgradeseparation) AS p FROM schema.road_paths a
    UNION
    SELECT DISTINCT CONCAT(a.endnode, a.endgradeseparation) AS p FROM schema.road_paths a
  ) foo
  GROUP BY foo.p;

--Create an index on node table

CREATE INDEX node_table_node_idx
  ON schema.node_table
  (node);

The second statement creates the directed graph which can be used for routing.

It utilises the node lookup to generate the required ‘source’ and ‘target’ integer fields; along with adding 'cost' and 'reverse_cost', these re simply based on length and further consideration will be needed depending on the types of road in your area. When building a network for vehicle routing you would incorporate one-ways at this point, however as we are creating a walking network these can be ignored.

It also uses the TOID field in the data to create an id for each link, however to use this as an integer we have stripped OSGB from the start of the value.

CREATE TABLE schema.edge_table AS
  SELECT row_number() OVER (ORDER BY ltrim(a.toid, 'osgb')) AS id, 
         a.formofway,
         b.id AS source,
         c.id AS target,
         st_length(a.geom) AS cost,
         st_length(a.geom) AS reverse_cost,
         a.geom AS the_geom
  FROM paths.roads_paths AS a
    JOIN schema.node_table AS b ON CONCAT(a.startnode, a.startgradeseparation) = b.node
    JOIN schema.node_table AS c ON CONCAT(a.endnode, a.endgradeseparation) = c.node;
--Create indexes
CREATE UNIQUE INDEX edge_table_id_idx ON schema.edge_table (id);
CREATE INDEX edge_table_source_idx ON schema.edge_table (source);
CREATE INDEX edge_table_target_idx ON schema.edge_table (target);

--Geometry index
CREATE INDEX edge_table_geom_idx
  ON schema.edge_table
  USING gist
  (the_geom);

Enhancing the network

The example creates a network which is suitable for use for basic walking routes. However, it assumes that all add roads have a footpath alongside them. It reality we know what Motorways and other trunk roads will not have a footpath and we may also want to use local knowledge to exclude certain roads from the next week. Consideration should also be given to the type of user avoiding features such as steps.

This can be done by either deleting features from the data, this would work for Motorways, or by giving links we want to avoid a negative value, so they are excluded from any analysis. The following example uses OS MasterMap Topography Layer features to identify where a road has a man-made roadside as an indication of a footpath. This will also include other features such as traffic islands and roundabout. As traffic islands can be used as a possible crossing point we have left this as part of the network however in the example have excluded any link where formofway equals roundabout.

As we only want to change the costs of using the roads the first step is to change these to a negative cost.

UPDATE schema.edge_table p  SET cost = -9999, reverse_cost = -9999
 WHERE p.formofway <> 'Path' AND p.formofway <> 'Connecting Link';

Now we are going to use the OS MasterMap Topography Layer to assign a cost to all roads that have a path alongside them.

UPDATE schema.edge_table p SET cost = st_length(p.the_geom), reverse_cost = st_length(p.the_geom)
WHERE id IN 
(SELECT p.id FROM  schema.edge_table p
	CROSS JOIN LATERAL
	(SELECT p.id, b.make, b.descriptivegroup[1] AS descriptivegroup
	FROM schema.topographicarea b
	WHERE  p.formofway <> 'Roundabout' 
	ORDER BY p.the_geom <-> b.geom limit 1) AS cjl
   WHERE cjl.make = 'Manmade' AND cjl.descriptivegroup = 'Road Or Track');

This has created a network data set comprising the path data with roads that may have a footpath on either side.

Example usage of pgRouting

This query calculates how far you can walk in 5 minutes and could be used to identify what facilities are available within walking distance of an address. The first step is to use the network we have created to calculate the walking distance.

CREATE TABLE schema.walk_15mins as
SELECT * FROM pgr_drivingDistance(
    'SELECT id, source, target, cost, reverse_cost FROM schema.edge_table',
    15879,  --source node id from the edge table
    900, --max time in seconds
    true --directed is true
);
--join to the edge table
CREATE TABLE schema.walk_15min_edges as
SELECT * FROM schema.edge_table as a 
join schema.walk_15mins as b on a.id = b.edge;

Create a Polygon Isochrone for your results

The results of this query are presented as a list of roads and paths that are reachable in the time specified. However, you may want to display isochrones for your output and for use in service area analysis. This query takes the output and creates a polygon that represents its boundaries using a concave hull.

CREATE TABLE schema.isochrone as
SELECT St_ConcaveHUll(St_collect(the_geom), 0.5) as geom
from schema.walk_15min_edges;

Points within the isochrone

Finally, to identify what is within the 15 minutes an intersect query can be used. In this example we are identifying all Community buildings near-by.

CREATE TABLE schema.community
SELECT * FROM schema.addressbaseplus AS a, schema.isochrone AS b
WHERE ST_intersects(a.geom, b.geom);

The results can then be displayed graphically in a GIS.

isochrone map created from pgRouting

Further information

https://github.com/mixedbredie/highways-for-pgrouting

https://github.com/tmnnrs/highways-network-pgrouting

PostGreSQL - https://www.postgresql.org/

PostGIS - http://www.postgis.net/

pgRouting - https://pgrouting.org/

http://planet.qgis.org/planet/tag/pgrouting/