Selecting all pixels above a given value in a raster in PostGIS

Question

I have a raster with one band whose value represents the date of the occurrence (a sequential integer). This raster file gets updated regularly, and each time that happens, I need to run a process that extracts the new pixels, converts them to points and populates them in a vector table along with the value and some other data (calculated on the fly and not represented here).

So far I've come up with this

with 
c as (select max(date_code) as m from vector_table),
p as (Select ST_PixelAsCentroids (rast) as pixel from raster)
insert into vector_table (geom, date_code)
(select (p.pixel).geom as geom, (p.pixel).val as date_code from p,c where (p.pixel).val>c.m)

The problem with this approach is that in order to get the new pixels I need to convert the whole raster to point in a CTE, and then filter it to just the latest values.

Looking at the PostGIS pixel and band accessors I couldn't find anything that would let me select pixels above a certain value in just one query (except maybe setting everything below, one by one, to no data, which doesn't look like the way to go at all).

Is there a way I can run ST_PixelAsCentroids only in pixels that meet certain criteria in their band value?

Answer 1

Even if this question is old, I was searching for something similar and couldn't find something. I was searching for a way to select the 10% brightest pixels in a specific band and came up with this solution:

    with quantile as (select id, (st_quantile(rast,4,ARRAY[0.90])).value as quantile90, rast from rast)
SELECT id, 
    ST_Intersection( 
        rast,
        ST_reclass(rast, 4, '0-' || quantile90::text ||':0, ' || quantile90::text ||'-70000:1', '16BUI',0)
    ) as rast from quantile

But it should work for your problem as well. I reclassified the raster according to the break value to zero and one and use this reclassified raster for intersection with your original raster. All values below your specific break value will be NULL afterwards and will not be taken into account for example to st_summarystats.