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.

Current Approach:

  1. filter the raster table with ST_Intersects(rast,geom)
  2. Clip raster to geom with ST_Clip(rast,geom)
  3. 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)
    year INT,
    day_of_year INT,
    result float8[]
AS $$
 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?

  • two suggestions 1. you might want to wrap st_union around the st_clip function 2. postgis.net/docs/RT_ST_Intersection.html this is a raster/geom intersection function. it may or maybe not be faster worth checking it out though – ziggy May 26 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.