LogoLogo
OS Docs HomeOS NGDOS APIsOS Download ProductsMore than MapsContact Us
  • More than Maps
  • Geographic Data Visualisation
    • Guide to cartography
      • Introduction to cartography
      • Types of maps
      • Symbology
      • Colour
      • Text on maps
      • Generalisation
      • Coordinate reference systems
      • Projections
      • Scale
      • Map legends
      • Map layout
      • Relief representation
      • North arrows
    • Guide to data visualisation
      • Introduction to data visualisation
      • GeoDataViz design principles
      • Types of visualisation
      • Thematic mapping techniques
      • Data visualisation critique
      • Accessible data visualisation
      • Ethical data visualisation
      • Software
      • Data
    • GeoDataViz assets
      • GeoDataViz basemaps
      • Stylesheets
      • GeoDataViz virtual gallery
      • Equal area cartograms
      • How did I make that?
        • Apollo 11 Landing
        • North York Moors National Park, 70 years
        • Snowdonia National Park, 70 years
        • Great Britain's National Parks
        • Great Britain's Islands
        • Great Britain's AONB's and National Scenic Areas
        • Famous shipwrecks of Pembrokeshire
        • Trig pillars today
        • Britain's most complex motorway junctions
      • #30DayMapChallenge
  • Data in Action
    • Examples
  • Demonstrators
    • 🆕Product Viewer
    • Addressing & location demonstrators
      • Address Portfolio overview
      • Which address product should you use?
      • AddressBase
      • AddressBase Core
      • AddressBase Plus
      • AddressBase Premium
      • Address Classifications
      • Addressing Lifecycle
      • OS Emergency Services Gazetteer
      • What are Vertical Streets?
      • Why are there differences in boundaries?
    • Contextual demonstrators
    • Customer best practice
      • Channel Shift
      • Data Management and OS Data Hub
      • End User Licence vs Contractor Licence
      • 🆕 IDs vs Spatial Relationships
      • Why we should capture good quality addresses at source
      • Why we Snap and Trace
    • Network Demonstrators
      • OS Detailed Path Network
      • OS Multi Modal Routing Network
        • OS Multi Modal Routing Network
      • Water Networks overview
      • OS MasterMap Highways Network and OS NGD Speeds
      • OS MasterMap® Highways Network and OS Open Roadsâ„¢
    • OS MasterMap Generation APIs
      • Using the OS Features API
      • Using the OS Features API Archive
      • Using the OS Downloads API
      • Using OS APIs in ESRI Software
    • 🆕OS NGD (National Geographic Database)
      • OS NGD Address
      • OS NGD Boundaries
      • 🆕OS NGD Buildings
        • 🆕Building and Building Access Feature Types
        • Building Part and Building Line Feature Types
      • 🆕OS NGD Geographical Names
      • OS NGD Land
      • OS NGD Land Cover enhancements
      • 🆕OS NGD Land Use
      • OS NGD Land Use enhancements
      • 🆕OS NGD Structures
        • 🆕OS NGD Structures
        • Field Boundaries
      • 🆕OS NGD Transport Features
      • 🆕OS NGD Transport Network
      • OS NGD Transport RAMI
      • OS NGD Water Features
      • OS NGD Water Network
      • OS NGD API - Features
      • Ordering OS NGD data
      • Change only updates
      • OS NGD Versioning
      • Creating a topographic map from OS NGD Data
      • Analytical styling for OS NGD data
    • OS MasterMap® demonstrators
    • 🆕Product & API Comparisons
      • 🆕Comparison of Water Network Products
  • Tutorials
    • GeoDataViz
      • Thematic Mapping Techniques
      • Downloading and using data from the OS Data Hub
      • How to download and use OS stylesheets
      • How to use the OS Maps API
      • Creating a bespoke style in Maputnik
    • GIS
      • Analysing pavement widths
      • Basic routing with OS Open Data and QGIS
      • Walktime analysis using OS Multi-modal Routing Network and QGIS
      • Creating 3D Symbols for GIS Applications
      • Constructing a Single Line Address using a Geographic Address
      • Creating a Digital Terrain Model (DTM)
      • Visualising a road gradient using a Digital Terrain Model
      • Visualising a road gradient using OSMM Highways
    • 🆕APIs
      • 🆕Using OS APIs with EPC API
      • 🆕OS APIs and ArcGIS
  • Deep Dive
    • Introduction to address matching
    • Guide to routing for the Public Sector
      • Part 1: Guide to routing
      • Part 2: Routing software and data options
      • Part 3: Building a routable network
    • Unlocking the Power of Geospatial Data
    • Using Blender for Geospatial Projects
    • A Guide to Coordinate Systems in Great Britain
      • Myths about coordinate systems
      • The shape of the Earth
      • What is position?
        • Types of coordinates
        • We need a datum
        • Position summary
      • Modern GNSS coordinate systems
        • Realising WGS84 with a TRF
        • The WGS84 broadcast TRF
        • The International Terrestrial Reference Frame (ITRF)
        • The International GNSS Service (IGS)
        • European Terrestrial Reference System 1989 (ETRS89)
      • Ordnance Survey coordinate systems
        • ETRS89 realised through OS Net
        • National Grid and the OSGB36 TRF
        • Ordnance Datum Newlyn
        • The future of British mapping coordinate systems
        • The future of British mapping coordinate systems
      • From one coordinate system to another: geodetic transformations
        • What is a geodetic transformation?
        • Helmert datum transformations
        • National Grid Transformation OSTN15 (ETRS89–OSGB36)
        • National Geoid Model OSGM15 (ETRS89-Orthometric height)
        • ETRS89 to and from ITRS
        • Approximate WGS84 to OSGB36/ODN transformation
        • Transformation between OS Net v2001 and v2009 realisations
      • Transverse Mercator map projections
        • The National Grid reference convention
      • Datum, ellipsoid and projection information
      • Converting between 3D Cartesian and ellipsoidal latitude, longitude and height coordinates
      • Converting between grid eastings and northings and ellipsoidal latitude and longitude
      • Helmert transformation worked example
      • Further information
  • Code
    • Ordnance Survey APIs
    • Mapping
    • Routing with pgRouting
      • Getting started with OS MasterMap Highways and pgRouting
      • Getting started with OS MasterMap Highways Network - Paths and pgRouting
      • Getting started with OS NGD Transport Theme and pgRouting
      • Getting started with OS NGD Transport Path features and pgRouting
  • RESOURCES
    • 🆕Data Visualisation External Resources
