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?
closed as off-topic by PolyGeo♦ May 21 '18 at 21:36
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking help to debug/write/improve code must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Providing a clear problem statement and evidence of a code attempt will help others to help you. See: How to create a Minimal, Complete, and Verifiable example." – PolyGeo
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.