0

So I am working with some pgrouting road network data in postgis2.0/postgresql 9.1. I am interested in routing around certain types of disasters like tornados or such. If a disaster event happens at some spatial coordinate x1,y1. Then I would generate an isochrone from that point to every other point that is reachable at some cost c. So basically I am creating a travel buffer around the event. Then I would like to loop through all of the road edges that fall within this isochrone and increase the costs of travel along that road segment. I am trying to figure out how to reconcile the pgrouting part with the postgis part--I have less familiarity with pgrouting.

Would I create a polygon out of the isochrone and then do some sort of contains or within query to get the included edges, and then run an sql update to increase their costs. An additional consideration is the efficiency of this operation since this type of spatial operation might present a problem for a large number of network edges.

1

No, you don't have to create polygon (convex, concave) since pgr_drivingdistance function is already selecting reachable edges from set of edges specified with sql query in certain time.

Assuming, that:

  • table with topology name is routing_table,
  • starting edge id = 7
  • cost = 1.5 (if using table produced by osm2po cost is in hours)

Pseudo-code query could be:

with 
  foo as (select id2 from pgr_drivingdistance(
            'SELECT id, source, target, cost FROM routing_table',
            7, 1.5, false, false))
update
  routing_table rt
set
  cost = cost*2
where
  exists (select 1 from foo where foo.id2 = rt.id)
  • Hmm, this is odd. I am getting some strange results with this query. So I was running some diagnostics and I added a count_events field to the routing_table. Then I made the update part of the query to include count_events = count_events + 1. I ran the query twice--expecting that the same routes would be updated twice. However, when I did a count(id) from routing_table group by count_events then I found that some routes were updated 2 times, some only 1 time, and most 0. So when I run the update query there are some differences between run 1 and the second run of the same query? – krishnab May 23 '14 at 19:40

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.