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
  • Building the network
  • Create the Node Table
  • Create the Edge Table
  • Adding indicative road speeds
  • Alternative using average speeds
  • Adding Restrictions
  • Turn Restrictions
  • Build one table for all restrictions
  • Example usage of pgRouting
  • Final Notes
  • Further information

Was this helpful?

  1. Code
  2. Routing with pgRouting

Getting started with OS NGD Transport Theme 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 data from the OS 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.

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.

Software Requirements

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

    QGIS with DB Manager plugin installed

Data Requirements

  • OS NGD Transport Network Road Link

  • OS NGD Routing and Asset Management Information (RAMI) Restriction (which includes trn_rami_restriction and trn_rami_restriction_restrictnetworkref)

  • OS NGD RAMI Average and Indicative Speeds

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 RAMI Restriction

trn_rami_restriction

trn_rami_restriction

pgrouting.ngd_restriction

trn_rami_restriction_restrictnetworkref

pgrouting.ngd_restriction_networkref

OS NGD RAMI Average and Indicative Speeds

trn_rami_averageandindicativespeed

trn_rami_averageandindicativespeed

pgrouting.ngd_speeds

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.

Building the network

The network consists primarily of two tables, the node table and the edge table which are created using OS NGD Road Link. The nodes are identified from the End and Start nodes attributes and include grade separation to account for physical separation between links.

Create the Node Table

create table pgrouting.ngd_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 pgrouting.ngd_roadlink a
union
SELECT DISTINCT CONCAT(a.endnode, a.endgradeseparation) AS p FROM pgrouting.ngd_roadlink a 
) foo
GROUP BY foo.p;

CREATE INDEX ngd_node_table_node_idx
on pgrouting.ngd_node_table 
(node);

Create the Edge Table

The edge table becomes the graph that is used for routing, it needs source and target IDs as integers and a cost (and reverse cost) value. These costs allow for a value which essentially quantifies how difficult or easy it is to travel that portion of road in that direction (or in the reverse direction for reverse cost). The cost can be a simple distance, a longer route has a higher cost and a shorter route has a lower cost, or we can involve speeds to have a high cost mean travel takes a long time, or involve restrictions which are a further way of including difficulty of travel into the graph – these additional factors will be addressed in the following sections.

Firstly, let’s make a basic edge table. We’re creating our source and targets using a unique ID for each link, we’ve pulled through several attribute columns from OS NGD Road Link that will be useful to us such as the directionality, route hierarchy, osid and classification. Cost is created based on geometry length and directionality is used to assign a cost and a reverse cost for both directions of travel along a link.

CREATE TABLE pgrouting.ngd_edge_table as
SELECT row_number() OVER (ORDER BY a.fid) AS id,
a.id as fid,
a.name1_text AS name,
a.alternatename1_text AS alt_name,
a.roadclassificationnumber AS ref,
a.roadclassification,
a.routehierarchy,
a.description,
a.directionality, 
a.osid,
b.id AS source,
c.id AS target,
CASE
WHEN directionality = 'In Opposite Direction' THEN a.geometry_length*1000000
ELSE a.geometry_length –- if you are using schema 2 data this is geometry_length_m
END AS cost,
CASE
WHEN directionality = 'In Direction' THEN a.geometry_length*1000000
ELSE a.geometry_length 
END AS reverse_cost,
a.geom AS the_geom 
FROM pgrouting.ngd_roadlink AS a
JOIN pgrouting.ngd_node_table AS b ON CONCAT(a.startnode, a.startgradeseparation) = b.node
JOIN pgrouting.ngd_node_table AS c ON CONCAT(a.endnode, a.endgradeseparation) = c.node;
 
--Create indexes

CREATE UNIQUE INDEX ngd_edge_table_id_idx ON pgrouting.ngd_edge_table (id);

CREATE INDEX ngd_edge_table_source_idx ON pgrouting.ngd_edge_table (source);

CREATE INDEX ngd_edge_table_target_idx ON pgrouting.ngd_edge_table (target);

--Geometry index

CREATE INDEX ngd_edge_table_geom_idx
ON pgrouting.ngd_edge_table
USING gist
(the_geom);

Adding indicative road speeds

