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, http://postgis.net/docs/manual-2.1/RT_ST_Count.html

not ST_ValueCount. ST_ValueCount looks at the pixel values and counts by pixel values (which is why you get 2 number -- 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) – Ellis Valentiner Apr 7 '15 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; – LR1234567 Apr 8 '15 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) – LR1234567 Apr 8 '15 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. – John Powell Apr 8 '15 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) – LR1234567 Apr 9 '15 at 9:57

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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