[postgis-users] PostGIS Raster Area from a Clip

[Pierre Racine]

Again why do ST_Union to compute the area when you could just sum() the areas?

You should also avoid calling two times ST_DumpAsPolygons. Call it once into a geomval and just SELECT (geomval).val and (geomval).geom.

> -----Original Message-----
> From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-
> bounces at lists.osgeo.org] On Behalf Of Marcello Benigno
> Sent: Monday, October 21, 2013 2:16 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] PostGIS Raster Area from a Clip
> 
> 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 <http://about.me/marcello.benigno>