I have tiled (50x50), 365 band, rasters in my postgis database. Each band represents a day in a year.
I want to return the raster pixels which intersect with an input geometry, including all bands. I'd like to figure out the most optimized way of doing this.
- filter the raster table with ST_Intersects(rast,geom)
- Clip raster to geom with ST_Clip(rast,geom)
- St_Dumpvalues(clip) the result of clip to examine the band-pixel values of the clip
CREATE OR REPLACE FUNCTION get_time_series (start_year INT, end_year INT, geom geometry) RETURNS TABLE ( year INT, day_of_year INT, result float8 ) AS $$ BEGIN RETURN QUERY with myresult as( with myclip as ( SELECT rast_table.rid,st_clip(rast_table.rast, geom) as clip,rast_table.year FROM rast_table_50x50 rast_table WHERE ST_Intersects(rast_table.rast, geom) AND rast_table.year BETWEEN start_year AND end_year ) SELECT (st_dumpvalues(clip)).*,myclip.year from myclip ) SELECT myresult.year,myresult.nband as day_of_year,valarray as result FROM myresult; END; $$ LANGUAGE 'plpgsql';
Is there another approach which might yield better peformance?
Would a plpython function be of any use for optimizing the clip/mask operation?
Should I be clipping at all?