Using the indicative speed limits from RAMI Average and Indicative Speeds theme we can make the costs more complex. The shortest route by distance may not be the quickest to travel, the indicative speed limits give the legal limits for travelling along a link. Using these speeds the network can account for faster travel along a route using major roads, such as motorways, rather than one with several residential roads. In this example we have used kilometres per hour have been used for simplicity in future calculations.

To add the speed data we first need a new column. This column is then populated from the table containing the speed data, and joined onto our edge table using the osid we got from Road Link.

alter table pgrouting.ngd_edge_table
add column speed_km integer ;
UPDATE pgrouting.ngd_edge_table as a
    SET speed_km = b.indicativespeedlimit_kph 
from pgrouting.ngd_speeds as b
where a.osid = b.osid ;

Alternative using average speeds

As an alternative to indicative speeds the average speeds for a time of day could be used as this reflects a truer value and account for traffic in a way that indicative speeds cannot. Here values for 4am to 7am are used to populate the speed_km column we created in the previous step. Average speed is dependent on direction, so directionality is used to populate the column by case and again joined by osid to the edge table.

UPDATE pgrouting.ngd_edge_table as a
    SET speed_km = (
    case
    when a.directionality = 'In Direction' THEN b.averagespeed_mf4to7indirection_kph
    WHEN a.directionality = 'In Opposite Direction' THEN b.averagespeed_mf4to7againstdirection_kph
    when a.directionality = 'Both Directions' THEN b.averagespeed_mf4to7indirection_kph
  end)
from pgrouting.ngd_speeds as b
where a.osid = b.osid

If there are null values in the average speed columns, this will affect our time costs so we need to account for them, in this example we have filled these with the indicative speed limits where the average is null.

UPDATE pgrouting.ngd_edge_table 
    SET speed_km = a.indicativespeedlimit_kph 
    from pgrouting.ngd_speeds as a
   where speed_km is null;

Finally, we need to add and update the cost_time and rcost_time columns so we can use the speed data our network. We could have replaced the existing distance-based cost and reverse cost columns; however we may want to keep them for distance based queries or to compare journey times.

-- Add columns for time
alter table pgrouting.ngd_edge_table
  ADD COLUMN cost_time double precision,
  ADD COLUMN rcost_time double precision ;
 
-- Populate columns with speed
 UPDATE pgrouting.ngd_edge_table 
      SET cost_time = ST_Length(the_geom)/1000.0/speed_km::numeric*3600.0,
          rcost_time = ST_Length(the_geom)/1000.0/speed_km::numeric*3600.0
      WHERE directionality IN ('Both Directions');
     
 UPDATE pgrouting.ngd_edge_table
      SET cost_time = ST_Length(the_geom)/1000.0/speed_km::numeric*3600.0,
          rcost_time = ST_Length(the_geom)*10000/1000.0/speed_km::numeric*3600.0
      WHERE directionality IN ('In Direction');    
    
 UPDATE pgrouting.ngd_edge_table
      SET rcost_time = ST_Length(the_geom)/1000.0/speed_km::numeric*3600.0,
          cost_time = ST_Length(the_geom)*10000/1000.0/speed_km::numeric*3600.0
      WHERE directionality IN ('In Opposite Direction');

-- Create indexes on the cost columns
CREATE INDEX ngd_edge_cost_time_idx ON pgrouting.ngd_edge_table (cost_time);
CREATE INDEX ngd_edge_rcost_time_idx ON pgrouting.ngd_edge_table (rcost_time);

Suggestion:

If the purpose is to run queries for a set time of day, it is suggested that you update the cost_time column with new costs for the time of day, using the values from the OS NGD speed data.

Alternatively, If you wish to set up the model for multiple times of day and want to quickly swap between different speeds then we would suggest adding multiple cost time columns for each separate time of day. When querying the data, you then alter your query to refer to the column needed as appropriate.

Adding Restrictions

The next step is to add the data held in the OS NGD Transport Restrictions Theme. Inputting restrictions into the network, allows the model to take into account the features that would impact travel including one-ways, turn restrictions, and other restrictions to vehicular movement. This first step joins the type of restriction found in the restrictions table to the restriction network reference table using osid and restrictionid, we can then address the restrictions by category.

ALTER TABLE pgrouting.ngd_restriction_restrictntwkref ADD COLUMN restriction varchar;

UPDATE pgrouting.ngd_restriction_restrictntwkref AS a
SET restriction = b.restriction FROM
pgrouting.ngd_restriction AS b
WHERE a.restrictionid = b.osid;