Powered by GitBook

Website

  • Ordnance Survey

Data

  • OS Data Hub
On this page
  • Software Requirements
  • Data Requirements
  • Building the network
  • Adding restrictions
  • Turn restrictions
  • Example usage of pgRouting
  • Create a Polygon Isochrone for your results
  • Further information

Was this helpful?

  1. Code
  2. Routing with pgRouting

Getting started with OS MasterMap Highways and pgRouting

OS MasterMap Highways network is a detailed and accurate navigable road network data set for Great Britian. It can be used in a variety of routing applications and these notes help get started with the data 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.

Routing information used to define where you can and cannot travel

  • Accessrestriction

  • Restrictionforvehicles

  • Turnrestriction

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.

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.roadlink a
    UNION
    SELECT DISTINCT CONCAT(a.endnode, a.endgradeseparation) AS p FROM schema.roadlink 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' fields for one ways (the wrong way has a very high cost).

It also uses the localid field in the data to create an id for each link, if you data does not contain this you can add it as serial.

CREATE TABLE schema.edge_table AS
  SELECT row_number() OVER (ORDER BY a.localid) AS id, 
         a.roadname AS name,
         a.alternatename AS alt_name,
         a.roadclassificationnumber AS ref,
         a.routehierarchy,
         a.formofway,
         b.id AS source,
         c.id AS target,
         CASE
           WHEN directionality = 'in opposite direction' THEN a.length*1000000
           ELSE a.length
         END AS cost,
         CASE
           WHEN directionality = 'in direction' THEN a.length*1000000
           ELSE a.length
         END AS reverse_cost,
         a.wkb_geometry AS the_geom
  FROM schema.roadlink 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);

Adding restrictions

The previous steps create a network which can be used for routing, however this does not take into account more complex turn features. The next step is to add these to your network.

Turn restrictions

No Turns

The Highways dataset provides tables of turn restrictions with a reference to the road links, the type of restriction and a turn sequence number. The turn restriction table is joined to the road link table and is used to generate the turn restrictions in pgRouting format.

