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

[Pierre Racine]

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-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users