[postgis-users] Raster "zonal histogram"

[Adrien ANDRÉ]

Thank you for the reply,

i was using CORTI P. 2014 PostGIS Cookbook, Chapter 5: Working with
Raster Data example
(http://www.packtpub.com/postgis-to-store-organize-manipulate-analyze-spatial-data-cookbook/book).

Switching union and clip did not change the processing time that much
(was actually a few seconds longer).

Thanks to Duncan Golicher's post
http://duncanjg.wordpress.com/2012/11/20/the-basics-of-postgis-raster
i tried

CREATE TABLE resource.count AS
  SELECT
    gid, (value_count).value, SUM((value_count).count) AS count
  FROM
    (
    SELECT
      gid,
      rid,
      ST_ValueCount(
        ST_Union(ST_Clip(rast, geom, TRUE)), 1, TRUE, ARRAY[0, 1]
      ) value_count
    FROM
      (SELECT gid, geom FROM common.parcel) v,
      (SELECT rid, rast FROM resource.all) r
    WHERE ST_Intersects(rast, geom)
    GROUP BY gid, rid, geom
    ) i
  GROUP BY gid, value
  ORDER BY gid, value

which took only 200 s to perform.


Adrien


On 15/04/2014 18:30, guido lemoine wrote:
> 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
>>
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> 
-------------- 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/20140507/78025a89/attachment.vcf>