Driving Distance Animations with pgRouting - a how to

Checklist

• QGIS with the Time Manager plugin installed.
• a PostgreSQL database with PostGIS and pgRouting extensions installed.
• access to said database through psql or pgAdmin3/4

A quick and dirty workflow to create some animations using a road network and some driving distance calculations. I won't go through the process of buiding your routing topology - there are examples here and here to get you up and running with open data. You should have two tables in the database - your_network and your_network_vertices_pgr - once you've completed the load and build process.

pgRouting comes with a number of built in routing functions one of which is the Driving Distance function. This function extracts all the nodes (and edges) from the network that are within a specified distance of a specified start point.

For the source features you can either select a node on the network that is closest to your area of interest and use the node id in your query or you can use an existing dataset and assign each feature a node id from the nearest node on the network. The query below creates a view of the point data in the school table (adjust as required) with the nearest network node id. You can then just pick the school and the node id you want to use.

CREATE OR REPLACE VIEW view_school_nodes AS
SELECT DISTINCT ON (a.schoolname) a.schoolname, b.id AS node, ST_Distance(a.geometry, b.the_geom) AS distance
FROM schools a, your_network_vertices_pgr b
  WHERE ST_DWithin(a.geometry, b.the_geom, 120)
ORDER BY a.schoolname, ST_Distance(a.geometry, b.the_geom);

The results of the nearest node query:

schoolname ; node ; distance
Aberlemno Primary School ; 58810 ; 27.8567765543682
Airlie Primary School ; 60834 ; 80.8949936646267
Andover Primary School ; 49398 ; 45.9162068337352
Arbroath Academy ; 55679 ; 42.0475920832573
Arbroath High School ; 56431 ; 26.1166270792929

My network is set up with cost fields - length and time - and I can use these to model journeys across the network. In the example below I am using time (in seconds) to show me how much of the network I can cover in 450 seconds (7 minutes 30 seconds). This number can be set to what ever you like.

SELECT seq, node, edge, cost, agg_cost FROM pgr_drivingDistance(
'SELECT ogc_fid AS id, source, target, cost, reverse_cost 
  FROM your_network',
  58810,  -- this is the start node
  450     -- this is the maximum cost (seconds in this case)
  ); 

The results are presented below.

seq  ;  node  ;  edge  ;  cost  ;  agg_cost
1 ; 58874 ; 67982 ; 4.68003566289286 ; 46.337640876984
2 ; 58727 ; 67931 ; 7.45750698693065 ; 7.45750698693065
3 ; 58728 ; 67932 ; 1.86503491582722 ; 9.32254190275786
4 ; 58793 ; 67946 ; 4.79924189436584 ; 23.1936531505147
5 ; 58794 ; 67939 ; 8.04220277283646 ; 18.3944112561489
6 ; 58808 ; 67928 ; 5.65869396139883 ; 14.9812358641567
7 ; 58809 ; 67930 ; 3.89677598786481 ; 13.2193178906227
8 ; 58810 ; -1 ; 0 ; 0
9 ; 58811 ; 67936 ; 0.800410785992549 ; 10.3522084833124
10 ; 58812 ; 67945 ; 2.09429071038919 ; 9.55179769731984
11 ; 58821 ; 67947 ; 24.9065391490445 ; 43.3009504051934
12 ; 58864 ; 67985 ; 28.4382873234685 ; 41.6576052140912

The field we are interested in is the agg_cost which can be sorted smallest to largest and we'll use this in QGIS to render them in order. But before we get there we need some geometry to work with. We need to join the results of the driving distance calculation to the network geometry. Then we'll create a view to load the data into QGIS. These views can be created using pgAdmin or through the DB Manager in QGIS.

To get the edge or line geometry do this:

SELECT seq, node, edge, cost, agg_cost, l.geometry FROM pgr_drivingDistance(
'SELECT ogc_fid AS id, source, target, cost, reverse_cost 
  FROM your_network',
  58810,  -- this is the start node
  450     -- this is the maximum cost (seconds in this case)
  ) AS foo
  LEFT JOIN your_network l ON l.ogc_fid = foo.edge; 

To get the node geometry do this:

