0

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.

Example result

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;
5
  • 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
  • 1
    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.
    – geozelot
    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

1 Answer 1

0

I can suggest small tweaks to help scaling down the temp memory space usage which may improve performance if your machine is short on available memory:

  1. Your temp_points memory table does not need any of the drivingdistances columns, only topo_nodes.id and the_geom.
  2. Next, perform the drop tables as soon as possible within the loop, leaving memory available to be swept up in garbage collection sooner rather than later.

-- As I said, only small tweaks, but if your machine is memory limited, this may help prevent spooling memory to disk.

1
  • Great ideas - thanks. I'll give them a try
    – Ddee
    Jul 7 at 6:46

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.