It is February 2019 and I had written about using pgrouting to display the shortest distance between 2 points on a network using Postgres and the Ordnance Survey Network on my blog CloudyDataBlog.net. Now in 2025 truth be told I had never used this in anger but wanted to revisit the topic and see if I could get it working again. Important for me developments since then.

1. I am now a lot slicker at creating database dumps so I am wanting to create a personal library of useful templates
2. My newer blog format has better support for embedded code
3. AI is better and quicker at formatting that code making it more readable
4. I wanted to have the OS Network for the whole of mainland Britain available

Steps-

1.Go to Ordnance Survey Data Hub and get the free UK Network

osdatahub.os.uk

And the specific one you are looking for is… I chose to download the GEOPACKAGE option

osdatahub.os.uk/downloads/open/OpenRoads

2. Create the database you will import Open Source Data download into

CREATE DATABASE db005osopennetwork;
CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;

3. Import Geopackage into your newly created database

I had some difficulty getting my download into postgres – with the dump file its not totally necessary to repeat but for reference see immediately below.

I didn’t write it down at the time but the successful option was to export to an SQL file oproad_gb.gpkg and then import oproad_gb.sql into a postgis enabled database. Of course I created db005osopennetwork before hand and installed postgis plugin. Remember the following is command line.

ogr2ogr -f "PGDUMP" oproad_gb.sql C:/gpkg/oproad_gb.gpkg
psql -h localhost -p 5435 -U postgres -d db005osopennetwork -f oproad_gb.sql

4. Check that the road_link network has been successfully imported

Link to QGIS and display

5. Clean up the road_link table.

The following SQL may not have been strictly necessary but I had made reference to them in 2019 so I wanted to make sure I did the same things. I will go back and try to determine whether they are necessary or not

Strip out z coordinates.

UPDATE road_link
SET geometry = ST_Force2D(geometry);

Add identity key if one does not exist.

ALTER TABLE road_link
ADD COLUMN id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY;

Next alter the column variable type named length from double precision to numeric. Without this alteration I was getting pgr_createTopology failure.

ALTER TABLE road_link
ALTER COLUMN length SET DATA TYPE numeric;

I then went and added further columns and built a topology using the following commands

ALTER TABLE public.road_link ADD COLUMN source integer;
ALTER TABLE public.road_link ADD COLUMN target integer;
ALTER TABLE public.road_link ADD COLUMN agg smallint default 1;
SELECT pgr_createTopology('public.road_link', 0.0001, 'geometry', 'id');

Note the pgr_createTopology took a significant amount of time to execute, about an hour on the whole of the UK on the computer I used It created a further table called road_link_vertices_pgr. I use this later to identify where to measure between.

6. CREATE TABLE to hold the Shortest Route in

Called here t005shortestopathresult

-- Table: public.t005shortestpathresult

-- DROP TABLE IF EXISTS public.t005shortestpathresult;

CREATE TABLE IF NOT EXISTS public.t005shortestpathresult
(
    path_id integer,
    source_node integer,
    target_node integer,
    edge_cost numeric,
    geom geometry
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.t005shortestpathresult
    OWNER to postgres;

7. Create Necessary Functions to measure distance

The first creates a function that does the measurement taking the parameters source_id and target_id – which can be identified by displaying the road_link_vertices_pgr and labelling up the id field.

The second inserts the records into table t005 so that we can see it later on.

1st Function

-- FUNCTION: public.shortest_path_function(integer, integer)

-- DROP FUNCTION IF EXISTS public.shortest_path_function(integer, integer);

CREATE OR REPLACE FUNCTION public.shortest_path_function(
	source_id integer,
	target_id integer)
    RETURNS TABLE(path_id integer, source_node integer, target_node integer, edge_cost numeric, geom geometry) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT
        edge.id,
        edge.source,
        edge.target,
        edge.length AS cost,
        edge.geometry
    FROM
        pgr_dijkstra(
            'SELECT id, source, target, length AS cost FROM public.road_link',
            source_id,
            target_id,
            directed := false
        ) AS path
    JOIN
        public.road_link AS edge
    ON
        path.edge = edge.id;
END;
$BODY$;

ALTER FUNCTION public.shortest_path_function(integer, integer)
    OWNER TO postgres;

2nd Function inserts constituent lines into table – we could go on to add all the line lengths together to get us a distance measurement. Here simply display the table to have a sense check on the route.

-- FUNCTION: public.insert_shortest_path_result(integer, integer)

-- DROP FUNCTION IF EXISTS public.insert_shortest_path_result(integer, integer);

CREATE OR REPLACE FUNCTION public.insert_shortest_path_result(
	source_id integer,
	target_id integer)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
    -- Truncate the t005shortestpathresult table to remove all existing records
    TRUNCATE TABLE public.t005shortestpathresult RESTART IDENTITY;

    -- Insert values into the t005shortestpathresult table
    INSERT INTO public.t005shortestpathresult (path_id, source_node, target_node, edge_cost, geom)
    SELECT 
        path_id,
        source_node,
        target_node,
        edge_cost,
        geom
    FROM 
        shortest_path_function(source_id, target_id);
END;
$BODY$;

ALTER FUNCTION public.insert_shortest_path_result(integer, integer)
    OWNER TO postgres;

And to run new points simply run the following Select substituting out the nodes you wish to measure between

SELECT public.insert_shortest_path_result(1509247, 995454);

Which gives the following result

And here is a very short measurement to demonstrate where I get the numbering of start and end

SELECT public.insert_shortest_path_result(1509247, 36286);

On my computer for this database it is taking about 20 seconds to measure a distance. From my limited testing this duration does not seem to differ whether its a long or a short distance.

8. pg_dump_template

Finally once I had this working I made a database template dump of the working database with working functions and saved it in the cloud for security. It is named

db_template001_march2025_pg16_uk_osroad_link_shortest_route_measurement.dump

Tables look like this