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.

For context the initial output of the TSRP query is in the following format, and more information can be found here - pgr_trsp - Turn Restriction Shortest Path (TRSP) — pgRouting Manual (2.2) in pgRouting’s documentation.

seq:	row sequence
id1:	node ID
id2:	edge ID (-1 for the last row)
cost:	cost to traverse from id1 using id2

Final Notes

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: pgRouting Project — Open Source Routing Library

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

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/