Calculate area of a raster for an irregular polygon in PostGIS

I have a number of polygons with irregular shapes (i.e. non-rectangular) and want to calculate their areas after I have converted them to rasters.

Here are my steps:

1. Convert my geometry to a raster, using a reference raster with fixed pixel sizes (50m x 50m).
2. Clip the new raster to the old geometry to get rid of the extra pixels outside of the area of interest.
3. Calculate the area by counting the pixels and multiplying by the known fixed sizes. (This is the part I am having trouble with)

My PostGIS query:

``````SELECT
ST_ValueCount(ST_CLIP(ST_AsRaster(my_geometry, reference_raster)), 1) as pixel_count,
ST_Area(my_geometry) as geom_area
FROM ra
``````

This gives me

`````` | pixel_count | geom_area
-+-------------------------
0|     (1, 123)|      4567
``````

which is not an array so I cannot index to get the count and directly compare to the geometry area.

I could extract it in Python and but it seems to me there should be a clean way to do this in PostGIS. Does anyone know how to do this?

You are using the wrong function. You want to use `ST_Count()` not `ST_ValueCount()`. `ST_ValueCount()` looks at the pixel values and counts by pixel values. This is why you get 2 numbers: Field 1 is the pixel value and field 2 is the count.
• What is the correct way to use `ST_ValueCount` to find the count of specific pixel values? For me the first element is always 1, e.g. (1, 15) or (1, 123) Apr 7, 2015 at 17:36