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

[Tom van Tilburg]

Hi Juli,

I think you were close but made a mistake in the aggregates.
Try to split your functionality with the help of 'WITH' statements and 
see if you can do the grouping in that way in a subselect.
Something like:

WITH clips AS (
     SELECT p.ecoregion_cod, ST_Union(ST_Clip(r.rast,1, p.geom, true)) 
AS rast
     FROM gis_wd.wd_ecoregiones AS p, rastertmp.prova AS r
     WHERE ST_Intersects(r.rast, p.geom)
     GROUP BY ecoregion_cod --now you don't have to group by the raster 
result itself
)
SELECT p.ecoregion_co, (ST_SummaryStats(rast, 1)).* FROM clips --this 
should give you the stats per ecoregion_co

best,
  Tom

On 7/22/2015 2:39 PM, juli g. pausas wrote:
> 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 <http://www.uv.es/jgpausas>  |
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150729/56ee5928/attachment.html>