Appendix 5: Database objects/CGMS14SYS

content
1. REGION-datasets
2. other datasets

REGION-datasets

content
1.1 REGION_ELEMENTS
1.2 REGION_SOURCES

REGION_ELEMENTS

Table.

Structure
Attribute Constraint Type Unit Comment IDREGION_ELEMENT NOT NULL NUMBER(22, 12) IDORIGINAL_CODE NOT NULL VARCHAR2(20) IDREGION NOT NULL VARCHAR2(20) IDREGION_SOURCE NOT NULL NUMBER(22, 12) NAME VARCHAR2(255) REGION_LEVEL NOT NULL NUMBER(22) IDFATHER NUMBER(22, 12) STANDARD NOT NULL VARCHAR2(20)

REGION_SOURCES

Table.

Structure
Attribute Constraint Type Unit Comment IDREGION_SOURCE NOT NULL NUMBER(22, 12) REGION_SOURCE NOT NULL VARCHAR2(20) DESCRIPTION VARCHAR2(255) UNC VARCHAR2(255)

other datasets

content
2.1 CATEGORIES
2.2 COVERS
2.3 COVER_SOURCES
2.4 CROPS
2.5 CROP_PARAMETERS
2.6 GRIDS
2.7 GRID_SOURCES
2.8 LINK_COVER_WINDOW_SOURCE
2.9 MODELS
2.10 REGIONS
2.11 SOIL_SOURCES
2.12 STATIONS
2.13 STATION_REFERENCE_COEFFICIENTS
2.14 STATISTIC_SOURCES
2.15 SUITABILITY_PROPERTIES
2.16 SUPIT_CONSTANTS
2.17 THRESHOLDS
2.18 TIME_DIMENSIONS
2.19 TREATMENTS
2.20 WINDOWS

CATEGORIES

Table.

Structure
Attribute Constraint Type Unit Comment IDCATEGORY NOT NULL NUMBER(22, 12) CATEGORY NOT NULL VARCHAR2(30) DESCRIPTOR NOT NULL VARCHAR2(1000)

COVERS

Table.

Structure
Attribute Constraint Type Unit Comment IDCOVER NOT NULL NUMBER(22, 12) COVER NOT NULL VARCHAR2(30) DESCRIPTOR NOT NULL VARCHAR2(1000)

COVER_SOURCES

Table.

Structure
Attribute Constraint Type Unit Comment IDCOVER_SOURCE NOT NULL NUMBER(22, 12) COVER_SOURCE NOT NULL VARCHAR2(30) DESCRIPTOR VARCHAR2(255) UNC VARCHAR2(255)

CROPS

Table.

Structure
Attribute Constraint Type Unit Comment IDCROP NOT NULL NUMBER(22, 12) CROP NOT NULL VARCHAR2(30) DESCRIPTOR NOT NULL VARCHAR2(255) EUROSTAT NOT NULL VARCHAR2(30)

CROP_PARAMETERS

Table.

Structure
Attribute Constraint Type Unit Comment IDCROP_PARAMETER NOT NULL NUMBER(22, 12) CROP_PARAMETER NOT NULL VARCHAR2(40) DESCRIPTOR NOT NULL VARCHAR2(255) MULTI NOT NULL VARCHAR2(1) IDCATEGORY NOT NULL NUMBER(22, 12)

GRIDS

Table.

Structure

Attribute Constraint Type Unit Comment IDGRID NOT NULL NUMBER(22, 12) IDGRID_SOURCE NOT NULL NUMBER(22, 12) IDORIGINAL_CODE NOT NULL VARCHAR2(20) LONGITUDE NOT NULL NUMBER(22) LATITUDE NOT NULL NUMBER(22) ALTITUDE NOT NULL NUMBER(22) ALTITUDE_16 NUMBER(22) ALTITUDE_25 NUMBER(22) X NOT NULL NUMBER(22) Y NOT NULL NUMBER(22) DISTANCE_FROM_COAST NUMBER(22) CLIMATE_BARRIER NUMBER(22) HAS_LAND NOT NULL VARCHAR2(1)

