I created a database in PostGIS with some MODIS data stored in the schema.table
modis10v04.gpp. A simple query to show the structure of my data is given as follows:
SELECT rid, filename, acquisition_range FROM modis10v04.gpp LIMIT 5;
My output is something like this:
As you can see, there are repeated info in the first 4 rows, this is because of my rasters are divided in tiles. To clarify, my dates range from 2003-01-01 to 2016-12-31.
Then, I have my geometry data in the schema.table
public.shape10v04 containing polygons with the column "id".
What I want is the average of my data for each polygon (within my shape10v04) for each date (from initial date to final date, e.g. 2005-10-10 to 2007-10-10).
I came across the post referred here: Calculate mean value of polygon from raster in PostGIS, but it stills blurry in terms of implementing it for a time-series of rasters.