Using OS Open Roads in PostGIS (from Geopackage)

open-roads-prep.sql

-- All credit to Marcus Young for the bulk of this:
-- https://www.marcusyoung.co.uk/using-pgrouting-with-the-os-open-roads-dataset
-- However, OS now provide Open Roads as a Geopackage dataset.
-- Don't use shapefile! Avoids classes of problems - no merge-and-dedupe needed, no rewriting column names, etc.
-- We import directly from Geopackage with the following command (using ogr2ogr from GDAL):
-- ogr2ogr -lco SCHEMA=os -F PostgreSQL PG:"your-connection-params" oproad_gb.gpkg 
-- Only noteworthy thing - this creates an id column and a fid column. id is basically serial; fid is OS' reference.
-- In some cases it may be preferable to drop id, rename fid to id, etc - we keep both.

CREATE INDEX roadnode_fid_idx ON os.roadnode USING btree (fid); 
CREATE INDEX roadnode_id_idx ON os.roadnode USING btree (id); 

-- 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.
-- add souce and target columns
ALTER TABLE os.roadlink
    ADD COLUMN source integer, 
		ADD COLUMN target integer;

-- populate source and target fields using unique id from roadnodes table
UPDATE os.roadlink l SET source = n.fid FROM os.roadnode n WHERE
l.startnode = n.id;

UPDATE os.roadlink l SET target = n.fid FROM os.roadnode n WHERE
l.endnode = n.id;


-- Add columns that will be populated with road speed and time cost
ALTER TABLE os.roadlink
    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 os.roadlink  
    RENAME COLUMN length TO cost_len;
		
-- Create and populate rcost_len (same as cost_len)
ALTER TABLE os.roadlink
	ADD COLUMN rcost_len double precision;
	
UPDATE os.roadlink 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 os.roadlink SET speed_mph =
    CASE
	WHEN roadfunction = 'A Road' AND formofway = 'Single Carriageway' THEN 45
	WHEN roadfunction = 'A Road' AND formofway = 'Dual Carriageway' THEN 50
	WHEN roadfunction = 'A Road' AND formofway = 'Collapsed Dual Carriageway' THEN 50
	WHEN roadfunction = 'A Road' AND formofway = 'Slip Road' THEN 40
	WHEN roadfunction = 'B Road' AND formofway = 'Single Carriageway' THEN 40
	WHEN roadfunction = 'B Road' AND formofway = 'Dual Carriageway' THEN 45
	WHEN roadfunction = 'B Road' AND formofway = 'Collapsed Dual Carriageway' THEN 45
	WHEN roadfunction = 'B Road' AND formofway = 'Slip Road' THEN 30
	WHEN roadfunction = 'Motorway' AND formofway = 'Single Carriageway' THEN 65
	WHEN roadfunction = 'Motorway' AND formofway = 'Dual Carriageway' THEN 65
	WHEN roadfunction = 'Motorway' AND formofway = 'Collapsed Dual Carriageway' THEN 65
	WHEN roadfunction = 'Motorway' AND formofway = 'Slip Road' THEN 50
	WHEN roadfunction = 'Minor Road' AND formofway != 'Roundabout' THEN 30
	WHEN roadfunction = 'Local Road' AND formofway != 'Roundabout' THEN 25
	WHEN roadfunction = 'Local Access Road' AND formofway != 'Roundabout' THEN 20
	WHEN roadfunction = 'Restricted Local Access Road' AND formofway != 'Roundabout' THEN 20
	WHEN roadfunction = '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 os.roadlink where speed_mph is null;

-- calculate the cost_time field - here I have calculated estimated journey time in minutes for each link
UPDATE os.roadlink 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 os.roadlink
    ADD COLUMN x1 double precision,
		ADD COLUMN y1 double precision,
		ADD COLUMN x2 double precision,
		ADD COLUMN y2 double precision;

UPDATE os.roadlink 
	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 roadlink_source_idx ON os.roadlink USING btree(source);
CREATE INDEX roadlink_target_idx ON os.roadlink USING btree(target);

-- index id

CREATE INDEX roadlink_fid_idx ON os.roadlink USING btree (fid); 
CREATE INDEX roadlink_id_idx ON os.roadlink USING btree (id); 


-- spatial index
CREATE INDEX roadlink_geom_idx ON os.roadlink USING gist (geom);
-- note roadnode isn't strictly needed for routing stuff, but makes life faster if you're doing kNN to find a routing start point
CREATE INDEX roadnode_geom_idx ON os.roadnode USING gist (geom);
-- cluster based on roadlinks_the_geom_idx to improve routing performance
-- see http://revenant.ca/www/postgis/workshop/indexing.html
CLUSTER os.roadlink using roadlink_geom_idx;

-- clean-up the table
VACUUM (ANALYZE, VERBOSE) os.roadlink;