<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Juli,<br>
<br>
I think you were close but made a mistake in the aggregates.<br>
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.<br>
Something like:<br>
<br>
WITH clips AS (<br>
SELECT p.ecoregion_cod, ST_Union(ST_Clip(r.rast,1, p.geom,
true)) AS rast<br>
FROM gis_wd.wd_ecoregiones AS p, rastertmp.prova AS r<br>
WHERE ST_Intersects(r.rast, p.geom) <br>
GROUP BY ecoregion_cod --now you don't have to group by the
raster result itself<br>
)<br>
SELECT p.ecoregion_co, (ST_SummaryStats(rast, 1)).* FROM clips
--this should give you the stats per ecoregion_co<br>
<br>
best,<br>
Tom<br>
<br>
<div class="moz-cite-prefix">On 7/22/2015 2:39 PM, juli g. pausas
wrote:<br>
</div>
<blockquote
cite="mid:CAD6WO6RZjEWCgbsuPWywrmMp8ALHUFywqcjTmoXn5UKWEZLXbg@mail.gmail.com"
type="cite">
<div dir="ltr">
<div>Hi<br>
</div>
I have a raster from which I'd to compute some stats. The raster
have with tiles, so <br>
<div><br>
SELECT (ST_SummaryStats(rast, 1)).* FROM rastertmp.prova <br>
-- give me the stats for each tiles<br>
<br>
SELECT (ST_SummaryStats(ST_Union(rast), 1)).* FROM
rastertmp.prova <br>
-- give me the stats for all raster map, OK<br>
<br>
<br>
</div>
<div>Now I'd like to intersect with a polygon map (ecoregion)
and get the stats for each region:<br>
</div>
<div><br>
SELECT ecoregion_cod, (res).* FROM<br>
(SELECT p.ecoregion_cod, ST_SummaryStats(ST_Clip(r.rast,1,
p.geom, true)) AS res<br>
FROM gis_wd.wd_ecoregiones AS p, rastertmp.prova AS r<br>
WHERE ST_Intersects(r.rast, p.geom) <br>
GROUP BY ecoregion_cod, res) AS foo; <br>
<br>
</div>
<div>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?<br>
<br>
</div>
<div>Including ST_Union before clipping didn't wok ..<br>
</div>
<div>ERROR: aggregate functions are not allowed in GROUP BY<br>
</div>
<div>Any other way?<br>
<br>
<br>
</div>
<div>The only thing I can think off is to do it 'manually' using
ST_ValueCount:<br>
<br>
SELECT ecoregion_cod, SUM(valor * suma)/SUM(suma) AS mean,
SUM(suma) AS n, MIN(valor) AS min, MAX(valor) AS max<br>
FROM<br>
(SELECT ecoregion_cod, (res).value AS valor, SUM((res).count)
AS suma<br>
FROM<br>
(SELECT p.ecoregion_cod, ST_ValueCount(ST_Clip(r.rast,1,
p.geom, true)) AS res<br>
FROM gis_wd.wd_ecoregiones AS p, rastertmp.provanet AS r<br>
WHERE ST_Intersects(r.rast, p.geom)<br>
) AS foo<br>
GROUP BY ecoregion_cod, valor<br>
) AS foo2<br>
GROUP BY ecoregion_cod<br>
ORDER BY ecoregion_cod; <br>
<br>
</div>
<div>In this way I can compute the mean (and min, max), but I
cannot compute StdDev from ValueCount. Is there a way?<br>
<br>
</div>
<div>Thanks for any suggestion<br>
Regards<br>
<br>
<div>
<div class="gmail_signature">
<div dir="ltr">
<div>
<div dir="ltr">Juli
<div><span style="color:rgb(51,51,153)">--</span><br>
<font color="#333399"><b>CIDE, CSIC</b> | <a
moz-do-not-send="true"
href="http://www.uv.es/jgpausas"
target="_blank">www.uv.es/jgpausas</a> | <br>
<br>
</font></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</body>
</html>