# pgRouting [pgRouting](https://pgrouting.org/) extends the PostGIS / PostgreSQL geospatial database to provide routing and other network analysis functionality. ## **What you need** * [PostgreSQL database](https://www.postgresql.org/) * [PostGIS spatial extension](https://postgis.net/) * [pgAdmin4](https://www.pgadmin.org/download/) (or any other database explorer) * [QGIS](https://www.qgis.org/) (optional – see note below) {% hint style="info" %} QGIS provides a few useful options for pgRouting and the handling of OS MRN data: * Part of the QGIS install gives access to a command prompt that comes with some spatial functionality for data management without needing much setup (this will be covered in more detail below). * It can be linked in with the PostgreSQL database to allow for visualising existing tables and pgRouting results. * It gives access to pgRouting in plugin form, allowing routes to be calculated within QGIS using those database tables. {% endhint %} ## **Introduction to graph data and routing** A graph data structure is made up of nodes and edges. With OS MRN, these are represented by the `mrn_ntwk_transportnode` and `mrn_ntwk_transportlink` layers. Nodes are points that represent junctions connecting one or more paths on a network. This is where a decision is made on where to turn next (or stop if you are at the end of the route). All nodes must connect to an edge. Edges connect two or more nodes, and represent the distance between them. They can be directed (for example, one-way restrictions) or undirected (any direction allowed). Their default direction will be in the direction the edge was digitised, from startpoint to endpoint. The distance is a reflection of the cost to travel along that edge. At the base level, this would be the length of the edge (in metres), but can be extended with maths to incorporate things like the speed of travel along the edge. Using other data you may have, cost can be extended even further, for example, to take into account factors that might make a traveller use a particular route more (such as scenery, traffic, points of interest and gradient). With pgRouting, these costs can be applied to each edge in a particular direction, that is, in direction (the direction of digitising) and in reverse direction. This enables the user to have more control on what the routing algorithms look for. ## **Routing algorithms** Routing algorithms are a set of rules used to determine the best path between two or more points. Some of the common ones used are: * [`pgr_dijkstra`](https://docs.pgrouting.org/3.1/en/pgr_dijkstra.html) – The classic shortest path algorithm, from one node to another, using a directed or non-directed graph. * [`pgr_drivingdistance`](https://docs.pgrouting.org/3.1/en/pgr_drivingDistance.html) – Using Dijkstra, finds all nodes that are reachable within a set distance using a directed or non-directed graph. Produces results that can then be visualised as an isochrone polygon. * [`pgr_trsp`](https://docs.pgrouting.org/3.1/en/pgr_trsp.html) (turn restriction shortest path) – Runs similar to Dijkstra, with the addition of using restriction information. With OS MRN, this restriction data is available in the `mrn_ntwk_turnrestriction` layer. These turn restrictions reference the edges from the network layer and are used as a lookup as the algorithm works. ## **Checking that PostGIS and pgRouting are enabled** In more recent versions of PostgreSQL, both PostGIS and pgRouting are enabled as standard, ready to be used. To check this: 1. Load pgAdmin4 (or your chosen database explorer). 2. Highlight your database. 3. Open a SQL window and run: ```sql CREATE EXTENSION postgis; ERROR: extension "postgis" already exists SQL state: 42710 ``` In the example above, PostGIS was already enabled. However, in the example below, pgRouting wasn't enabled, but is now ready to be used within the selected database: ```sql CREATE EXTENSION pgrouting; Query returned successfully in 151 msec. ``` ## **Loading the GeoPackage** The easiest way to load the GeoPackage (GPKG) into PostgreSQL via [ogr2ogr](https://gdal.org/programs/ogr2ogr.html) is to use the OSGeo4W Shell that is provided with the QGIS install package:
QGIS install package menu.

QGIS install package menu.

1. Before loading the GPKG, check that environment variables have been set up so that PostgreSQL will be recognised by the command prompt.\ \&#xNAN;*Admin permissions are required to edit these variables.* 2. Add the folder location of your PostgreSQL install to the `User->Path` and `System->Path` variables. 3. Once you have done this, open the OSGeo4W Shell (as shown above), and type in `ogr2ogr`.\ \&#xNAN;*If everything is setup correctly, you should see:*
OSGeo4W Shell UI showing QGIS>ogr2ogr options.