Turn Restrictions

Turn restrictions indicate where are vehicle can and cannot turn, for example a no right turn at a junction. They can be linked to their respective road links using their networkreferenceid and the osid in road link.

No Turns

Where the restriction type is ‘No Turn’ then the roadlinksequence will equal 1 on the roadlink being traveled along and the link you are not allowed to turn into is numbered 2 then the restriction is active.

create or replace VIEW pgrouting.ngd_view_hw_nt_links AS
SELECT row_number() OVER () AS gid,
a.networkreferenceid AS from_link,
c.fid AS from_fid,
b.networkreferenceid AS to_link,
d.fid AS to_fid,
a.roadlinkdirection,
a.roadlinksequence AS from_seq,
b.roadlinksequence AS to_seq,
a.restriction,
c.geom 
FROM pgrouting.ngd_restriction_restrictntwkref a
INNER JOIN pgrouting.ngd_restriction_restrictntwkref b ON a.networkreferenceid = b.networkreferenceid 
INNER JOIN pgrouting.ngd_roadlink c ON c.osid = a.networkreferenceid 
INNER JOIN pgrouting.ngd_roadlink d ON d.osid = b.networkreferenceid 
WHERE b.roadlinksequence  = 2
AND a.roadlinksequence = 1
AND a.restriction = 'No Turn';

Mandatory Turns

For Mandatory turns the reverse is true, we need to make connections to other links ‘No Turns’ so that only the Mandatory Turn is allowed, by using similar logic to select these links and restrict access to them.

CREATE OR REPLACE VIEW pgrouting.ngd_view_hw_mt_link1 AS
SELECT a.networkreferenceid,
b.fid :: bigint,
a.roadlinksequence,
a.roadlinkdirection,
(CASE WHEN a.roadlinkdirection = 'In Opposite Direction' AND roadlinksequence = 1 THEN b.startnode 
WHEN a.roadlinkdirection = 'In Direction' AND roadlinksequence = 2 THEN b.startnode
ELSE b.endnode END) AS nodeosid,
b.geom 
FROM pgrouting.ngd_restriction_restrictntwkref a
INNER JOIN pgrouting.ngd_roadlink b ON a.networkreferenceid = b.osid 
WHERE a.restriction = 'Mandatory Turn'
AND a.roadlinksequence = 1;

 
CREATE OR REPLACE VIEW pgrouting.ngd_view_hw_mt_link2 AS
SELECT a.networkreferenceid,
b.fid :: bigint,
a.roadlinksequence,
a.roadlinkdirection ,
(CASE WHEN a.roadlinkdirection = 'In Opposite Direction' AND roadlinksequence = 1 THEN b.startnode
WHEN a.roadlinkdirection = 'In Direction' AND roadlinksequence = 2 THEN b.startnode
ELSE b.endnode END) AS nodeosid,
b.geom
FROM pgrouting.ngd_restriction_restrictntwkref a
INNER JOIN pgrouting.ngd_roadlink b ON a.networkreferenceid = b.osid 
WHERE a.restriction = 'Mandatory Turn'
AND a.roadlinksequence = 2;


CREATE OR REPLACE VIEW pgrouting.ngd_view_hw_mt_nt_links AS
SELECT row_number() OVER () AS gid,
a.networkreferenceid AS from_osid,
a.fid AS from_fid,
b.osid AS to_osid,
b.fid AS to_fid
FROM pgrouting.ngd_view_hw_mt_link1 a
INNER JOIN pgrouting.ngd_roadlink b ON (a.nodeosid = b.startnode OR a.nodeosid = b.endnode)
AND a.networkreferenceid <> b.osid 
AND b.osid NOT IN (SELECT networkreferenceid FROM pgrouting.ngd_view_hw_mt_link2);

No Entry

A No Entry represents where access is completely forbidden between two links.

CREATE OR REPLACE VIEW pgrouting.ngd_view_hw_ne_link AS
SELECT row_number() OVER () AS gid,
a.restriction,
a.trafficsign1,
a.trafficsign2,
b.roadlinkdirection,
b.networkreferenceid,
c.fid,
(CASE WHEN b.roadlinkdirection = 'In Opposite Direction' THEN c.endnode ELSE c.startnode END) AS node_osid, c.geom
FROM pgrouting.ngd_restriction a
INNER JOIN pgrouting.ngd_restriction_restrictntwkref b ON a.osid = b.networkreferenceid 
INNER JOIN pgrouting.ngd_roadlink c ON c.osid = b.networkreferenceid 
WHERE a.trafficsign1 = 'No Entry' or a.trafficsign2 = 'No Entry';


