1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
SET client_min_messages TO warning;
DROP TABLE IF EXISTS raster_dumpvalues;
CREATE TABLE raster_dumpvalues (
rid integer,
rast raster
);
CREATE OR REPLACE FUNCTION make_raster(
rast raster DEFAULT NULL,
pixtype text DEFAULT '8BUI',
rows integer DEFAULT 3,
columns integer DEFAULT 3,
nodataval double precision DEFAULT 0,
start_val double precision DEFAULT 1,
step double precision DEFAULT 1,
skip_expr text DEFAULT NULL
)
RETURNS raster
AS $$
DECLARE
x int;
y int;
value double precision;
values double precision[][][];
result boolean;
expr text;
_rast raster;
nband int;
BEGIN
IF rast IS NULL THEN
nband := 1;
_rast := ST_AddBand(ST_MakeEmptyRaster(columns, rows, 0, 0, 1, -1, 0, 0, 0), nband, pixtype, 0, nodataval);
ELSE
nband := ST_NumBands(rast) + 1;
_rast := ST_AddBand(rast, nband, pixtype, 0, nodataval);
END IF;
value := start_val;
values := array_fill(NULL::double precision, ARRAY[columns, rows]);
FOR y IN 1..columns LOOP
FOR x IN 1..rows LOOP
IF skip_expr IS NULL OR length(skip_expr) < 1 THEN
result := TRUE;
ELSE
expr := replace(skip_expr, '[v]'::text, value::text);
EXECUTE 'SELECT (' || expr || ')::boolean' INTO result;
END IF;
IF result IS TRUE THEN
values[y][x] := value;
END IF;
value := value + step;
END LOOP;
END LOOP;
_rast := ST_SetValues(_rast, nband, 1, 1, values);
RETURN _rast;
END;
$$ LANGUAGE 'plpgsql';
INSERT INTO raster_dumpvalues
SELECT 1, make_raster(NULL, '8BSI', 3, 3, 0, 1) UNION ALL
SELECT 2, make_raster(NULL, '8BSI', 3, 3, 0, -1) UNION ALL
SELECT 3, make_raster(NULL, '8BSI', 3, 3, 0, 1) UNION ALL
SELECT 4, make_raster(NULL, '8BSI', 3, 3, 0, -2) UNION ALL
SELECT 5, make_raster(NULL, '8BSI', 3, 3, 0, 2)
;
INSERT INTO raster_dumpvalues
SELECT
rid + 10,
make_raster(rast, '16BSI', 3, 3, rid, (rid / 2)::integer)
FROM raster_dumpvalues
WHERE rid <= 10;
INSERT INTO raster_dumpvalues
SELECT
rid + 10,
make_raster(rast, '32BSI', 3, 3, rid, (rid / 2)::integer)
FROM raster_dumpvalues
WHERE rid BETWEEN 11 AND 20;
DROP FUNCTION IF EXISTS make_raster(raster, text, integer, integer, double precision, double precision, double precision, text);
SELECT
rid,
(ST_DumpValues(rast)).*
FROM raster_dumpvalues
ORDER BY rid;
SELECT
rid,
(ST_DumpValues(rast, ARRAY[3,2,1])).*
FROM raster_dumpvalues
WHERE rid > 20
ORDER BY rid;
DROP TABLE IF EXISTS raster_dumpvalues;
SELECT (ST_DumpValues(ST_AddBand(ST_MakeEmptyRaster(0, 0, 0, 0, 1), ARRAY[ROW(NULL, '8BUI', 255, 0),ROW(NULL, '16BUI', 1, 2)]::addbandarg[]))).*;
-- #3086
DROP TABLE IF EXISTS raster_tile;
CREATE TABLE raster_tile AS
WITH foo AS (
SELECT ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0), 1, '8BUI', 1, 0), 2, '8BUI', 10, 0) AS rast UNION ALL
SELECT ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 3, 0, 1, -1, 0, 0, 0), 1, '8BUI', 2, 0), 2, '8BUI', 20, 0) AS rast
)
SELECT ST_Union(rast) AS rast FROM foo;
WITH foo AS (SELECT ST_Tile(rast, 3, 3, TRUE) AS rast FROM raster_tile) SELECT (ST_DumpValues(rast, array[1,2,3])).* FROM foo;
DROP TABLE IF EXISTS raster_tile;
|
