[postgis-users] raster aggregate query

[Pierre Racine]

I use ST_Union(rast, 'MEAN') for this...

SELECT r.rid, ST_Union(ST_Clip(r.rast,g.the_geom), 'MEAN') AS rast INTO rastmean 
FROM daily_temp r, usa_lcc g 
WHERE observation_date BETWEEN '2011-12-01' AND '2011-12-01';

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Stephen Crawford
> Sent: Wednesday, February 08, 2012 1:54 PM
> To: PostGIS Users Discussion
> Subject: [postgis-users] raster aggregate query
> 
> 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
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users