1

I have very large rasters (7000x3500 cells) in postgis (2.1.3) postgres (9.3.4) that I want to pull down via psycogp2 and create numpy arrays and ultimately a geotiff.

Initially I found I had to use ST_AsBinary(rast) instead of St_AsGDALRaster(rast) in order to prevent python from running out of memory. I'm using windows 32bit python 2.7.7.

The script I wrote does exactly this:

  1. Query the raster as binary
  2. Decode the binary and create numpy array
  3. Manipulate the numpy array
  4. Save array to geotiff.

I used it occasionally over the last two months without any issues and yesterday I started getting DatabaseError: out of memory for query result

At first I thought this was a python memory issue so I tried the query in PgAdmin III and got the same result. out of memory for query result

Here is my query:

SELECT ST_AsBinary(rast), timezone, validdatetime 
from my_raster_table
where validdatetime = '2015-10-1'
and timezone = -6  -- use UTC offset
and model = 'the_only_model_we_currently_have'
and parameter = 'the_only_param_we_currently_have'

After some troubleshooting one of our developers found he was able to pull the same raster down ok. The difference between our queries was he didn't include the model and parameter in the where clause. They are currently not necessary so I removed them and found I'm able to execute a single select OK.

SELECT ST_AsBinary(rast), timezone, validdatetime 
from my_raster_table
where validdatetime = '2015-10-1'
and timezone = -6  -- use UTC offset

HOWEVER, when I kick off my script to request multiple dates I'm only able to pull down the first couple rasters before I run into the error again.

I should note that each time I query the database I create a new connection and cursor which are closed immediately after the cur.fetchall(). The script is doing this in serial.

I do not know which memory this error is referring to. It has that feel of a memory leak, but I have no evidence to suggest that it is one. All signs on the server suggest postgres is not out of memory. All signs on my client machine suggest I'm not out of memory. So where is this error occurring?

TLDR: When querying a large raster via ST_AsBinary, why does 'out of memory for query result' occur and what can I do about it?

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.