There are several methods to do this that have been shared on the Internet but in my view they over-complicate a problem that is actually relatively easy to solve with a few SQL queries. There are two key problems that we need to solve:
I am assuming that you already have PostgreSQL, PostGIS and pgRouting installed and have created a spatial database.
Install the OS Translator II QGIS plugin from Lutra Consulting (https://www.lutraconsulting.co.uk/projects/ostranslator-ii/)
In your spatial database create a schema: os_mm_roads
Launch the OS Translator II plugin and set the following:
We need to convert the alphanumeric node references in the road link table to integers.
-- create new columns for source and target
alter table os_mm_roads.roadlink
add column source int8,
add column target int8;
-- trim off leading characters of node references and populate new columns
update os_mm_roads.roadlink
set source = ltrim(startnodehref, '#osgb')::int8,
target = ltrim(endnodehref, '#osgb')::int8;
When there is grade separation at a node in the roadnode table, the field formofroadnodetitle
has the value ‘pseudo node’ and the field classification
has the value 'Grade Separation'. We first create an alternative UID for these nodes and place them into a new lookup table. Then, for every roadlink where the field startgradeseparation
and/or endgradeseparation
has the value ‘1’, we update the roadlink table and change the target and/or source node to the alternative UID as applicable. Effectively this duplicates the impacted node. Where a road crosses another road the relevant node UIDs of the component links are changed while they remain unchanged for the links underneath.
The principle is illustrated in the diagrams below, where the road comprising of links A and B crosses above the road comprising of links D and E. In the top diagram, representing the native roadlink data structure, pgRouting would consider there to be a road junction at node 4000000004435260. However, we know from the grade separation information that this would be incorrect. In the bottom diagram this has been corrected by altering the target node UID of Link A and the source node UID of Link B to 7000000004435260. There is now no valid route between the upper and lower road.
The SQL code to implement this change is shown below.
-- node id is localid (change from charvar to bigint).
alter table os_mm_roads.roadnode
alter column localid type int8 using localid::int8;
-- create lookup table of the pseudo grade separation nodes with newid
create table os_mm_roads.pseudo_nodes_gs as
select localid, (localid + 3000000000000000) AS newid from os_mm_roads.roadnode where formofroadnodetitle = 'pseudo node' and classification = 'Grade Separation';
-- update roadlink table
update os_mm_roads.roadlink a
set source = b.newid
from os_mm_roads.pseudo_nodes_gs b
where a.startgradeseparation = 1 and a.source = b.localid;
update os_mm_roads.roadlink a
set target = b.newid
from os_mm_roads.pseudo_nodes_gs b
where a.endgradeseparation = 1 and a.target = b.localid;
To route correctly using pgRouting when one-way roads are present, we must specify to use a directed graph in the relevant function, such as pgr_dijkstra()
, and have a column for both cost and reverse_cost. We set either cost or reverse_cost to an extremely high value (e.g. 1000000) to prevent routing the wrong way along an edge. The MM roadlinks table has a field called directionalitytitle
which indicates whether the link is two-way ('both directions') or can only be traversed in the direction of the line (from source to target - ‘in direction’) or can only be traversed in the opposite direction of the line (from target to source - 'in opposite direction'). The SQL code below creates the cost columns and then sets the cost and reverse_cost values.
-- prepare for directed
-- based on field directionalitytitle with the possible values:
-- 'both directions'
-- 'in direction'
-- 'in opposite direction'
alter table os_mm_roads.roadlink
add column cost real,
add column reverse_cost real;
update os_mm_roads.roadlink
set cost = length
where directionalitytitle in ('both directions', 'in direction');
update os_mm_roads.roadlink
set cost = 1000000
where directionalitytitle = 'in opposite direction';
update os_mm_roads.roadlink
set reverse_cost = length
where directionalitytitle in ('both directions', 'in opposite direction');
update os_mm_roads.roadlink
set reverse_cost= 1000000
where directionalitytitle = 'in direction';
SELECT
X.*,
A.formofway,
A.name
FROM
pgr_dijkstra('SELECT ogc_fid as id, source, target, cost, reverse_cost FROM os_mm_roads.roadlink', 4000000023134762, 4000000023104587, directed => true) as X
LEFT JOIN (select ogc_fid, formofway, roadname as name from os_mm_roads.roadlink) AS A ON A.ogc_fid = X.edge
ORDER BY
seq;
The returned route from A to B is shown in the table and map below.
The most direct route using the unprocessed MM roadlinks table is shown in orange. This would involve joining the motorway at a pseudo node with grade separation and travelling the wrong way down the west carriageway. The suggested route using the amended MM roadlinks table is shown in purple (with the route down to the next motorway junction clipped out for clarity). The grade separation and one-way roads are now fully respected.
Base map © OpenStreetMap contributors
For the full SQL script go to the GitHub Repo.