pgRouting alphashapes

The pgRouting Layer plugin in QGIS gives you access to all the complex routing algorithms in pgRouting through a nice user interface. Once you have solved your route you can export your results saving them for Ron*.

But you can't export the results of the driving distance and alpha shapes algorithms at the moment (great minds are working on it) so here are my notes at hacking a workaround. Skip to the last query if you just want the answer without the workings...

I am using PgAdmin III to run the following queries against my network (OS ITN with RRI).

First, calculate the driving distance.

SELECT seq, id1 AS id, cost
FROM pgr_drivingdistance(
'SELECT gid AS id, source, target, cost_len AS cost, rcost_len AS reverse_cost 
FROM itn_network', 
58724, -- my node from which I am calculating distances
250, -- the distance
false, -- not a directed graph
true -- use reverse costs
);

This returns 18 rows with a cost from the source node (58724) to all other nodes within 250m.

seq;id;cost
0;37597;208.651074869232
1;37598;86.7071188009952
2;37629;143.100486490991
3;37630;181.19847946488
4;37634;118.034150534475
5;37635;16.3831833502435
6;37641;94.4608653823958
7;37642;61.6372838288835
8;37644;227.375946084094
9;37689;175.13045615549
10;37690;59.6503859688475
11;37691;241.571668314037
12;37694;230.305518365719
13;37728;148.828819490177
14;37782;205.611590696348
15;37784;177.582365790125
16;58724;0
17;58725;35.4566730458006

Then calculate the alpha shape of that collection of points with this query:

SELECT x, y FROM pgr_alphashape(
'SELECT id::int4, ST_X(the_geom)::float8 AS x, ST_Y(the_geom)::float8 AS y 
FROM itn_network_vertices_pgr 
WHERE id IN (
  SELECT id1 AS id
	FROM pgr_drivingdistance(
	  ''SELECT gid AS id, source, target, cost_len AS cost, rcost_len AS reverse_cost 
	  FROM itn_network'', 
	  58724, 250, true, true
	  )
)'
);

This returns the coordinates of the points forming the alpha shape around the points. If we wrap this query in another using the pgr_pointsaspolygon function we can get the geometry of the alpha shape.

SELECT pgr_pointsAsPolygon(
 'SELECT id::int4, ST_X(the_geom)::float8 AS x, ST_Y(the_geom)::float8 AS y 
 FROM itn_network_vertices_pgr 
 WHERE id IN (
  SELECT id1 AS id
	FROM pgr_drivingdistance(
	  ''''SELECT gid AS id, source, target, cost_len AS cost, rcost_len AS reverse_cost 
	  FROM itn_network'''', 
	  58724, 250, true, true
	  )
 )'
);

Neat. How do we view this in QGIS? Let's create a view in the database holding the results of the queries above.

 CREATE OR REPLACE VIEW routing.alpha250m_v AS -- change the name of the view here
 SELECT EXTRACT(MICROSECONDS FROM now()) AS gid,
 pgr_pointsAsPolygon(
 'SELECT id::int4, ST_X(the_geom)::float8 AS x, ST_Y(the_geom)::float8 AS y 
 FROM itn_network_vertices_pgr 
 WHERE id IN (
  SELECT id1 AS id
	FROM pgr_drivingdistance(
	  ''''SELECT gid AS id, source, target, cost_len AS cost, rcost_len AS reverse_cost 
	  FROM itn_network'''', 
	  58724, 
250, -- change this value 500, 1000, 1500, etc.
false, true
	  )
 )'
) AS the_geom;

I duplicated this last query a number of times to create views of different size alpha shapes. Just change the name of the view in the first line and the driving distance value. This could probably be improved but it proves the point.

As Anita Graser has noted, the alpha shape could be tweaked if we could pass an alpha value to the function as it only takes a set of points as input at the moment.

Check the docs too:
http://docs.pgrouting.org/v2.0.0-alpha/src/driving_distance/doc/dd_alphashape.html#pgr-alphashape
http://docs.pgrouting.org/v2.0.0-alpha/src/driving_distance/doc/index.html#pgr-driving-distance
http://docs.pgrouting.org/v2.0.0-alpha/src/driving_distance/doc/dd_points_as_polygon.html#pgr-points-as-polygon

*Ron = later on