Getting started with OS NGD Transport Theme and pgRouting
Last updated
Website
Ordnance SurveyData
OS Data HubLast updated
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.
PostgreSQL with the PostGIS and pgRouting extensions (pgRouting is packaged with PostGIS from version 2.1)
QGIS with DB Manager plugin installed
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
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 |
---|---|---|---|
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
A No Entry represents where access is completely forbidden between two links.
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.
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.
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.
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.
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
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/