3

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 100.

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)
4

You are looking for ST_Reclass. There are three types of function signature, essentially differing on how you pass the reclassargs, which is essentially a textual encoding for mapping input to output pixel ranges.

UPDATE bin_rast SET reclass_rast = ST_Reclass(bin_rast, 1, '0:-999, 1:100', '8BUI', 0) 
WHERE rid = 1;

where obviously you will need to put in your own values for pixel type, band number, no value, raster names, and reclass_rast simply represents a new raster column (rather than updating in place).

If you want to actually do this from a lookup table dynamically, then you can use string concatenation along with a ::reglassargs cast, eg,

SELECT string_agg(concat(key,':', val)::text, ',') FROM lookup;

which will produce 0:-999,1:100. Putting that all together,

UPDATE bin_rast SET reclass_rast = 
   ST_Reclass(bin_rast, 1, 
    (select concat('''',string_agg(concat(key,':', val)::text, ','), '''') 
     FROM lookup)::regclassexpr, 
    '8BUI', 0) 
WHERE rid = 1;

If you don't like all the apostrophe escaping '''', look at the quote_ident function, but I have always done it this way.

  • Thanks a lot. This is what I am looking for. I tried though, and I am always getting an error saying: Invalid argument for reclassargset. Any suggestions? – tinlyx Dec 21 '14 at 23:18
  • Sorry, there was a typo in the cast, it should be regclassarg, though I'm not sure that will fix it. You are talking about the 2nd query, no, or the first one, with hardcoded reclass values? I don't have a box with raster functions on right now to test, so can't look properly till the morning. – John Powell Dec 21 '14 at 23:25
  • I noticed the typo and fixed it when I try. The problem seems to be elsewhere Please see my update. – tinlyx Dec 21 '14 at 23:31
  • Try casting the inner query that creates the actual expression, '[0-0]:-99999, [1-1]:100', as a reclassexpr rather than text. Again, apologies, I can't test right now. – John Powell Dec 21 '14 at 23:45
  • I tried your update modulo the typo:P. It says type "reclassexpr" does not exist" – tinlyx Dec 21 '14 at 23:47

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.