rt_quantile.sql 5.85 KB
Newer Older
1 2 3 4
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
FROM ST_Quantile(
5 6 7 8
	ST_SetValue(
		ST_SetValue(
			ST_SetValue(
				ST_AddBand(
9
					ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
10 11 12 13 14 15 16 17
					, 1, '64BF', 0, 0
				)
				, 1, 1, 1, -10
			)
			, 1, 5, 4, 0
		)
		, 1, 5, 5, 3.14159
	)
18
	, 1, FALSE, ARRAY[0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1]::double precision[]
19
);
20 21 22 23
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
FROM ST_Quantile(
24 25 26 27
	ST_SetValue(
		ST_SetValue(
			ST_SetValue(
				ST_AddBand(
28
					ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
29 30 31 32 33 34 35 36
					, 1, '64BF', 0, 0
				)
				, 1, 1, 1, -10
			)
			, 1, 5, 4, 0
		)
		, 1, 5, 5, 3.14159
	)
37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
	, 1, FALSE, ARRAY[0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1]::double precision[]
);
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
FROM ST_Quantile(
	ST_SetValue(
		ST_SetValue(
			ST_SetValue(
				ST_AddBand(
					ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
					, 1, '64BF', 0, NULL
				)
				, 1, 1, 1, -10
			)
			, 1, 5, 4, 0
		)
		, 1, 5, 5, 3.14159
	)
56 57
	, 1, ARRAY[0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1]::double precision[]
);
58 59 60 61
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
FROM ST_Quantile(
62 63 64 65
	ST_SetValue(
		ST_SetValue(
			ST_SetValue(
				ST_AddBand(
66
					ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
67 68 69 70 71 72 73 74
					, 1, '64BF', 0, 0
				)
				, 1, 1, 1, -10
			)
			, 1, 5, 4, 0
		)
		, 1, 5, 5, 3.14159
	),
75
	1, FALSE
76
);
77 78 79 80
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
FROM ST_Quantile(
81 82 83 84
	ST_SetValue(
		ST_SetValue(
			ST_SetValue(
				ST_AddBand(
85
					ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
86 87 88 89 90 91 92 93 94 95
					, 1, '64BF', 0, 0
				)
				, 1, 1, 1, -10
			)
			, 1, 5, 4, 0
		)
		, 1, 5, 5, 3.14159
	),
	1
);
96 97 98 99
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
FROM ST_Quantile(
100 101 102 103
	ST_SetValue(
		ST_SetValue(
			ST_SetValue(
				ST_AddBand(
104
					ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
105 106 107 108 109 110 111 112 113 114
					, 1, '64BF', 0, 0
				)
				, 1, 1, 1, -10
			)
			, 1, 5, 4, 0
		)
		, 1, 5, 5, 3.14159
	),
	ARRAY[0.05, 0.95]::double precision[]
);
115 116 117 118
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
FROM ST_Quantile(
119 120 121 122
	ST_SetValue(
		ST_SetValue(
			ST_SetValue(
				ST_AddBand(
123
					ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
124 125 126 127 128 129 130 131 132
					, 1, '64BF', 0, 0
				)
				, 1, 1, 1, -10
			)
			, 1, 5, 4, 0
		)
		, 1, 5, 5, 3.14159
	)
);
133 134
SELECT round(
	ST_Quantile(
135 136
		ST_SetValue(
			ST_SetValue(
137 138
				ST_SetValue(
					ST_AddBand(
139
						ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
140 141 142
						, 1, '64BF', 0, 0
					)
					, 1, 1, 1, -10
143
				)
144
				, 1, 5, 4, 0
145
			)
146 147 148 149
			, 1, 5, 5, 3.14159
		),
		1, FALSE, 0.05
	)::numeric, 3
150
);
151 152
SELECT round(
	ST_Quantile(
153 154
		ST_SetValue(
			ST_SetValue(
155 156
				ST_SetValue(
					ST_AddBand(
157
						ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
158 159 160
						, 1, '64BF', 0, 0
					)
					, 1, 1, 1, -10
161
				)
162
				, 1, 5, 4, 0
163
			)
164 165 166 167
			, 1, 5, 5, 3.14159
		),
		1, 0.95
	)::numeric, 3
168
);
169 170
SELECT round(
	ST_Quantile(
171 172
		ST_SetValue(
			ST_SetValue(
173 174
				ST_SetValue(
					ST_AddBand(
175
						ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
176 177 178
						, 1, '64BF', 0, 0
					)
					, 1, 1, 1, -10
179
				)
180
				, 1, 5, 4, 0
181
			)
182 183 184 185
			, 1, 5, 5, 3.14159
		),
		FALSE, 0.7
	)::numeric, 3
186
);
187 188
SELECT round(
	ST_Quantile(
189 190
		ST_SetValue(
			ST_SetValue(
191 192
				ST_SetValue(
					ST_AddBand(
193
						ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
194 195 196
						, 1, '64BF', 0, 0
					)
					, 1, 1, 1, -10
197
				)
198
				, 1, 5, 4, 0
199
			)
200 201 202 203
			, 1, 5, 5, 3.14159
		),
		0.45
	)::numeric, 3
204
);
205 206 207 208 209 210
SELECT round(
	ST_Quantile(
		ST_SetValue(
			ST_SetValue(
				ST_SetValue(
					ST_AddBand(
211
						ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
212 213 214 215 216 217 218 219 220 221 222
						, 1, '64BF', 0, 0
					)
					, 1, 1, 1, -10
				)
				, 1, 5, 4, 0
			)
			, 1, 5, 5, 3.14159
		),
		2, 0.45
	)::numeric, 3
);
223
BEGIN;
224
CREATE TEMP TABLE test_quantile
225 226 227 228 229 230 231 232
	ON COMMIT DROP AS
	SELECT
		rast.rast
	FROM (
		SELECT ST_SetValue(
			ST_SetValue(
				ST_SetValue(
					ST_AddBand(
233
						ST_MakeEmptyRaster(10, 10, 10, 10, 2, 2, 0, 0,0)
234 235 236 237 238 239 240 241 242 243
						, 1, '64BF', 0, 0
					)
					, 1, 1, 1, -10
				)
				, 1, 5, 4, 0
			)
			, 1, 5, 5, 3.14159
		) AS rast
	) AS rast
	FULL JOIN (
244
		SELECT generate_series(1, 2) AS id
245 246 247 248 249
	) AS id
		ON 1 = 1;
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
250
FROM ST_Quantile('test_quantile', 'rast', 1, TRUE, ARRAY[0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1]::double precision[]);
251 252 253
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
254
FROM ST_Quantile('test_quantile', 'rast', 1, TRUE);
255 256 257
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
258
FROM ST_Quantile('test_quantile', 'rast', 1, FALSE, ARRAY[0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1]::double precision[]);
259 260 261
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
262
FROM ST_Quantile('test_quantile', 'rast', 1, FALSE);
263 264 265
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
266
FROM ST_Quantile('test_quantile', 'rast', 1, ARRAY[0.05, 0.95]::double precision[]);
267 268 269
SELECT
	round(quantile::numeric, 3),
	round(value::numeric, 3)
