[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