I'm using pgrouting 2.0 and postgis in postgresql.My problem is to calculate the driving time to the closest facility for each node of my OSM derived network.These are the steps to be implemented (if I'm not mistaken..):1. Find the closest node of the network for each facility (let't call them "nodes_fac");2. Calculate iteratively the driving distance to all nodes (or within a given distance) from each of the previous nodes_fac writing the results in a table;3. Filter the results using the minimum operator to select the driving time to the closest facility (the result should be a table including the nodes geometries with the minimum values);
Step 1: I think that this code can work (posts is the table of facilities):
SELECT posts.id AS post_id, (SELECT nodes.id AS node_id FROM ways_vertices_pgr as nodes ORDER BY posts.geom nodes.the_geom LIMIT 1)FROM post_offices as postsORDER BY post_id;Step 2: The following code could be used, but I don't know how to loop it to produce a table with the driving distance for each node_fac:
SELECT ways_vertices_pgr.id, ways_vertices_pgr.the_geom, basin.cost FROM ways_vertices_pgr JOIN ( SELECT pgr_drivingdistance.id1, pgr_drivingdistance.cost FROM pgr_drivingdistance(' SELECT gid AS id, source::integer, target::integer, avg_cost_min::double precision AS cost FROM ways'::text, 22938, 10::double precision, false, false)) basin ON ways_vertices_pgr.id = basin.id1;avg_cost_min is a calculated column with the driving time for each network segment, 22938 is one of the node_fac, 10 is the maximum driving time.
Thank you in advance for any help you can provide!!Marco
أكثر...
Step 1: I think that this code can work (posts is the table of facilities):
SELECT posts.id AS post_id, (SELECT nodes.id AS node_id FROM ways_vertices_pgr as nodes ORDER BY posts.geom nodes.the_geom LIMIT 1)FROM post_offices as postsORDER BY post_id;Step 2: The following code could be used, but I don't know how to loop it to produce a table with the driving distance for each node_fac:
SELECT ways_vertices_pgr.id, ways_vertices_pgr.the_geom, basin.cost FROM ways_vertices_pgr JOIN ( SELECT pgr_drivingdistance.id1, pgr_drivingdistance.cost FROM pgr_drivingdistance(' SELECT gid AS id, source::integer, target::integer, avg_cost_min::double precision AS cost FROM ways'::text, 22938, 10::double precision, false, false)) basin ON ways_vertices_pgr.id = basin.id1;avg_cost_min is a calculated column with the driving time for each network segment, 22938 is one of the node_fac, 10 is the maximum driving time.
Thank you in advance for any help you can provide!!Marco
أكثر...