<div dir="ltr"><div>Thanks so much for the quick response! </div><div><br></div><div>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).</div><div><br></div><div>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).</div><div><br></div><div>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): </div><div>"Subquery Scan on foo (cost=397916927198.37..666930378149.35 rows=1082211 width=54)"<br>" -> GroupAggregate (cost=397916927198.37..666930356505.13 rows=1082211 width=399)"<br>" Group Key: bldgs.bin, bldgs.geom_2263"<br>" -> Sort (cost=397916927198.37..398581155843.84 rows=265691458188 width=399)"<br>" Sort Key: bldgs.bin, bldgs.geom_2263"<br>" -> Nested Loop (cost=0.00..10586283943.07 rows=265691458188 width=399)"<br>" -> Seq Scan on gee_ndvi_2013_merged (cost=0.00..9796.08 rows=245508 width=202)"<br>" -> Materialize (cost=0.00..90481.17 rows=1082211 width=197)"<br>" -> Seq Scan on bldgs (cost=0.00..55478.11 rows=1082211 width=197)"</div><div><br></div><div>Thanks again nad I'll try to add any more I can soon</div><div>mike<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, May 4, 2016 at 7:36 AM, Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@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"><div class="gmail_default" style="font-family:monospace,monospace">Hey,<br></div><div class="gmail_default" style="font-family:monospace,monospace">are you positive, if you have several disks, <br>that your temp file and all are not filling all of one of your disk space.<br></div><div class="gmail_default" style="font-family:monospace,monospace">Last time I got this error, my OS disk was full, not my storage disk, due to a mistake with tablespace.<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">Have you checked that indexes are used in your query (Should be used by st_clip I guess)?<br></div><div class="gmail_default" style="font-family:monospace,monospace">(EXPLAIN).</div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">You can also separate your query in 2 part, using a temp table in between.<br></div><div class="gmail_default" style="font-family:monospace,monospace">First:<br></div><div class="gmail_default" style="font-family:monospace,monospace">CREATE TABLE temp_my_results AS <br><span> 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<br></span></div><div class="gmail_default" style="font-family:monospace,monospace"><br>then the other table.<br><br>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.<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">You could first try to limit the number of polygons you work on,<br></div><div class="gmail_default" style="font-family:monospace,monospace">for instance by using <br></div><span><div class="gmail_default" style="font-family:monospace,monospace">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<br></div></span><div class="gmail_default" style="font-family:monospace,monospace"> ORDER BY bin ASC, LIMIT 10000 OFFSET 0; <br><br></div><div class="gmail_default" style="font-family:monospace,monospace">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.<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">Cheers,<br></div><div class="gmail_default" style="font-family:monospace,monospace">Rémi-C<br></div></div><div class="gmail_extra"><br><div class="gmail_quote"><div><div class="h5">2016-05-04 12:35 GMT+02:00 Michael Treglia <span dir="ltr"><<a href="mailto:mtreglia@gmail.com" target="_blank">mtreglia@gmail.com</a>></span>:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="h5"><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>_______________________________________________<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" target="_blank" rel="noreferrer">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank" rel="noreferrer">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>