[postgis-users] ST_SummaryStats(ST_Clip(rast, geom)) with tiles

[juli g. pausas]

Hi
I have a raster from which I'd to compute some stats. The raster have with
tiles, so

SELECT (ST_SummaryStats(rast, 1)).* FROM rastertmp.prova
 -- give me the stats for each  tiles

SELECT (ST_SummaryStats(ST_Union(rast), 1)).* FROM rastertmp.prova
 -- give me the stats for all raster map, OK


Now I'd like to intersect with a polygon map (ecoregion) and get the stats
for each region:

SELECT ecoregion_cod, (res).* FROM
  (SELECT p.ecoregion_cod, ST_SummaryStats(ST_Clip(r.rast,1, p.geom, true))
AS res
    FROM gis_wd.wd_ecoregiones AS p, rastertmp.prova AS r
    WHERE ST_Intersects(r.rast, p.geom)
    GROUP BY ecoregion_cod, res) AS foo;

this give me the stats for each region in each tile, but I'd like the
overall stats, i.e. the stats for each region in the whole raster. How can
I do it?

Including ST_Union before clipping didn't wok ..
ERROR: aggregate functions are not allowed in GROUP BY
Any other way?


The only thing I can think off is to do it 'manually' using ST_ValueCount:

SELECT ecoregion_cod, SUM(valor * suma)/SUM(suma) AS mean, SUM(suma) AS n,
MIN(valor) AS min, MAX(valor) AS max
FROM
(SELECT ecoregion_cod, (res).value AS valor, SUM((res).count) AS suma
FROM
  (SELECT p.ecoregion_cod, ST_ValueCount(ST_Clip(r.rast,1, p.geom, true))
AS res
    FROM gis_wd.wd_ecoregiones AS p, rastertmp.provanet AS r
    WHERE ST_Intersects(r.rast, p.geom)
   ) AS foo
GROUP BY ecoregion_cod, valor
) AS foo2
GROUP BY ecoregion_cod
ORDER BY ecoregion_cod;

In this  way I can compute the mean (and min, max), but I cannot compute
StdDev from ValueCount. Is there a way?

Thanks for any suggestion
Regards

Juli
--
*CIDE, CSIC*  |  www.uv.es/jgpausas  |
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150722/25092047/attachment.html>