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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
SET client_min_messages TO warning;
-- Test for table without explicit schema
CREATE TABLE res1 AS SELECT
ST_AddBand(
ST_MakeEmptyRaster(10, 10, x, y, 1, -1, 0, 0, 0)
, 1, '8BUI', 0, 0
) r
FROM generate_series(-170, 160, 10) x,
generate_series(80, -70, -10) y;
SELECT addrasterconstraints('res1', 'r');
SELECT ST_CreateOverview('res1', 'r', 2)::text = 'o_2_res1';
SELECT ST_CreateOverview('res1', 'r', 4)::text = 'o_4_res1';
SELECT ST_CreateOverview('res1', 'r', 8)::text = 'o_8_res1';
SELECT ST_CreateOverview('res1', 'r', 16)::text = 'o_16_res1';
SELECT r_table_name tab, r_raster_column c, srid s,
scale_x sx, scale_y sy,
blocksize_x w, blocksize_y h, same_alignment a,
-- regular_blocking (why not regular?)
--extent::box2d e,
st_covers(extent::box2d, 'BOX(-170 -80,170 80)'::box2d) ec,
st_xmin(extent::box2d) = -170 as eix,
st_ymax(extent::box2d) = 80 as eiy,
(st_xmax(extent::box2d) - 170) <= scale_x as eox,
--(st_xmax(extent::box2d) - 170) eoxd,
abs(st_ymin(extent::box2d) + 80) <= abs(scale_y) as eoy
--,abs(st_ymin(extent::box2d) + 80) eoyd
FROM raster_columns
WHERE r_table_name like '%res1'
ORDER BY scale_x, r_table_name;
SELECT o_table_name, o_raster_column,
r_table_name, r_raster_column, overview_factor
FROM raster_overviews
WHERE r_table_name = 'res1'
ORDER BY overview_factor;
SELECT 'count',
(SELECT count(*) r1 from res1),
(SELECT count(*) r2 from o_2_res1),
(SELECT count(*) r4 from o_4_res1),
(SELECT count(*) r8 from o_8_res1),
(SELECT count(*) r16 from o_16_res1)
;
-- End of overview test on table without explicit schema
DROP TABLE o_16_res1;
DROP TABLE o_8_res1;
DROP TABLE o_4_res1;
DROP TABLE o_2_res1;
-- Keep the source table
-- Test overview with table in schema
--
CREATE SCHEMA oschm;
-- offset the schema tableto distinguish it from original
-- let it be small to reduce time cost.
CREATE TABLE oschm.res1 AS SELECT
ST_AddBand(
ST_MakeEmptyRaster(10, 10, x, y, 1, -1, 0, 0, 0)
, 1, '8BUI', 0, 0
) r
FROM generate_series(100, 270, 10) x,
generate_series(140, -30, -10) y;
SELECT addrasterconstraints('oschm'::name,'res1'::name, 'r'::name);
-- add overview with explicit schema
SELECT ST_CreateOverview('oschm.res1', 'r', 8)::text = 'oschm.o_8_res1';
-- set search path to 'oschm' first
SET search_path to oschm,public;
-- create overview with schema in search_path
SELECT ST_CreateOverview('res1', 'r', 4)::text = 'o_4_res1';
-- Create overview for table in public schema with explict path
-- at same factor of schema table
SELECT ST_CreateOverview('public.res1', 'r', 8)::text = 'public.o_8_res1';
-- Reset the search_path
SET search_path to public;
-- Create public overview of public table with same name
-- and scale as schema table above using reset search path.
SELECT ST_CreateOverview('res1', 'r', 4)::text = 'o_4_res1';
-- Check scale and extent
-- Offset means that original raster overviews won't match
-- extent and values only mach on schema table not public one
SELECT r_table_schema, r_table_name tab, r_raster_column c,
srid s, scale_x sx, scale_y sy,
blocksize_x w, blocksize_y h, same_alignment a,
-- regular_blocking (why not regular?)
--extent::box2d e,
st_covers(extent::box2d, 'BOX(100 -30,270 140)'::box2d) ec,
st_xmin(extent::box2d) = 100 as eix,
st_ymax(extent::box2d) = 140 as eiy,
(st_xmax(extent::box2d) - 280) <= scale_x as eox,
--(st_xmax(extent::box2d) - 170) eoxd,
abs(st_ymin(extent::box2d) + 40) <= abs(scale_y) as eoy
--,abs(st_ymin(extent::box2d) + 80) eoyd
FROM raster_columns
WHERE r_table_name like '%res1'
ORDER BY r_table_schema, scale_x, r_table_name;
-- this test may be redundant - it's just confirming that the
-- overview factor is correct, which we do for the original table.
SELECT o_table_schema, o_table_name, o_raster_column,
r_table_schema, r_table_name, r_raster_column, overview_factor
FROM raster_overviews
WHERE r_table_name = 'res1'
AND r_table_schema = 'oschm'
ORDER BY o_table_schema,overview_factor;
-- get the oschm table sizes
SELECT 'count',
(SELECT count(*) r1 from oschm.res1),
(SELECT count(*) r4 from oschm.o_4_res1),
(SELECT count(*) r8 from oschm.o_8_res1) ;
-- clean up scheam oschm
DROP TABLE oschm.o_8_res1;
DROP TABLE oschm.o_4_res1;
DROP TABLE oschm.res1;
DROP SCHEMA oschm;
-- Drop the tables for noschema test
DROP TABLE o_8_res1;
DROP TABLE o_4_res1;
DROP TABLE res1;
-- Reset the session environment
-- possibly a bit harsh, but we had to set the search_path
-- and need to reset it back to default.
DISCARD ALL;
|