270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290
FROM ST_Quantile('test_quantile', 'rast', ARRAY[0.05, 0.95]::double precision[]);
SELECT round(ST_Quantile('test_quantile', 'rast', 1, FALSE, 0.95)::numeric, 3);
SELECT round(ST_Quantile('test_quantile', 'rast', 1, 0.95)::numeric, 3);
SELECT round(ST_Quantile('test_quantile', 'rast', TRUE, 0.95)::numeric, 3);
SELECT round(ST_Quantile('test_quantile', 'rast', 0.5)::numeric, 3);
SAVEPOINT test;
SELECT round(ST_Quantile('test_quantile', 'rast', 2, 0.5)::numeric, 3);
ROLLBACK TO SAVEPOINT test;
RELEASE SAVEPOINT test;
SAVEPOINT test;
SELECT round(ST_Quantile('test_quantile1', 'rast', 0.5)::numeric, 3);
ROLLBACK TO SAVEPOINT test;
RELEASE SAVEPOINT test;
SAVEPOINT test;
SELECT round(ST_Quantile('test_quantile', 'rast2', 1, 0.5)::numeric, 3);
ROLLBACK TO SAVEPOINT test;
RELEASE SAVEPOINT test;
SAVEPOINT test;
SELECT round(ST_Quantile('test_quantile', 'rast', -1.)::numeric, 3);
ROLLBACK TO SAVEPOINT test;
RELEASE SAVEPOINT test;
291
ROLLBACK;