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... – Pierre Racine Sep 5 '18 at 17:34
  • Thanks, I forgot: all values lower or equal to those from raster 2 should change to 0. – blueJay Sep 7 '18 at 9:18

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
| improve this answer | |
  • 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 '18 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 '18 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);
| improve this answer | |
  • 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. – John Powell Sep 10 '18 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 '18 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. – John Powell Sep 12 '18 at 15:34

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.