Getting zonal statistics of polygons not touching center raster cells in PostGIS?

Question

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:

The results of Stats

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,
       (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;