Catchment Areas with pgRouting driving_distance()
In a previous post, I’ve described how to create catchment areas with pgRouting shortest_path() function. The solution described there calculates costs from the starting node (aka vertex) to all other nodes in the network. Depending on the network size, this can take a long time. Especially, if you are only interested in relatively small catchment areas (e.g. 50 km around a node in a network covering 10,000 km) there is a lot of unnecessary calculation going on. This is where you might want to use driving_distance() instead.
Driving_distance() offers a parameter for maximum distance/cost and will stop calculations when the costs exceed this limit. But let’s start at the beginning: installing the necessary functions.
If you have followed my guide to installing pgRouting, you already have some routing functions installed – but not driving_distance(). Weirdly, the necessary SQL scripts are not shipped with the .zip file available on pgRouting’s download page. You need:
Both are available through the project repository at Github. Get them and execute them in your pgRouting-enabled database. Now, you should be ready.
Calculating driving distances
To calculate driving distances, we need a query very similar to shortest_path():
CREATE OR REPLACE FUNCTION driving_distance(
RETURNS SETOF path_result
The only new value is “distance”. That’s the maximum distance/cost you want to be contained in the result set. “distance” has to be specified in the same units as the cost attribute (which is specified in the “sql” text parameter).
Note: In my opinion, the name “(driving) distance” is misleading. While you can use distance as a cost attribute, you’re not limited to distances. You can use any cost attribute you like, e.g. travel time, fuel consumption, co2 emissions, …
The actual query for a catchment area of 100 km around node # 2000 looks like this:
SELECT * FROM driving_distance(' SELECT gid AS id, start_id::int4 AS source, end_id::int4 AS target, shape_leng::float8 AS cost FROM network', 2000, 100000, false, false)
Interpreting the result
These are the first lines of the result set:
The cost attribute contains the total cost of travel from the starting node to the vertex_id node.
We will only be using vertex_id and cost. The use of edge_id is a mystery to me.
Visualizing the result
The easiest way to visualize driving_distance() results is using RT Sql Layer plugin. We need to join the results of driving_distance() with the table containing node geometries:
SELECT * FROM node JOIN (SELECT * FROM driving_distance(' SELECT gid AS id, start_id::int4 AS source, end_id::int4 AS target, shape_leng::float8 AS cost FROM network', 2000, 100000, false, false)) AS route ON node.id = route.vertex_id
If you color the nodes based on the cost attribute, it will look something like this:
Nice, but you’ve not yet defined catchment *areas* – you’ve got a set of points. Visually I can see how they form areas, but you’ve not done that step yet. How would you go from these points to create a set of polygons giving costs between certain ranges?
One approach I recall from some work I did years ago was to create voronoi polygons based on the points and then dissolve on discrete values of the cost value at each point. Did that with Arc/Info version 5 if I remember rightly!
You are right. One possibility would be to use the approach I’ve described in “Creating Catchment Areas With pgRouting and QGIS”: Interpolating a cost raster and then calculating contours.
I’ll get back to this post as soon as possible to add some thoughts on how to create the final equal cost areas.
Yup! Looks like you’ve done this before!
Pingback: A Closer Look at Alpha Shapes in pgRouting | Free and Open Source GIS Ramblings
Do you have advice for creating walking maps around subway/metro stops?
I am hoping to make something similar to these maps, made with ArcGIS 9.
I haven’t read the post yet, but the images look like something you can achieve with pgRouting. The bigger challenge in my experience is creating a good routable network for pedestrians. After creation of the network, the workflow will be same as described in this post. Are you planning to assume constant walking speed on all roads? Then you could simply work with the network link lengths as routing costs.
driving_distance() is very usefull function but its documentation is very confused. I left a few questions on the project page. Maybe the developers will make documentation more clear.