forked from opendatacube/datacube-ows
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcreate_tables.sql
127 lines (107 loc) · 3.84 KB
/
create_tables.sql
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
-- Create wms schema
create schema if not exists wms;
-- Create wms ranges table
create table if not exists wms.product_ranges (
-- ID PK and FK to dataset_type (product) table.
id smallint not null primary key references agdc.dataset_type (id),
-- Lat/Long ranges, for ExGeographicBoundingBox
lat_min decimal not null,
lat_max decimal not null,
lon_min decimal not null,
lon_max decimal not null,
-- dates. A JSON array of strings in 'YYYY-MM-DD' format.
dates jsonb not null,
-- bboxes. A JSON object of bounding boxes for all supported CRSs.
-- Format: {
-- "CRS1": {
-- "left": 1.000,
-- "right": 2.000,
-- "bottom": 1.000,
-- "top": 2.000
-- },
-- "CRS2": { ... },
-- ...
-- }
bboxes jsonb not null
);
create table if not exists wms.sub_product_ranges (
-- ID PK and FK to dataset_type (product) table.
product_id smallint not null references agdc.dataset_type (id),
sub_product_id smallint not null,
-- Lat/Long ranges, for ExGeographicBoundingBox
lat_min decimal not null,
lat_max decimal not null,
lon_min decimal not null,
lon_max decimal not null,
-- dates. A JSON array of strings in 'YYYY-MM-DD' format.
dates jsonb not null,
-- bboxes. A JSON object of bounding boxes for all supported CRSs.
-- Format: {
-- "CRS1": {
-- "left": 1.000,
-- "right": 2.000,
-- "bottom": 1.000,
-- "top": 2.000
-- },
-- "CRS2": { ... },
-- ...
-- }
bboxes jsonb not null,
constraint pk_sub_product_ranges primary key ( product_id, sub_product_id)
);
create table if not exists wms.multiproduct_ranges (
-- ID PK and FK to dataset_type (product) table.
wms_product_name varchar(128) not null primary key,
-- Lat/Long ranges, for ExGeographicBoundingBox
lat_min decimal not null,
lat_max decimal not null,
lon_min decimal not null,
lon_max decimal not null,
-- dates. A JSON array of strings in 'YYYY-MM-DD' format.
dates jsonb not null,
-- bboxes. A JSON object of bounding boxes for all supported CRSs.
-- Format: {
-- "CRS1": {
-- "left": 1.000,
-- "right": 2.000,
-- "bottom": 1.000,
-- "top": 2.000
-- },
-- "CRS2": { ... },
-- ...
-- }
bboxes jsonb not null,
);
grant USAGE on schema wms to cube;
CREATE OR REPLACE FUNCTION wms_get_min(integer[], text) RETURNS numeric AS $$
DECLARE
ret numeric;
ul text[] DEFAULT array_append('{extent, coord, ul}', $2);
ur text[] DEFAULT array_append('{extent, coord, ur}', $2);
ll text[] DEFAULT array_append('{extent, coord, ll}', $2);
lr text[] DEFAULT array_append('{extent, coord, lr}', $2);
BEGIN
WITH m AS ( SELECT metadata FROM agdc.dataset WHERE dataset_type_ref in $1 AND archived IS NULL )
SELECT MIN(LEAST((m.metadata#>>ul)::numeric, (m.metadata#>>ur)::numeric,
(m.metadata#>>ll)::numeric, (m.metadata#>>lr)::numeric))
INTO ret
FROM m;
RETURN ret;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION wms_get_max(integer[], text) RETURNS numeric AS $$
DECLARE
ret numeric;
ul text[] DEFAULT array_append('{extent, coord, ul}', $2);
ur text[] DEFAULT array_append('{extent, coord, ur}', $2);
ll text[] DEFAULT array_append('{extent, coord, ll}', $2);
lr text[] DEFAULT array_append('{extent, coord, lr}', $2);
BEGIN
WITH m AS ( SELECT metadata FROM agdc.dataset WHERE dataset_type_ref in $1 AND archived IS NULL )
SELECT MAX(GREATEST((m.metadata#>>ul)::numeric, (m.metadata#>>ur)::numeric,
(m.metadata#>>ll)::numeric, (m.metadata#>>lr)::numeric))
INTO ret
FROM m;
RETURN ret;
END;
$$ LANGUAGE plpgsql;