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
