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.

  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))

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/ Commented Jul 9, 2018 at 7:39
  • I need to get at least mean, standard deviation and count. Commented Jul 11, 2018 at 14:35

1 Answer 1


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,
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. Commented Jul 11, 2018 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. Commented Jul 12, 2018 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 Commented Jul 13, 2018 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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