[postgis-users] No Space Left on Device Error

[Michael Treglia]

Just to follow up - that all ran great, completing the entire task in ~20
minutes.
Thanks for everybody's suggestions!
mike

On Wed, May 4, 2016 at 9:39 AM, Michael Treglia <mtreglia at gmail.com> wrote:

> I think I'm making progress - and am about to try the below on the full
> dataset, but  adding WHERE ST_INTERSECTS seems to help the issue you point
> to, correct Nicolas? It takes about 1/4 the time on my small sample dataset
> vs. without the WHERE ST_INTERSECTS
>
> 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, staging.bldgs
> WHERE ST_INTERSECTS(bldgs.geom_2263,gee_ndvi_2013.rast)
> GROUP BY bin, geom_2263
>
>
> I realize that in both situations, I'm getting the message below though,
> which I gather is related to this issue, so I might need to so something
> similar for ST_Clip specifically?
> NOTICE:  The input raster and input geometry do not intersect. Returning
> empty raster
> CONTEXT:  PL/pgSQL function st_clip(raster,integer[],geometry,double
> precision[],boolean) line 8 at RETURN
>
>
>
>
> On Wed, May 4, 2016 at 9:13 AM, Michael Treglia <mtreglia at gmail.com>
> wrote:
>
>> Okay, this makes sense - the tables have 1,082,210 and 245,508 rows for
>> the bldgs and gee_ndvi_2013_merged, respectively. Can you suggest how to
>> add the join condition? Or point to an example?
>>
>> Thanks for this insight!
>> mike
>>
>> On Wed, May 4, 2016 at 9:07 AM, Nicolas Ribot <nicolas.ribot at gmail.com>
>> wrote:
>>
>>> 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
>>>>
>>>
>>>
>>> _______________________________________________
>>> 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/968cb245/attachment.html>