[postgis-users] raster aggregate query

[Bborie Park]

Hey Steve,

I do something like this.  My query template looks something like:

SELECT
	sum(sum) / sum(count) AS average
FROM (
	SELECT
		(ST_SummaryStats(ST_Intersection(r.tile, 1, g.shape))).*
	FROM tops.tmean AS r
	CROSS JOIN (
		SELECT
			ST_GeomFromEWKB(E'%s') AS shape
	) AS g
	WHERE r.observation_date BETWEEN '%s' AND '%s'
		AND ST_Intersects(r.tile, g.shape)
) foo

The key bits are the ST_Intersection of the geometry used as a spatial 
filter.

-bborie

On 02/08/2012 10:53 AM, Stephen Crawford wrote:
> 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
>
>

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu