pgr_drivingDistance performance with array of start points

[dkastl]

Expected behavior and actual behavior

Calculating drivetime polygons with arrays of start nodes takes way too long or even crashes.

Steps to reproduce the problem

I created a larger OSM extract from this URL: https://grp01.georepublic.net/mlns/osm_niedersachsen_plus.dump.backup

Possible list of node ID as start points (city points of larger cities): 1266526,255351,5501,2159469,1463228,1193744,2256415,1171116,250749,653982,232643,434415,296758,1186197,1883826,2018268,1937240,1185009,12495,858545,1816077,1180151,2199872,1111186,1446369,2174551,1169891,152542,54377,1738822

Sample queries:

Without equicost:=true the query seems to crash:

CREATE TABLE data.distances_opt AS
   SELECT a.*, b.id, b.osm_id, b.the_geom
     FROM pgr_drivingDistance(
       'SELECT gid AS id, source, target, cost_s AS cost, reverse_cost_s AS reverse_cost FROM osm.ways',
      array[1266526,255351,5501],
       10000
     ) AS a
     LEFT JOIN osm.ways_vertices_pgr AS b ON a.node = b.id;
ERROR:  invalid memory alloc request size 4491664800

With just 3 start nodes it takes too long (actually I always killed the process):

CREATE TABLE data.distances_batch AS
  SELECT a.*, b.id, b.osm_id, b.the_geom
    FROM pgr_drivingDistance(
      'SELECT gid AS id, source, target, cost_s AS cost, reverse_cost_s AS reverse_cost FROM osm.ways',
      array[1266526,255351,5501],
      10000, equicost:=true
    ) AS a
    LEFT JOIN osm.ways_vertices_pgr AS b ON a.node = b.id;

Running seperately pgr_drivingDistance for a single start node worked well enough and produces a map similar to this one:

Specifications like the version of pgRouting/PostGIS and PostgreSQL as well as Operating System

• PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0-12ubuntu2) 6.3.0 20170406, 64-bit
• pgr_version: 2.4.1,v2.4.1,722e04e03,master,1.62.0

[cvvergara]

@dkastl I will be posting here some steps for reproducing the problem. (so I dont forget)

After downloading

Data comes with user Daniel:

psql -U postgres
CREATE ROLE daniel SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN PASSWORD 'daniel';
\q

Create the db and restore the dump

createdb niedersachsen
psql -d niedersachsen -c 'CREATE EXTENSION postgis'
psql -d niedersachsen -c 'CREATE EXTENSION pgrouting'
pg_restore -d  niedersachsen  osm_niedersachsen_plus.dump.backup

Wait some time

[cvvergara]

@dkastl Please create new issue if problem persists when equicost flag is true.

Nothing I can do (now) about the memory allocation when returning that big amount of data when the equicost flag is not on. (But I am thinking)

[dkastl]

Thanks! Will test it.

[philiporlando]

Is it possible to fill the contents of your array with a subquery instead of having to hardcode the target integers?

[dkastl]

Yes, if your subquery returns an array of IDs.

[philiporlando]

I found out I was using an older version of pgRouting that does not support an array for the pgr_drivingdistance() target argument.

[giliam]

Hi @cvvergara, @dkastl, Sorry to post here but I didn't want to create a new issue if it is related to this one (even if I'm not using the same function). I have the same kind of problem with pgr_johnson and pgr_floydWarshall algorithms on a (rather) big graphs: 19000, 25000 and 34000 edges approximately. Do you have any clue on what to do to avoid that problem? Is it only possible? Maybe should I change some settings in my PostgreSQL conf file? Am I trying to do something absurdly complex and heavily resources consuming? EDIT: btw, my data comes from QGis import.

[cvvergara]

You might want to read this: http://docs.pgrouting.org/2.5/en/allpairs-family.html#data

[giliam]

Thank you but I don't understand. Should I use some bbox ?

Le 23 mars 2018 18:00:56 GMT+01:00, Vicky Vergara notifications@github.com a écrit :

You might want to read this: http://docs.pgrouting.org/2.5/en/allpairs-family.html#data

-- You are receiving this because you commented. Reply to this email directly or view it on GitHub: https://github.com/pgRouting/pgrouting/issues/882#issuecomment-375733330

[AlonsoCortes]

Hi! Recently i run the script for many start points and..... it worked!

CREATE TABLE cent_500m AS SELECT a.*, b.id, b.geom FROM pgr_drivingDistance( 'SELECT id, source, target, length::float8 as cost FROM red', array [555, 702, 4137,136, 826,5469,3386, 647], 500, equicost:=true ) AS a LEFT JOIN red_vertices_pgr AS b ON a.node = b.id;