First, we create a view of the links involved in the turn restriction. We select the FROM link with a sequence of 0 and the To link with a sequence number of 1 where the restriction type is NO TURN. The restrictions are represented in the GML as ordered pairs.

N.B. When translated a sequence attribute may be added with values of 0 and 1 to represent the order of the links in the restriction. However you may have translated your link references into an array containing a pair of values, the first should be accessed as 0 and the second 1.

Add the restriction type to the network reference table (this step is required if the network references are stored in a separate table to the turn restriction).

ALTER TABLE schema.turnrestriction_networkref ADD COLUMN restriction character(20); 

UPDATE schema.turnrestriction_networkref AS a
SET restriction = b.restriction FROM 
schema.turnrestriction AS b
WHERE a.toid = b.toid;

CREATE OR REPLACE VIEW schema.view_hw_nt_links AS
SELECT row_number() OVER () AS gid, 
    	a.roadlink_toid AS from_link, 
    	c.localid AS from_fid, 
    	b.roadlink_toid AS to_link, 
    	d.localid AS to_fid, 
    	a.applicabledirection, 
    	a.sequence AS from_seq, 
    	b.sequence AS to_seq, 
    	a.restriction, 
    	c.centrelinegeometry –- this is the name of roadlink geometry column
FROM schema.turnrestriction_networkref a
INNER JOIN schema.turnrestriction_networkref b ON a.toid = b.toid 
INNER JOIN schema.roadlink c ON c.toid = a.roadlink_toid
INNER JOIN schema.roadlink d ON d.toid = b.roadlink_toid
WHERE b.sequence = 1 
AND a.sequence = 0
AND a.restriction = 'No Turn';

Mandatory turns

Mandatory turns are modelled in the dataset as turns which are allowed, giving an entry link and an exit link which, together with the associated node that make up the mandatory turn. To create a restriction to enforce the mandatory turn you need to select all the other links in the turn and restrict access to them.

CREATE OR REPLACE VIEW schema.view_hw_mt_link1 AS
    SELECT a.roadlink_toid,
      b.localid, 
      a.sequence,
      a.applicabledirection, 
      (CASE WHEN a.applicabledirection = 'inOppositeDirection' AND sequence = 0 THEN b.startnode 
        WHEN a.applicabledirection = 'inDirection' AND sequence = 1 THEN b.startnode
        ELSE b.endnode END) AS nodetoid,
      b.geom 
    FROM schema.turnrestriction_networkref a
    INNER JOIN schema.roadlink b ON a.roadlink_toid = b.toid
    WHERE a.restriction = 'Mandatory Turn'
    AND a.sequence = 0;
    
CREATE OR REPLACE VIEW schema.view_hw_mt_link2 AS
    SELECT a.roadlink_toid,
      b.localid, 
      a.sequence,
      a.applicabledirection, 
      (CASE WHEN a.applicabledirection = 'inOppositeDirection' AND sequence = 0 THEN b.startnode 
        WHEN a.applicabledirection = 'inDirection' AND sequence = 1 THEN b.startnode
        ELSE b.endnode END) AS nodetoid,
      b.geom 
    FROM schema.turnrestriction_networkref a
    INNER JOIN schema.highways b ON a.roadlink_toid = b.toid
    WHERE a.restriction = 'Mandatory Turn'
    AND a.sequence = 1;
    
   CREATE OR REPLACE VIEW schema.view_hw_mt_nt_links AS
    SELECT row_number() OVER () AS gid,
      a.roadlink_toid AS from_toid,
      a.localid AS from_fid,
      b.toid AS to_toid,
      b.localid AS to_fid
    FROM schema.view_hw_mt_link1 a
    INNER JOIN schema.roadlink b ON (a.nodetoid = b.startnode OR a.nodetoid = b.endnode)
    AND a.roadlink_toid <> b.toid
    AND b.toid NOT IN (SELECT roadlink_toid FROM schema.view_hw_mt_link2);

No Entry

No entries represent links where access is forbidden and are held in the Access Restrictions table.

