-1

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
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Can 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. – tilt Oct 7 '16 at 15:33
1

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.