[postgis-users] No Space Left on Device Error

[Michael Treglia]

Hi All,

I'm new to the list and pretty new to PostGIS, and would greatly appreciate
any suggestions for troubleshooting an error I get when running a query on
a large set of data. It seems like PostGIS is the most efficient way to
accomplish what I'm going for, so I'm excited to get this working as a I
add this to my toolkit.

I'm running the query below on a vector layer of ~1 million polygons
(bldgs) and a raster of ~2 billion pixels (gee_ndvi_2013_merged). This
works on a small sample area and does exactly what I want (having
previously clipped both the raster and vector to a small area), but when I
run it on the full dataset, I get this error after ~4 hours:
ERROR: could not write block 212381621 of temporary file: No space left on
device
SQL state: 53100

My machine has plenty of available disk space, and I set the
temp_file_limit in the postgresql.conf file to 500gb (and I have 32GB of
ram if that's informative at all). I'm running the db from PostgreSQL 9.5
and postgis 2.2.2 on Windows 7 x64 (and get the error when running both on
that setup, and from an Ubuntu virtualbox as a client)

Here's the query:
--SQL
SELECT bin,
geom,
countgrn,
countttl,
(countgrn::float / countttl::float)*100 as propgrn
into resultsvectorlayers.try1_may3
FROM (
 SELECT bin,
 (ST_SummaryStats(ST_Union(ST_Clip(rast,st_buffer(bldgs.geom_2263,
-9.84252))),true)).count as countgrn,
 (ST_SummaryStats(ST_Union(ST_Clip(rast,st_buffer(bldgs.geom_2263,
-9.84252))),false)).count as countttl,
 (ST_BUFFER(bldgs.geom_2263, -9.84252)) as geom
 FROM staging.gee_ndvi_2013_merged, staging.bldgs
 GROUP BY bin, geom_2263) AS foo;

Any suggestions on how to troubleshoot or resolve this? Or if it seems like
there's a better way I should execute this operation, I'm open to that too


Thanks in advance for any advice!

Best regards,
Mike Treglia
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160504/e00476b5/attachment.html>