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 * FROM pgr_drivingDistance( '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 * FROM pgr_drivingDistance( 'SELECT gid as id, source, target, len_miles as cost FROM ways_osm' ,2000,1,false,false)) as route on ways_pgr_osm.id = 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.
