I was wondering if it is possible to write a PostgreSQL statement to replace the value of each pixel in an input PostGIS-raster with a value obtained from a look up table.
For example, suppose I have a binary raster
bin_rast, and a look up table
lookup as follows:
key val ------------ 0 -999 1 100
I wanted to replace every 0 pixel with
-999, and every
1 pixel with
How can I write such statement(s)?
This is what eventually worked for me, per @John's answer (with minor changes).
DROP TABLE IF EXISTS lookup; CREATE TEMP TABLE lookup AS SELECT * FROM ( VALUES (0,-99999::numeric), (1,100::numeric) ) AS t (key, val); UPDATE my_rast SET rast = ST_Reclass(rast, ROW(1, (SELECT string_agg(concat('[',key,'-',key,']:', val), ',') FROM lookup), '16BSI', 0)::reclassarg)