[postgis-users] No Space Left on Device Error

[Nicolas Ribot]

Hi,

There is no join condition between your bldgs and gee_ndvi_2013_merged (how
many rows in this table ?) tables so your query is doing a cartesian
product: all bldgs rows x all gee_ndvi_2013_merged rows.



On 4 May 2016 at 12:35, Michael Treglia <mtreglia at gmail.com> wrote:

> 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/e324355b/attachment.html>