ST_SetValues
Name
ST_SetValues — Returns modified raster resulting from setting the values of a given band.
Synopsis
raster
ST_SetValues
(
raster
rast
, integer
nband
, integer
columnx
, integer
rowy
, double precision[][]
newvalueset
, boolean[][]
noset=NULL
, boolean
keepnodata=FALSE
)
;
raster
ST_SetValues
(
raster
rast
, integer
nband
, integer
columnx
, integer
rowy
, double precision[][]
newvalueset
, double precision
nosetvalue
, boolean
keepnodata=FALSE
)
;
raster
ST_SetValues
(
raster
rast
, integer
nband
, integer
columnx
, integer
rowy
, integer
width
, integer
height
, double precision
newvalue
, boolean
keepnodata=FALSE
)
;
raster
ST_SetValues
(
raster
rast
, integer
columnx
, integer
rowy
, integer
width
, integer
height
, double precision
newvalue
, boolean
keepnodata=FALSE
)
;
raster
ST_SetValues
(
raster
rast
, integer
nband
, geomval[]
geomvalset
, boolean
keepnodata=FALSE
)
;
Description
Returns modified raster resulting from setting specified pixels to new value(s) for the designated band.
columnx
and
rowy
are 1-indexed.
If
keepnodata
is TRUE, those pixels whose values are NODATA will not be set with the corresponding value in
newvalueset
.
For Variant 1, the specific pixels to be set are determined by the
columnx
,
rowy
pixel coordinates and the dimensions of the
newvalueset
array.
noset
can be used to prevent pixels with values present in
newvalueset
from being set (due to PostgreSQL not permitting ragged/jagged arrays). See example Variant 1.
Variant 2 is like Variant 1 but with a simple double precision
nosetvalue
instead of a boolean
noset
array. Elements in
newvalueset
with the
nosetvalue
value with be skipped. See example Variant 2.
For Variant 3, the specific pixels to be set are determined by the
columnx
,
rowy
pixel coordinates,
width
and
height
. See example Variant 3.
Variant 4 is the same as Variant 3 with the exception that it assumes that the first band's pixels of
rast
will be set.
For Variant 5, an array of geomval is used to determine the specific pixels to be set. If all the geometries in the array are of type POINT or MULTIPOINT, the function uses a shortcut where the longitude and latitude of each point is used to set a pixel directly. Otherwise, the geometries are converted to rasters and then iterated through in one pass. See example Variant 5.
Availability: 2.1.0
Examples
Variant 1.
These examples pass
FALSE
for
exclude_nodata_value
so that every
raster cell remains visible. NODATA cells display the band's
NODATA value, which is
0
here.
Set the lower-right 2 by 2 block of a 3 by 3 raster to
9
.
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(ST_SetValues(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 2, 2, ARRAY[[9, 9], [9, 9]]::double precision[][]
),
1, FALSE
) AS poly
) foo
ORDER BY 1, 2;
x | y | val ---+---+----- 1 | 1 | 1 1 | 2 | 1 1 | 3 | 1 2 | 1 | 1 2 | 2 | 9 2 | 3 | 9 3 | 1 | 1 3 | 2 | 9 3 | 3 | 9
Replace the complete raster from an array; the central
NULL
becomes NODATA.
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(ST_SetValues(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 1, 1, ARRAY[[9, 9, 9], [9, NULL, 9], [9, 9, 9]]::double precision[][]
),
1, FALSE
) AS poly
) foo
ORDER BY 1, 2;
x | y | val ---+---+----- 1 | 1 | 9 1 | 2 | 9 1 | 3 | 9 2 | 1 | 9 2 | 2 | 0 2 | 3 | 9 3 | 1 | 9 3 | 2 | 9 3 | 3 | 9
Use the
noset
mask to retain the
left-middle source pixel while replacing the other cells.
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(ST_SetValues(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 1, 1,
ARRAY[[9, 9, 9], [9, NULL, 9], [9, 9, 9]]::double precision[][],
ARRAY[[false], [true]]::boolean[][]
),
1, FALSE
) AS poly
) foo
ORDER BY 1, 2;
x | y | val ---+---+----- 1 | 1 | 9 1 | 2 | 1 1 | 3 | 9 2 | 1 | 9 2 | 2 | 0 2 | 3 | 9 3 | 1 | 9 3 | 2 | 9 3 | 3 | 9
With
keepnodata
enabled, an
existing NODATA pixel is not overwritten.
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(ST_SetValues(ST_SetValue(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 1, 1, NULL
),
1, 1, 1,
ARRAY[[9, 9, 9], [9, NULL, 9], [9, 9, 9]]::double precision[][],
ARRAY[[false], [true]]::boolean[][],
TRUE
),
1, FALSE
) AS poly
) foo
ORDER BY 1, 2;
x | y | val ---+---+----- 1 | 1 | 0 1 | 2 | 1 1 | 3 | 9 2 | 1 | 9 2 | 2 | 0 2 | 3 | 9 3 | 1 | 9 3 | 2 | 9 3 | 3 | 9
Variant 2.
Use
-1
as the value that should not be
written; only the lower-right 2 by 2 block changes.
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(ST_SetValues(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 1, 1, ARRAY[[-1, -1, -1], [-1, 9, 9], [-1, 9, 9]]::double precision[][], -1
),
1, FALSE
) AS poly
) foo
ORDER BY 1, 2;
x | y | val ---+---+----- 1 | 1 | 1 1 | 2 | 1 1 | 3 | 1 2 | 1 | 1 2 | 2 | 9 2 | 3 | 9 3 | 1 | 1 3 | 2 | 9 3 | 3 | 9
The same update can use
NULL
as the
value that should not be written.
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(ST_SetValues(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 1, 1, ARRAY[[NULL, NULL, NULL], [NULL, 9, 9], [NULL, 9, 9]]::double precision[][], NULL::double precision
),
1, FALSE
) AS poly
) foo
ORDER BY 1, 2;
x | y | val ---+---+----- 1 | 1 | 1 1 | 2 | 1 1 | 3 | 1 2 | 1 | 1 2 | 2 | 9 2 | 3 | 9 3 | 1 | 1 3 | 2 | 9 3 | 3 | 9
Variant 3.
Set a 2 by 2 rectangle starting at column 2, row 2.
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(ST_SetValues(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 2, 2, 2, 2, 9
),
1, FALSE
) AS poly
) foo
ORDER BY 1, 2;
x | y | val ---+---+----- 1 | 1 | 1 1 | 2 | 1 1 | 3 | 1 2 | 1 | 1 2 | 2 | 9 2 | 3 | 9 3 | 1 | 1 3 | 2 | 9 3 | 3 | 9
When
keepnodata
is true, the
NODATA cell inside the rectangle remains unchanged.
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(ST_SetValues(ST_SetValue(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 2, 2, NULL
),
1, 2, 2, 2, 2, 9, TRUE
),
1, FALSE
) AS poly
) foo
ORDER BY 1, 2;
x | y | val ---+---+----- 1 | 1 | 1 1 | 2 | 1 1 | 3 | 1 2 | 1 | 1 2 | 2 | 0 2 | 3 | 9 3 | 1 | 1 3 | 2 | 9 3 | 3 | 9
Variant 5.
WITH foo AS (
SELECT 1 AS rid, ST_AddBand(ST_MakeEmptyRaster(5, 5, 0, 0, 1, -1, 0, 0, 0), 1, '8BUI', 0, 0) AS rast
), bar AS (
SELECT 1 AS gid, 'SRID=0;POINT(2.5 -2.5)'::geometry geom UNION ALL
SELECT 2 AS gid, 'SRID=0;POLYGON((1 -1,4 -1,4 -4,1 -4,1 -1))'::geometry geom UNION ALL
SELECT 3 AS gid, 'SRID=0;POLYGON((0 0,5 0,5 -1,1 -1,1 -4,0 -4,0 0))'::geometry geom UNION ALL
SELECT 4 AS gid, 'SRID=0;MULTIPOINT(0 0,4 4,4 -4)'::geometry
), changed AS (
SELECT rid, gid, ST_SetValue(rast, 1, geom, gid) AS rast
FROM foo
CROSS JOIN bar
)
SELECT
rid,
gid,
y,
array_agg(ST_Value(rast, 1, x, y) ORDER BY x) AS values
FROM changed
CROSS JOIN generate_series(1, 5) AS x
CROSS JOIN generate_series(1, 5) AS y
GROUP BY rid, gid, y
ORDER BY rid, gid, y;
rid | gid | y | values
-----+-----+---+----------------------------
1 | 1 | 1 | {NULL,NULL,NULL,NULL,NULL}
1 | 1 | 2 | {NULL,NULL,NULL,NULL,NULL}
1 | 1 | 3 | {NULL,NULL,1,NULL,NULL}
1 | 1 | 4 | {NULL,NULL,NULL,NULL,NULL}
1 | 1 | 5 | {NULL,NULL,NULL,NULL,NULL}
1 | 2 | 1 | {NULL,NULL,NULL,NULL,NULL}
1 | 2 | 2 | {NULL,2,2,2,NULL}
1 | 2 | 3 | {NULL,2,2,2,NULL}
1 | 2 | 4 | {NULL,2,2,2,NULL}
1 | 2 | 5 | {NULL,NULL,NULL,NULL,NULL}
1 | 3 | 1 | {3,3,3,3,3}
1 | 3 | 2 | {3,NULL,NULL,NULL,NULL}
1 | 3 | 3 | {3,NULL,NULL,NULL,NULL}
1 | 3 | 4 | {3,NULL,NULL,NULL,NULL}
1 | 3 | 5 | {NULL,NULL,NULL,NULL,NULL}
1 | 4 | 1 | {4,NULL,NULL,NULL,NULL}
1 | 4 | 2 | {NULL,NULL,NULL,NULL,NULL}
1 | 4 | 3 | {NULL,NULL,NULL,NULL,NULL}
1 | 4 | 4 | {NULL,NULL,NULL,NULL,NULL}
1 | 4 | 5 | {NULL,NULL,NULL,NULL,4}
(20 rows)
The following shows that geomvals later in the array can overwrite prior geomvals
WITH foo AS (
SELECT 1 AS rid, ST_AddBand(ST_MakeEmptyRaster(5, 5, 0, 0, 1, -1, 0, 0, 0), 1, '8BUI', 0, 0) AS rast
), bar AS (
SELECT 1 AS gid, 'SRID=0;POINT(2.5 -2.5)'::geometry geom UNION ALL
SELECT 2 AS gid, 'SRID=0;POLYGON((1 -1,4 -1,4 -4,1 -4,1 -1))'::geometry geom UNION ALL
SELECT 3 AS gid, 'SRID=0;POLYGON((0 0,5 0,5 -1,1 -1,1 -4,0 -4,0 0))'::geometry geom UNION ALL
SELECT 4 AS gid, 'SRID=0;MULTIPOINT(0 0,4 4,4 -4)'::geometry
), changed AS (
SELECT ST_SetValues(
rast,
1,
ARRAY[
ROW((SELECT geom FROM bar WHERE gid = 1), 1),
ROW((SELECT geom FROM bar WHERE gid = 2), 2)
]::geomval[]
) AS rast
FROM foo
)
SELECT
y,
array_agg(ST_Value(rast, 1, x, y) ORDER BY x) AS values
FROM changed
CROSS JOIN generate_series(1, 5) AS x
CROSS JOIN generate_series(1, 5) AS y
GROUP BY y
ORDER BY y;
y | values
---+----------------------------
1 | {NULL,NULL,NULL,NULL,NULL}
2 | {NULL,2,2,2,NULL}
3 | {NULL,2,2,2,NULL}
4 | {NULL,2,2,2,NULL}
5 | {NULL,NULL,NULL,NULL,NULL}
(5 rows)
This example is the opposite of the prior example
WITH foo AS (
SELECT 1 AS rid, ST_AddBand(ST_MakeEmptyRaster(5, 5, 0, 0, 1, -1, 0, 0, 0), 1, '8BUI', 0, 0) AS rast
), bar AS (
SELECT 1 AS gid, 'SRID=0;POINT(2.5 -2.5)'::geometry geom UNION ALL
SELECT 2 AS gid, 'SRID=0;POLYGON((1 -1,4 -1,4 -4,1 -4,1 -1))'::geometry geom UNION ALL
SELECT 3 AS gid, 'SRID=0;POLYGON((0 0,5 0,5 -1,1 -1,1 -4,0 -4,0 0))'::geometry geom UNION ALL
SELECT 4 AS gid, 'SRID=0;MULTIPOINT(0 0,4 4,4 -4)'::geometry
), changed AS (
SELECT ST_SetValues(
rast,
1,
ARRAY[
ROW((SELECT geom FROM bar WHERE gid = 2), 2),
ROW((SELECT geom FROM bar WHERE gid = 1), 1)
]::geomval[]
) AS rast
FROM foo
)
SELECT
y,
array_agg(ST_Value(rast, 1, x, y) ORDER BY x) AS values
FROM changed
CROSS JOIN generate_series(1, 5) AS x
CROSS JOIN generate_series(1, 5) AS y
GROUP BY y
ORDER BY y;
y | values
---+----------------------------
1 | {NULL,NULL,NULL,NULL,NULL}
2 | {NULL,2,2,2,NULL}
3 | {NULL,2,1,2,NULL}
4 | {NULL,2,2,2,NULL}
5 | {NULL,NULL,NULL,NULL,NULL}
(5 rows)