GRID_SOURCES

Table.

Structure
Attribute Constraint Type Unit Comment IDGRID_SOURCE NOT NULL NUMBER(22, 12) IDWINDOW NOT NULL NUMBER(22, 12) GRID_SOURCE NOT NULL VARCHAR2(20) RESOLUTION NOT NULL NUMBER(22) UNIT NOT NULL VARCHAR2(20) DESCRIPTION VARCHAR2(255) UNC VARCHAR2(255)

LINK_COVER_WINDOW_SOURCE

Table.

Structure
Attribute Constraint Type Unit Comment IDWINDOW NOT NULL NUMBER(22, 12) IDCOVER NOT NULL NUMBER(22, 12) IDCOVER_SOURCE NOT NULL NUMBER(22, 12)

MODELS

Table.

Structure
Attribute Constraint Type Unit Comment IDMODEL NOT NULL NUMBER(22, 12) MODEL NOT NULL VARCHAR2(30) DESCRIPTOR NOT NULL VARCHAR2(1000)

REGIONS

Table.

Structure
Attribute Constraint Type Unit Comment IDREGION NOT NULL VARCHAR2(20) IDWINDOW NUMBER(22, 12) REGION NOT NULL VARCHAR2(20) NAME NOT NULL VARCHAR2(255) CONTINENT NOT NULL VARCHAR2(20) POPULATION NUMBER(22)

SOIL_SOURCES

Table.

Structure
Attribute Constraint Type Unit Comment IDSOIL_SOURCE NOT NULL NUMBER(22, 12) SOIL_SOURCE NOT NULL VARCHAR2(30) DESCRIPTOR NOT NULL VARCHAR2(255)

STATIONS

Table.

Structure

Attribute Constraint Type Unit Comment IDSTATION NOT NULL NUMBER(22, 12) STATION NOT NULL VARCHAR2(32) ORGANIZATION NOT NULL VARCHAR2(32) IDWMO NUMBER(22, 12) WMO VARCHAR2(32) LATITUDE NOT NULL NUMBER(22, 7) LONGITUDE NOT NULL NUMBER(22, 8) Y_COORDINATE NOT NULL NUMBER(22, 13) X_COORDINATE NOT NULL NUMBER(22, 13) ALTITUDE NOT NULL NUMBER(22, 5) IDCLIMATE_ZONE NOT NULL NUMBER(22, 12) COUNTRY NOT NULL VARCHAR2(26) DISTANCE_TO_COAST NOT NULL NUMBER(22, 4) ANGSTROM_A NOT NULL NUMBER(22, 18) ANGSTROM_B NOT NULL NUMBER(22, 18) SUPIT_A NOT NULL NUMBER(22, 18) SUPIT_B NOT NULL NUMBER(22, 18) SUPIT_C NOT NULL NUMBER(22, 18) HARGREAVES_A NOT NULL NUMBER(22, 18) HARGREAVES_B NOT NULL NUMBER(22, 18) RELIABLE_STATIONS NOT NULL NUMBER(22, 1) ACTIVE_STATIONS NOT NULL NUMBER(22, 1) AVAIL_PRECIPITATION_THRESHOLD NOT NULL NUMBER(22, 4) AVAIL_SNOWDEPTH_THRESHOLD NOT NULL NUMBER(22, 4) AVAIL_TEMPERATURE_THRESHOLD NOT NULL NUMBER(22, 4) AVAIL_HUMIDITY_THRESHOLD NOT NULL NUMBER(22, 4) AVAIL_REST_THRESHOLD NOT NULL NUMBER(22, 4)

STATION_REFERENCE_COEFFICIENTS

Table.

• Description: contains calculated regression constants for selected weather stations covering a large part of Europe
• Use: is used by view SUPIT_REFERENCE_STATIONS
• Type: permanent table
• Remarks: none

