ST_SetValues
Prev 9.6. Raster Pixel Accessors and Setters Next

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.

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

/*
The ST_SetValues() does the following...

+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 1 | 1 | 1 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |    =>    | 1 | 9 | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 1 | 9 | 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[][]
		)
	) 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 ST_SetValues() does the following...

+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 9 | 9 | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |    =>    | 9 |   | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 9 | 9 | 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, 1, 1, ARRAY[[9, 9, 9], [9, NULL, 9], [9, 9, 9]]::double precision[][]
		)
	) AS poly
) foo
ORDER BY 1, 2;

 x | y | val 
---+---+-----
 1 | 1 |   9
 1 | 2 |   9
 1 | 3 |   9
 2 | 1 |   9
 2 | 2 |    
 2 | 3 |   9
 3 | 1 |   9
 3 | 2 |   9
 3 | 3 |   9
				
/*
The ST_SetValues() does the following...

+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 9 | 9 | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |    =>    | 1 |   | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 9 | 9 | 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, 1, 1,
				ARRAY[[9, 9, 9], [9, NULL, 9], [9, 9, 9]]::double precision[][],
				ARRAY[[false], [true]]::boolean[][]
		)
	) AS poly
) foo
ORDER BY 1, 2;

 x | y | val 
---+---+-----
 1 | 1 |   9
 1 | 2 |   1
 1 | 3 |   9
 2 | 1 |   9
 2 | 2 |    
 2 | 3 |   9
 3 | 1 |   9
 3 | 2 |   9
 3 | 3 |   9
				
/*
The ST_SetValues() does the following...

+ - + - + - +          + - + - + - +
|   | 1 | 1 |          |   | 9 | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |    =>    | 1 |   | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 9 | 9 | 9 |
+ - + - + - +          + - + - + - +
*/
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
		)
	) AS poly
) foo
ORDER BY 1, 2;

 x | y | val 
---+---+-----
 1 | 1 |   
 1 | 2 |   1
 1 | 3 |   9
 2 | 1 |   9
 2 | 2 |    
 2 | 3 |   9
 3 | 1 |   9
 3 | 2 |   9
 3 | 3 |   9

Examples: Variant 2

/*
The ST_SetValues() does the following...

+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 1 | 1 | 1 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |    =>    | 1 | 9 | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 1 | 9 | 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, 1, 1, ARRAY[[-1, -1, -1], [-1, 9, 9], [-1, 9, 9]]::double precision[][], -1
		)
	) 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
				
/*
This example is like the previous one.  Instead of nosetvalue = -1, nosetvalue = NULL

The ST_SetValues() does the following...

+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 1 | 1 | 1 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |    =>    | 1 | 9 | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 1 | 9 | 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, 1, 1, ARRAY[[NULL, NULL, NULL], [NULL, 9, 9], [NULL, 9, 9]]::double precision[][], NULL::double precision
		)
	) 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

Examples: Variant 3

/*
The ST_SetValues() does the following...

+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 1 | 1 | 1 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |    =>    | 1 | 9 | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 1 | 9 | 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, 2, 2, 9
		)
	) 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 ST_SetValues() does the following...

+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 1 | 1 | 1 |
+ - + - + - +          + - + - + - +
| 1 |   | 1 |    =>    | 1 |   | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 1 | 9 | 9 |
+ - + - + - +          + - + - + - +
*/
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
		)
	) AS poly
) foo
ORDER BY 1, 2;

 x | y | val 
---+---+-----
 1 | 1 |   1
 1 | 2 |   1
 1 | 3 |   1
 2 | 1 |   1
 2 | 2 |    
 2 | 3 |   9
 3 | 1 |   1
 3 | 2 |   9
 3 | 3 |   9

Examples: 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
)
SELECT
	rid, gid, ST_DumpValues(ST_SetValue(rast, 1, geom, gid))
FROM foo t1
CROSS JOIN bar t2
ORDER BY rid, gid;

 rid | gid |                                                                st_dumpvalues                                                                
-----+-----+---------------------------------------------------------------------------------------------------------------------------------------------
   1 |   1 | (1,"{{NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL},{NULL,NULL,1,NULL,NULL},{NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL}}")
   1 |   2 | (1,"{{NULL,NULL,NULL,NULL,NULL},{NULL,2,2,2,NULL},{NULL,2,2,2,NULL},{NULL,2,2,2,NULL},{NULL,NULL,NULL,NULL,NULL}}")
   1 |   3 | (1,"{{3,3,3,3,3},{3,NULL,NULL,NULL,NULL},{3,NULL,NULL,NULL,NULL},{3,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL}}")
   1 |   4 | (1,"{{4,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,4}}")
(4 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
)
SELECT
	t1.rid, t2.gid, t3.gid, ST_DumpValues(ST_SetValues(rast, 1, ARRAY[ROW(t2.geom, t2.gid), ROW(t3.geom, t3.gid)]::geomval[]))
FROM foo t1
CROSS JOIN bar t2
CROSS JOIN bar t3
WHERE t2.gid = 1
	AND t3.gid = 2
ORDER BY t1.rid, t2.gid, t3.gid;

 rid | gid | gid |                                                    st_dumpvalues                                                    
-----+-----+-----+---------------------------------------------------------------------------------------------------------------------
   1 |   1 |   2 | (1,"{{NULL,NULL,NULL,NULL,NULL},{NULL,2,2,2,NULL},{NULL,2,2,2,NULL},{NULL,2,2,2,NULL},{NULL,NULL,NULL,NULL,NULL}}")
(1 row)

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
)
SELECT
	t1.rid, t2.gid, t3.gid, ST_DumpValues(ST_SetValues(rast, 1, ARRAY[ROW(t2.geom, t2.gid), ROW(t3.geom, t3.gid)]::geomval[]))
FROM foo t1
CROSS JOIN bar t2
CROSS JOIN bar t3
WHERE t2.gid = 2
	AND t3.gid = 1
ORDER BY t1.rid, t2.gid, t3.gid;

 rid | gid | gid |                                                    st_dumpvalues                                                    
-----+-----+-----+---------------------------------------------------------------------------------------------------------------------
   1 |   2 |   1 | (1,"{{NULL,NULL,NULL,NULL,NULL},{NULL,2,2,2,NULL},{NULL,2,1,2,NULL},{NULL,2,2,2,NULL},{NULL,NULL,NULL,NULL,NULL}}")
(1 row)

See Also

ST_Value, ST_SetValue, ST_PixelAsPolygons

Prev Up Next
ST_SetValue Home ST_DumpValues