[postgis-users] raster aggregate query

[Stephen Crawford]

Hi All,

I have a raster table "daily_temp" which has a month's worth of daily 
average temperate data for the USA.  The table looks like this:

rid   serial
obervation_date   date
rast   raster

Each day is tiled so for a given date there are 187 rows. I have been 
able to use "WHERE observation_date = '2011-12-01'" to select the data 
for one day, and this query worked create to clip it to the USA borders:

SELECT r.rid, ST_Clip(r.rast,g.the_geom) AS rast INTO cliptest FROM 
daily_temp r, usa_lcc g WHERE r.observation_date = '2011-12-01'

But what I would really like to do is aggregate multiple days to create 
a new raster table, for example "mean temperature for the last two 
weeks".  My "pseudo SQL" for this:

SELECT MEAN(rast) AS rast INTO mean_temp FROM avg_temp WHERE 
observation_date BETWEEN '2011-12-01' AND '2011-12-01'

Of course that doesn't work.  But I can't figure out how to do it.  Do I 
use ST_Union(rast, 'MEAN') for this?  Or ST_MapAlgebraExpr()?  Both??

Thanks,
Steve


-- 
Stephen Crawford
Center for Environmental Informatics
The Pennsylvania State University