[postgis-users] PostGIS Raster Area from a Clip

[Pierre Racine]

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>