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:
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?
أكثر...
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:
- Query the raster as binary
- Decode the binary and create numpy array
- Manipulate the numpy array
- Save array to geotiff.
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?
أكثر...