<div dir="ltr">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<div><br></div><div><div><span class="" style="white-space:pre"> </span>SELECT bin,</div><div><span class="" style="white-space:pre"> </span>(ST_SummaryStats(ST_Union(ST_Clip(rast,st_buffer(bldgs.geom_2263, -9.84252))),true)).count as countgrn,</div><div><span class="" style="white-space:pre"> </span>(ST_SummaryStats(ST_Union(ST_Clip(rast,st_buffer(bldgs.geom_2263, -9.84252))),false)).count as countttl,</div><div><span class="" style="white-space:pre"> </span>(ST_BUFFER(bldgs.geom_2263, -9.84252)) as geom</div><div><span class="" style="white-space:pre"> </span>FROM staging.gee_ndvi_2013, staging.bldgs </div><div><span class="" style="white-space:pre"> </span>WHERE ST_INTERSECTS(bldgs.geom_2263,gee_ndvi_2013.rast)</div><div><span class="" style="white-space:pre"> </span>GROUP BY bin, geom_2263</div><div><br></div><div><br></div><div>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? </div><div>NOTICE: The input raster and input geometry do not intersect. Returning empty raster<br></div><div>CONTEXT: PL/pgSQL function st_clip(raster,integer[],geometry,double precision[],boolean) line 8 at RETURN</div><div><div><br></div><div><br></div><div><br></div></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, May 4, 2016 at 9:13 AM, Michael Treglia <span dir="ltr"><<a href="mailto:mtreglia@gmail.com" target="_blank">mtreglia@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">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?<div><br></div><div>Thanks for this insight!</div><div>mike</div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Wed, May 4, 2016 at 9:07 AM, Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi,<div><br></div><div>There is no join condition between your <span style="font-size:12.8px">bldgs and </span><span style="font-size:12.8px">gee_ndvi_2013_merged (how many rows in this table ?) tables so your query is doing a cartesian product: all bldgs rows x all </span><span style="font-size:12.8px">gee_ndvi_2013_merged rows.</span></div><div><span style="font-size:12.8px"><br></span></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote"><div><div>On 4 May 2016 at 12:35, Michael Treglia <span dir="ltr"><<a href="mailto:mtreglia@gmail.com" target="_blank">mtreglia@gmail.com</a>></span> wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><div dir="ltr"><div>Hi All,</div><div><br></div><div>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.</div><div><br></div><div>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: <br>ERROR: could not write block 212381621 of temporary file: No space left on device<br>SQL state: 53100</div><div><br></div><div>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)</div><div><br></div><div>Here's the query:</div><div>--SQL</div><div>SELECT bin,<br>geom,<br>countgrn,<br>countttl,<br>(countgrn::float / countttl::float)*100 as propgrn<br>into resultsvectorlayers.try1_may3 <br>FROM (<br> SELECT bin,<br> (ST_SummaryStats(ST_Union(ST_Clip(rast,st_buffer(bldgs.geom_2263, -9.84252))),true)).count as countgrn,<br> (ST_SummaryStats(ST_Union(ST_Clip(rast,st_buffer(bldgs.geom_2263, -9.84252))),false)).count as countttl,<br> (ST_BUFFER(bldgs.geom_2263, -9.84252)) as geom<br> FROM staging.gee_ndvi_2013_merged, staging.bldgs <br> GROUP BY bin, geom_2263) AS foo;</div><div><br></div><div>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</div><div><br></div><div><br></div><div>Thanks in advance for any advice!</div><div><br></div><div>Best regards,</div><div>Mike Treglia<br></div></div>
<br></div></div><span>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></span></blockquote></div><br></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</div></div></blockquote></div><br></div>