[postgis-users] No Space Left on Device Error

Michael Treglia mtreglia at gmail.com
Wed May 4 05:30:24 PDT 2016


Thanks so much for the quick response!

To your first point, looks like I have plenty of room on both my storage
and OS drives (and my ram hasn't been at capacity at all, if that's of any
use).

Regarding what I'm actually trying to so: Basically, I have a set of
buildings, and for each, I want to calculate the proportion of pixels from
a raster that have a positive value (all others should be nodata). I only
want to do this within an inner buffer of each building though, as there
are some funny things happening along the edges that I don't want to
consider. And ideally, I'd like to have this as either a new vector layer
(as currently set up seems to meet my needs based on the small test area).

I'm on my commute right now so can try more in a bit, but here's the output
of EXPLAIN on the full, original query - can you indicate where I should
see if it's using indexes? (sorry if that's a really naïve question - still
learning some of how PostGIS works):
"Subquery Scan on foo  (cost=397916927198.37..666930378149.35 rows=1082211
width=54)"
"  ->  GroupAggregate  (cost=397916927198.37..666930356505.13 rows=1082211
width=399)"
"        Group Key: bldgs.bin, bldgs.geom_2263"
"        ->  Sort  (cost=397916927198.37..398581155843.84 rows=265691458188
width=399)"
"              Sort Key: bldgs.bin, bldgs.geom_2263"
"              ->  Nested Loop  (cost=0.00..10586283943.07
rows=265691458188 width=399)"
"                    ->  Seq Scan on gee_ndvi_2013_merged
(cost=0.00..9796.08 rows=245508 width=202)"
"                    ->  Materialize  (cost=0.00..90481.17 rows=1082211
width=197)"
"                          ->  Seq Scan on bldgs  (cost=0.00..55478.11
rows=1082211 width=197)"

Thanks again nad I'll try to add any more I can soon
mike

On Wed, May 4, 2016 at 7:36 AM, Rémi Cura <remi.cura at gmail.com> wrote:

> 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
>>
>
>
> _______________________________________________
> 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/1050ff8a/attachment.html>


More information about the postgis-users mailing list