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),
CONSTRAINT rc_enum_val_unique UNIQUE (enum_val)

Sabine Schröder
committed
);
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 (
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),
CONSTRAINT rs_enum_val_unique UNIQUE (enum_val)
);
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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT ok_enum_val_unique UNIQUE (enum_val)
);
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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT da_enum_val_unique UNIQUE (enum_val)
);
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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT sf_enum_val_unique UNIQUE (enum_val)
);
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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT at_enum_val_unique UNIQUE (enum_val)
);
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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT ds_enum_val_unique UNIQUE (enum_val)
);
INSERT INTO DS_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'Model', 'model'),
(1, 'Measurement', 'measurement');

Sabine Schröder
committed
-- 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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT mm_enum_val_unique UNIQUE (enum_val)

Sabine Schröder
committed
);
INSERT INTO MM_vocabulary (enum_val, enum_str, enum_display_str) VALUES
(0, 'UVAbsorption', 'UV absorption'),
(1, 'UnknownInstrument', 'unknown instrument');
-- 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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT cz_enum_val_unique UNIQUE (enum_val)
);
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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT cv_enum_val_unique UNIQUE (enum_val)
);
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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT st_enum_val_unique UNIQUE (enum_val)
);
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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT ta_enum_val_unique UNIQUE (enum_val)
);
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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT tc_enum_val_unique UNIQUE (enum_val)
);
INSERT INTO TC_vocabulary (enum_val, enum_str, enum_display_str) VALUES

Sabine Schröder
committed
(-1, 'Unknown', 'unknown'),
( 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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT tr_enum_val_unique UNIQUE (enum_val)
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
290
291
292
293
);
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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT dl_enum_val_unique UNIQUE (enum_val)
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
);
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)');
-- 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,

Sabine Schröder
committed
PRIMARY KEY(enum_val, enum_str),
CONSTRAINT rt_enum_val_unique UNIQUE (enum_val)
);
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),
CONSTRAINT df_enum_val_unique UNIQUE (enum_val)

Sabine Schröder
committed
);
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');