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
