[postgis-users] Raster "zonal histogram"

[guido lemoine]

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 at 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 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/20140415/6900289f/attachment.html>