Hi Pierre, Thank you so much. Are the SummarystatsAgg and AreaWeightedSummaryStats included in the latest trunk or should i install from plpgsql?
best, Andreas 2012/3/6 Pierre Racine <pierre.rac...@sbf.ulaval.ca>: > In raster space, on a tiled raster coverage (equivalent to your second query > but a bit more simple): > > SELECT gt. id, (ST_SummaryStatsAgg(ST_Clip(rt.rast, gt. geom, true)).* > FROM rasttable rt, geomtable gt > WHERE ST_Intersects(rt.rast, gt.the_geom) > GROUP BY gt.id > > In raster space, on a non-tiled raster coverage: > > SELECT gt. id, (ST_SummaryStats(ST_Clip(rt.rast, gt. geom, true)).* > FROM rasttable rt, geomtable gt > > In vector space (more precise, works better with big pixels and small > intersecting areas and points and lines, has to be done on a tiled raster > coverage): > > SELECT (ST_AreaWeightedSummaryStats(gv)).* > FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv > FROM rasttable rt, geomtable gt > WHERE ST_Intersects(rt.rast, gt.geom) > ) foo > GROUP BY gt.id > > Pierre > >> -----Original Message----- >> From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- >> boun...@postgis.refractions.net] On Behalf Of Andreas Forø Tollefsen >> Sent: Tuesday, March 06, 2012 4:12 AM >> To: PostGIS Users Discussion >> Subject: [postgis-users] Summary Statistics (vector, raster) >> >> Hi all, >> >> I have used a number of different queries to find the mean raster value >> within a >> polygon. >> However, this is beginning to be outdated i guess since functions have been >> changing. >> What is the quickest way to summarize mean raster values within polygons? >> >> I used these queries before where lspop is the raster and groupclusters is >> the >> polygon: >> >> SELECT a.clusterid, >> (ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.the_geom, >> b.rast, '32BF'), b.rast, 'rast2', '32BF','INTERSECTION','0','0',0))), >> true)).* FROM groupclusters a LEFT JOIN lspop b ON ST_Intersects(a.the_geom, >> b.rast) WHERE a.clusterid = 4422 GROUP BY a.clusterid ORDER BY a.clusterid; >> >> A different query: >> >> SELECT clusterid, (ss).* FROM ( >> SELECT clusterid, ST_SummaryStatsAgg(gv) ss FROM ( SELECT gt.clusterid, >> ST_Clip(rt.rast, gt.the_geom) gv FROM lspop rt, groupclusters gt WHERE >> ST_Intersects(rt.rast, gt.the_geom) AND >> (gt.type=1 OR gt.type=3 OR gt.type=6) AND gt.endyear>=1990 >> ) foo >> WHERE clusterid = 4422 >> GROUP BY clusterid >> ) foo2; >> >> Are there any other ways of doing this? >> What is the PostGIS official raster summary statistics query for finding >> raster >> values within polygons? >> >> Best regards, >> >> Andreas >> _______________________________________________ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