Structure
Attribute Constraint Type Unit Comment LATITUDE NOT NULL NUMBER(22) - LONGITUDE NOT NULL NUMBER(22) - ALTITUDE NOT NULL NUMBER(22) - ANGSTROM_A NUMBER(22) - ANGSTROM_B NUMBER(22) - SUPIT_A NUMBER(22) - SUPIT_B NUMBER(22) - SUPIT_C NUMBER(22) - HARGREAVES_A NUMBER(22) - HARGREAVES_B NUMBER(22) -

STATISTIC_SOURCES

Table.

Structure
Attribute Constraint Type Unit Comment IDSTATISTIC_SOURCE NOT NULL NUMBER(22, 12) STATISTIC_SOURCE NOT NULL VARCHAR2(60) DESCRIPTOR NOT NULL VARCHAR2(255)

SUITABILITY_PROPERTIES

Table.

Structure
Attribute Constraint Type Unit Comment IDSUITABILITY_PROPERTY NOT NULL NUMBER(22, 12) SUITABILITY_PROPERTY NOT NULL VARCHAR2(30) DESCRIPTOR NOT NULL VARCHAR2(255)

SUPIT_CONSTANTS

Table.

• Description: the regression constants per weather station used for the estimation of global radiation for the station
• Use: support table program SupitConstant; results are copied to table STATION
• Type: permanent table
• Remarks: populated by program SupitConstant (recalculate this table when new stations are added to the weather_station table or when table new supit_reference_stations is updated table)

Structure

Attribute Constraint Type Unit Comment WMO_NO NOT NULL NUMBER(22, 7) - station number ANGSTROM_A NUMBER(22) - ångström constant A using sun shine duration ANGSTROM_B NUMBER(22) - ångström constant B using sun shine duration SUPIT_A NUMBER(22) - supit constant A using cloud cover and temperature SUPIT_B NUMBER(22) - supit constant B using cloud cover and temperature SUPIT_C NUMBER(22) KJ.m-2.d-1 supit constant C using cloud cover and temperature HARGREAVES_A NUMBER(22) - hargreaves constant A using temperature HARGREAVES_B NUMBER(22) KJ.m-2.d-1 hargreaves constant B using temperature

THRESHOLDS

Table.

Structure
Attribute Constraint Type Unit Comment IDTHRESHOLD NOT NULL NUMBER(22, 12) IDWINDOW NOT NULL NUMBER(22, 12) THRESHOLD NOT NULL VARCHAR2(30) INDICATOR NOT NULL VARCHAR2(30) OPERATOR NOT NULL VARCHAR2(30) VALUE_MIN NOT NULL NUMBER(22) VALUE_MAX NOT NULL NUMBER(22)

TIME_DIMENSIONS

Table.

Structure
Attribute Constraint Type Unit Comment DAY NOT NULL DATE DECADE NUMBER(22) DECADE_ID NUMBER(22) MONTH NUMBER(22) MONTH_ID NUMBER(22) QUARTER NUMBER(22) QUARTER_ID NUMBER(22) YEAR NUMBER(22) LAST_DAY_DECADE VARCHAR2(1)

TREATMENTS

Table.

Structure
Attribute Constraint Type Unit Comment IDTREATMENT NOT NULL NUMBER(22, 12) TREATMENT NOT NULL VARCHAR2(60) DESCRIPTOR NOT NULL VARCHAR2(255)

WINDOWS

Table.

Structure

Attribute Constraint Type Unit Comment IDWINDOW NOT NULL NUMBER(22, 12) WINDOW NOT NULL VARCHAR2(10) NAME NOT NULL VARCHAR2(255) REFERENCE_SYSTEM NOT NULL VARCHAR2(2000) DESCRIPTOR VARCHAR2(255) MAX_LAT NOT NULL NUMBER(22) MAX_LON NOT NULL NUMBER(22) MIN_LAT NOT NULL NUMBER(22) MIN_LON NOT NULL NUMBER(22) MAX_X NOT NULL NUMBER(22) MAX_Y NOT NULL NUMBER(22) MIN_X NOT NULL NUMBER(22) MIN_Y NOT NULL NUMBER(22) ROI NUMBER(22, 3)