🆕pgRouting

pgRouting extends the PostGIS/PostgreSQL geospatial database to provide routing and other network analysis functionality.

Prerequisites

PostgreSQL database
PostGIS spatial extension
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.

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 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

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.

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, load pgAdmin4 (or your chosen database explorer), highlight your database, open a SQL window and run:

CREATE EXTENSION postgis;

ERROR: extension "postgis" already exists
SQL state: 42710

As PostGIS was already enabled (in the above example), you can now run:

CREATE EXTENSION pgrouting;

Query returned successfully in 151 msec.

Now pgRouting is also enabled and ready to be used within the selected database.

Loading the GeoPackage

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:

ogr2ogr -f PostgreSQL "PG:user=<username> password=<password> dbname=<database name>" OSMulti-modalRoutingNetwork.pbf

This will load the three layers of the OS MRN GPKG into the public schema of your database, and create any table indexes included.

Loading the OpenStreetMap PBF

Osm2pgsql

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.

osm2po

Note

osm2po parses OpenStreetMap data and makes it routable in pgRouting.
Java (17+) is required to run osm2po.

To find out if you have Java installed, open PowerShell / Command Prompt (Windows) or Shell (Linux) and type:

java -version

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):

postp.0.class = de.cm.osm2po.plugins.postp.PgRoutingWriter

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:

java -jar osm2po-core-5.5.11-signed.jar cmd=c prefix=osm2po OSMulti-modalRoutingNetwork.pbf

This will create the .sql file version of the .pbf file, which can then be imported to the PostgreSQL database using the following:

psql -h <host name> -U <username> -d <database name> -q -f <filename>.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. 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:

with start as (
    select topo.source
      from osm2po_2po_4pgr as topo
     order by topo.geom_way <-> ST_SetSRID(
              ST_GeomFromText('POINT (-3.53532 50.72046)'), 4326)
     limit 1),
     destination as (
    select topo.source
      from osm2po_2po_4pgr as topo
     order by topo.geom_way <-> ST_SetSRID(
              ST_GeomFromText('POINT (-3.52133 50.72294)'), 4326)
     limit 1)
select ST_Union(geom_way) as route
FROM pgr_dijkstra('SELECT id, source, target, ST_Length(ST_Transform(geom_way, 4326)) AS cost
                     FROM osm2po_2po_4pgr',
                  array(SELECT source FROM start),
                  array(SELECT source FROM destination),
                  directed := false) AS di
JOIN osm2po_2po_4pgr AS pt
  ON di.edge = pt.id;

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:

PreviousGraphHopper NextWorking with OpenStreetMap files

Last updated 1 month ago