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: