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

[juli g. pausas]

Yes! it works nicely and give the correct stats
Thanks! that was helpful

Juli
--
*CIDE, CSIC*  |  www.uv.es/jgpausas  |


On Wed, Jul 29, 2015 at 3:07 PM, Tom van Tilburg <tom.van.tilburg at gmail.com>
wrote:

>  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  |
>
>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> 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/f535cafe/attachment.html>