toar_controlled_vocabulary--0.2.sql

--
-- toardb/extension/toar_controlled_vocabulary/toar_controlled_vocabulary--0.2.sql
--
-- [Step to install]
--
-- 1. 
--

-- INSTALL VERSION: '0.2'

-- 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

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, 
    PRIMARY KEY(enum_val, enum_str),
    CONSTRAINT rc_enum_val_unique UNIQUE (enum_val)
);

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

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, 
    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, 
    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');

-- Changelogs
-- ==========

-- Type of Change

CREATE TABLE IF NOT EXISTS CL_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),
    CONSTRAINT cl_enum_val_unique UNIQUE (enum_val)
);

INSERT INTO CL_vocabulary (enum_val, enum_str, enum_display_str) VALUES
    (0, 'Created', 'created'),
    (1, 'SingleValue', 'single value correction in metadata'),
    (2, 'Comprehensive', 'comprehensive metadata revision'),
    (3, 'Typo', 'typographic correction of metadata'),
    (4, 'UnspecifiedData', 'unspecified data value corrections'),
    (5, 'Replaced', 'replaced data with a new version'),
    (6, 'Flagging', 'data value flagging');


-- 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),
    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, 
    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, 
    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, 
    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');

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

INSERT INTO MM_vocabulary (enum_val, enum_str, enum_display_str) VALUES
    (0, 'UVAbsorption', 'UV absorption'),
    (1, 'UnknownInstrument', 'unknown instrument');

-- Stationmeta
-- ===========

-- climatic zones
-- see https://esdac.jrc.ec.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),
    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, 
    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, 
    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, 
    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, 
    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
    (-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, 
    PRIMARY KEY(enum_val, enum_str),
    CONSTRAINT tr_enum_val_unique UNIQUE (enum_val)
);      

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

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, 
    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, 
    PRIMARY KEY(enum_val, enum_str),
    CONSTRAINT df_enum_val_unique UNIQUE (enum_val)
);      

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');