pgRouting extends the PostGIS/PostgreSQL geospatial database to provide routing and other network analysis functionality.
pgAdmin4 (or any other database explorer)
QGIS (Optional). Having this software available gives a few useful options for pgRouting and the handling of the 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.
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 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) and 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, like 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 are a set of rules used to determine the best path between two or more points. Some of the common ones are:
pgr_dijkstra - The classic shortest path algorithm, from one node to another, using a directed or non-directed graph.
pgr_drivingdistance - 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 (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.
In more recent versions of PostgreSQL, both PostGIS and pgRouting are enabled as standard, ready to be used.
To check this, load pgAdmin4 (or your chosen database explorer), highlight your database, open a SQL window and run:
As PostGIS was already enabled (in the above example), you can now run:
Now pgRouting is also enabled and ready to be used within the selected database.
The easiest way to load the GeoPackage (GPKG) into PostgreSQL via ogr2ogr is to use the OSGeo4W Shell that is provided with the QGIS install package:
Before loading the GPKG, check that environment variables have been set up so that PostgreSQL will be recognised by the command prompt. Admin permissions are required to edit these variables.
Add the folder location of your PostgreSQL install to the User->Path and System->Path variables.
Once you have done this, open the Shell (as shown above), and type in ogr2ogr. If everything is setup correctly, you should see:
To then load the OS MRN GPKG into your PostgreSQL database, navigate to the location of the file, and type the following:
This will load the three layers of the OS MRN GPKG into the public schema of your database, and create any table indexes included.
Note
Osm2pgsql imports OpenStreetMap (OSM) data into a PostgreSQL/PostGIS database.
Once the binary is downloaded and extracted, the file path to the osm2pgsql.exe file must be added to the System->Path variable.
Osm2pgsql should now be available to run. You can test this by opening up a new command prompt window and typing osm2pgsql.
Osm2pgsql requires OSM data with the correct sort order. Please refer to Sorting OSM files for more information.
To find out if you have Java installed, open PowerShell / Command Prompt (Windows) or Shell (Linux) and type:
Press Enter. 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] using the link Note box above.
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.
Uncomment the following line (this helps with getting the topology into a format pgRouting likes):
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:
This will create the .sql file version of the .pbf file, which can then be imported to the PostgreSQL database using the following:
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.
The mandatory things pgRouting needs to be able to route with are a routing table, start node and end node. The osm2po method will create those things as part of loading the .pbf file into PostgreSQL.
Note
The other two methods mentioned, ogr2ogr (GPKG) and osm2pgsql (PBF), will get the OS MRN data into the database, but it will require further effort to make it routable.
Once the routing table is created, pgRouting can be run straight away.
For the example below, we used the OS MRN sample data, with the .pbf data being loaded into PostgreSQL via osm2po. We selected a couple of waypoints in Exeter, and used a sample Dijkstra route query:
This is made up of 3 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 id’s, and returns a merged geometry of links that form the route.
When visualised in QGIS, the result should look similar to this:
