I am using pgr_DrivingDistance from pgrouting in PostGIS on a Linux server to calculate isolines of distances from a set given starting points. I include an example below. I have thousands of starting points and I am generating a polygon around each individual point representing 5km driving distances along the road network.
I have some functioning code which works but is both inelegant and very slow. So it works fine if there's only a few points but for thousands it is very slow indeed to the point of not actually being useful. I realise that calculating routes along a network will not be particularly fast but I'm thinking more is there anyway to improve the code structure (e.g. can I get rid of the loop somehow?) in order to get the best out of it? I'm still very much a novice to Postgresql/PostGIS so still learning.
-- Calculate driving distance for each starting_point for id_number in select vertex_id, addresses_id from starting_points loop vid = id_number.vertex_id; aid = id_number.addresses_id; -- Create a table of points for each starting node within driving distance of buffer_size drop table if exists temp_points; Create temp table temp_points as SELECT drivingdistances.*, topo_nodes.id, topo_nodes.the_geom FROM pgr_drivingDistance( 'SELECT id, source, target, cost_len as cost FROM segmented_roads', vid, buffer_size, false) as drivingdistances LEFT JOIN topo_roads_vertices as topo_nodes on drivingdistances.node = topo_nodes.id; -- Put a polygon around the nodes for one address drop table if exists temp_polys; create temp table temp_polys as select ST_ConcaveHull(ST_Collect(the_geom),0.99,false) as geom from temp_points; ALTER TABLE temp_polys ADD vertex_id integer; UPDATE temp_polys SET vertex_id = vid; ALTER TABLE temp_polys ADD address_id integer; UPDATE temp_polys SET address_id = aid; -- insert polygon for single address into final output_polys insert into output_polys (geom, starting_vid, address_id) select geom, vertex_id, address_id from temp_polys; end loop;