<div dir="ltr"><div>Hello Pierre,</div><div><br></div><div>Thanks for the reply. I had some problems related to the cell borders (something like this post: <a href="http://goo.gl/TPKqVu">http://goo.gl/TPKqVu</a>), so I ended up using a solution based on what you explained, but first making a buffer in the original area, then making a clip. With this I eliminate the problems of borders:</div>
<div><br></div><div><pre class="" style="font-family:Consolas,'Liberation Mono',Courier,monospace;font-size:12px;line-height:16px;width:798px;margin-top:0px;margin-bottom:0px;padding:0px;color:rgb(0,0,0)"><div class="" id="file-testes_postgis_raster-sql-LC13">
<span class="" style="font-weight:bold">SELECT</span> <span class="" style="color:rgb(51,51,51)">tmp</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">pixel_value</span><span class="">,</span> <span class="" style="color:rgb(51,51,51)">ST_area</span><span class="">(</span><span class="" style="color:rgb(51,51,51)">Geography</span><span class="">(</span><span class="" style="color:rgb(51,51,51)">ST_Union</span><span class="">(</span><span class="" style="color:rgb(51,51,51)">ST_Intersection</span><span class="">(</span><span class="" style="color:rgb(51,51,51)">f</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">geom</span><span class="">,</span> <span class="" style="color:rgb(51,51,51)">tmp</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">geom</span><span class="">))))</span><span class="" style="font-weight:bold">/</span><span class="" style="color:rgb(0,153,153)">10000</span> <span class="" style="font-weight:bold">AS</span> <span class="" style="color:rgb(51,51,51)">area_ha</span></div>
<div class="" id="file-testes_postgis_raster-sql-LC14"><span class="" style="font-weight:bold">FROM</span> <span class="" style="color:rgb(51,51,51)">fazendas</span> <span class="" style="color:rgb(51,51,51)">f</span><span class="">,(</span></div>
<div class="" id="file-testes_postgis_raster-sql-LC15">                <span class="" style="font-weight:bold">SELECT</span> <span class="">(</span><span class="" style="color:rgb(51,51,51)">ST_DumpAsPolygons</span><span class="">(</span><span class="" style="color:rgb(51,51,51)">ST_Clip</span><span class="">(</span><span class="" style="font-weight:bold">c</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">rast</span><span class="">,</span> <span class="" style="color:rgb(51,51,51)">ST_Buffer</span><span class="">(</span><span class="" style="color:rgb(51,51,51)">f</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">geom</span><span class="">,</span> <span class="" style="color:rgb(0,153,153)">0</span><span class="">.</span><span class="" style="color:rgb(0,153,153)">01</span><span class="">),</span> <span class="" style="font-weight:bold">TRUE</span><span class="">))).</span><span class="" style="color:rgb(51,51,51)">val</span> <span class="" style="font-weight:bold">AS</span> <span class="" style="color:rgb(51,51,51)">pixel_value</span><span class="">,</span></div>
<div class="" id="file-testes_postgis_raster-sql-LC16">                       <span class="">(</span><span class="" style="color:rgb(51,51,51)">ST_DumpAsPolygons</span><span class="">(</span><span class="" style="color:rgb(51,51,51)">ST_Clip</span><span class="">(</span><span class="" style="font-weight:bold">c</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">rast</span><span class="">,</span> <span class="" style="color:rgb(51,51,51)">ST_Buffer</span><span class="">(</span><span class="" style="color:rgb(51,51,51)">f</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">geom</span><span class="">,</span> <span class="" style="color:rgb(0,153,153)">0</span><span class="">.</span><span class="" style="color:rgb(0,153,153)">01</span><span class="">),</span> <span class="" style="font-weight:bold">TRUE</span><span class="">))).</span><span class="" style="color:rgb(51,51,51)">geom</span> <span class="" style="font-weight:bold">AS</span> <span class="" style="color:rgb(51,51,51)">geom</span></div>
<div class="" id="file-testes_postgis_raster-sql-LC17">                <span class="" style="font-weight:bold">FROM</span> <span class="" style="color:rgb(51,51,51)">fazendas</span> <span class="" style="color:rgb(51,51,51)">f</span><span class="">,</span> <span class="" style="color:rgb(51,51,51)">capacidade_rast</span> <span class="" style="font-weight:bold">c</span></div>
<div class="" id="file-testes_postgis_raster-sql-LC18">                <span class="" style="font-weight:bold">WHERE</span>  <span class="" style="color:rgb(51,51,51)">ST_Intersects</span><span class="">(</span><span class="" style="color:rgb(51,51,51)">ST_Buffer</span><span class="">(</span><span class="" style="color:rgb(51,51,51)">f</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">geom</span><span class="">,</span> <span class="" style="color:rgb(0,153,153)">0</span><span class="">.</span><span class="" style="color:rgb(0,153,153)">01</span><span class="">),</span> <span class="" style="font-weight:bold">c</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">rast</span><span class="">)</span></div>
<div class="" id="file-testes_postgis_raster-sql-LC19">                <span class="" style="font-weight:bold">AND</span> <span class="" style="color:rgb(51,51,51)">f</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">gid</span> <span class="" style="font-weight:bold">=</span> <span class="" style="color:rgb(0,153,153)">6</span></div>
<div class="" id="file-testes_postgis_raster-sql-LC20">               <span class="">)</span> <span class="" style="font-weight:bold">AS</span> <span class="" style="color:rgb(51,51,51)">tmp</span></div><div class="" id="file-testes_postgis_raster-sql-LC21">
<span class="" style="font-weight:bold">WHERE</span>  <span class="" style="color:rgb(51,51,51)">ST_Intersects</span><span class="">(</span><span class="" style="color:rgb(51,51,51)">f</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">geom</span><span class="">,</span> <span class="" style="color:rgb(51,51,51)">tmp</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">geom</span><span class="">)</span></div>
<div class="" id="file-testes_postgis_raster-sql-LC22"><span class="" style="font-weight:bold">GROUP</span> <span class="" style="font-weight:bold">BY</span> <span class="" style="color:rgb(51,51,51)">tmp</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">pixel_value</span></div>
<div class="" id="file-testes_postgis_raster-sql-LC23"><span class="" style="font-weight:bold">ORDER</span> <span class="" style="font-weight:bold">BY</span> <span class="" style="color:rgb(51,51,51)">tmp</span><span class="">.</span><span class="" style="color:rgb(51,51,51)">pixel_value</span><span class="">;</span></div>
</pre></div><div><br></div><div class="gmail_extra">Regards <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">Marcello,<br>
<br>
You have two choices:<br>
<br>
a) load the rasters as rasters and vectorizing INSIDE the database at the same time of doing the intersection or<br>
<br>
b) load the rasters as rasters, clip the raster tiles with the 'fazendas' and computing the number of with-value pixels in the clipped rasters tiles. 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 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>
<div><div class="h5"><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>
> 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 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 in raster<br>
> format (in this case capacidade_rast, not _vect), because as described<br>
> above takes too long. I'm complete newbie in PostGIS Raster... 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 - 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>
</div></div>> <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>
</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></div>