CREATE OR REPLACE VIEW pgrouting.ngd_view_hw_ne_other_links AS
SELECT row_number() OVER () AS gid,
a.osid AS from_osid,
a.fid AS from_fid,
b.networkreferenceid AS to_osid,
b.fid AS to_fid
FROM pgrouting.ngd_roadlink a
INNER JOIN pgrouting.ngd_view_hw_ne_link b ON (b.node_osid = a.startnode OR b.node_osid = a.endnode) AND a.osid <> b.networkreferenceid ;

Build one table for all restrictions

The final step is to insert the views into one table for restrictions. PgRouting requires a specific format for a restriction table. It needs a unique restriction ID (rid column) the from edge link ID (feid), to edge ID (teid) and a via column where there are multiple links in a restriction (such as for an illegal U-turn). Finally, it needs a to_cost column which will initially be empty.

CREATE TABLE pgrouting.ngd_restrictions_table
( rid bigint NOT NULL,
to_cost double precision,
teid bigint,
feid bigint,
via text )
WITH (
OIDS=FALSE
);
COMMENT ON TABLE pgrouting.ngd_restrictions_table IS 'NGD Turn Restrictions';


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


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


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

Update the costs to reflect the restrictions:

The to_cost column is updated with either a high value, such as 9999, or a negative value, to prevent travel along those links of the network in line with the restrictions implemented.

UPDATE pgrouting.ngd_restrictions_table SET to_cost = 9999;

Example usage of pgRouting

Once the network is created it is then ready for use with pgRouting queries or using the pgRouting Layer plugin in QGIS. This example uses the restriction table in a Turn Restriction Shortest Path (TRSP) query. You will need to ensure that the columns used in the query are of the correct type and name so that pgRouting recognises them. For example, where the name ‘cost’ has been changed to cost_time, this is specified in the query. In the example we have not specified the column ‘source’ because our column is already named source, but we have had to cast it to an integer as this is what is required by pgRouting.

The source and target items represent the locations/nodes we wish to travel between. We can use the edge table to find the identifiers of the source and target locations and input these into the query. This is probably easiest to do by viewing the edge table in a GIS with a base map, such as OS Maps API, to locate yourself. However, you can also use a query on the edge table. You may need to join your road links to the edge table so that you can search by road name or USRN.

As the graph is directed the reverse cost values should be used for travel in the opposite direction. Restrictions are queried similarly by referring to the restrictions table where we gave the columns specific names for a similar reason. The via_path column has been formed from the feid, via and teid columns.

create table pgrouting.trsp as
SELECT * FROM pgr_trsp('SELECT id::integer, source::integer, target::integer, cost_time as cost, rcost_time as reverse_cost FROM pgrouting.ngd_edge_table',
5076, 3393, --source id, target id
    true, true,
'select to_cost, teid as target_id, feid||coalesce('',''||via,'''') 
as via_path from pgrouting.ngd_restrictions_table'
);
--rejoin to table
select * from pgrouting.ngd_edge_table et 

The output is then joined back to our edge table, the selected edges can be exported and displayed in a GIS so their geometry and the route can be viewed. We use id2 from the output as that is the edge ID which is joinable to our edge table’s ID.

seq:

row sequence

id1:

node ID

id2:

edge ID (-1 for the last row)

cost:

cost to traverse from id1 using id2

Final Notes

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

Further information

PreviousGetting started with OS MasterMap Highways Network - Paths and pgRoutingNextGetting started with OS NGD Transport Path features and pgRouting

Last updated 1 year ago

Was this helpful?

For context the initial output of the TSRP query is in the following format, and more information can be found here - in pgRouting’s documentation.

There are many further queries that can be run with pgRouting and this code is very adaptable to suit individual use cases. For example, the path network could be included in the network with restrictions placed on motorways to provide routing for pedestrians. More information on the options available with pgRouting is on their website and documentation which can be found here:

PostGreSQL -

PostGIS -

pgRouting -

pgr_trsp - Turn Restriction Shortest Path (TRSP) — pgRouting Manual (2.2)
pgRouting Project — Open Source Routing Library
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/
Page cover image