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

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.


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

So would be for your case

 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.

| improve this answer | |
  • This method works perfectly and is fast. Changed selected answer to this one, as its more efficient. thanks! – yellowcap Sep 26 '14 at 8:05
  • Can't argue with that. A very useful function ST_ValueCount. How much faster is it, than the ST_Value with generate_series approach, as a matter of interest. – John Powell Sep 26 '14 at 8:06
  • 1
    On my machine, if including the SUM/Count part, the ST_ValueCount function returns in 11 ms, which compares with 141 ms for the generate_series approach for a 100x100 pixel raster. And I guess that might become worse for larger rasters, not sure if they both scale the same way... – yellowcap Sep 26 '14 at 15:42
  • Thanks guys. Got inspired by this to write a tip. I have demonstration of using lateral clause and also using with clipping and intersect. postgis.net/2014/09/26/tip_count_of_pixel_values – LR1234567 Sep 26 '14 at 21:34

There are various forms of ST_Value and the one that will probably help you is the ST_Value(rast, band, x, y) one.

Modifying one of the examples from the docs, you can dump all the values for each pixel using generate_series in the x and y direction, and then use group by (or distinct) on those to get the list of unique pixel values.

SELECT count(b1val), b1val FROM
  (SELECT ST_Value(rast, 1, x, y) As b1val  
   FROM dummy_rast CROSS JOIN
     generate_series(1, raster_width) As x 
     CROSS JOIN generate_series(1, raster_height) As y
   WHERE rid =  raster_id) as rast_values
 group by b1val;

where you will need to provide values for raster_id, raster_width and raster_height and I have assumed you are looking at band 1.

| improve this answer | |
  • Thanks for the suggestion, this works indeed. The problem I ran into with this solution is that it is slow. For a raster tile of 100x100 pixels it took 120ms, and the data I am using it on has >5000 such tiles. Now I am wondering if there is a more version of it that does not involve cross joins. – yellowcap Sep 25 '14 at 16:00
  • 1
    I haven't used the raster stuff much (sadly, yet), though for some operations people talk about speed issues. Summarizing 10,000 pixels in 120ms doesn't strike me as excessively slow though. If you really want to speed things up, you would need to use C or something similar and pointers into the pixel array. – John Powell Sep 25 '14 at 16:04
  • That's right, the query is not slow for what it does. But I think a SELECT DISTINCT query on an integer column can be much faster. It seems like the values of rasters are not so directly accessible. – yellowcap Sep 25 '14 at 16:15
  • You should be able to change the above to select distinct bval from (....) and lose the group by. I can't test right now, but if you already have integer values, I would think this would work. – John Powell Sep 25 '14 at 16:20

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.