open source gis - Are Zonal Statistics possible in PostGIS2?

Now with postgis2 which supports rasters by default is it possible to do a zonal statistics analysis?

I've google it but I haven't found anything solid? Is there a tutorial outthere to get me started?

Can anyone give me an sql example on how to do it?

EDIT :

An updated (simplified) query according the blog Aragon mention :

CREATE TABLE sum_pop3 AS 
 SELECT gid, SUM((ST_SummaryStats(ST_Clip(rast,1,geom))).sum)
 FROM perez_grid, ls_den
 WHERE ST_Intersects(geom,rast) 
GROUP BY gid;

Answer

you should check out Store, manipulate and analyze raster data within the PostgreSQL/PostGIS spatial database document by Pierre Racine and Steve Cumming which was presented at FOSS, here. There are lots of function defined as raster statistics to solve your problem. i think ST_SummaryStats will help you about zonal statics, of course, not enough.

ST_SummaryStats(raster) will turn you a set of (min, max, sum, mean, stddev, count (of withdata pixels)) records.

Postgis 2.0 supports zonal statistics analysis with some sql queries which you have work on. i have came across a good documentation on net about zonal statitics with esri vs postgis 2.0. you should check out for some information at Moving Spatial here. both of them was given the same statistics result with some hard querying.

example query which was taken from Anthony Lopez site i have mentioned before...

 CREATE TABLE sum_pop2 AS
WITH 
   feat AS (SELECT gid, geom FROM perez_grid AS b ),
   b_stats AS
(SELECT  gid, (stats).*

FROM (
SELECT gid, ST_SummaryStats(ST_Clip(rast,1,geom)) AS stats
FROM ls_den
INNER JOIN feat
ON ST_Intersects(feat.geom,rast) ) AS foo )
SELECT gid, SUM(count) AS cell_count
  ,SUM(sum) AS population
FROM b_stats
 WHERE count > 0
GROUP BY gid

ORDER BY gid;

Esri Zonal Statistics result: Population: 207,578 Cell Count: 14,400

Postgis Method result: Population: 207,578 Cell Count: 14,400 .

Population Percent Difference: 0%

i havent tried it yet but Anthony mentioned that there was some performance problem with raster analysis with postgis.

i hope it helps you...