[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Raster "zonal histogram"
From: guido lemoine <guido.lemoine () jrc ! ec ! europa ! eu>
Date: 2014-04-15 21:30:10
Message-ID: 7430cb181d47.534dc102 () jrc ! ec ! europa ! eu
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
The order of union and clip seems illogical. First union, then clip (once). This may \
not be a big difference for a small parcel (2048 seems to be smaller than a 100 x 100 \
tile), but should be for others (multi-tile parcel coverages).
On 04/15/14, Adrien ANDRÉ <adrien.andre@onf.fr> wrote:
>
> Hi list,
>
> i have a polygon table named "common.parcel" (3432 records).
> and a 11038x13438 pixels raster table "resource.zones" (100x100 tiled)
> with the following values:
> 0: wetland;
> 1: strong slope;
> 2: workable surface.
>
> As a "zonal histogram", i'd like to get this kind of result:
>
> parcel_gid | 0 | 1 | 2
> -------------+-----+------+---
> 2048 | 972 | 2428 | 0
>
>
> I began with
>
> SELECT gid, (c.counts).*
> FROM
> (
> SELECT
> p.gid,
> ST_ValueCount(
> ST_Union(ST_Clip(e.rast, 1, p.geom, TRUE)),
> 1,
> TRUE,
> ARRAY[0, 1, 2]
> ) AS counts
> FROM resource.zones e
> JOIN common.parcel p ON ST_Intersects(e.rast, p.geom)
> WHERE
> p.gid = 2048
> GROUP BY
> p.gid
> ) c
> ;
>
> which returns (in 60 ms):
>
> gid | value | count
> ------+-------+-------
> 2048 | 0 | 972
> 2048 | 1 | 2428
> 2048 | 2 | 0
>
> The problem is that when i remove the WHERE clause, the query runs
> during much more than 123 seconds (I actually stopped the query after
> 900 seconds).
> Intending to run this query on 275 computed versions of resource.zones,
> i'm embarrassed by this processing time.
>
> Could someone tell me if it's the right way to begin,
> if there is an obvious error in my code?
>
>
> Thank you in advance,
>
> regards,
>
> Adrien
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
[Attachment #5 (text/html)]
<div style="font-family: 'Times New Roman'; font-size: 16px; "><div>The order of \
union and clip seems illogical. First union, then clip (once). This may not be a big \
difference for a small parcel (2048 seems to be smaller than a 100 x 100 tile), but \
should be for others (multi-tile parcel coverages).</div><div><br /></div><div><br \
/></div><div><br /></div><div>On 04/15/14, <b class="name">Adrien ANDRÉ </b> \
<adrien.andre@onf.fr> wrote:</div><blockquote \
cite="mid:534D9465.1080608@onf.fr" class="iwcQuote" style="border-left: 1px solid \
#00F; padding-left: 13px; margin-left: 0;" type="cite"><div class="mimepart multipart \
mixed">Hi list,<br /><br />i have a polygon table named "common.parcel" \
(3432 records).<br />and a 11038x13438 pixels raster table "resource.zones" \
(100x100 tiled)<br />with the following values:<br />0: wetland;<br />1: strong \
slope;<br />2: workable surface.<br /><br />As a "zonal histogram", i'd \
like to get this kind of result:<br /><br /> parcel_gid | 0 | 1 | 2<br \
/>-------------+-----+------+---<br /> 2048 | 972 | 2428 | 0<br /><br /><br \
/>I began with<br /><br />SELECT gid, (c.counts).*<br />FROM<br /> (<br /> \
SELECT<br /> p.gid,<br /> ST_ValueCount(<br /> \
ST_Union(ST_Clip(e.rast, 1, p.geom, TRUE)),<br /> 1,<br /> TRUE,<br /> \
ARRAY[0, 1, 2]<br /> ) AS counts<br /> FROM resource.zones e<br /> JOIN \
common.parcel p ON ST_Intersects(e.rast, p.geom)<br /> WHERE<br /> p.gid = \
2048<br /> GROUP BY<br /> p.gid<br /> ) c<br />;<br /><br />which returns \
(in 60 ms):<br /><br /> gid | value | count<br />------+-------+-------<br /> 2048 | \
0 | 972<br /> 2048 | 1 | 2428<br /> 2048 | 2 | 0<br /><br />The \
problem is that when i remove the WHERE clause, the query runs<br />during much more \
than 123 seconds (I actually stopped the query after<br />900 seconds).<br \
/>Intending to run this query on 275 computed versions of resource.zones,<br />i'm \
embarrassed by this processing time.<br /><br />Could someone tell me if it's the \
right way to begin,<br />if there is an obvious error in my code?<br /><br /><br \
/>Thank you in advance,<br /><br />regards,<br /><br />Adrien<br /><p></p><hr \
size="2" /><p>_______________________________________________<br />postgis-users \
mailing list<br />postgis-users@lists.osgeo.org<br /><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></p></div></blockquote></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