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?