SELECT seq, node, edge, cost, agg_cost, l.the_geom FROM pgr_drivingDistance(
'SELECT ogc_fid AS id, source, target, cost, reverse_cost 
  FROM your_network',
  58810,  -- this is the start node
  450     -- this is the maximum cost (seconds in this case)
  ) AS foo
  LEFT JOIN your_network_vertices_pgr l ON l.id = foo.node; 

Turn these results into a view by adding the create view statement (make up your own name):

CREATE OR REPLACE VIEW view_dd_lines_58810_450 AS
SELECT seq, node, edge, cost, agg_cost, l.geometry FROM pgr_drivingDistance(
'SELECT ogc_fid AS id, source, target, cost, reverse_cost 
  FROM your_network',
  58810,  -- this is the start node
  450     -- this is the maximum cost (seconds in this case)
  ) AS foo
  LEFT JOIN your_network l ON l.ogc_fid = foo.edge; 

To get the node geometry do this:

CREATE OR REPLACE VIEW view_dd_nodes_58810_450 AS
SELECT seq, node, edge, cost, agg_cost, l.the_geom FROM pgr_drivingDistance(
'SELECT ogc_fid AS id, source, target, cost, reverse_cost 
  FROM your_network',
  58810,  -- this is the start node
  450     -- this is the maximum cost (seconds in this case)
  ) AS foo
  LEFT JOIN your_network_vertices_pgr l ON l.id = foo.node; 

OK, we've got some data with aggregated costs and geometry. Add the views to QGIS and let's style it up. We can style the points and lines using a colour ramp and the aggregated cost field so that colours change with distance. We'll use data-defined colour expressions for this. Open the layer properties and switch to the Style tab. Select the simple line or point symbol and click the data-defined override button to edit.

Copy this expression in:

ramp_color( 'PiYG',  scale_linear( "agg_cost", 0, 450, 0, 1))

This uses the PiGn colour ramp and scales the colour in a linear way based on the values in our data (min: 0, max: 450) to values between 0 and 1. So as costs increase colours will change from purple through white to green. Make sure both the point and line layers are styled like this.

Note: you can use any colour ramp name and even a custom one you designed yourself. Check the Style Manager under Settings to get the list of available colour ramps.

I set the project background colour to black for that classy touch. You can also set the feature blend in the point layer to multiply. Or not - see how you're feeling.

Right, fire up QGIS Time Manager plugin. Hear that? The sound of a quality QGIS plugin loading... Turn the plugin on using the on/off button and click Settings.

Add the point layer. Choose it from the drop down. Choose the cost field as your start time. Set the end time to No end time - accumulate features. Click OK. Repeat for the line layer. In the animation options I set the Show Frame For option to 40 ms for a frame rate of 25. Click OK.

Back in the Time Manager window adjust the time frame size to the value of [max cost / 25]. This will ensure your animation plays out in a second at 25fps. Again adjust as required. Click the play button and see how the animation runs.

Before we export the video, adjust the canvas size to the size of the output video. You can go big by hiding all the panels or smaller by expanding the panels to get the desired dimensions. The images that are exported are the same size as the canvas. Zoom to the extent of you layer to fill the screen. You can rotate the canvas too.

To export the animation click the Export Video button. On Windows the animation is exported as a PNG image sequence. On Mac OSX and Linux you can export directly to video. Choose an output directory and click OK. The Time Manager starts exporting your animation one frame at a time. You can watch the files appearing in the directory or go and get a biscuit. When you get back you will have a directory full of PNG and PNGW (world) files. Delete the PNGW files.

I use Avidemux to create my video output (Windows Movie Maker works well too) and it needs JPG files. Convert the PNG files to JPG. I use the batch converter in Irfanview for that. Once you have some JPG files fire up Avidemux and open the first image in the sequence.

Important: make sure you don't change the file name otherwise it work work. It needs the Name### format.

Avidemux automatically recognises the image sequence and creates the video. Save it to AVI format. This makes for a large file as it is basically all the JPG images bundled into a container and then told to display in order. If you want to upload your animations to the Twitter then you need to convert to MP4. I convert the AVI to OGG or MP4 with VLC.

And a couple of Blue Peter outputs...