[postgis-users] Summary Statistics (vector, raster)

[Andreas Forø Tollefsen]

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