Newer
Older

Sabine Schröder
committed
--
-- toardb/extension/toar_controlled_vocabulary/toar_controlled_vocabulary--0.1.sql

Sabine Schröder
committed
--
-- [Step to install]
--
-- 1.
--
-- INSTALL VERSION: '0.1'
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION toar_controlled_vocabulary" to load this file. \quit
-- Roles
-- =====
-- Role Codes

Sabine Schröder
committed
CREATE TABLE IF NOT EXISTS RC_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO RC_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'PointOfContact', 'point of contact'),
(1, 'PrincipalInvestigator', 'principal investigator'),
(2, 'Originator', 'originator'),
(3, 'Contributor', 'contributor'),
(4, 'Collaborator', 'collaborator'),
(5, 'ResourceProvider', 'resource provider');
-- Role Status

Sabine Schröder
committed
CREATE TABLE IF NOT EXISTS RS_vocabulary (
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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO RS_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'active', 'active'),
(1, 'inactive', 'inactive'),
(2, 'unknown', 'unknown');
-- Contacts
-- ========
-- Kind of Organizations
CREATE TABLE IF NOT EXISTS OK_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO OK_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(1, 'Government', 'government'),
(2, 'Research', 'research'),
(3, 'International', 'international'),
(4, 'NonProfit', 'non-profit'),
(5, 'Commercial', 'commercial'),
(6, 'Individual', 'individual'),
(7, 'Other', 'other');
-- Timeseries
-- ==========
-- Data Access Rights
CREATE TABLE IF NOT EXISTS DA_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO DA_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'ByAttribution', 'by attribution'),
(1, 'ShareAlike', 'share alike'),
(2, 'Restricted', 'restricted');
-- Sampling Frequencies
CREATE TABLE IF NOT EXISTS SF_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO SF_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'Hourly', 'hourly'),
(1, 'ThreeHourly', '3-hourly'),
(2, 'SixHourly', '6-hourly'),
(3, 'Daily', 'daily'),
(4, 'Weekly', 'weekly'),
(5, 'Monthly', 'monthly'),
(6, 'Yearly', 'yearly'),
(7, 'Irregular', 'irregular data samples of constant length'),
(8, 'Irregular2', 'irregular data samples of varying length');
-- Aggregation Types
CREATE TABLE IF NOT EXISTS AT_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO AT_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'Mean', 'mean'),
(1, 'Mean1Of2', 'mean of two values'),
(2, 'Mean1OfWeek', 'weekly mean'),
(3, 'Mean4Samples', 'mean out of 4 samples'),
(4, 'MeanMonth', 'monthly mean'),
(5, 'None', 'none'),
(6, 'Unknown', 'unknown');
-- Data Sources
CREATE TABLE IF NOT EXISTS DS_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO DS_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'Model', 'model'),
(1, 'Measurement', 'measurement');
-- Stationmeta
-- ===========
-- climatic zones
-- see http://eusoils.jrc.eu.europa.eu/projects/RenewableEnergy
CREATE TABLE IF NOT EXISTS CZ_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO CZ_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(-1, 'Undefined', 'undefined'),
( 0, 'Unclassified', 'unclassified'),
( 1, 'WarmTemperateMoist', 'warm temperate moist'),
( 2, 'WarmTemperateDry', 'warm temperate dry'),
( 3, 'CoolTemperateMoist', 'cool temperate moist'),
( 4, 'CoolTemperateDry', 'cool temperate dry'),
( 5, 'PolarMoist', 'polar moist'),
( 6, 'PolarDry', 'polar dry'),
( 7, 'BorealMoist', 'boreal moist'),
( 8, 'BorealDry', 'boreal dry'),
( 9, 'TropicalMontane', 'tropical montane'),
(10, 'TropicalWet', 'tropical wet'),
(11, 'TropicalMoist', 'tropical moist'),
(12, 'TropicalDry', 'tropical dry');
-- Station Coordinate Validity
CREATE TABLE IF NOT EXISTS CV_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO CV_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'NotChecked', 'not checked'),
(1, 'Verified', 'verified'),
(2, 'Plausible', 'plausible'),
(3, 'Doubtful', 'doubtful'),
(4, 'Unverifyable', 'not verifyable');
-- Station Types
CREATE TABLE IF NOT EXISTS ST_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO ST_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'Unknown', 'unknown'),
(1, 'Background', 'background'),
(2, 'Traffic', 'traffic'),
(3, 'Industrial', 'industrial'),
(4, 'Other', 'other');
-- Station Types Of Area
CREATE TABLE IF NOT EXISTS TA_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO TA_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'Unknown', 'unknown'),
(1, 'Urban', 'urban'),
(2, 'Suburban', 'suburban'),
(3, 'Rural', 'rural'),
(4, 'Remote', 'remote');
-- Station TOAR Categories
CREATE TABLE IF NOT EXISTS TC_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO TC_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'Unclassified', 'unclassified'),
(1, 'RuralLowElevation', 'rural low elevation'),
(2, 'RuralHighElevation', 'rural high elevation'),
(3, 'Urban', 'urban');
-- Station HTAP Regions (TIER1)
-- The integer denoting the “tier1” region defined in the task force on hemispheric transport of air pollution (TFHTAP) coordinated model studies.
CREATE TABLE IF NOT EXISTS TR_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO TR_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(-1, 'HTAPTier1Undefined', '-1 (undefined)'),
( 1, 'HTAPTier1World', '1 (World)'),
( 2, 'HTAPTier1OCN', '2 (OCN Non-arctic/Antarctic Ocean)'),
( 3, 'HTAPTier1NAM', '3 (NAM US+Canada (upto 66 N; polar circle))'),
( 4, 'HTAPTier1EUR', '4 (EUR Western + Eastern EU+Turkey (upto 66 N polar circle))'),
( 5, 'HTAPTier1SAS', '5 (SAS South Asia: India, Nepal, Pakistan, Afghanistan, Bangadesh, Sri Lanka)'),
( 6, 'HTAPTier1EAS', '6 (EAS East Asia: China, Korea, Japan)'),
( 7, 'HTAPTier1SEA', '7 (SEA South East Asia)'),
( 8, 'HTAPTier1PAN', '8 (PAN Pacific, Australia+ New Zealand)'),
( 9, 'HTAPTier1NAF', '9 (NAF Northern Africa+Sahara+Sahel)'),
(10, 'HTAPTier1SAF', '10 (SAF Sub Saharan/sub Sahel Africa)'),
(11, 'HTAPTier1MDE', '11 (MDE Middle East: S. Arabia, Oman, etc, Iran, Iraq)'),
(12, 'HTAPTier1MCA', '12 (MCA Mexico, Central America, Caribbean, Guyanas, Venezuela, Columbia)'),
(13, 'HTAPTier1SAM', '13 (SAM S. America)'),
(14, 'HTAPTier1RBU', '14 (RBU Russia, Belarussia, Ukraine)'),
(15, 'HTAPTier1CAS', '15 (CAS Central Asia)'),
(16, 'HTAPTier1NPO', '16 (NPO Arctic Circle (North of 66 N) + Greenland)'),
(17, 'HTAPTier1SPO', '17 (SPO Antarctic)');
-- Station Dominant Landcover Types
-- see: https://lpdaac.usgs.gov/dataset_discovery/modis/modis_products_table/mcd12c1
CREATE TABLE IF NOT EXISTS DL_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO DL_vocabulary (enum_val, enum_str, enum_display_str) VALUES
( -1, 'Undefined', '-1 (undefined)'),
( 0, 'Water', '0 (Water)'),
( 1, 'EGNeedleleaf', '1 (Evergreen Needleleaf forest)'),
( 2, 'EGBroadleaf', '2 (Evergreen Broadleaf forest)'),
( 3, 'DCNeedleleaf', '3 (Deciduous Needleleaf forest)'),
( 4, 'DCBroadleaf', '4 (Deciduous Broadleaf forest)'),
( 5, 'MixedForest', '5 (Mixed forest)'),
( 6, 'ClosedShrublands', '6 (Closed shrublands)'),
( 7, 'OpenShrublands', '7 (Open shrublands)'),
( 8, 'WoodySavannas', '8 (Woody savannas)'),
( 9, 'Savannas', '9 (Savannas)'),
( 10, 'Grasslands', '10 (Grasslands)'),
( 11, 'Wetlands', '11 (Permanent wetlands)'),
( 12, 'Croplands', '12 (Croplands)'),
( 13, 'Urban', '13 (Urban and built-up)'),
( 14, 'Mosaic', '14 (Cropland/Natural vegetation mosaic)'),
( 15, 'Snow', '15 (Snow and ice)'),
( 16, 'Barren', '16 (Barren or sparsely vegetated)'),
(255, 'Fill', '255 (Fill Value/Unclassified)');
-- Measurement Methods
CREATE TABLE IF NOT EXISTS MM_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO MM_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'UVAbsorption', 'UV absorption'),
(1, 'UnknownInstrument', 'unknown instrument');
-- Result Types
CREATE TABLE IF NOT EXISTS RT_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO RT_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'String', 'str'),
(1, 'Integer', 'int'),
(2, 'Float', 'float');
-- Data
-- ====
-- Data Flags
CREATE TABLE IF NOT EXISTS DF_vocabulary (
enum_val INT NOT NULL,
enum_str character varying(128) NOT NULL,
enum_display_str character varying(128) NOT NULL,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str)
);
INSERT INTO DF_vocabulary (enum_val, enum_str, enum_display_str) VALUES
( 0,'OK', 'OK'),
( 1,'OKPreliminary', 'OK preliminary'),
( 2,'OKModified', 'OK modified'),
( 3,'OKPreliminaryModified', 'OK preliminary modified'),
( 4,'Inconsistent', 'inconsistent'),
( 5,'InconsistentPreliminary', 'inconsistent preliminary'),
( 6,'Doubtful', 'doubtful'),
( 7,'DoubtfulPreliminary', 'doubtful preliminary'),
( 8,'DoubtfulModified', 'doubtful modified'),
( 9,'DoubtfulPreliminaryModified', 'doubtful preliminary modified'),
(10,'Wrong', 'wrong'),
(11,'WrongPreliminary', 'wrong preliminary'),
(12,'NotCheckedPreliminary', 'not checked preliminary'),
(13,'Changed', 'changed'),
(14,'Estimated', 'estimated'),
(15,'MissingValue', 'missing value');