Vjylku

Is above average number of years spent on PhD considered a red flag in future academia or industry positions?

What does the "x" in "x86" represent?

Examples of mediopassive verb constructions

"Seemed to had" is it correct?

Does accepting a pardon have any bearing on trying that person for the same crime in a sovereign jurisdiction?

What do you call a plan that's an alternative plan in case your initial plan fails?

When -s is used with third person singular. What's its use in this context?

How can players work together to take actions that are otherwise impossible?

Right-skewed distribution with mean equals to mode?

How do I keep my slimes from escaping their pens?

Why does Python start at index 1 when iterating an array backwards?

Storing hydrofluoric acid before the invention of plastics

What is the longest distance a 13th-level monk can jump while attacking on the same turn?

How do I determine if the rules for a long jump or high jump are applicable for Monks?

How to draw this diagram using TikZ package?

Why was the term "discrete" used in discrete logarithm?

How to motivate offshore teams and trust them to deliver?

Can Pao de Queijo, and similar foods, be kosher for Passover?

Java 8 stream max() function argument type Comparator vs Comparable

Does surprise arrest existing movement?

When is phishing education going too far?

How do I mention the quality of my school without bragging

List *all* the tuples!

Gastric acid as a weapon

PostGIS: Change raster values depending on values from other raster

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

0

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



SELECT

    p.id,

    case

        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

FROM

    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.

postgis raster postgresql values

share|improve this question

edited Sep 5 '18 at 22:41

Damini Jain

1,560217

asked Sep 4 '18 at 9:34

blueJayblueJay

1

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

0

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



SELECT

    p.id,

    case

        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

FROM

    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.

postgis raster postgresql values

share|improve this question

edited Sep 5 '18 at 22:41

Damini Jain

1,560217

asked Sep 4 '18 at 9:34

blueJayblueJay

1

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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



SELECT

    p.id,

    case

        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

FROM

    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.

postgis raster postgresql values

share|improve this question

edited Sep 5 '18 at 22:41

Damini Jain

1,560217

asked Sep 4 '18 at 9:34

blueJayblueJay

1

DROP TABLE IF EXISTS result_table;

CREATE TABLE result_table AS



SELECT

    p.id,

    case

        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

FROM

    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;

share|improve this question

edited Sep 5 '18 at 22:41

Damini Jain

1,560217

asked Sep 4 '18 at 9:34

blueJayblueJay

1

share|improve this question

edited Sep 5 '18 at 22:41

Damini Jain

1,560217

asked Sep 4 '18 at 9:34

blueJayblueJay

1

edited Sep 5 '18 at 22:41

Damini Jain

1,560217

edited Sep 5 '18 at 22:41

Damini Jain

1,560217

asked Sep 4 '18 at 9:34

blueJayblueJay

1

asked Sep 4 '18 at 9:34

blueJayblueJay

1

2 Answers
2

active

oldest

votes

0

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

share|improve this answer

answered Sep 5 '18 at 17:34

Pierre RacinePierre Racine

1,51698

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

0

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

share|improve this answer

answered Sep 7 '18 at 10:07

blueJayblueJay

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "79"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f294865%2fpostgis-change-raster-values-depending-on-values-from-other-raster%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

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

share|improve this answer

answered Sep 5 '18 at 17:34

Pierre RacinePierre Racine

1,51698

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

0

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

share|improve this answer

answered Sep 5 '18 at 17:34

Pierre RacinePierre Racine

1,51698

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Sep 5 '18 at 17:34

Pierre RacinePierre Racine

1,51698

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

share|improve this answer

answered Sep 5 '18 at 17:34

Pierre RacinePierre Racine

1,51698

answered Sep 5 '18 at 17:34

Pierre RacinePierre Racine

1,51698

answered Sep 5 '18 at 17:34

Pierre RacinePierre Racine

1,51698

0

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

share|improve this answer

answered Sep 7 '18 at 10:07

blueJayblueJay

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

0

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

share|improve this answer

answered Sep 7 '18 at 10:07

blueJayblueJay

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Sep 7 '18 at 10:07

blueJayblueJay

1

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

share|improve this answer

answered Sep 7 '18 at 10:07

blueJayblueJay

1

answered Sep 7 '18 at 10:07

blueJayblueJay

1

answered Sep 7 '18 at 10:07

blueJayblueJay

1