pgr_dijkstra ERROR: invalid memory alloc request size 1080000000

Question

I am using pgr_version 2.6.3

I tried googling, most of the posts suggest the data is corrupted. So I tried pg_dump and select row by row to try finding the corrupted row, but did not encounter any problem. Some posts also suggest the output row size is too large and suggest to reduce the query size, but I don't see how I can achieve this. The error is identical with every try even changing the source and target id.

I setup the database using osm2po with planet-latest.osm. I have loaded the .sql to my table pl_2po_4pgr, but I cannot get routing to work.

My query is

select * from pgr_dijkstra('select id, source, target, cost from pl_2po_4pgr', 110491754, 110490812, false);

and the error shows:

ERROR:  invalid memory alloc request size 1080000000
CONTEXT:  PL/pgSQL function pgr_dijkstra(text,integer,integer,boolean,boolean) line 17 at RETURN QUERY

I also tried query as https://live.osgeo.org/en/quickstart/pgrouting_quickstart.html suggests, but the error still exist

SELECT seq, node, edge, cost
        FROM pgr_dijkstra('
                SELECT id, source, target,
                        length as cost FROM pl_2po_4pgr',
                100, 600, false
        );

I also went on http://localhost:8888/Osm2poService and was also be route prefectly within 1 second. The source and target id is right next to each other within 100 meters.

How do I fix this problem? or is there a workaround? or maybe there is a way to output by batch?

I also saw this pgr_dijkstra invalid memory request size but doesn't seem like there is a solid solution

Edit

I tried limiting by using st_buffer and st_dwithin, but neither of them works, something like:

SELECT *
        FROM pgr_dijkstra('
                SELECT osm_id as id, osm_source_id as source, osm_target_id as target,length as cost FROM pl_2po_4pgr
                WHERE geom && st_buffer(st_makeline(
st_setsrid(st_makepoint(-122.39212 46.95), 4326),
st_setsrid(st_makepoint(-122.360618 47.186731), 4326)
                ), 100
             )',
                110491754, 110490812, false
        );

SELECT *
        FROM pgr_dijkstra('
                SELECT osm_id as id, osm_source_id as source, osm_target_id as target,length as cost FROM pl_2po_4pgr
                WHERE st_dwithin(geom,
st_setsrid(st_makepoint(-122.39212 46.95), 4326)
                ), 1000
             )',
             110491754, 110490812, false
        );

Answer 1

Probably a bit late now.

But your limiting should work, you just need to change the units.

Your data is likely in WGS84 (based on the coordinates), so: st_dwithin(geom,st_setsrid(st_makepoint(-122.39212 46.95), 4326)), 1000

1000 is in degrees, so around 2.7 times around the world.

Try: st_dwithin(geom,st_setsrid(st_makepoint(-122.39212 46.95), 4326)), 0.1

One decimal is up to 11km: Measuring accuracy of latitude and longitude?