Last active
March 9, 2021 01:38
-
-
Save JamesHarrison/09b68a11e1efacaa10b98f169772c6a1 to your computer and use it in GitHub Desktop.
Using OS Open Roads in PostGIS (from Geopackage)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment