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: [email protected] [mailto:postgis-users- > [email protected]] 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 <[email protected]> > > > 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: [email protected] [mailto:postgis- > users- > > [email protected]] 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 > [email protected] > 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> _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
