2

I would need to get different statistics than are available in ST_SummaryStats.

In particular I need mode (majority) and variety (number of different values) for a discrete raster and according polygonal zones. This calculations are available for example in zonal statistics tool in QGIS, but I am looking for solution with PostGIS query.

Any clue?

Improve this question
1
  • 1
    I haven't done much (ie almost nothing) with raster in PostGIS, so don't want to add this as an answer. Having said that, I believe you can get the stats you want with ST_ValueCount.
    – jgm_GIS
    Commented Aug 31, 2020 at 19:48

1 Answer 1

Reset to default
1

To get mode and number of pixel of each values, there is the function ST_ValueCount. You can get all the polygons of all the differents values using ST_DumpAsPolygons, and select after the one you wants.

Actually there is a lot of ways of doing this, for exemple by selecting the values that you want before dumping the raster into polygons, it can be more efficient if you have performance issues.

Also, if you want to do specific computation on raster, you should look into ST_MapAlgebra_expr for exemple.

The doc of raster functions is here: raster documentation

EDIT:

I forgot the selection by polygon, you can use ST_Clip, more info here: Clipping tiled raster with polygon using PostGIS?

Improve this answer
1
  • Looks reasonable but looks like there are serious performance issues. Just limited 100 rows took me 2 min and full extent zonal stats with 132 000 features in QGIS lasted 45 s (probably python based).
    – Albine Pro
    Commented Sep 1, 2020 at 14:11

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.