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
