Creating a routing network for pgRouting

This recipe shows you how to import a line layer into PostGIS and create a routable network out of it, which can be used by PostGIS's routing library, pgRouting. (For details about pgRouting, please visit the project website at http://docs.pgrouting.org.)

The installation of PostGIS with pgRouting won't be covered in detail here because instructions for the different operating systems can be found on the project's website at http://docs.pgrouting.org/2.0/en/doc/src/installation/index.html.

If you are using Windows, both PostGIS and pgRouting can be installed directly from the Stack Builder application, which is provided by the standard PostgreSQL installation, as described at http://anitagraser.com/2013/07/06/pgrouting-2-0-for-windows-quick-guide/.

Getting ready

To follow this exercise, you need a PostGIS database with pgRouting enabled. In QGIS, you should set up the connection to the database using the New button in the Add PostGIS Layers dialog. Additionally, you should load network_pgr.shp from the sample data.

How to do it…

These steps will create a routable network table in your PostGIS database:

Open DB Manager by navigating to Database | DB Manager.
In Tree on the left-hand side of the dialog, select the database that you want to load the network to.
Go to Table | Import Layer/File to load the network_pgr layer into your database, as shown in the following screenshot:
After network_pgr has been imported successfully, open the SQL window of DB Manager by pressing F2, clicking on the corresponding toolbar button, or in the Database menu.
pgRouting is a little picky when it comes to column data types. You will notice this when you see Error, columns 'source', 'target' must be of type int4, 'cost' must be of type float8. When we import network_pgr with QGIS's DB Manager, it creates the cost column as numeric. As pgRouting won't accept numeric, we will use Table | Edit Table in DB Manager to edit the cost column. Click on the Edit column button and change Type from numeric to double precision (which equals the required float8).
Now that the data is loaded and ready, we can build the network topology. This will create a new network_pgr_vertices_pgr table, which contains the computed network nodes:
```
SELECT pgr_createTopology('network_pgr',0.001);
```
Once this topology is ready, we can test the network by calculating a simple shortest path from the node number 16 to the node number 9:
```
SELECT pgr_dijkstra('SELECT id, source, target, cost
  FROM network_pgr', 16, 9, false, false);
```
This will result in the following:
```
(0,16,6,1)
(1,17,7,1)
(2,5,8,1)
(3,6,9,1)
(4,11,15,1)
(5,9,-1,0)
```

How it works…

The preceding pgr_dijkstra query consists of the following parts:

'SELECT id, source, target, cost FROM network_pgr': This is a SQL query, which returns a set of rows with the following columns:
id: This is the unique edge ID (type int4)
source: This is the ID of the edge source node (type int4)
target: This is the ID of the edge target node (type int4)
cost: This is the cost of the edge traversal (type float8)
16, 9: These are the IDs of the route source and target nodes (type int4)
false: This is true if the graph is directed
false: If true, the reverse_cost column of the SQL-generated set of rows will be used for the cost of the traversal of the edge in the opposite direction

The results of pgr_dijkstra contain the list of network links that our route uses to get from the start to the destination. The four values in reach result row are as follows:

seq: This is the sequence number, which tells us the order of the links within the route starting from 0
id1: This is the node ID
id2: This is the edge ID
cost: This is the cost of the link (can be distance, travel time, a monetary value, or any other measure that you chose)

Table of Contents for
QGIS: Becoming a GIS Power User

Creating a routing network for pgRouting

Getting ready

How to do it…

How it works…

See also

Table of Contents for QGIS: Becoming a GIS Power User

Creating a routing network for pgRouting

Getting ready

How to do it…

How it works…

See also

Table of Contents for
QGIS: Becoming a GIS Power User