<div dir="ltr">Ops, my bad:<div><br></div><div><span style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px">>> a) would look like this (you don't have to do a geometric costly </span><span style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px"> ST_Union in order to sum the areas):</span><br>
</div><div><span style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px"><br></span></div>I remove it and the performance is really better. Thanks!</div><div class="gmail_extra"><br><br><div class="gmail_quote">
2013/10/21 Pierre Racine <span dir="ltr"><<a href="mailto:Pierre.Racine@sbf.ulaval.ca" target="_blank">Pierre.Racine@sbf.ulaval.ca</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Again why do ST_Union to compute the area when you could just sum() the areas?<br>
<br>
You should also avoid calling two times ST_DumpAsPolygons. Call it once into a geomval and just SELECT (geomval).val and (geomval).geom.<br>
<div class="im HOEnZb"><br>
> -----Original Message-----<br>
> From: <a href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-users-">postgis-users-</a><br>
> <a href="mailto:bounces@lists.osgeo.org">bounces@lists.osgeo.org</a>] On Behalf Of Marcello Benigno<br>
</div><div class="im HOEnZb">> Sent: Monday, October 21, 2013 2:16 PM<br>
> To: PostGIS Users Discussion<br>
</div><div class="HOEnZb"><div class="h5">> Subject: Re: [postgis-users] PostGIS Raster Area from a Clip<br>
><br>
> Hello Pierre,<br>
><br>
> Thanks for the reply. I had some problems related to the cell borders<br>
> (something like this post: <a href="http://goo.gl/TPKqVu" target="_blank">http://goo.gl/TPKqVu</a>), so I ended up using a<br>
> solution based on what you explained, but first making a buffer in the<br>
> original area, then making a clip. With this I eliminate the problems of<br>
> borders:<br>
><br>
><br>
> SELECT tmp.pixel_value,<br>
> ST_area(Geography(ST_Union(ST_Intersection(f.geom, tmp.geom))))/10000<br>
> AS area_ha<br>
><br>
> FROM fazendas f,(<br>
><br>
> SELECT (ST_DumpAsPolygons(ST_Clip(c.rast,<br>
> ST_Buffer(f.geom, 0.01), TRUE))).val AS pixel_value,<br>
><br>
> (ST_DumpAsPolygons(ST_Clip(c.rast, ST_Buffer(f.geom,<br>
> 0.01), TRUE))).geom AS geom<br>
><br>
> FROM fazendas f, capacidade_rast c<br>
><br>
> WHERE ST_Intersects(ST_Buffer(f.geom, 0.01), c.rast)<br>
><br>
> AND f.gid = 6<br>
><br>
> ) AS tmp<br>
><br>
> WHERE ST_Intersects(f.geom, tmp.geom)<br>
><br>
> GROUP BY tmp.pixel_value<br>
><br>
> ORDER BY tmp.pixel_value;<br>
><br>
><br>
> Regards<br>
><br>
><br>
> 2013/10/21 Pierre Racine <<a href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre.Racine@sbf.ulaval.ca</a>><br>
><br>
><br>
> Marcello,<br>
><br>
> You have two choices:<br>
><br>
> a) load the rasters as rasters and vectorizing INSIDE the database at<br>
> the same time of doing the intersection or<br>
><br>
> b) load the rasters as rasters, clip the raster tiles with the 'fazendas'<br>
> and computing the number of with-value pixels in the clipped rasters tiles.<br>
> b) is generally faster but less precise.<br>
><br>
> We assume that rasters are loaded tiles (-t) and indexes (-I).<br>
><br>
> a) would look like this (you don't have to do a geometric costly<br>
> ST_Union in order to sum the areas):<br>
><br>
> SELECT (gv).val value, sum(ST_Area((gv).geom::geography) area<br>
> FROM (SELECT ST_Intersection(f.geom, c.rast) gv<br>
> FROM fazendas f, capacidade_rast c<br>
> WHERE ST_Intersects(f.geom, c.rast) AND f.gid = 2<br>
> GROUP BY (gv).val<br>
> ORDER BY (gv).val<br>
> ) foo<br>
><br>
> b) would look like this (areaofonepixel would be a constant):<br>
><br>
> SELECT (vc).val value, sum((vc).count) * areaofonepixel area<br>
> FROM (SELECT ST_ValueCount(ST_Clip(c.rast, f.geom)) vc<br>
> FROM fazendas f, capacidade_rast c<br>
> WHERE ST_Intersects(f.geom, c.rast) AND f.gid = 2<br>
> GROUP BY (vc).val<br>
> ORDER BY (vc).val<br>
> ) foo<br>
><br>
> Pierre<br>
><br>
><br>
> > -----Original Message-----<br>
> > From: <a href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-">postgis-</a><br>
> users-<br>
> > <a href="mailto:bounces@lists.osgeo.org">bounces@lists.osgeo.org</a>] On Behalf Of Marcello Benigno<br>
> > Sent: Saturday, October 19, 2013 8:54 AM<br>
> > To: PostGIS Users Discussion<br>
> > Subject: [postgis-users] PostGIS Raster Area from a Clip<br>
> ><br>
> > Hi All<br>
> ><br>
> > I converted a raster to vector format, where the pixel values range<br>
> from 1 to<br>
> > 8, then exported to PostGIS and made the following query:<br>
> ><br>
> ><br>
> ><br>
> > SELECT foo.value, ST_area(foo.geom::geography)<br>
> > FROM (<br>
> > SELECT c.value,<br>
> > ST_Union(ST_Intersection(f.geom, c.geom)) as geom<br>
> > FROM fazendas f, capacidade_vect c<br>
> > WHERE ST_Intersects(f.geom, c.geom)<br>
> > AND f.gid = 2<br>
> > GROUP BY c.value<br>
> > ORDER BY c.value<br>
> > ) AS foo;<br>
> > I wonder if it is possible to obtain the same results using the table<br>
> in raster<br>
> > format (in this case capacidade_rast, not _vect), because as<br>
> described<br>
> > above takes too long. I'm complete newbie in PostGIS Raster...<br>
> Could<br>
> > anyone help me to build this query?<br>
> ><br>
> ><br>
> > Thanks in advance,<br>
> ><br>
> > --<br>
> > Marcello Benigno B. de Barros Filho<br>
> > Prof. do Curso Superior de Tecnologia em Geoprocessamento -<br>
> IFPB<br>
> > Mestre em Ciências Geodésicas e Tecnologias da Geoinformação -<br>
> UFPE<br>
> > Doutorando em Tecnologia Ambiental e Recursos Hídricos - UFPE<br>
> > <a href="http://profmarcello.blogspot.com" target="_blank">http://profmarcello.blogspot.com</a><br>
><br>
> > <a href="http://about.me/marcello.benigno" target="_blank">http://about.me/marcello.benigno</a><br>
> <<a href="http://about.me/marcello.benigno" target="_blank">http://about.me/marcello.benigno</a>><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/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
><br>
><br>
><br>
><br>
><br>
> --<br>
> Marcello Benigno B. de Barros Filho<br>
> Prof. do Curso Superior de Tecnologia em Geoprocessamento - IFPB<br>
> Mestre em Ciências Geodésicas e Tecnologias da Geoinformação - UFPE<br>
> Doutorando em Tecnologia Ambiental e Recursos Hídricos - UFPE<br>
> <a href="http://profmarcello.blogspot.com" target="_blank">http://profmarcello.blogspot.com</a><br>
> <a href="http://about.me/marcello.benigno" target="_blank">http://about.me/marcello.benigno</a> <<a href="http://about.me/marcello.benigno" target="_blank">http://about.me/marcello.benigno</a>><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/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br><b>Marcello Benigno B. de Barros Filho</b><br><font size="1">Prof. do Curso Superior de Tecnologia em Geoprocessamento - IFPB<br>Mestre em Ciências Geodésicas e Tecnologias da Geoinformação - UFPE</font><div>
<font size="1">Doutorando em Tecnologia Ambiental e Recursos Hídricos - UFPE</font><font size="1"><br></font><font size="1"><a href="http://profmarcello.blogspot.com" target="_blank">http://profmarcello.blogspot.com</a></font><div>
<a href="http://about.me/marcello.benigno" target="_blank"><font size="1">http://about.me/marcello.benigno</font></a></div></div>
</div>