pgrouting-openroads.sql

-- Tested using PostgreSQL 11.1, PostGIS 2.5.0, and pgRouting 2.6.1

-- This SQL script expects two tables generated by importing the roadlink and roadnode shapefiles
-- from the Ordnance Survey Open Roads dataset. It is assumed that the tables are named 'roadlinks'
-- and 'roadnodes' and that they are located within a schema named 'openroads'.
-- The tables should have an id field consisting of unique integer numbers.
-- Roadlinks must be linestring and not multilinestring.

-- Due to the size of the roadlinks table this script may take some time to complete
-- (30 minutes on my workstation with a local postgreSQL database).


-- Create initial indexes to speed up identifying duplicates
 
CREATE INDEX roadnodes_id_idx ON openroads.roadnodes USING btree (id); 
CREATE INDEX roadnodes_identifier_idx ON openroads.roadnodes USING btree (identifier); 
 
CREATE INDEX roadlinks_id_idx ON openroads.roadlinks USING btree (id); 
CREATE INDEX roadlinks_identifier_idx ON openroads.roadlinks USING btree (identifier); 

-- Open Roads is now provided with separate shapefiles, each covering 100 x 100 km, and 
-- these need to be merged into a single shapefile prior to import into PostgreSQL.
-- There is overlap between these shapefiles so we now need to find and remove duplicate nodes and links.
-- This can be done using the 'identifier' field.

-- Count and delete duplicates from the roadnodes table

SELECT COUNT
	( * ) 
FROM
	( SELECT id, ROW_NUMBER () OVER ( PARTITION BY identifier ORDER BY id ) AS rnum FROM openroads.roadnodes ) t 
WHERE
	t.rnum > 1;

DELETE FROM openroads.roadnodes
WHERE id IN (SELECT id
              FROM (SELECT id,
                             ROW_NUMBER() OVER (partition BY identifier ORDER BY id) AS rnum
                     FROM openroads.roadnodes) t
              WHERE t.rnum > 1);
	

	
-- count and delete duplicates from the roadlinks table

SELECT COUNT
	( * ) 
FROM
	( SELECT id, ROW_NUMBER () OVER ( PARTITION BY identifier ORDER BY id ) AS rnum FROM openroads.roadlinks ) t 
WHERE
	t.rnum > 1;
	

DELETE FROM openroads.roadlinks
WHERE id IN (SELECT id
              FROM (SELECT id,
                             ROW_NUMBER() OVER (PARTITION BY identifier ORDER BY id) AS rnum
                     FROM openroads.roadlinks) t
              WHERE t.rnum > 1);


-- pgRouting requires the source and target nodes to be integer ids.
-- In the current Open Roads shapefile dataset the start and end nodes are provided in 38 character alphanumeric format.
-- We can create new start and end node ids by looking up the identifier of each node in the roadnodes table
-- and retrieving the unique id of that record.

-- drop roadlinks indexes before updating table for performance reasons

DROP INDEX openroads.roadlinks_id_idx; 
DROP INDEX openroads.roadlinks_identifier_idx; 


-- add souce and target columns
ALTER TABLE openroads.roadlinks
    ADD COLUMN source integer, 
		ADD COLUMN target integer;

-- populate source and target fields using unique id from roadnodes table
UPDATE openroads.roadlinks l SET source = n.id FROM openroads.roadnodes n WHERE
l.startnode = n.identifier;

UPDATE openroads.roadlinks l SET target = n.id FROM openroads.roadnodes n WHERE
l.endnode = n.identifier;


-- Add columns that will be populated with road speed and time cost
ALTER TABLE openroads.roadlinks
    ADD COLUMN speed_mph integer,
    ADD COLUMN cost_time double precision;

-- Rename the existing length column (which is in metres) to cost_len
 ALTER TABLE openroads.roadlinks  
    RENAME COLUMN length TO cost_len;
		
-- Create and populate rcost_len (same as cost_len)
ALTER TABLE openroads.roadlinks
	ADD COLUMN rcost_len double precision;
	
UPDATE openroads.roadlinks SET rcost_len = cost_len;
	

-- update the speed_mph column with average speeds for each road type. These are my initial values
-- based on function and formofway type. You can set these to whatever you like.
UPDATE openroads.roadlinks SET speed_mph =
    CASE
	WHEN function = 'A Road' AND formofway = 'Single Carriageway' THEN 45
	WHEN function = 'A Road' AND formofway = 'Dual Carriageway' THEN 50
	WHEN function = 'A Road' AND formofway = 'Collapsed Dual Carriageway' THEN 50
	WHEN function = 'A Road' AND formofway = 'Slip Road' THEN 40
	WHEN function = 'B Road' AND formofway = 'Single Carriageway' THEN 40
	WHEN function = 'B Road' AND formofway = 'Dual Carriageway' THEN 45
	WHEN function = 'B Road' AND formofway = 'Collapsed Dual Carriageway' THEN 45
	WHEN function = 'B Road' AND formofway = 'Slip Road' THEN 30
	WHEN function = 'Motorway' AND formofway = 'Single Carriageway' THEN 65
	WHEN function = 'Motorway' AND formofway = 'Dual Carriageway' THEN 65
	WHEN function = 'Motorway' AND formofway = 'Collapsed Dual Carriageway' THEN 65
	WHEN function = 'Motorway' AND formofway = 'Slip Road' THEN 50
	WHEN function = 'Minor Road' AND formofway != 'Roundabout' THEN 30
	WHEN function = 'Local Road' AND formofway != 'Roundabout' THEN 25
	WHEN function = 'Local Access Road' AND formofway != 'Roundabout' THEN 20
	WHEN function = 'Restricted Local Access Road' AND formofway != 'Roundabout' THEN 20
	WHEN function = 'Secondary Access Road' AND formofway != 'Roundabout' THEN 15
	WHEN formofway = 'Roundabout' THEN 10
	ELSE 1
	END;
	
-- check every link has a speed assigned

select count (*) from openroads.roadlinks where speed_mph is null;

-- calculate the cost_time field - here I have calculated estimated journey time in minutes for each link
UPDATE openroads.roadlinks SET
	cost_time = (cost_len/1000.0/(speed_mph*1.609344))*60::numeric;
	
-- Populate coordinates of the start and end points of the links (required by the ASTAR function).

ALTER TABLE openroads.roadlinks
    ADD COLUMN x1 double precision,
		ADD COLUMN y1 double precision,
		ADD COLUMN x2 double precision,
		ADD COLUMN y2 double precision;

UPDATE openroads.roadlinks 
	SET x1 = st_x (
	st_startpoint ( geom )),
	y1 = st_y (
	st_startpoint ( geom )),
	x2 = st_x (
	st_endpoint ( geom )),
	y2 = st_y (
	st_endpoint ( geom ));

-- create indexes for source and target columns
CREATE INDEX roadlinks_source_idx ON openroads.roadlinks USING btree(source);
CREATE INDEX roadlinks_target_idx ON openroads.roadlinks USING btree(target);

-- index id
CREATE INDEX roadlinks_id_idx ON openroads.roadlinks USING btree (id);

-- spatial index
CREATE INDEX roadlinks_geom_idx ON openroads.roadlinks USING gist (geom);

-- cluster based on roadlinks_the_geom_idx to improve routing performance
-- see http://revenant.ca/www/postgis/workshop/indexing.html
CLUSTER openroads.roadlinks using roadlinks_geom_idx;

-- clean-up the table
VACUUM (ANALYZE, VERBOSE) openroads.roadlinks;