OSGeo4W Shell UI showing QGIS>ogr2ogr options.

4. To then load the OS MRN GPKG into your PostgreSQL database, navigate to the location of the file, and type the following: {% code overflow="wrap" %} ``` ogr2ogr -f PostgreSQL -progress -gt 65000 -a_srs EPSG:4326 -lco SCHEMA= -lco GEOMETRY_NAME=geometry PG:"dbname= host= port= user= password=" OSMulti-modalRoutingNetwork.gpkg ``` {% endcode %} You could alternatively use the following command (where `/vsizip/` is a file handler that allows reading ZIP archives on-the-fly without decompressing them beforehand): {% code overflow="wrap" %} ``` ogr2ogr -f PostgreSQL -progress -gt 65000 -a_srs EPSG:4326 -lco SCHEMA= -lco GEOMETRY_NAME=geometry PG:"dbname= host= port= user= password=" /vsizip/OSMulti-modalRoutingNetwork.zip/OSMulti-modalRoutingNetwork.gpkg ``` {% endcode %} *This will load the three layers of the OS MRN GPKG into the defined database schema, and create the relevant spatial indexes.* 5. You can now add some further attribute indexes for the unique identifiers: ```sql CREATE INDEX mrn_ntwk_turnrestriction_wayid_idx ON .mrn_ntwk_transportlink USING btree (wayid); CREATE INDEX mrn_ntwk_turnrestriction_nodeid_idx ON .mrn_ntwk_transportnode USING btree (nodeid); CREATE INDEX mrn_ntwk_turnrestriction_relationid_idx ON .mrn_ntwk_turnrestriction USING btree (relationid); ``` 6. With all the indexes added, you can create a basic edge table on which to start routing: ```sql CREATE TABLE .edge_table AS SELECT wayid AS id, name, foot, highway, railway, rail, ferry, toll, junction, route, ford, bridge, tunnel, service, oneway, ST_Length(geometry::geography) AS length, (STRING_TO_ARRAY(nodes, ',')::int[])[1] AS source, -- converts the nodes string to an array and extracts the first index item (STRING_TO_ARRAY(nodes, ',')::int[])[array_upper((STRING_TO_ARRAY(nodes, ',')::int[]), 1)] AS target, -- converts the nodes string to an array and extracts the last index item CASE WHEN oneway = '-1' THEN -1 ELSE ST_Length(geometry::geography) -- returns the geometry length in metres END AS cost, CASE WHEN oneway = 'yes' THEN -1 ELSE ST_Length(geometry::geography) -- returns the geometry length in metres END AS reverse_cost, geometry FROM .mrn_ntwk_transportlink; CREATE UNIQUE INDEX edge_table_id_idx ON .edge_table (id); CREATE INDEX edge_table_source_idx ON .edge_table (source); CREATE INDEX edge_table_target_idx ON .edge_table (target); CREATE INDEX edge_table_geometry_idx ON .edge_table USING gist (geometry); ``` ## **Loading the** Protocolbuffer Binary Format [osm2po](http://osm2po.de/) is a cross platform tool written in Java that can be used to parse OS MRN PBF data and make it routable in pgRouting. 1. To find out if you have Java installed, open PowerShell / Command Prompt (Windows) or Shell (Linux) and type: ``` java -version ``` 2. Press *Enter*.\ \&#xNAN;*If Java is installed, you should see a few lines of text detailing which version you have installed. If you get something along the lines of `"java is not recognized ..."`, please download JVM \[or OpenJDK] from* [*https://adoptium.net/en-GB/*](https://adoptium.net/en-GB/)*.* 3. Once osm2po is downloaded and extracted, open and edit the config file to satisfy your usage requirements (for example, change the `wtr.finalMask = car` line (use *Ctrl+F* to search the file) to `car,foot,bike` travel types). 4. Uncomment the following line (this helps with getting the topology into a format pgRouting likes): ```yaml postp.0.class = de.cm.osm2po.plugins.postp.PgRoutingWriter ``` 5. When this is done, and assuming Java is installed and accessible, open a command prompt and navigate to the location of the .pbf file, and then run the following: {% code overflow="wrap" %} ``` java -jar osm2po-core-5.5.11-signed.jar cmd=c prefix=osm2po OSMulti-modalRoutingNetwork.pbf ``` {% endcode %} *This will create the .sql file version of the .pbf file.* 6. Import the .sql file version of the .pbf file to the PostgreSQL database using the following: ``` psql -h -U -d -q -f .sql ``` *In this newly created table, each record should have a source and target node, and an integer classification. Cost and reverse cost columns are also applied. Cost in this case is worked out using distance (in km) and speed (kmh) defined in the config file.* ## **Running a test route with pgRouting (Dijkstra)** The mandatory things pgRouting needs to be able to route with are a routing table, start node and end node. Once the routing table is created, pgRouting can be run straight away. For the following example, OS MRN sample data has been used, with the GPKG data being loaded into PostgreSQL via **ogr2ogr** (with the SQL run to create basic edge table). A couple of waypoints have been selected in Exeter, and a sample Dijkstra route query can look like the one below: {% code overflow="wrap" %} ```sql WITH start AS ( SELECT source FROM .edge_table ORDER BY geometry <-> ST_SetSRID( ST_GeomFromText('POINT (-3.53532 50.72046)'), 4326) LIMIT 1 ), destination AS ( SELECT source FROM .edge_table ORDER BY geometry <-> ST_SetSRID( ST_GeomFromText('POINT (-3.52133 50.72294)'), 4326) LIMIT 1 ) SELECT ST_Union(geometry) AS route FROM pgr_dijkstra( 'SELECT id, source, target, cost, reverse_cost FROM .edge_table', ARRAY(SELECT source FROM start), ARRAY(SELECT source FROM destination) ) AS di JOIN .edge_table AS pt ON di.edge = pt.id; ``` {% endcode %} This route query is made up of three parts: * First, the 'start' section finds the start node to use from the network that is closest to the point given. This could be fed in to the query in multiple ways, such as clicking on a web map, or using a plugin. * Second, the 'destination' section finds the start node closest to the destination point given. * The final part includes the `pgr_dijkstra` function, reading in the start and destination ids, and returns a merged geometry of links that form the route. When visualised in QGIS, the result should look similar to this:
QGIS UI showing an example query result of a route in Exeter in the map area.

