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
  • Loading the data
  • Preparing the data
  • Building the Network
  • Example usage of pgRouting
  • Create a Polygon Isochrone for your results
  • Points within the isochrone
  • Further information

Was this helpful?

  1. Code
  2. Routing with pgRouting

Getting started with OS NGD Transport Path features and pgRouting

The OS National Geographic Database (OS NGD) Transport Theme provides a definitive network dataset and topographic depiction of Great Britain’s roads, railways, tracks and paths. These notes provide an overview of how to create a routable network using the road and path featuers from the NGD Transport Theme with pgRouting. They are only intended to provide guidance and may need to be modified to suit specific use cases and system requirements.

PgRouting is an open-source tool that extends the spatial capabilities of the PostGIS extension to the PostgreSQL database to provide routing functions.

Software Requirements

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

Data Requirements

  • OS NGD Transport Network Road Link – network line geometry and attribution describing the link.

  • OS NGD Transport Network Path Link – network line geometry and attribution describing the link.

  • OS NGD Transport Network Connecting Link – line segmentation enabling the connection between road and path; it does not represent a real-world feature.

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.

Loading the data

The first step is to download the data from OS DataHub by using OS Select+Build to create a recipe. These are the table names used for this example, you will need to ensure they match or are substituted correctly when replicating.

OS NGD Collection and Feature Type
GeoPackage
Table name
pgRouting

OS NGD Transport Network Road Link

trn_ntwk_roadlink

trn_ntwk_roadlink

pgrouting.ngd_roadlink

OS NGD Transport Network Path Link

trn_ntwk_pathlink

trn_ntwk_pathlink

pgrouting.pathlink

OS NGD Transport Network Connecting Link

trn_ntwk_connectinglink

trn_ntwk_connectinglink

pgrouting.connectinglink

All OS NGD is available in the GeoPackage format which will be needed to load the data into our PostgreSQL database in order to be prepared for pgRouting. In this example we have created a database schema named ‘pgrouting’ and loaded the data using the DB Manager plugin in QGIS 3.8.1. When loading the data you will need to check the option to Create spatial index as these notes assume the data is indexed.

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:

endnode

varchar

startnode

varchar

endgradeseparation

Int4

0

startgradeseparation

Int4

0

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.-- 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 = connectingnodeid;
update schema.connectinglink set startnode = pathnodeid;

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.description
FROM  schema.ngd_roadlink  a
UNION ALL
SELECT b.geom, b.TOID, b.endnode, b.startnode, b.endgradeseparation, b.startgradeseparation, b.description
FROM schema.pathlink  b
UNION ALL
SELECT c.geom, c.TOID,  c.endnode, c.startnode, c.endgradeseparation, c.startgradeseparation, c.description
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 Links 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 futher 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.description,
         b.id AS source,
         c.id AS target,
         CASE 
         	WHEN description = 'Connecting Link' THEN st_length(a.geom)
         	WHEN description LIKE 'Path%' THEN st_length(a.geom)
         	WHEN EXISTS (SELECT r.presenceofpavement_overall_m FROM pgrouting.ngd_roadlink r 
         	WHERE r.presenceofpavement_overall_m  > 0 )
         	THEN st_length(a.geom)
         	ELSE '-9999'
         END AS cost,
        CASE 
         	WHEN description = 'Connecting Link' THEN st_length(a.geom)
         	WHEN description LIKE 'Path%' THEN st_length(a.geom)
         	WHEN EXISTS (SELECT r.presenceofpavement_overall_m FROM   pgrouting.ngd_roadlink r 
         	WHERE r.presenceofpavement_overall_m  > 0 )
         	THEN st_length(a.geom)
         	ELSE '-9999'
         END AS reverse_cost,
         a.geom AS the_geom
  FROM pgrouting.roads_paths AS a
    JOIN pgrouting.node_table AS b ON CONCAT(a.startnode, a.startgradeseparation) = b.node
    JOIN pgrouting.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);

This has created a network data set comprising the path data with roads that may have a footpath on either side, using the presence of pavement attribute which is part of the NGD Transport Schema 2 data.

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.

Further information

PreviousGetting started with OS NGD Transport Theme and pgRoutingNextData Visualisation External Resources

Last updated 11 months 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