The mentioned function

Returns summarystats consisting of count, sum, mean, stddev, min, max for a given raster band of a raster or raster coverage.

I have an image of land cover classes (a discrete dataset), returning the mean does not make much sense since there are only classes (essentially integer numbers).

The procedure is like this: the image is clipped (using ST_clip) to the extent of a polygon (representing multiple farm fields) and then the dominant (majority) land cover class should be determined.


  • How to get the majority from the ST_SummaryStatsAgg() function directly?


  • How to modify ST_SummaryStatsAgg() to calculate the majority?


  • Is there another function/procedure that gives me the result that I want?
  • When you say majority, do you mean the class with the maximum count ? What happens if two of them share the same count (just to make sure nothing breaks in my code idea..) – GHRF Apr 27 '18 at 8:47
  • @GHRF yes, the class with the maximum count. Two classes with the same count: random would be fine, or the class that reached maximum first would also be an option. what's your idea? – Michael Apr 27 '18 at 8:55

Try something like this (assuming you have two tables : one with the polygons and an id, one with the raster which contains their class as an attribute)

With stats as (
        (ST_SummaryStatsAgg(ST_Clip(rast, geom), 1, true)).count, 
    FROM rasters, polygons
    GROUP BY id, geom, class)
SELECT distinct on (count)
    id, geom, count, class
FROM stats
WHERE count = (select max(count) from stats)
ORDER BY count, id, class;

The "distinct on (count)" makes sure that only one line is returned if several classes are the majority. Since we're ordering by class, it will take the first one alphabetically. There might be too many items in my GROUP BY clause but I'm never sure how much is too much and would rather be safe than sorry. Hope this works/helps !

  • Seems like a good answer, though not sure about group by id, whixh I think will just deaggregate everything. – John Powell Apr 27 '18 at 13:00
  • 1
    Well, here the id are linked to the polygons, not the rasters. Perhaps grouping by id and geom is redundant, though. – GHRF Apr 27 '18 at 13:03
  • Good point, id and geom refer to the same entitity, so grouping by both doesn't matter. – John Powell Apr 27 '18 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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