[postgis-users] No Space Left on Device Error

[Rémi Cura]

Hey,
are you positive, if you have several disks,
that your temp file and all are not filling all of one of your disk space.
Last time I got this error, my OS disk was full, not my storage disk, due
to a mistake with tablespace.

Have you checked that indexes are used in your query (Should be used by
st_clip I guess)?
(EXPLAIN).

You can also separate your query in 2 part, using a temp table in between.
First:
CREATE TABLE temp_my_results AS
 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

then the other table.

I don't understand what you are trying to do but according to  your query
there are many other ways to get the same result.

You could first try to limit the number of polygons you work on,
for instance by using
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
 ORDER BY bin ASC, LIMIT 10000 OFFSET 0;

This query will only work on 10k (grouped) polygons, over a million. If it
runs successfuly, you can use a EXPLAIN ANALYSE to see how much ressources
were used and by what parts.

Cheers,
Rémi-C

2016-05-04 12:35 GMT+02:00 Michael Treglia <mtreglia at gmail.com>:

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160504/39ec584c/attachment.html>