3

I have a PostGIS query returning geographies and attributes. The result set is deterministic and ordered (so OFFSET and LIMIT work for getting subsets).

The result set of my query would exceed my RAM by many times.

ogr2ogr can read directly from PostGIS and load SQL from a file so I built this command line:

ogr2ogr -f "ESRI Shapefile" output.shp PG:"host=host user=user dbname=gis" -sql \@q.sql

When I run this, my memory is exhausted and the process is killed by the system.

How can I make PostGIS+ogr2ogr "stream" the results and flush them in reasonable intervals instead of trying to get the full result set at once?

I am not looking for scripting, instead I wonder if there is some magic of GDAL I just have to enable to get paging.

A different output format would be fine if that makes it work. But only if I can convert that to Shapefile or GeoPackage without exhausting RAM again.

Improve this question
1
  • Ogr2ogr has originally written long time ago when computers were weak. It should normally not read all the data into memory. With these details it is hard to say why it happens for you. Is the situation the same if you convert the whole table without SQL part into GeoPackage? LIMIT and OFFSET are not optimal for databases, limit 1000 offset 1000000 reads 1001000 rows and throws the million first rows away. – user30184 Jul 11 '20 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.