# Optimization of pgr_drivingdistance from pgrouting in PostGIS

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;

-- 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;
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;
``````
• It's expected that `pgr_drivingDistance()` takes some time to compute. Also depending on the shape `ST_ConcaveHull()` can also eat another bit of time. Did you measure the individual components timings? I doubt you can get this much faster when running on a single machine. Have you thought about splitting up the center points and running it on separate machines? Jul 6 at 10:04
• @timothy-dalton Agreed that pgr_drivingDistance() is certainly the problem and not ST_ConcaveHull() which is pretty quick. But at present I have to compute the driving distances for each starting point, one at a time in a loop. I would have thought it ought to be possible to send the whole lot to pgr_drivingDistances to run in a single go, but I can't figure out how nor whether than would be any quicker if I did anyway. Splitting things up is messy for my current implementation so I'm reluctant, although it is a good idea
– Ddee
Jul 6 at 12:58
• You can use the multi-vertex signature of `pgr_drivingDistance` - simply pass in all your starting points as an array; this would minimize the overhead of loading the graph into memory on each individual call. Jul 7 at 8:15
• What geozelot suggests is probably the key docs.pgrouting.org/3.1/en/… to make it far more performant Jul 7 at 10:23
• Ok - thanks. Good to know there are possible solutions out there. But I'm still struggling to understand the documentation and get a working solution. Can someone give me an example of what the call line for pgr_drivingDistance would be in my case if I passed my starting_points in as an array?
– Ddee
Jul 8 at 7:51