[postgis-users] Raster "zonal histogram"
Adrien ANDRÉ
adrien.andre at onf.fr
Wed May 7 12:07:23 PDT 2014
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>
More information about the postgis-users
mailing list