[GIS] How to extract all distinct values from a PostGIS Raster

postgisqueryrastersql

I am working with an integer raster in PostGIS 2.1 and I would like to extract all distinct values in that raster. I was hoping to be able to do something along the lines of the following query, but I did not find a solution that works so far.

SELECT DISTINCT ST_Value(rast) FROM myrastertable

Best Answer

There is an easier and more efficient way of doing this. Works for both PostGIS 2.1 and 2.0. Just use the ST_ValueCount function.

http://postgis.net/docs/manual-2.1/RT_ST_ValueCount.html

that will give you both the pixel value and number of pixels that have that value.

So would be for your case

SELECT DISTINCT (pvc).value
 FROM (SELECT ST_ValueCount(rast) As pvc
   FROM myrastertable) As f;

If you also want count of pixels that have each value, do 

SELECT (pvc).value, SUM((pvc).count) As total
 FROM (SELECT ST_ValueCount(rast) As pvc
   FROM myrastertable) As f
    GROUP BY (pvc).value;

You might also want to throw in an order by (pvc).value to sort by the pixel value.

Related Question