PG routing – driving distance

Building on the work of previous posts looking at building routable networks and finding the shortest path in a network, this post will look at creating buffers around nodes based on a cost attribute specifed by the user.

The driving distance function, which comes with pg routing 2.0, creates an alpha shape around a node, normally based on the distance someone could travel from that node, or how far someone could go in a time interval.

For this example, we are looking at where someone could travel in 1km along the network from a central node. The following query selects all the nodes that are within 1 km of node 2000.

SELECT * from "ways_pgr_osm" join (

'SELECT gid as id, source, target, len_miles as cost FROM "ways_osm"'
,2000,1,false,false)) as route on "ways_osm_pgr".id = route.id1

Loading into QGIS gives us this representation with the yellow node representing node 2000. I have symbolised the nodes by distance from node 2000:

To really capture the distance from this node in a useful visual, it would useful to turn these points into a shapefile. To achieve this we can use the pgr_pointsAsPolgon function. Firstly we need to select the query outputs from the ways_osm_pgr table so they match the inputs required in the pgr_pointsAsPolygon function. I have also created the output as a table ‘alphashape’

create table alphashape as SELECT id::int4, ST_X(the_geom)::float8 as x, ST_Y(the_geom)::float8 as y from ways_pgr_osm join (

'SELECT gid as id, source, target, len_miles as cost FROM ways_osm'
,2000,1,false,false)) as route on = route.id1

To load this layer into QGIS both a geom (already selected by the query) and a unique id for each record (not selected by the query) are required. To attach a unique number to each record use

select 1 as a,* from pgr_pointsAsPolygon('SELECT id,x,y from alphashape')

This provides a nice outline to our outer nodes, highlighting the exact area where 1 km of travel on this network will get you.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s