5

I'm working a postgis processing zonal stats from raster and I couldn't manage the problem of raster with so low resolution (like 9km width) and small polygons.

My problem is, these rasters are big (for all South America) if I try to resample the pixels to smaller ones, it will spend a lot of time.

There are a possible solution using the ST_SummaryStats() style, like my example code bellow.

I try this code and it only get stats when the polygon covers the centroid of pixel image.

SELECT gid, St_SummaryStats(St_Union(ST_Clip(ST_SetSRID(rast, 4326), 1, ST_SetSRID(geom, 4326), 0.0, true))) FROM raster_image, shape_polygons WHERE st_intersects(ST_SetSRID(rast, 4326), ST_SetSRID(the_geom, 4326)) GROUP BY gid;

The Example:

example of big pixels and small polygons

The results of Stats

stats results

  • 1
    Which stats are you interested in? You can use ST_NearestValue if you just want the raster value, and this should work even if the centroid is not covered by your polygon/ – John Powell Jul 9 '18 at 7:39
  • I need to get at least mean, standard deviation and count. – Isaque Daniel Jul 11 '18 at 14:35
5

When pixels are large comparing with polygons you better go "vector way". i.e. vectorize the raster tiles and then procede to a vector/vector intersection/computation. If you use the PostGIS Addons, you can do it like this:

SELECT gt.id,
       (aws).geom,
       (aws).totalarea,
       (aws).weightedmean,
FROM (SELECT id, ST_AreaWeightedSummaryStats(gv) aws
      FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv
            FROM rasttable rt, geomtable gt
            WHERE ST_Intersects(rt.rast, gt.geom)
           ) foo1
      GROUP BY gt.id
     ) foo2;
  • 1
    Source for Postgis addons. I am glad that Pierre has answered this because he is the expert in Postgis raster. I was going to suggest vector to vector as the way forward, but was concerned there might be a better way. Apparently not. – John Powell Jul 11 '18 at 15:14
  • @pierre-racine how to install your Postgis package functions? Only to left in Postgis folder? I found your blog with some instructions, but I couldn't figure how to work correctly Pierre Racine - Geospatial Elucubrations Ps.: I'm using ubuntu. – Isaque Daniel Jul 12 '18 at 18:34
  • 2
    Just execute the postgis_addons.sql file in PgAdmin (or with PSQL). You can then run the test file postgis_addons_test.sql – Pierre Racine Jul 13 '18 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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