toar_controlled_vocabulary--0.1.sql

--
-- toardb/extension/toar_controlled_vocabulary/toar_controlled_vocabulary--0.1.sql
--
-- [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

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

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

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