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?
1
-
2Can you point out what you already did yourself? Are there specific issues that you are encountering? It always helps when you provide people with a clear question instead of a generic one.– tiltOct 7, 2016 at 15:33
Add a comment
|
1 Answer
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.
