[postgis-users] PostGIS Raster Area from a Clip
Marcello Benigno
benigno.marcello at gmail.com
Mon Oct 21 14:57:09 PDT 2013
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 <Pierre.Racine at sbf.ulaval.ca>
> 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>
> _______________________________________________
> 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/e73b5f1e/attachment.html>
More information about the postgis-users
mailing list