[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] ST_SummaryStats(ST_Clip(rast, geom)) with tiles
From: "juli g. pausas" <juli.g.pausas () uv ! es>
Date: 2015-07-29 17:45:45
Message-ID: CAD6WO6RUqrK6toeXwSLgzAwA+2e1-C8=d_9kfX+axEovVbaZDQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@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@lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
[Attachment #5 (text/html)]
<div dir="ltr"><div>Yes! it works nicely and give the correct stats<br></div>Thanks! \
that was helpful<br><div class="gmail_extra"><br clear="all"><div><div><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 href="http://www.uv.es/jgpausas" target="_blank">www.uv.es/jgpausas</a> | \
<br><br></font></div></div></div></div></div></div> <br><div class="gmail_quote">On \
Wed, Jul 29, 2015 at 3:07 PM, Tom van Tilburg <span dir="ltr"><<a \
href="mailto:tom.van.tilburg@gmail.com" \
target="_blank">tom.van.tilburg@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">
<div 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<span><br>
FROM gis_wd.wd_ecoregiones AS p, rastertmp.prova AS r<br>
WHERE ST_Intersects(r.rast, p.geom) <br></span>
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<div><div><br>
<br>
<div>On 7/22/2015 2:39 PM, juli g. pausas
wrote:<br>
</div>
</div></div><blockquote type="cite"><div><div>
<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>
<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 \
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></fieldset>
<br>
</div></div><pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a> <a \
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" \
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> <a \
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div></div>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic