Given a multi band raster, I would like to summarise the distinct combinations of values found, in a manner similar to ST_ValueCount, but across all the bands rather than a single band at a time.

Example data

(2 band 3x3 raster) test_rast:

Band 1

1 1 1
2 2 2
1 1 1

Band 2

1 1 1
1 3 3
1 3 3

Summarizing single bands:

SELECT (vc).* FROM (SELECT ST_ValueCount(rast,1) As vc FROM test_rast) AS d;
value count
1 6
2 3
SELECT (vc).* FROM (SELECT ST_ValueCount(rast,2) As vc FROM test_rast) AS d;
value count
1 5
3 4

Summarizing multiple bands:

What I would like to return is the following:

band1 band2 count
1 1 4
2 1 1
2 3 2
1 3 2

I could achieve this using generate_series to index the raster and aggregate the values returned as outlined in this answer, but as the comments to the answer suggest, this is very inefficient. Is there a more efficient approach that I could take?

1 Answer 1


Here's a solution to this that I have developed, using the ST_DumpValues functionality:

SELECT r1val,r2val, COUNT(*)
        ST_DumpValues(SELECT rast FROM test_rast,1,FALSE),
        ST_DumpValues(SELECT rast FROM test_rast,2,FALSE)
    AS u(r1val,r2val)
    GROUP BY r1val,r2val;

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.