0

I am creating some isochrones in pgrouting based upon a table from osm2po. Now I have a query that will get all of the edges that are c driving distance away from a point. How can I save this query or these individual isochrones into a table of polygons?

Perhaps a little background might help: I want to show how the isochrones change over time as different events occur or as event intensities change. So I wanted to be able to create a video that shows how the contours of the isochrones changes over time. Hence I need to create a table that has each isochrone polygon, as well as a timestamp for the date.

Any help you can provide in converting the isochrones into polygons — hopefully in PostGIS — is welcome.

2

Just join the geometry column from the original edge table which holds the geometry column. Your query will look like this:

SELECT t1.seq, t1.id1 AS Node, t1.id2 AS Edge, t1.cost, t2.geometry FROM PGR_DrivingDistance(
  'SELECT id, source, target, cost FROM edge_table',
  1, 10, false, false) t1, edge_table t2
WHERE t1.id2 = t2.edge_id

If you want the Nodes instead of the Edges then just refer to the vertex table's geometry column like this:

SELECT t1.seq, t1.id1 AS Node, t1.id2 AS Edge, t1.cost, t2.geometry FROM PGR_DrivingDistance(
  'SELECT id, source, target, cost FROM edge_table',
  1, 10, false, false) t1, edge_vertex_table t2
WHERE t1.id2 = t2.vertex_id
  • This one works as well :). – krishnab May 23 '14 at 9:46
2

As I remember isochrone is not a set of roads reachable in certain time, but polygon containing all of that roads, so in this case you have to get familiar with two functions: ST_ConvexHull and ST_ConcaveHull. First is like placing something in a rubber baloon, second is like apply suction to this baloon.

To store it in the table of isochrones you have to do sth like (written ad-hoc so might not work as is):

insert into isochrones_table (start_edge, version, geom)
values
1234,
1,
(SELECT ST_ConcaveHull(ST_Collect(geom_way),<<concave>>,false) 
FROM edges 
JOIN (SELECT * FROM pgr_drivingdistance('SELECT id, source, target, cost AS cost from edges', 1234, <<c_time>>, false, false)) 
AS route 
ON edges.source = route.id1)

concave is the target percent of concave - 0.7 is good compromise of quality and performace
c_time is the time of isochrone (as you probably already know)

  • Thanks so much for your help dude. I really appreciate your taking the time to answer all of these questions I keep posting. I am getting much more comfortable with pgrouting and postgis now, thanks to your answers. – krishnab May 20 '14 at 8:26
  • No problem - I was at the same boat few months ago so i know that pg_routing without any help could be 'little confusing'. – Jendrusk May 20 '14 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.