From 729741d8b6da4a94f922dd9a71e39ceb091cdcd5 Mon Sep 17 00:00:00 2001 From: Bborie Park Date: Fri, 3 Feb 2012 22:57:36 +0000 Subject: [PATCH] Implemented keywords for 2-raster ST_MapAlgebraExpr as described in #1525. Will do 2-raster ST_MapAlgebraFct next. git-svn-id: http://svn.osgeo.org/postgis/trunk@9023 b70326c6-7e19-0410-871a-916f4a2858ee --- raster/rt_pg/rt_pg.c | 198 ++++++++++-------- raster/rt_pg/rtpostgis.sql.in.c | 30 +-- raster/scripts/plpgsql/st_clip.sql | 4 +- raster/scripts/plpgsql/st_union.sql | 30 +-- .../regress/rt_mapalgebraexpr_2raster.sql | 44 ++-- 5 files changed, 165 insertions(+), 141 deletions(-) diff --git a/raster/rt_pg/rt_pg.c b/raster/rt_pg/rt_pg.c index cf69cd1db..f5e79d12e 100644 --- a/raster/rt_pg/rt_pg.c +++ b/raster/rt_pg/rt_pg.c @@ -8390,6 +8390,7 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) double _rastoffset[2][4] = {{0.}}; int _haspixel[2] = {0}; double _pixel[2] = {0}; + int _pos[2][2] = {{0}}; uint16_t _dim[2][2] = {{0}}; char *pixtypename = NULL; @@ -8407,15 +8408,17 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) Oid calltype = InvalidOid; - int spicount = 3; - uint16_t exprpos[3] = {4, 7, 8}; + const int spi_count = 3; + uint16_t spi_exprpos[3] = {4, 7, 8}; + uint32_t spi_argcount[3] = {0}; char *expr = NULL; char *sql = NULL; - SPIPlanPtr spiplan[3] = {NULL}; - uint16_t spiempty = 0; + SPIPlanPtr spi_plan[3] = {NULL}; + uint16_t spi_empty = 0; Oid *argtype = NULL; - uint32_t argcount[3] = {0}; - int argexists[3][2] = {{0}}; + const int argkwcount = 8; + uint8_t argpos[3][8] = {{0}}; + char *argkw[] = {"[rast1.x]", "[rast1.y]", "[rast1.val]", "[rast1]", "[rast2.x]", "[rast2.y]", "[rast2.val]", "[rast2]"}; Datum *values = NULL; bool *nulls = NULL; TupleDesc tupdesc; @@ -8433,6 +8436,7 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) FunctionCallInfoData ufcinfo; int ufcnullcount = 0; + int idx = 0; uint32_t i = 0; uint32_t j = 0; uint32_t k = 0; @@ -8820,44 +8824,46 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) } /* reset hasargval */ - memset(hasargval, 0, spicount); + memset(hasargval, 0, spi_count); /* process expressions - exprpos elements are: - 4 - expression => spiplan[0] - 7 - nodata1expr => spiplan[1] - 8 - nodata2expr => spiplan[2] + spi_exprpos elements are: + 4 - expression => spi_plan[0] + 7 - nodata1expr => spi_plan[1] + 8 - nodata2expr => spi_plan[2] */ - for (i = 0; i < spicount; i++) { - if (!PG_ARGISNULL(exprpos[i])) { - char *tmp = text_to_cstring(PG_GETARG_TEXT_P(exprpos[i])); - POSTGIS_RT_DEBUGF(3, "raw expr #%d: %s", i, tmp); - - len = 0; - expr = rtpg_strreplace(tmp, "RAST1", "$1", &len); - pfree(tmp); - if (len) { - argcount[i]++; - argexists[i][0] = 1; - } - - len = 0; - tmp = rtpg_strreplace(expr, "RAST2", (argexists[i][0] ? "$2" : "$1"), &len); - pfree(expr); - expr = tmp; - if (len) { - argcount[i]++; - argexists[i][1] = 1; + for (i = 0; i < spi_count; i++) { + if (!PG_ARGISNULL(spi_exprpos[i])) { + char *tmp = NULL; + char place[5] = "$1"; + expr = text_to_cstring(PG_GETARG_TEXT_P(spi_exprpos[i])); + POSTGIS_RT_DEBUGF(3, "raw expr #%d: %s", i, expr); + + for (j = 0, k = 1; j < argkwcount; j++) { + /* attempt to replace keyword with placeholder */ + len = 0; + tmp = rtpg_strreplace(expr, argkw[j], place, &len); + pfree(expr); + expr = tmp; + + if (len) { + spi_argcount[i]++; + argpos[i][j] = k++; + + sprintf(place, "$%d", k); + } + else + argpos[i][j] = 0; } len = strlen("SELECT (") + strlen(expr) + strlen(")::double precision"); sql = (char *) palloc(len + 1); if (sql == NULL) { - elog(ERROR, "RASTER_mapAlgebra2: Unable to allocate memory for expression parameter %d", exprpos[i]); + elog(ERROR, "RASTER_mapAlgebra2: Unable to allocate memory for expression parameter %d", spi_exprpos[i]); - for (k = 0; k < spicount; k++) SPI_freeplan(spiplan[k]); + for (k = 0; k < spi_count; k++) SPI_freeplan(spi_plan[k]); SPI_finish(); for (k = 0; k < set_count; k++) rt_raster_destroy(_rast[k]); @@ -8874,13 +8880,13 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) POSTGIS_RT_DEBUGF(3, "sql #%d: %s", i, sql); /* create prepared plan */ - if (argcount[i]) { - argtype = (Oid *) palloc(argcount[i] * sizeof(Oid)); + if (spi_argcount[i]) { + argtype = (Oid *) palloc(spi_argcount[i] * sizeof(Oid)); if (argtype == NULL) { - elog(ERROR, "RASTER_mapAlgebra2: Unable to allocate memory for prepared plan argtypes of expression parameter %d", exprpos[i]); + elog(ERROR, "RASTER_mapAlgebra2: Unable to allocate memory for prepared plan argtypes of expression parameter %d", spi_exprpos[i]); pfree(sql); - for (k = 0; k < spicount; k++) SPI_freeplan(spiplan[k]); + for (k = 0; k < spi_count; k++) SPI_freeplan(spi_plan[k]); SPI_finish(); for (k = 0; k < set_count; k++) rt_raster_destroy(_rast[k]); @@ -8888,15 +8894,15 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) PG_RETURN_NULL(); } - for (j = 0; j < argcount[i]; j++) argtype[j] = FLOAT8OID; + for (j = 0; j < spi_argcount[i]; j++) argtype[j] = FLOAT8OID; - spiplan[i] = SPI_prepare(sql, argcount[i], argtype); - if (spiplan[i] == NULL) { - elog(ERROR, "RASTER_mapAlgebra2: Unable to create prepared plan of expression parameter %d", exprpos[i]); + spi_plan[i] = SPI_prepare(sql, spi_argcount[i], argtype); + if (spi_plan[i] == NULL) { + elog(ERROR, "RASTER_mapAlgebra2: Unable to create prepared plan of expression parameter %d", spi_exprpos[i]); pfree(sql); pfree(argtype); - for (k = 0; k < spicount; k++) SPI_freeplan(spiplan[k]); + for (k = 0; k < spi_count; k++) SPI_freeplan(spi_plan[k]); SPI_finish(); for (k = 0; k < set_count; k++) rt_raster_destroy(_rast[k]); @@ -8912,10 +8918,10 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) else { err = SPI_execute(sql, TRUE, 0); if (err != SPI_OK_SELECT || SPI_tuptable == NULL || SPI_processed != 1) { - elog(ERROR, "RASTER_mapAlgebra2: Unable to evaluate expression parameter %d", exprpos[i]); + elog(ERROR, "RASTER_mapAlgebra2: Unable to evaluate expression parameter %d", spi_exprpos[i]); pfree(sql); - for (k = 0; k < spicount; k++) SPI_freeplan(spiplan[k]); + for (k = 0; k < spi_count; k++) SPI_freeplan(spi_plan[k]); SPI_finish(); for (k = 0; k < set_count; k++) rt_raster_destroy(_rast[k]); @@ -8931,11 +8937,11 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) datum = SPI_getbinval(tuple, tupdesc, 1, &isnull); if (SPI_result == SPI_ERROR_NOATTRIBUTE) { - elog(ERROR, "RASTER_mapAlgebra2: Unable to get result of expression parameter %d", exprpos[i]); + elog(ERROR, "RASTER_mapAlgebra2: Unable to get result of expression parameter %d", spi_exprpos[i]); pfree(sql); if (SPI_tuptable) SPI_freetuptable(tuptable); - for (k = 0; k < spicount; k++) SPI_freeplan(spiplan[k]); + for (k = 0; k < spi_count; k++) SPI_freeplan(spi_plan[k]); SPI_finish(); for (k = 0; k < set_count; k++) rt_raster_destroy(_rast[k]); @@ -8955,7 +8961,7 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) pfree(sql); } else - spiempty++; + spi_empty++; } /* nodatanodataval */ @@ -9030,7 +9036,7 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) /* if any expression present, run */ if (( (calltype == TEXTOID) && ( - (spiempty != spicount) || hasnodatanodataval + (spi_empty != spi_count) || hasnodatanodataval ) ) || ( (calltype == REGPROCEDUREOID) && (ufcnoid != InvalidOid) @@ -9047,6 +9053,10 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) _x = x - (int) _rastoffset[i][0]; _y = y - (int) _rastoffset[i][1]; + /* store _x and _y in 1-based */ + _pos[i][0] = _x + 1; + _pos[i][1] = _y + 1; + /* get pixel value */ if (_band[i] == NULL) { if (!_hasnodata[i]) { @@ -9064,7 +9074,7 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) elog(ERROR, "RASTER_mapAlgebra2: Unable to get pixel of %s raster", (i < 1 ? "FIRST" : "SECOND")); if (calltype == TEXTOID) { - for (k = 0; k < spicount; k++) SPI_freeplan(spiplan[k]); + for (k = 0; k < spi_count; k++) SPI_freeplan(spi_plan[k]); SPI_finish(); } @@ -9078,7 +9088,7 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) _haspixel[i] = 1; } - POSTGIS_RT_DEBUGF(5, "r%d(%d, %d) = %d, %f", + POSTGIS_RT_DEBUGF(5, "pixel r%d(%d, %d) = %d, %f", i, _x, _y, @@ -9097,15 +9107,15 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) if (!_haspixel[0] && !_haspixel[1]) i = 3; /* pixel0 && !pixel1 */ - /* run spiplan[2] (nodata2expr) */ + /* run spi_plan[2] (nodata2expr) */ else if (_haspixel[0] && !_haspixel[1]) i = 2; /* !pixel0 && pixel1 */ - /* run spiplan[1] (nodata1expr) */ + /* run spi_plan[1] (nodata1expr) */ else if (!_haspixel[0] && _haspixel[1]) i = 1; /* pixel0 && pixel1 */ - /* run spiplan[0] (expression) */ + /* run spi_plan[0] (expression) */ else i = 0; @@ -9122,16 +9132,18 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) pixel = argval[i]; } /* prepared plan exists */ - else if (spiplan[i] != NULL) { - POSTGIS_RT_DEBUGF(5, "Using prepared plan: %d", i); + else if (spi_plan[i] != NULL) { + POSTGIS_RT_DEBUGF(4, "Using prepared plan: %d", i); + values = NULL; + nulls = NULL; /* expression has argument(s) */ - if (argcount[i]) { - values = (Datum *) palloc(sizeof(Datum) * argcount[i]); + if (spi_argcount[i]) { + values = (Datum *) palloc(sizeof(Datum) * spi_argcount[i]); if (values == NULL) { elog(ERROR, "RASTER_mapAlgebra2: Unable to allocate memory for value parameters of prepared statement %d", i); - for (k = 0; k < spicount; k++) SPI_freeplan(spiplan[k]); + for (k = 0; k < spi_count; k++) SPI_freeplan(spi_plan[k]); SPI_finish(); for (k = 0; k < set_count; k++) rt_raster_destroy(_rast[k]); @@ -9140,13 +9152,13 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) PG_RETURN_NULL(); } - nulls = (bool *) palloc(sizeof(bool) * argcount[i]); + nulls = (bool *) palloc(sizeof(bool) * spi_argcount[i]); if (nulls == NULL) { elog(ERROR, "RASTER_mapAlgebra2: Unable to allocate memory for NULL parameters of prepared statement %d", i); pfree(values); - for (k = 0; k < spicount; k++) SPI_freeplan(spiplan[k]); + for (k = 0; k < spi_count; k++) SPI_freeplan(spi_plan[k]); SPI_finish(); for (k = 0; k < set_count; k++) rt_raster_destroy(_rast[k]); @@ -9154,43 +9166,55 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) PG_RETURN_NULL(); } - memset(nulls, FALSE, argcount[i]); + memset(nulls, FALSE, spi_argcount[i]); + + /* set values and nulls */ + for (j = 0; j < argkwcount; j++) { + idx = argpos[i][j]; + if (idx < 1) continue; + idx--; /* 1-based becomes 0-based */ - /* two arguments */ - if (argcount[i] > 1) { - for (j = 0; j < argcount[i]; j++) { - if (_isempty[j] || !_haspixel[j]) - nulls[j] = TRUE; + if (strstr(argkw[j], "[rast1.x]") != NULL) { + values[idx] = _pos[0][0]; + } + else if (strstr(argkw[j], "[rast1.y]") != NULL) { + values[idx] = _pos[0][1]; + } + else if ( + (strstr(argkw[j], "[rast1.val]") != NULL) || + (strstr(argkw[j], "[rast1]") != NULL) + ) { + if (_isempty[0] || !_haspixel[0]) + nulls[idx] = TRUE; else - values[j] = Float8GetDatum(_pixel[j]); + values[idx] = Float8GetDatum(_pixel[0]); } - } - /* only one argument */ - else { - if (argexists[i][0]) - j = 0; - else - j = 1; - - if (_isempty[j] || !_haspixel[j]) { - POSTGIS_RT_DEBUG(5, "using null"); - nulls[0] = TRUE; + else if (strstr(argkw[j], "[rast2.x]") != NULL) { + values[idx] = _pos[1][0]; } - else { - POSTGIS_RT_DEBUGF(5, "using value %f", _pixel[j]); - values[0] = Float8GetDatum(_pixel[j]); + else if (strstr(argkw[j], "[rast2.y]") != NULL) { + values[idx] = _pos[1][1]; + } + else if ( + (strstr(argkw[j], "[rast2.val]") != NULL) || + (strstr(argkw[j], "[rast2]") != NULL) + ) { + if (_isempty[1] || !_haspixel[1]) + nulls[idx] = TRUE; + else + values[idx] = Float8GetDatum(_pixel[1]); } } } /* run prepared plan */ - err = SPI_execute_plan(spiplan[i], values, nulls, TRUE, 1); + err = SPI_execute_plan(spi_plan[i], values, nulls, TRUE, 1); if (values != NULL) pfree(values); if (nulls != NULL) pfree(nulls); if (err != SPI_OK_SELECT || SPI_tuptable == NULL || SPI_processed != 1) { elog(ERROR, "RASTER_mapAlgebra2: Unexpected error when running prepared statement %d", i); - for (k = 0; k < spicount; k++) SPI_freeplan(spiplan[k]); + for (k = 0; k < spi_count; k++) SPI_freeplan(spi_plan[k]); SPI_finish(); for (k = 0; k < set_count; k++) rt_raster_destroy(_rast[k]); @@ -9209,7 +9233,7 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) elog(ERROR, "RASTER_mapAlgebra2: Unable to get result of prepared statement %d", i); if (SPI_tuptable) SPI_freetuptable(tuptable); - for (k = 0; k < spicount; k++) SPI_freeplan(spiplan[k]); + for (k = 0; k < spi_count; k++) SPI_freeplan(spi_plan[k]); SPI_finish(); for (k = 0; k < set_count; k++) rt_raster_destroy(_rast[k]); @@ -9262,7 +9286,7 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) elog(ERROR, "RASTER_mapAlgebra2: Unable to set pixel value of output raster"); if (calltype == TEXTOID) { - for (k = 0; k < spicount; k++) SPI_freeplan(spiplan[k]); + for (k = 0; k < spi_count; k++) SPI_freeplan(spi_plan[k]); SPI_finish(); } @@ -9281,8 +9305,8 @@ Datum RASTER_mapAlgebra2(PG_FUNCTION_ARGS) /* CLEANUP */ if (calltype == TEXTOID) { - for (i = 0; i < spicount; i++) { - if (spiplan[i] != NULL) SPI_freeplan(spiplan[i]); + for (i = 0; i < spi_count; i++) { + if (spi_plan[i] != NULL) SPI_freeplan(spi_plan[i]); } SPI_finish(); } diff --git a/raster/rt_pg/rtpostgis.sql.in.c b/raster/rt_pg/rtpostgis.sql.in.c index 4f83c1555..7bfa15273 100644 --- a/raster/rt_pg/rtpostgis.sql.in.c +++ b/raster/rt_pg/rtpostgis.sql.in.c @@ -3198,14 +3198,14 @@ CREATE OR REPLACE FUNCTION _st_intersection( rtn := NULL; CASE WHEN _returnband = 'FIRST' THEN - rtn := ST_MapAlgebraExpr(rast1, band1, rast2, band2, 'RAST1', NULL, extenttype); + rtn := ST_MapAlgebraExpr(rast1, band1, rast2, band2, '[rast1.val]', NULL, extenttype); WHEN _returnband = 'SECOND' THEN - rtn := ST_MapAlgebraExpr(rast2, band2, rast1, band1, 'RAST1', NULL, extenttype); + rtn := ST_MapAlgebraExpr(rast2, band2, rast1, band1, '[rast1.val]', NULL, extenttype); WHEN _returnband = 'OTHER' THEN rtn := ST_MapAlgebraFct(rast1, band1, rast2, band2, otheruserfunc, NULL, extenttype); ELSE -- BOTH - rtn := ST_MapAlgebraExpr(rast1, band1, rast2, band2, 'RAST1', NULL, extenttype); - rtn := ST_AddBand(rtn, ST_MapAlgebraExpr(rast2, band2, rast1, band1, 'RAST1', NULL, extenttype)); + rtn := ST_MapAlgebraExpr(rast1, band1, rast2, band2, '[rast1.val]', NULL, extenttype); + rtn := ST_AddBand(rtn, ST_MapAlgebraExpr(rast2, band2, rast1, band1, '[rast1.val]', NULL, extenttype)); END CASE; RETURN rtn; @@ -3322,20 +3322,20 @@ CREATE OR REPLACE FUNCTION _ST_MapAlgebra4UnionState(rast1 raster, rast2 raster -- There we always set that to band 1 regardless of what band num is requested IF upper(p_expression) = 'LAST' THEN --RAISE NOTICE 'last asked for '; - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'RAST2'::text, NULL::text, 'UNION'::text, 'RAST2'::text, 'RAST1'::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, '[rast2.val]'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); ELSIF upper(p_expression) = 'FIRST' THEN - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'RAST1'::text, NULL::text, 'UNION'::text, 'RAST2'::text, 'RAST1'::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, '[rast1.val]'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); ELSIF upper(p_expression) = 'MIN' THEN - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'LEAST(RAST1, RAST2)'::text, NULL::text, 'UNION'::text, 'RAST2'::text, 'RAST1'::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'LEAST([rast1.val], [rast2.val])'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); ELSIF upper(p_expression) = 'MAX' THEN - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'GREATEST(RAST1, RAST2)'::text, NULL::text, 'UNION'::text, 'RAST2'::text, 'RAST1'::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'GREATEST([rast1.val], [rast2.val])'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); ELSIF upper(p_expression) = 'COUNT' THEN - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'RAST1 + 1'::text, NULL::text, 'UNION'::text, '1'::text, 'RAST1'::text, 0::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, '[rast1.val] + 1'::text, NULL::text, 'UNION'::text, '1'::text, '[rast1.val]'::text, 0::double precision); ELSIF upper(p_expression) = 'SUM' THEN - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'RAST1 + RAST2'::text, NULL::text, 'UNION'::text, 'RAST2'::text, 'RAST1'::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, '[rast1.val] + [rast2.val]'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); ELSIF upper(p_expression) = 'RANGE' THEN -- have no idea what this is - t_raster = ST_MapAlgebraExpr(rast1, 2, rast2, 1, 'LEAST(RAST1, RAST2)'::text, NULL::text, 'UNION'::text, 'RAST2'::text, 'RAST1'::text, NULL::double precision); + t_raster = ST_MapAlgebraExpr(rast1, 2, rast2, 1, 'LEAST([rast1.val], [rast2.val])'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); p_raster := _ST_MapAlgebra4UnionState(rast1, rast2, 'MAX'::text, NULL::text, NULL::text, NULL::double precision, NULL::text, NULL::text, NULL::text, NULL::double precision); RETURN ST_AddBand(p_raster, t_raster, 1, 2); ELSIF upper(p_expression) = 'MEAN' THEN @@ -3343,7 +3343,7 @@ CREATE OR REPLACE FUNCTION _ST_MapAlgebra4UnionState(rast1 raster, rast2 raster -- and p_raster is there to keep track of accumulated sum and final state function -- would then do a final map to divide them. This one is currently broken because -- have not reworked it so it can do without a final function - t_raster = ST_MapAlgebraExpr(rast1, 2, rast2, 1, 'RAST1 + 1'::text, NULL::text, 'UNION'::text, '1'::text, 'RAST1'::text, 0::double precision); + t_raster = ST_MapAlgebraExpr(rast1, 2, rast2, 1, '[rast1.val] + 1'::text, NULL::text, 'UNION'::text, '1'::text, '[rast1.val]'::text, 0::double precision); p_raster := _ST_MapAlgebra4UnionState(rast1, rast2, 'SUM'::text, NULL::text, NULL::text, NULL::double precision, NULL::text, NULL::text, NULL::text, NULL::double precision); RETURN ST_AddBand(p_raster, t_raster, 1, 2); ELSE @@ -3395,7 +3395,7 @@ CREATE OR REPLACE FUNCTION _ST_MapAlgebra4UnionFinal1(rast raster) -- NOTE: I have to sacrifice RANGE. Sorry RANGE. Any 2 banded raster is going to be treated -- as a MEAN IF ST_NumBands(rast) = 2 THEN - RETURN ST_MapAlgebraExpr(rast, 1, rast, 2, 'CASE WHEN RAST2 > 0 THEN RAST1 / RAST2::float8 ELSE NULL END'::text, NULL::text, 'UNION'::text, NULL::text, NULL::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast, 1, rast, 2, 'CASE WHEN [rast2.val] > 0 THEN [rast1.val] / [rast2.val]::float8 ELSE NULL END'::text, NULL::text, 'UNION'::text, NULL::text, NULL::text, NULL::double precision); ELSE RETURN rast; END IF; @@ -3495,7 +3495,7 @@ CREATE OR REPLACE FUNCTION st_clip(rast raster, band int, geom geometry, nodata sourceraster := ST_SetBandNodataValue(sourceraster, bandstart, newnodata); -- Compute the first raster band - newrast := ST_MapAlgebraExpr(sourceraster, bandstart, geomrast, 1, 'RAST1', newpixtype, newextent); + newrast := ST_MapAlgebraExpr(sourceraster, bandstart, geomrast, 1, '[rast1.val]', newpixtype, newextent); FOR bandi IN bandstart+1..bandend LOOP --RAISE NOTICE 'bandi=%', bandi; @@ -3503,7 +3503,7 @@ CREATE OR REPLACE FUNCTION st_clip(rast raster, band int, geom geometry, nodata newpixtype := ST_BandPixelType(rast, bandi); newnodata := coalesce(nodata, ST_BandNodataValue(sourceraster, bandi), ST_MinPossibleValue(newpixtype)); sourceraster := ST_SetBandNodataValue(sourceraster, bandi, newnodata); - newrast := ST_AddBand(newrast, ST_MapAlgebraExpr(sourceraster, bandi, geomrast, 1, 'RAST1', newpixtype, newextent)); + newrast := ST_AddBand(newrast, ST_MapAlgebraExpr(sourceraster, bandi, geomrast, 1, '[rast1.val]', newpixtype, newextent)); END LOOP; RETURN newrast; diff --git a/raster/scripts/plpgsql/st_clip.sql b/raster/scripts/plpgsql/st_clip.sql index 1c68c810e..604482d69 100644 --- a/raster/scripts/plpgsql/st_clip.sql +++ b/raster/scripts/plpgsql/st_clip.sql @@ -90,7 +90,7 @@ CREATE OR REPLACE FUNCTION ST_Clip(rast raster, band int, geom geometry, nodata sourceraster := ST_SetBandNodataValue(sourceraster, bandstart, newnodata); -- Compute the first raster band - newrast := ST_MapAlgebraExpr(sourceraster, bandstart, geomrast, 1, 'rast1', newpixtype, newextent); + newrast := ST_MapAlgebraExpr(sourceraster, bandstart, geomrast, 1, '[rast1.val]', newpixtype, newextent); FOR bandi IN bandstart+1..bandend LOOP --RAISE NOTICE 'bandi=%', bandi; @@ -98,7 +98,7 @@ CREATE OR REPLACE FUNCTION ST_Clip(rast raster, band int, geom geometry, nodata newpixtype := ST_BandPixelType(rast, bandi); newnodata := coalesce(nodata, ST_BandNodataValue(sourceraster, bandi), ST_MinPossibleValue(newpixtype)); sourceraster := ST_SetBandNodataValue(sourceraster, bandi, newnodata); - newrast := ST_AddBand(newrast, ST_MapAlgebraExpr(sourceraster, bandi, geomrast, 1, 'rast1', newpixtype, newextent)); + newrast := ST_AddBand(newrast, ST_MapAlgebraExpr(sourceraster, bandi, geomrast, 1, '[rast1.val]', newpixtype, newextent)); END LOOP; RETURN newrast; END; diff --git a/raster/scripts/plpgsql/st_union.sql b/raster/scripts/plpgsql/st_union.sql index ed2c46ccf..c5184c348 100644 --- a/raster/scripts/plpgsql/st_union.sql +++ b/raster/scripts/plpgsql/st_union.sql @@ -38,23 +38,23 @@ CREATE OR REPLACE FUNCTION MapAlgebra4Union(rast1 raster, -- ST_MapAlgebraExpr(rast1 raster, band1 integer, rast2 raster, band2 integer, expression text, pixeltype text, extentexpr text, nodata1expr text, nodata2expr text, nodatanodatadaexpr double precision) -- We must make sure that when NULL is passed as the first raster to ST_MapAlgebraExpr, ST_MapAlgebraExpr resolve the nodata1expr IF upper(p_expression) = 'LAST' THEN - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'rast2'::text, NULL::text, 'UNION'::text, 'rast2'::text, 'rast1'::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, '[rast2.val]'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); ELSIF upper(p_expression) = 'FIRST' THEN - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'rast1'::text, NULL::text, 'UNION'::text, 'rast2'::text, 'rast1'::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, '[rast1.val]'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); ELSIF upper(p_expression) = 'MIN' THEN - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'LEAST(rast1, rast2)'::text, NULL::text, 'UNION'::text, 'rast2'::text, 'rast1'::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'LEAST([rast1.val], [rast2.val])'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); ELSIF upper(p_expression) = 'MAX' THEN - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'GREATEST(rast1, rast2)'::text, NULL::text, 'UNION'::text, 'rast2'::text, 'rast1'::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'GREATEST([rast1.val], [rast2.val])'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); ELSIF upper(p_expression) = 'COUNT' THEN - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'rast1 + 1'::text, NULL::text, 'UNION'::text, '1'::text, 'rast1'::text, 0::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, '[rast1.val] + 1'::text, NULL::text, 'UNION'::text, '1'::text, '[rast1.val]'::text, 0::double precision); ELSIF upper(p_expression) = 'SUM' THEN - RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'rast1 + rast2'::text, NULL::text, 'UNION'::text, 'rast2'::text, 'rast1'::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, '[rast1.val] + [rast2.val]'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); ELSIF upper(p_expression) = 'RANGE' THEN - t_raster = ST_MapAlgebraExpr(rast1, 2, rast2, 1, 'LEAST(rast1, rast2)'::text, NULL::text, 'UNION'::text, 'rast2'::text, 'rast1'::text, NULL::double precision); + t_raster = ST_MapAlgebraExpr(rast1, 2, rast2, 1, 'LEAST([rast1.val], [rast2.val])'::text, NULL::text, 'UNION'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision); p_raster := MapAlgebra4Union(rast1, rast2, 'MAX'::text, NULL::text, NULL::text, NULL::double precision, NULL::text, NULL::text, NULL::text, NULL::double precision); RETURN ST_AddBand(p_raster, t_raster, 1, 2); ELSIF upper(p_expression) = 'MEAN' THEN - t_raster = ST_MapAlgebraExpr(rast1, 2, rast2, 1, 'rast1 + 1'::text, NULL::text, 'UNION'::text, '1'::text, 'rast1'::text, 0::double precision); + t_raster = ST_MapAlgebraExpr(rast1, 2, rast2, 1, '[rast1.val] + 1'::text, NULL::text, 'UNION'::text, '1'::text, '[rast1.val]'::text, 0::double precision); p_raster := MapAlgebra4Union(rast1, rast2, 'SUM'::text, NULL::text, NULL::text, NULL::double precision, NULL::text, NULL::text, NULL::text, NULL::double precision); RETURN ST_AddBand(p_raster, t_raster, 1, 2); ELSE @@ -85,9 +85,9 @@ CREATE OR REPLACE FUNCTION MapAlgebra4UnionFinal1(rast rastexpr) DECLARE BEGIN IF upper(rast.f_expression) = 'RANGE' THEN - RETURN ST_MapAlgebraExpr(rast.rast, 1, rast.rast, 2, 'rast1 - rast2'::text, NULL::text, 'UNION'::text, NULL::text, NULL::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast.rast, 1, rast.rast, 2, '[rast1.val] - [rast2.val]'::text, NULL::text, 'UNION'::text, NULL::text, NULL::text, NULL::double precision); ELSEIF upper(rast.f_expression) = 'MEAN' THEN - RETURN ST_MapAlgebraExpr(rast.rast, 1, rast.rast, 2, 'CASE WHEN rast2 > 0 THEN rast1 / rast2::float8 ELSE NULL END'::text, NULL::text, 'UNION'::text, NULL::text, NULL::text, NULL::double precision); + RETURN ST_MapAlgebraExpr(rast.rast, 1, rast.rast, 2, 'CASE WHEN [rast2.val] > 0 THEN [rast1.val] / [rast2.val]::float8 ELSE NULL END'::text, NULL::text, 'UNION'::text, NULL::text, NULL::text, NULL::double precision); ELSE RETURN rast.rast; END IF; @@ -252,9 +252,9 @@ FROM (SELECT ST_PixelAsPolygons(ST_Union(rast, 'last'), 1) rast -- Explicit implementation of 'MEAN' to make sure directly passing expressions works properly SELECT ST_AsBinary((rast).geom), (rast).val -FROM (SELECT ST_PixelAsPolygons(ST_Union(rast, 'rast1 + rast2'::text, 'rast2'::text, 'rast1'::text, NULL::double precision, - 'rast1 + 1'::text, '1'::text, 'rast1'::text, 0::double precision, - 'CASE WHEN rast2 > 0 THEN rast1 / rast2::float8 ELSE NULL END'::text, NULL::text, NULL::text, NULL::double precision), 1) rast +FROM (SELECT ST_PixelAsPolygons(ST_Union(rast, '[rast1.val] + [rast2.val]'::text, '[rast2.val]'::text, '[rast1.val]'::text, NULL::double precision, + '[rast1.val] + 1'::text, '1'::text, '[rast1.val]'::text, 0::double precision, + 'CASE WHEN [rast2.val] > 0 THEN [rast1.val] / [rast2.val]::float8 ELSE NULL END'::text, NULL::text, NULL::text, NULL::double precision), 1) rast FROM (SELECT ST_TestRaster(0, 0, 2) AS rast UNION ALL SELECT ST_TestRaster(1, 1, 4) AS rast @@ -268,7 +268,7 @@ FROM (SELECT ST_PixelAsPolygons(ST_Union(rast, 'rast1 + rast2'::text, 'rast2'::t SELECT ST_AsBinary((rast).geom), (rast).val FROM (SELECT ST_PixelAsPolygons(ST_Union(rast, 'SUM'::text, 'COUNT'::text, - 'CASE WHEN rast2 > 0 THEN rast1 / rast2::float8 ELSE NULL END'::text), 1) rast + 'CASE WHEN [rast2.val] > 0 THEN [rast1.val] / [rast2.val]::float8 ELSE NULL END'::text), 1) rast FROM (SELECT ST_TestRaster(0, 0, 2) AS rast UNION ALL SELECT ST_TestRaster(1, 1, 4) AS rast @@ -290,4 +290,4 @@ SELECT ST_AsBinary((rast).geom), (rast).val FROM (SELECT ST_PixelAsPolygons(ST_Union(rast, 'mean'), 1) AS rast FROM (SELECT ST_TestRaster(0, 0, 1) AS rast UNION ALL SELECT ST_TestRaster(1, 0, 2) UNION ALL SELECT ST_TestRaster(0, 1, 6) ) foi - ) foo \ No newline at end of file + ) foo diff --git a/raster/test/regress/rt_mapalgebraexpr_2raster.sql b/raster/test/regress/rt_mapalgebraexpr_2raster.sql index 8860aec82..3bdd6fba6 100644 --- a/raster/test/regress/rt_mapalgebraexpr_2raster.sql +++ b/raster/test/regress/rt_mapalgebraexpr_2raster.sql @@ -56,7 +56,7 @@ DROP FUNCTION make_test_raster(integer, integer, integer, double precision, doub -- INTERSECTION INSERT INTO raster_mapalgebra_out (SELECT r1.rid, r2.rid, 'INTERSECTION', st_mapalgebraexpr( - r1.rast, r2.rast, 'RAST1', '32BF', 'INTERSECTION' + r1.rast, r2.rast, '[rast1.val]', '32BF', 'INTERSECTION' ) FROM raster_mapalgebra r1 JOIN raster_mapalgebra r2 @@ -65,7 +65,7 @@ INSERT INTO raster_mapalgebra_out AND r2.rid BETWEEN 1 AND 9 ) UNION ALL ( SELECT r1.rid, r2.rid, 'INTERSECTION', st_mapalgebraexpr( - r1.rast, r2.rast, 'RAST1', '32BF', 'INTERSECTION' + r1.rast, r2.rast, '[rast1.val]', '32BF', 'INTERSECTION' ) FROM raster_mapalgebra r1 JOIN raster_mapalgebra r2 @@ -76,28 +76,28 @@ INSERT INTO raster_mapalgebra_out INSERT INTO raster_mapalgebra_out SELECT NULL AS rid, rid, 'INTERSECTION', st_mapalgebraexpr( - NULL::raster, rast, 'RAST1', '32BF', 'INTERSECTION' + NULL::raster, rast, '[rast1.val]', '32BF', 'INTERSECTION' ) FROM raster_mapalgebra ; INSERT INTO raster_mapalgebra_out SELECT rid, NULL AS rid, 'INTERSECTION', st_mapalgebraexpr( - rast, NULL::raster, 'RAST1', '32BF', 'INTERSECTION' + rast, NULL::raster, '[rast1.val]', '32BF', 'INTERSECTION' ) FROM raster_mapalgebra ; INSERT INTO raster_mapalgebra_out SELECT NULL AS rid, NULL AS rid, 'INTERSECTION', st_mapalgebraexpr( - NULL::raster, NULL::raster, 'RAST1', '32BF', 'INTERSECTION' + NULL::raster, NULL::raster, '[rast1.val]', '32BF', 'INTERSECTION' ) ; -- UNION INSERT INTO raster_mapalgebra_out (SELECT r1.rid, r2.rid, 'UNION', st_mapalgebraexpr( - r1.rast, r2.rast, '((RAST1 + RAST2)/2.)::numeric', '32BF', 'UNION', 'RAST2', 'RAST1', NULL + r1.rast, r2.rast, '(([rast1.val] + [rast2.val])/2.)::numeric', '32BF', 'UNION', '[rast2.val]', '[rast1.val]', NULL ) FROM raster_mapalgebra r1 JOIN raster_mapalgebra r2 @@ -106,7 +106,7 @@ INSERT INTO raster_mapalgebra_out AND r2.rid BETWEEN 1 AND 9 ) UNION ALL ( SELECT r1.rid, r2.rid, 'UNION', st_mapalgebraexpr( - r1.rast, r2.rast, '((RAST1 + RAST2)/2.)::numeric', '32BF', 'UNION', 'RAST2', 'RAST1', NULL + r1.rast, r2.rast, '(([rast1.val] + [rast2.val])/2.)::numeric', '32BF', 'UNION', '[rast2.val]', '[rast1.val]', NULL ) FROM raster_mapalgebra r1 JOIN raster_mapalgebra r2 @@ -117,7 +117,7 @@ INSERT INTO raster_mapalgebra_out INSERT INTO raster_mapalgebra_out (SELECT r1.rid, r2.rid, 'UNION', st_mapalgebraexpr( - r1.rast, r2.rast, '((RAST1 + RAST2)/2.)::numeric', '32BF', 'UNION', '100', '200', NULL + r1.rast, r2.rast, '(([rast1.val] + [rast2.val])/2.)::numeric', '32BF', 'UNION', '100', '200', NULL ) FROM raster_mapalgebra r1 JOIN raster_mapalgebra r2 @@ -126,7 +126,7 @@ INSERT INTO raster_mapalgebra_out AND r2.rid BETWEEN 1 AND 9 ) UNION ALL ( SELECT r1.rid, r2.rid, 'UNION', st_mapalgebraexpr( - r1.rast, r2.rast, '((RAST1 + RAST2)/2.)::numeric', '32BF', 'UNION', '100', '200', NULL + r1.rast, r2.rast, '(([rast1.val] + [rast2.val])/2.)::numeric', '32BF', 'UNION', '100', '200', NULL ) FROM raster_mapalgebra r1 JOIN raster_mapalgebra r2 @@ -137,28 +137,28 @@ INSERT INTO raster_mapalgebra_out INSERT INTO raster_mapalgebra_out SELECT NULL AS rid, rid, 'UNION', st_mapalgebraexpr( - NULL::raster, rast, '((RAST1 + RAST2)/2.)::numeric', '32BF', 'UNION', 'RAST2', 'RAST1', NULL + NULL::raster, rast, '(([rast1.val] + [rast2.val])/2.)::numeric', '32BF', 'UNION', '[rast2.val]', '[rast1.val]', NULL ) FROM raster_mapalgebra ; INSERT INTO raster_mapalgebra_out SELECT rid, NULL AS rid, 'UNION', st_mapalgebraexpr( - rast, NULL::raster, '((RAST1 + RAST2)/2.)::numeric', '32BF', 'UNION', 'RAST2', 'RAST1', NULL + rast, NULL::raster, '(([rast1.val] + [rast2.val])/2.)::numeric', '32BF', 'UNION', '[rast2.val]', '[rast1.val]', NULL ) FROM raster_mapalgebra ; INSERT INTO raster_mapalgebra_out SELECT NULL AS rid, NULL AS rid, 'UNION', st_mapalgebraexpr( - NULL::raster, NULL::raster, '((RAST1 + RAST2)/2.)::numeric', '32BF', 'UNION', 'RAST2', 'RAST1', NULL + NULL::raster, NULL::raster, '(([rast1.val] + [rast2.val])/2.)::numeric', '32BF', 'UNION', '[rast2.val]', '[rast1.val]', NULL ) ; -- FIRST INSERT INTO raster_mapalgebra_out (SELECT r1.rid, r2.rid, 'FIRST', st_mapalgebraexpr( - r1.rast, r2.rast, 'CASE WHEN RAST2 IS NOT NULL THEN NULL ELSE RAST1 END', '32BF', 'FIRST', NULL, 'RAST1', NULL + r1.rast, r2.rast, 'CASE WHEN [rast2.val] IS NOT NULL THEN NULL ELSE [rast1.val] END', '32BF', 'FIRST', NULL, '[rast1.val]', NULL ) FROM raster_mapalgebra r1 JOIN raster_mapalgebra r2 @@ -167,7 +167,7 @@ INSERT INTO raster_mapalgebra_out AND r2.rid BETWEEN 1 AND 9 ) UNION ALL ( SELECT r1.rid, r2.rid, 'FIRST', st_mapalgebraexpr( - r1.rast, r2.rast, 'CASE WHEN RAST2 IS NOT NULL THEN NULL ELSE RAST1 END', '32BF', 'FIRST', NULL, 'RAST1', NULL + r1.rast, r2.rast, 'CASE WHEN [rast2.val] IS NOT NULL THEN NULL ELSE [rast1.val] END', '32BF', 'FIRST', NULL, '[rast1.val]', NULL ) FROM raster_mapalgebra r1 JOIN raster_mapalgebra r2 @@ -178,28 +178,28 @@ INSERT INTO raster_mapalgebra_out INSERT INTO raster_mapalgebra_out SELECT NULL AS rid, rid, 'FIRST', st_mapalgebraexpr( - NULL::raster, rast, 'CASE WHEN RAST1 IS NOT NULL THEN NULL ELSE RAST2 END', '32BF', 'FIRST', 'RAST2', NULL, NULL + NULL::raster, rast, 'CASE WHEN [rast1.val] IS NOT NULL THEN NULL ELSE [rast2.val] END', '32BF', 'FIRST', '[rast2.val]', NULL, NULL ) FROM raster_mapalgebra ; INSERT INTO raster_mapalgebra_out SELECT rid, NULL AS rid, 'FIRST', st_mapalgebraexpr( - rast, NULL::raster, 'CASE WHEN RAST2 IS NOT NULL THEN NULL ELSE RAST1 END', '32BF', 'FIRST', NULL, 'RAST1', NULL + rast, NULL::raster, 'CASE WHEN [rast2.val] IS NOT NULL THEN NULL ELSE [rast1.val] END', '32BF', 'FIRST', NULL, '[rast1.val]', NULL ) FROM raster_mapalgebra ; INSERT INTO raster_mapalgebra_out SELECT NULL AS rid, NULL AS rid, 'FIRST', st_mapalgebraexpr( - NULL::raster, NULL::raster, 'CASE WHEN RAST2 IS NOT NULL THEN NULL ELSE RAST1 END', '32BF', 'FIRST', NULL, 'RAST1', NULL + NULL::raster, NULL::raster, 'CASE WHEN [rast2.val] IS NOT NULL THEN NULL ELSE [rast1.val] END', '32BF', 'FIRST', NULL, '[rast1.val]', NULL ) ; -- SECOND INSERT INTO raster_mapalgebra_out (SELECT r1.rid, r2.rid, 'SECOND', st_mapalgebraexpr( - r1.rast, r2.rast, 'CASE WHEN RAST1 IS NOT NULL THEN NULL ELSE RAST2 END', '32BF', 'SECOND', 'RAST2', NULL, NULL + r1.rast, r2.rast, 'CASE WHEN [rast1.val] IS NOT NULL THEN NULL ELSE [rast2.val] END', '32BF', 'SECOND', '[rast2.val]', NULL, NULL ) FROM raster_mapalgebra r1 JOIN raster_mapalgebra r2 @@ -208,7 +208,7 @@ INSERT INTO raster_mapalgebra_out AND r2.rid BETWEEN 1 AND 9 ) UNION ALL ( SELECT r1.rid, r2.rid, 'SECOND', st_mapalgebraexpr( - r1.rast, r2.rast, 'CASE WHEN RAST1 IS NOT NULL THEN NULL ELSE RAST2 END', '32BF', 'SECOND', 'RAST2', NULL, NULL + r1.rast, r2.rast, 'CASE WHEN [rast1.val] IS NOT NULL THEN NULL ELSE [rast2.val] END', '32BF', 'SECOND', '[rast2.val]', NULL, NULL ) FROM raster_mapalgebra r1 JOIN raster_mapalgebra r2 @@ -219,21 +219,21 @@ INSERT INTO raster_mapalgebra_out INSERT INTO raster_mapalgebra_out SELECT NULL AS rid, rid, 'SECOND', st_mapalgebraexpr( - NULL::raster, rast, 'CASE WHEN RAST1 IS NOT NULL THEN NULL ELSE RAST2 END', '32BF', 'SECOND', 'RAST2', NULL, NULL + NULL::raster, rast, 'CASE WHEN [rast1.val] IS NOT NULL THEN NULL ELSE [rast2.val] END', '32BF', 'SECOND', '[rast2.val]', NULL, NULL ) FROM raster_mapalgebra ; INSERT INTO raster_mapalgebra_out SELECT rid, NULL AS rid, 'SECOND', st_mapalgebraexpr( - rast, NULL::raster, 'CASE WHEN RAST1 IS NOT NULL THEN NULL ELSE RAST2 END', '32BF', 'SECOND', 'RAST2', NULL, NULL + rast, NULL::raster, 'CASE WHEN [rast1.val] IS NOT NULL THEN NULL ELSE [rast2.val] END', '32BF', 'SECOND', '[rast2.val]', NULL, NULL ) FROM raster_mapalgebra ; INSERT INTO raster_mapalgebra_out SELECT NULL AS rid, NULL AS rid, 'SECOND', st_mapalgebraexpr( - NULL::raster, NULL::raster, 'CASE WHEN RAST1 IS NOT NULL THEN NULL ELSE RAST2 END', '32BF', 'SECOND', 'RAST2', NULL, NULL + NULL::raster, NULL::raster, 'CASE WHEN [rast1.val] IS NOT NULL THEN NULL ELSE [rast2.val] END', '32BF', 'SECOND', '[rast2.val]', NULL, NULL ) ; -- 2.40.0