I have around 2000 raster file in TIFF format of 4.5Gb size. I want to store them in a PostGIS DB in order to do some mapalgebra operation between them and than query the results of the calculation with a vector of polygons doing zonal statics, in particular mean value. I'll like to do this operation in the quickest way possible. can you lease suggest me an SQL query do so in the fastest way, or other possible solutions?
The typical query for zonal statistics over a raster table looks like this:
SELECT polyid, sum(ST_Area((gv).geom) * (gv).val) / sum(ST_Area((gv).geom)) AS meanval FROM (SELECT polyid, ST_Intersection(rast, geom) AS gv FROM tiled_raster_table, polygon_table WHERE ST_Intersects(rast, geom)) foo GROUP BY polyid;
Your raster table has to be tiled (e.g. 32x32) and indexed (on the rast column) for best performance.
You might also have to project your polygon table to the same coordinate system as your raster table (not the opposite) if it's not already the case.
This tutorial show the whole process but it is a bit outdated.