I have two rasters and I want to change all the values from "raster1" to 4, where they are larger than the values from "raster2" and to 0, where they are smaller.

So raster2 has only one value, which is 1.

raster1 has two values: 0,5 and 2

0,5 should change to 0 and 2 should change to 4

I tried with st_reclass and a case/when/else-argument, but then it changed ALL values to 0 or 4... Here's my code:

DROP TABLE IF EXISTS result_table;
CREATE TABLE result_table AS

        WHEN (pvc).value > (cvc).value
        THEN st_reclass(p.rast,1, '-999-999:4', '8BUI', NULL)
        ELSE st_reclass(p.rast,1, '-999-999:0', '8BUI', NULL)
    END AS rast
    raster1 as p,
    raster2 as c,
    (select st_valuecount(dcp.rast) as pvc from raster1 as dcp) as pp,
    (Select st_valuecount(dcubc.rast) as cvc from raster2 as dcubc) as cc;

Ok, I could just reclass the values without looking up the other raster, but I want to do this multiple times with different rasters and values.

  • You did not say what must happen when values are equal... Sep 5, 2018 at 17:34
  • Thanks, I forgot: all values lower or equal to those from raster 2 should change to 0.
    – blueJay
    Sep 7, 2018 at 9:18

2 Answers 2


You have to use the two raster version of ST_MapAlgebra(). Something like:

SELECT ST_MapAlgebra(p.rast, 1, 
                     c.rast, 1, 
                    'CASE WHEN [rast1] > [rast2] THEN 4 ELSE 0 END') rast
FROM raster1 as p, raster2 as c
  • Thanks for the answer, but this doesn't work the way I want, because it changes ALL values from raster1 to '4', not only those greater than 1.
    – blueJay
    Sep 7, 2018 at 9:17
  • I found out a different solution though, which seems to work for me: I changed the reclass-expression to: '-999-'||(Select distinct (a).value from (select st_valuecount(aaa.rast) as a from raster2 as aaa) as aa)|| ']:0, ('||(Select distinct (a).value from (select st_valuecount(aaa.rast) as a from raster2 as aaa) as aa)|| '-100]:2'
    – blueJay
    Sep 7, 2018 at 9:20

As commented in the answer above I found a solution to the problem:

    Update raster1 
    Set rast = ST_Reclass
    (rast, 1, '[-100-'||(Select distinct (a).value from (select st_valuecount(aaa.rast) as a raster2 as aaa) as aa) ||']:0,
                    ('||(Select distinct (a).value from (select st_valuecount(aaa.rast) as a raster2 as aaa) as aa) ||'-100]:4', '8BUI', NULL);
  • I don't understand how this works, when ST_ValueCount is an aggregate function. MapAlgebra is the only way you can do a pixel by pixel comparison. Sep 10, 2018 at 15:19
  • I don't really understand it either.. But when I try this with MapAlgebra (Pierre Racine's answer) and then check the result, the raster has only one value (4) where it should have two values (0 and 4). In my solution, I could just replace the whole subselection with 0.5, as raster2 has only this one value and I am already thinking about doing just that. Would make it a lot easier
    – blueJay
    Sep 12, 2018 at 13:18
  • Pierre Racine is the guy who created Postgis raster, so I tend to pay attention to what he says :-). I would suggest fiddling with his answer, because ST_ValueCount is an aggregate, and not pixel by pixel, so you may have got it right by chance. Sep 12, 2018 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.