PostGIS How to: Calculate the average raster pixel value inside a polygon

Posted on 02 November 2011

In my daily work I use both vector and raster data and the combination of both. Often, I want to include raster data into our common vector grid. Hence, I often want averaged raster pixel values inside each of the grid cells in our vector grid.
To do this, we need to use both the vector and raster functionality in PostGIS.
In this example i want to calculate the average infant mortality rate pixel value inside each vector grid cell.
One query we use for this is:

SELECT gid, CAST(AVG(((foo.geomval).val)) AS decimal(9,3)) as avgimr FROM (SELECT p.gid, ST_Intersection(r.rast, p.cell) AS geomval FROM imrgrid2p5m r, priogrid_land p WHERE ST_Intersects(p.cell, r.rast)) AS foo WHERE (foo.geomval).val >=0 GROUP BY gid ORDER BY gid;

To explain, we select the vector grid cell’s gid, and the average of (foo.geomval).val FROM the Intersection of the infant mortality raster and the vector grid. Hence, the geomval is a list of each pixels intersection with the vector grid cell. This geomval contain both the actual geometry of this intersection and the value of the pixel. The we apply a where clause of where the cell and raster intersects since the raster is tiled. We also do not want to include negative values (missings) since infant mortality rate is not negative. Then we group the query by the grid cell’s gid since we are aggregating the average pixel value.