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É * <[email protected]> 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 >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
<<attachment: adrien_andre.vcf>>
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
