Ops, my bad: >> a) would look like this (you don't have to do a geometric costly ST_Union in order to sum the areas):
I remove it and the performance is really better. Thanks! 2013/10/21 Pierre Racine <[email protected]> > 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 > -- *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
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
