[postgis-users] PostGIS Raster Area from a Clip

[Marcello Benigno]

Hello Pierre,

Thanks for the reply. I had some problems related to the cell borders
(something like this post: http://goo.gl/TPKqVu), 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:

SELECT tmp.pixel_value,
ST_area(Geography(ST_Union(ST_Intersection(f.geom, tmp.geom))))/10000
AS area_ha
FROM fazendas f,(
		 SELECT (ST_DumpAsPolygons(ST_Clip(c.rast, ST_Buffer(f.geom, 0.01),
TRUE))).val AS pixel_value,
		        (ST_DumpAsPolygons(ST_Clip(c.rast, ST_Buffer(f.geom, 0.01),
TRUE))).geom AS geom
		 FROM fazendas f, capacidade_rast c
		 WHERE  ST_Intersects(ST_Buffer(f.geom, 0.01), c.rast)
		 AND f.gid = 6
		) AS tmp
WHERE  ST_Intersects(f.geom, tmp.geom)
GROUP BY tmp.pixel_value
ORDER BY tmp.pixel_value;


Regards

2013/10/21 Pierre Racine <Pierre.Racine at sbf.ulaval.ca>

> Marcello,
>
> You have two choices:
>
> a) load the rasters as rasters and vectorizing INSIDE the database at the
> same time of doing the intersection or
>
> 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.
>
> We assume that rasters are loaded tiles  (-t) and indexes (-I).
>
> a) would look like this (you don't have to do a geometric costly ST_Union
> in order to sum the areas):
>
> SELECT (gv).val value, sum(ST_Area((gv).geom::geography) area
> FROM (SELECT ST_Intersection(f.geom, c.rast) gv
>       FROM fazendas f, capacidade_rast c
>       WHERE  ST_Intersects(f.geom, c.rast) AND f.gid = 2
>       GROUP BY (gv).val
>       ORDER BY (gv).val
> ) foo
>
> b) would look like this (areaofonepixel would be a constant):
>
> SELECT (vc).val value, sum((vc).count) * areaofonepixel area
> FROM (SELECT ST_ValueCount(ST_Clip(c.rast, f.geom)) vc
>       FROM fazendas f, capacidade_rast c
>       WHERE  ST_Intersects(f.geom, c.rast) AND f.gid = 2
>       GROUP BY (vc).val
>       ORDER BY (vc).val
> ) foo
>
> Pierre
>
> > -----Original Message-----
> > From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-
> > bounces at lists.osgeo.org] On Behalf Of Marcello Benigno
> > Sent: Saturday, October 19, 2013 8:54 AM
> > To: PostGIS Users Discussion
> > Subject: [postgis-users] PostGIS Raster Area from a Clip
> >
> > Hi All
> >
> > I converted a raster to vector format, where the pixel values range from
> 1 to
> > 8, then exported to PostGIS and made the following query:
> >
> >
> >
> >    SELECT foo.value, ST_area(foo.geom::geography)
> >    FROM (
> >           SELECT c.value,
> >                  ST_Union(ST_Intersection(f.geom, c.geom)) as geom
> >           FROM fazendas f, capacidade_vect c
> >           WHERE  ST_Intersects(f.geom, c.geom)
> >           AND f.gid = 2
> >           GROUP BY c.value
> >           ORDER BY c.value
> >        ) AS foo;
> > I wonder if it is possible to obtain the same results using the table in
> raster
> > format (in this case capacidade_rast, not _vect), because as described
> > above takes too long. I'm complete newbie in PostGIS Raster... Could
> > anyone help me to build this query?
> >
> >
> > Thanks in advance,
> >
> > --
> > Marcello Benigno B. de Barros Filho
> > Prof. do Curso Superior de Tecnologia em Geoprocessamento - IFPB
> > Mestre em Ciências Geodésicas e Tecnologias da Geoinformação - UFPE
> > Doutorando em Tecnologia Ambiental e Recursos Hídricos - UFPE
> > http://profmarcello.blogspot.com
> > http://about.me/marcello.benigno <http://about.me/marcello.benigno>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>



-- 
*Marcello Benigno B. de Barros Filho*
Prof. do Curso Superior de Tecnologia em Geoprocessamento - IFPB
Mestre em Ciências Geodésicas e Tecnologias da Geoinformação - UFPE
Doutorando em Tecnologia Ambiental e Recursos Hídricos - UFPE
http://profmarcello.blogspot.com
http://about.me/marcello.benigno
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131021/4f492d4d/attachment.html>