ST_SetValues — Returns modified raster resulting from setting the values of a given band.
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);
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
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)