CREATE OR REPLACE VIEW schema.view_hw_ne_link AS
    SELECT row_number() OVER () AS gid,
      a.restriction, 
      a.trafficsign, 
      b.applicabledirection, 
      b.atposition, 
      b.roadlink_toid, 
      c.localid,
      (CASE WHEN b.applicabledirection = 'inOppositeDirection' THEN c.endnode ELSE c.startnode END) AS roadnode_toid, c.geom
    FROM schema.accessrestriction a
    INNER JOIN schema.accessrestriction_networkref b ON a.toid = b.toid
    INNER JOIN schema.roadlink c ON c.toid = b.roadlink_toid
    WHERE a.trafficsign = 'No Entry';

   CREATE OR REPLACE VIEW schema.view_hw_ne_other_links AS
    SELECT row_number() OVER () AS gid, 
      a.toid AS from_toid, 
      a.localid AS from_fid, 
      b.roadlink_toid AS to_toid, 
      b.localid AS to_fid
    FROM schema.roadlink a
    INNER JOIN schema.view_hw_ne_link b ON (b.roadnode_toid = a.startnode OR b.roadnode_toid = a.endnode) AND a.toid <> b.roadlink_toid;

Build the restrictions table

This step takes the view previous created to build one table of all restrictions.

       CREATE TABLE schema.restrictions
    (  rid integer NOT NULL,
      to_cost double precision,
      teid integer,
      feid integer,
      via text )
    WITH (
      OIDS=FALSE
    );
    COMMENT ON TABLE schema.restrictions   IS 'Highways Turn Restrictions';
          
 INSERT INTO schema.restrictions(rid,feid,teid)
      SELECT gid AS rid,
      from_fid AS feid,
      to_fid AS teid 
      FROM schema.view_hw_nt_links v
      WHERE v.to_fid <> 0
      AND v.to_fid NOT IN (SELECT DISTINCT t.teid FROM schema.restrictions t WHERE t.rid = v.gid);

INSERT INTO schema.restrictions(rid,feid,teid)
      SELECT gid AS rid,
      from_fid AS feid,
      to_fid AS teid 
      FROM schema.view_hw_mt_nt_links v
      WHERE v.to_fid <> 0
      AND v.to_fid NOT IN (SELECT DISTINCT t.teid FROM schema.restrictions t WHERE t.rid = v.gid);

    INSERT INTO schema.restrictions(rid,feid,teid)
      SELECT gid AS rid,
      from_fid AS feid,
      to_fid AS teid 
      FROM schema.view_hw_ne_other_links v
      WHERE v.to_fid <> 0
      AND v.to_fid NOT IN (SELECT DISTINCT t.teid FROM schema.restrictions t WHERE t.rid = v.gid);    

Example usage of pgRouting

A Driving Distance query finds everywhere along the network that can be reached within a fixed distance, in this example 1.5km, from a chosen starting point. The query uses the cost and reverse cost attribute to manage which networks elements are included in the query. In this example we have used the distance or time cost which were specified during edge table set up. The source node id is the point on the network from which the driving distance is calculated and can either be identified by querying the data in a GIS or in the database.

create table schema.drive_1500m as
SELECT * FROM pgr_drivingDistance(
    'SELECT id, source, target, cost, reverse_cost FROM schema.edge_table',
    1111,  --source node id
    1500, --max distance in metres 
    true --directed is true
);

--Join to the edge table to create a list of all roads within the drive time. This can either be listed as a table or viewed in a GIS
create table schema.drive_1500m_edges as
select
id,
name,
alt_name,
ref,
routehierarchy,
directionality,
osid,
source,
target,
a.cost,
reverse_cost,
the_geom,
from schema.edge_table as a
join schema.drive_1500m 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 that are reachable in the time or distance 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.drive_1500m_edges;

The results can then be displayed graphically in a GIS.

Further information

PreviousRouting with pgRoutingNextGetting started with OS MasterMap Highways Network - Paths and pgRouting

Last updated 1 year ago

Was this helpful?

PostGreSQL -

PostGIS -

pgRouting -

https://github.com/mixedbredie/highways-for-pgrouting
https://github.com/tmnnrs/highways-network-pgrouting
https://www.postgresql.org/
http://www.postgis.net/
https://pgrouting.org/
http://planet.qgis.org/planet/tag/pgrouting/
isochrone map created from pgRouting
Page cover image