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:

    ST_ValueCount(ST_CLIP(ST_AsRaster(my_geometry, reference_raster)), 1) as pixel_count,
    ST_Area(my_geometry) as geom_area

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?

1 Answer 1


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
  • It returns a composite object So you'd do something like (postgresql 9.3 LATERAL syntax) : SELECT (pv).*, ST_Area(my_geometry) as geom_area FROM ra CROSS JOIN LATERAL ST_ValueCount(ST_CLIP(ST_AsRaster(my_geometry, reference_raster)), 1) As pv;
    – Regina Obe
    Apr 8, 2015 at 4:34
  • Forgot to say non-lateral syntax examples in docs -- postgis.net/docs/manual-2.1/RT_ST_ValueCount.html (you would wrap in a subselect or WITH clause)
    – Regina Obe
    Apr 8, 2015 at 4:37
  • I am familiar with the examples in the docs, but how does the Lateral help in the above. I am happy to ask a new question, if you would prefer. Apr 8, 2015 at 13:05
  • LATERAL prevents the ST_ValueCount from being called multiple times for each field you would use otherwise (pv).* would get called twice for each row (one for the value and one for the count column)
    – Regina Obe
    Apr 9, 2015 at 9:57

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.