QGIS UI showing an example query result of a route in Exeter in the map area.

It should be noted that the above route query does not take any of the additional OS MRN attribution into account. This means that every edge will be considered when calculating the shortest path. We can therefore apply some additional logic in the `SELECT` statement to facilitate either vehicular or pedestrian routing: * **Vehicular routing** – Determines the best vehicular route where the edges are deemed to be [roads](https://wiki.openstreetmap.org/wiki/Key:highway). ```sql 'SELECT id, source, target, cost, reverse_cost FROM .edge_table WHERE "rail" = ''no'' AND "highway" IN ( ''motorway'', ''motorway_link'', ''trunk'', ''trunk_link'', ''primary'', ''primary_link'', ''secondary'', ''secondary_link'', ''tertiary'', ''tertiary_link'', ''unclassified'', ''residential'' )' ``` * **Pedestrian routing** – Determines where the edges can be traversed by [foot](https://docs.os.uk/osngd/os-premium-download-products/premium-products-overview/networks/os-multi-modal-routing-network/technical-specification/features/transport-link#foot) and whether there is designated infrastructure for walking. ```sql 'SELECT id, source, target, cost, reverse_cost FROM .edge_table WHERE "foot" != ''no'' AND "rail" = ''no'' AND "highway" IS NOT NULL' ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.os.uk/os-downloads/products/transport-network-portfolio/os-multi-modal-routing-network/os-mrn-getting-started-guide/pgrouting.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.