[postgis-users] Raster "zonal histogram"

[Adrien ANDRÉ]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: adrien_andre.vcf
Type: text/x-vcard
Size: 432 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140415/baa0f36f/attachment.vcf>