Appendix 5: Database objects/GWSI E 16

content
1. CROP-datasets
2. GRID-datasets
3. SOILS-datasets
4. other datasets

CROP-datasets

content
1.1 CROPS
1.2 CROP_COEFFICIENTS
1.3 CROP_MASKS
1.4 CROP_SEASONS
1.5 CROP_SEASON_AREAS

CROPS

• Description: Crops
• Use: To store the crop names and to maintain db integrity via idcrop as reference key
• Type: Table
• Remarks: In this implementation, the implemented crops represent generalized crops, rather than actual crops.

Structure
Attribute Constraint Type Unit Comment IDCROP False NUMBER - Unique number to identify the crop CROP False VARCHAR2 - Crop name DESCRIPTOR False VARCHAR2 - Optional additional description

CROP_COEFFICIENTS

• Description: Single crop coefficients (Kc) and crop rooting depth per crop on a 25x25 km resolution.
• Use: To calculate crop evapotranspiration (ETc) under standard conditions needed for deriving the Water Satisfaction Index (WSI).
• Type: Table
• Remarks:

KC values: Single crop coefficients (Kc) incorporate crop characteristics and averaged effects of evaporation from the soil. Currently two crops are implemented that represent landcover types arable land and grassland. The crop coefficients are area-weighted averages of crop coefficients of individual crops assigned to that landcover type. Arable land represents: Maize, Spring wheat, Winter wheat, Millet, Sorghum. Grassland represents: Rotated grazing and Extensive Grazing. Areas used for calculating the weighted average crop coefficients are derived from MAPSPAM (aggregated to 25x25 km resolution). Crop coefficient values are obtained from coefficients FAO.

CROP_ROOTING_DEPTH are area-weighted averages of maximum crop rooting depth of individual crops assigned to that landcover type. Areas used for calculating the weighted average crop rooting depth are derived from MAPSPAM (aggregated to 25x25 km resolution).

Structure

Attribute Constraint Type Unit Comment IDCROP_COEFFICIENT False NUMBER - Unique number to identify each record IDGRID_25KM False NUMBER - Unique id of the 25km grid to link weather and crop coefficients IDCROP False NUMBER - Unique number to identify the crop KC_INI False NUMBER - KC before/until SEASONS.START_OF_SEASON KC_MID False NUMBER - KC between SEASONS.MAX_OF_SEASON and SEASONS.START_OF_SENESCENCE KC_END False NUMBER - KC at SEASONS.END_OF_SEASON CROP_ROOTING_DEPTH False NUMBER km Maximum crop rooting depth allowed by the crop KC_INT_DIST False NUMBER cm Distance to nearest 0.25 degree grid cell for which KC is available

CROP_MASKS

• Description: Crop mask storing the area where the crops are grown on a grid_1km resolution
• Use: To hide or exclude crop simulations outside the mask, or with a limited area. etc.
• Type: Table
• Remarks: The table contains all 1km grids of a crop that have an area > 0. The mask also includes grids for which no seasons are available. Grids for which no seasons are available are currently not stored in grid_1km. Therefore the foreign key referencing grid_1km.idgrid_1km is not installed.

Structure
Attribute Constraint Type Unit Comment IDCROP_MASK False NUMBER - Unique number to identify each record IDCROP False NUMBER - Unique number to identify crop IDGRID_1KM False NUMBER - Unique number to identify 1km grid AREA_FRACTION True NUMBER  % Percentage of crop covering the 1 km grid cell

CROP_SEASONS

• Description: Crop seasons storing unique season id per 1 km grid per crop
• Use: To link multiple seasons and multiple crops to each grid cell of the grid_1km table
• Type: Table
• Remarks:

Structure
Attribute Constraint Type Unit Comment IDCROP_SEASON False NUMBER - Unique number to identify each record IDCROP False NUMBER - Unique number to identify crop IDGRID_1KM False NUMBER - Unique number to identify 1km grid IDSEASON False NUMBER - Unique number to identify the specific season SEASON_NO False NUMBER - Season number

CROP_SEASON_AREAS

• Description: support table to holds several mappings between 1 and 25 km grids and SMU and unique seasons for different crops and seasons
• Use: This result can actually be obtained by different join queries on input tables but because of performance reasons (joining results of unique calculation units to the full 1 km grid) the result of these joins is stored in this support table used to fill table UNITS_CALC_INPUT
• Type: Table
• Remarks:

Structure

Attribute Constraint Type Unit Comment IDGRID_1KM False NUMBER - Unique number to identify 1km grid LATITUDE False NUMBER decimal degrees LONGITUDE False NUMBER decimal degrees AREA_M2 False NUMBER m2 area of the 1 km grid cell IDGRID_25KM False NUMBER - Unique id of the 25km grid to link weather and crop coefficients IDSMU True NUMBER - Unique id of the soil map unit IDSEASON False NUMBER - Unique number to identify the specific season SEASON_NO False NUMBER - Season number IDCROP False NUMBER - Unique number to identify the crop

GRID-datasets

content
2.1 GRID_1KM
2.2 GRID_25KM
2.3 GRID_25KMJOBS

GRID_1KM

• Description: The full grid
• Use: Specifies the visualization grid and maps each grid cell to a soil, weather, season and set of crop coefficients.
• Type: Table
• Remarks: The resolution is equal to spot images which is around 1kmx1km. The table only contains land grid cells. At the moment the table also only contains grid cells for which a seasons could be derived (based on ndvi time series).

Structure
Attribute Constraint Type Unit Comment IDGRID_1KM False NUMBER - Unique number to identify 1km grid LATITUDE False NUMBER decimal degrees LONGITUDE False NUMBER decimal degrees AREA_M2 False NUMBER m2 area of the 1 km grid cell IDGRID_25KM True NUMBER - Unique id of the 25km grid to link weather and crop coefficients IDSMU True NUMBER - Unique id of the soil map unit (SMU)

GRID_25KM

• Description: Weather grid
• Use: Specifies the weather grid.
• Type: View
• Remarks:

Structure
Attribute Constraint Type Unit Comment IDGRID_25KM False NUMBER - Unique id of the 25km grid to link weather and crop coefficients LATITUDE False NUMBER decimal degrees LONGITUDE False NUMBER decimal degrees HAS_LAND False VARCHAR2 Y/N Flag to identify if the grid cell is on land definition: select IDGRID as idgrid_25km , LATITUDE , LONGITUDE , HAS_LAND from grid_his@cgms14glo_glodas

GRID_25KMJOBS

• Description: 25 km grid list
• Use: 25 km grid list to support parallel processing, 25 km grid cells are mapped to jobs
• Type: Table
• Remarks:

Structure
Attribute Constraint Type Unit Comment IDGRID_25KMJOB False NUMBER - Unique number to identify each record IDGRID_25KM False NUMBER - Unique id of the 25km grid to link weather and crop coefficients IDJOB False NUMBER - Unique number to identify the job INJOB False VARCHAR2 Y/N Job that are activated ('Y') or not activated ('N')

SOILS-datasets

content
3.1 SOILS_SMU
3.2 SOILS_STU
3.3 HW30S_FULL
3.4 HW30S_MAPUNIT

SOILS_SMU

• Description: unique list of soil mapping units (soil associations)
• Use: unique list of soil mapping units (soil associations)
• Type: Table
• Remarks:

Structure
Attribute Constraint Type Unit Comment IDSMU True NUMBER - Unique id of the soil map unit (SMU)

SOILS_STU

• Description: mapping between soil mapping unit (SMU) and soil type units (STU) and soil physical characteristics per soil type unit, averaged to a single layer.
• Use: provides soil physical characteristics per soil type unit, averaged to a single layer
• Type: Table
• Remarks:

Structure

Attribute Constraint Type Unit Comment IDSOILS_STU False NUMBER - Unique number to identify each record IDSMU False NUMBER - Unique id of the soil map unit (SMU) IDSTU False NUMBER - Unique id of the soil type unit (STU) PERCENTAGE False NUMBER  % proportion of soil type in soil map unit SOIL_ROOTING_DEPTH False NUMBER cm bottom of deepest soil layer AVAILABLE_WATER_CAPACITY False NUMBER cm per m-1 (%) available water capacity, corrected for coarse fragments > 2mm data: select idsoils_stu_seq.nextval idsoils_stu , to_number(substr(newsuid,4)) idsmu , scid idstu , prop percentage , max(botdep) soil_rooting_depth , sum(tawcc*(botdep-topdep))/sum(botdep-topdep) available_water_capacity from (select newsuid newsuid -- soil map unit id , scid scid -- soil type id , prop prop -- proportion of soil type in map unit , topdep topdep -- top depth of layer , botdep botdep -- bottom depth of layer , (1-(cfrag/100))*tawc tawcc -- tawc corrected for coarse fragments > 2mm from hw30s_full where botdep <= 100 -- skip layers deeper than 1 meter and tawc >= 0 -- skip records with -1, -2, -3, -4, -5, -7, -9 and to_number(substr(newsuid,4)) not in (0,6997,6998) -- skip records with suid 0, 6997, 6998 ) group by to_number(substr(newsuid,4)), scid, prop order by to_number(substr(newsuid,4)), scid, prop

HW30S_FULL

• Description: List of layer specific derived soil types given for all map units (soil associations) (full unmodified WISE30SEC_V_1_0 data)
• Use: as background data (sBatjes, N.H., 2015. World soil property estimates for broad-scale modelling (WISE30sec, ver. 1.0). Report 2015/01, ISRIC—World Soil Information, Wageningen (available via: http://www.isric.org/data/datadownload)
• Type: Table
• Remarks:

Structure

Attribute Constraint Type Unit Comment NEWSUID False VARCHAR2 - Unique map unit code SCID False NUMBER - Number of soil unit within the given map unit PROP True NUMBER - Relative proportion of above in given map unit CLAF True VARCHAR2 - FAO90 legend code PRID True VARCHAR2 - Profile ID DRAIN True VARCHAR2 - FAO soil class DRAINNUM True NUMBER - Numerical FAO soil class LAYER False VARCHAR2 - Code for depth layer TOPDEP True NUMBER cm Upper depth of layer BOTDEP True NUMBER cm Lower depth of layer CFRAG True NUMBER vol % Mean coarse fragments (> 2mm) CFRAG_STD True NUMBER vol % Standard deviation coarse fragments (> 2mm) SDTO True NUMBER mass % Mean sand mass SDTO_STD True NUMBER mass % Standard deviation sand mass STPC True NUMBER mass % Mean silt mass STPC_STD True NUMBER mass % Standard deviation silt mass CLPC True NUMBER mass % Mean clay mass CLPC_STD True NUMBER mass % Standard deviation clay mass PSCL True VARCHAR2 - Texture class (SOTER conventions) BULK True NUMBER kg.dm-3 Mean bulk density BULK_STD True NUMBER kg.dm-3 Standard deviation bulk density TAWC True NUMBER cm.m-1 Mean available water capacity (-33 to -1500 kPa, USDA standard) TAWC_STD True NUMBER cm.m-1 Standard deviation available water capacity (-33 to -1500 kPa, USDA standard) ORGC True NUMBER g.kg-1 Mean organic carbon content ORGC_STD True NUMBER g.kg-1 Standard deviation organic carbon content TOTN True NUMBER g.kg-1 Mean total nitrogen TOTN_STD True NUMBER g.kg-1 Standard deviation total nitrogen CNRT True NUMBER - Mean C/N ratio CNRT_STD True NUMBER - Standard deviation C/N ratio CECS True NUMBER cmolc.kg-1 Mean cation exchange capacity of fine earth fraction CECS_STD True NUMBER cmolc.kg-1 Standard deviation cation exchange capacity of fine earth fraction CECC True NUMBER cmolc.kg-1 Mean CECclay, corrected for contribution of organic matter CECC_STD True NUMBER cmolc.kg-1 Standard deviation CECclay, corrected for contribution of organic matter ECEC True NUMBER cmolc.kg-1 Mean effective CEC ECEC_STD True NUMBER cmolc.kg-1 Standard deviation effective CEC TEB True NUMBER - Total exchangeable bases TEB_STD True NUMBER - Standard deviation total exchangeable bases BSAT True NUMBER  % Mean base saturation as percentage of CECs BSAT_STD True NUMBER  % Standard deviation base saturation as percentage of CECs ALSA True NUMBER  % Mean exchangeable aluminium (as proportion of ECEC) ALSA_STD True NUMBER  % Standard deviation exchangeable aluminium (as proportion of ECEC) ESP True NUMBER  % Mean exchangeable aluminium of CEC ESP_STD True NUMBER  % Standard deviation exchangeable aluminium of CEC PHAQ True NUMBER - Mean pH measured in water PHAQ_STD True NUMBER - Standard deviation pH measured in water TCEQ True NUMBER g C kg-1 Mean total carbonate equivalent TCEQ_STD True NUMBER g C kg-1 Standard deviation total carbonate equivalent GYPS True NUMBER g.kg-1 Mean gypsum content GYPS_STD True NUMBER g.kg-1 Standard deviation gypsum content ELCO True NUMBER dS.m-1 Mean electrical conductivity ELCO_STD True NUMBER dS.m-1 Standard deviation electrical conductivity

HW30S_MAPUNIT

• Description: full composition of each map unit in terms of its main soil units (FAO 1988), their relative extent, and the identifier for the corresponding synthetic soil profile, and climate class. The contents of this table can be joined to the spatial data using the NEWSUID field.
• Use: To derive a full list of unique soil map units to fill SOILS_SMU
• Type: Table
• Remarks: -

Structure

Attribute Constraint Type Unit Comment NEWSUID False VARCHAR2 Globally unique code, e.g. ‘WD1000234’ NOSOILCOMP True NUMBER Maximum number of soil components in map unit (up to 10) DOMFAO_NAME True VARCHAR2 Dominant FAO90 major group in map unit (Note: This need not always be SOIL1) DOMFAO_PROP True NUMBER Proportion of dominant FAO major soil/climate class within in soil unit PROPSYNTHPROF True NUMBER Proportion of map unit represented by synthetic profiles (always 100% for WISE30sec) SOILMAPUNIT* True VARCHAR2 Aggregated code for map unit summarizing the overall composition; final letter stands for the climate class SOIL1 True VARCHAR2 - Characterization of the first (main) soil unit according to the FAO90 Legend PROP1 True NUMBER  % Proportion, as a percentage, that the main soil unit occupies within the given map unit PRID1 True VARCHAR2 - Unique code for the corresponding synthetic soil profile (e.g GLe/A, for a gleyic Acrisol mapped in the Tropics) SOIL2 True VARCHAR2 - As above but for the next soil unit PROP2 True NUMBER  % As above PRID2 True VARCHAR2 - As above SOIL3 True VARCHAR2 - As above but for the next soil unit PROP3 True NUMBER  % As above PRID3 True VARCHAR2 - As above SOIL4 True VARCHAR2 - As above but for the next soil unit PROP4 True NUMBER  % As above PRID4 True VARCHAR2 - As above SOIL5 True VARCHAR2 - As above but for the next soil unit PROP5 True NUMBER  % As above PRID5 True VARCHAR2 - As above SOIL6 True VARCHAR2 - As above but for the next soil unit PROP6 True NUMBER  % As above PRID6 True VARCHAR2 - As above SOIL7 True VARCHAR2 - As above but for the next soil unit PROP7 True NUMBER  % As above PRID7 True VARCHAR2 - As above SOIL8 True VARCHAR2 - As above but for the next soil unit PROP8 True NUMBER  % As above PRID8 True VARCHAR2 - As above SOIL9 True VARCHAR2 - As above but for the next soil unit PROP9 True NUMBER  % As above PRID9 True VARCHAR2 - As above SOIL10 True VARCHAR2 - As above but for the next soil unit PROP10 True NUMBER  % As above PRID10 True VARCHAR2 - As above WATE True NUMBER  % Proportion of units permanently covered by water (left blank) * As indicated, each ‘soil-climate’ map unit may comprise up to ten component soils. For ease of legibility (or reference purposes), the relative extent of the component units of each map unit has been coded to arrive at a compact map unit code: 1 – from 80 to 100 %; 2 – from 60 to 80 %; 3 – from 40 to 60 %; 4 – from 20 to 40 %, and 5 – less than 20 %.

other datasets

content
5.1 AREA
5.2 JOBS
5.3 SEASONS
5.4 SYSPARAMETERS
5.5 SYSTABLES
5.6 UNITS_CALC_INPUT
5.7 WEATHER_HRES_GRID_10

AREA

• Description: support table to hold areas of each location across the latitude range for one longitude
• Use: to fill areas in table GRID_1KM
• Type: Table
• Remarks:

Structure
Attribute Constraint Type Unit Comment IDAREA False NUMBER - Unique number to identify each record AREA False NUMBER m2 Area per 1 km grid cell X False NUMBER decimal degrees Longitude Y False NUMBER decimal degrees Latitude X_NEW True NUMBER decimal degrees Longitude (rounded) Y_NEW True NUMBER decimal degrees Latitude (rounded)

JOBS

• Description: List of jobs, all connected to a set of grid cells, where for each job the status of processing is registered
• Use: Allow parallel calculation of GWSI by starting more than one GWSI application
• Type: Table
• Remarks:

Structure
Attribute Constraint Type Unit Comment IDPROCESS False NUMBER - Unique number of each job STATUS False NUMBER - Status of each job (values 0 = to be calculated, 1 = calculating and 2 = finished)

SEASONS

• Description: The unique combinations of phenology metric per season
• Use: Input for the calculations
• Type: Table
• Remarks:

Structure
Attribute Constraint Type Unit Comment IDSEASON False NUMBER - Unique number to identify the specific season SEASON_NO False NUMBER - Season number START_OF_SEASON False NUMBER dekad Start of season MAX_OF_SEASON False NUMBER dekad Moment where maximum NDVI is reached START_OF_SENESCENCE False NUMBER dekad Moment where senescence starts END_OF_SEASON False NUMBER dekad End of season

SYSPARAMETERS

• Description: Includes configuration parameters
• Use: To trigger and manage the WSI calculations
• Type: Table
• Remarks:

Structure
Attribute Constraint Type Unit Comment MNAME False VARCHAR2 - Name of configuration parameter MVALUE False VARCHAR2 - Value of configuration parameter

SYSTABLES

• Description: Normally used for the configuration of internal IDs
• Use: In this project only rarely used. In this project the configuration of internal IDs is organized in ORACLE sequences making the content not relevant. However the table must exist because certain components in the daemon by default check the existence
• Type: Table
• Remarks:

Structure
Attribute Constraint Type Unit Comment TABLE_NAME True VARCHAR2 - Table name HIGHEST_ID True NUMBER - Highest ID used in unique record identifier of table

UNITS_CALC_INPUT

• Description: list of unique calculation (simulation) units by defining unique combinations of soil mapping units and seasons per 25 km grid cell for the two generic crops
• Use: List of units for which the WSI must be calculated
• Type: Table
• Remarks:

Structure
Attribute Constraint Type Unit Comment IDUNIT_CALC_INPUT False NUMBER - Unique number to identify each record IDGRID_25KM False NUMBER - Unique id of the 25km grid to link weather and crop coefficients IDSMU False NUMBER - Unique id of the soil map unit (SMU) IDSEASON False NUMBER - Unique number to identify the specific season IDCROP False NUMBER - Unique number to identify the crop

WEATHER_HRES_GRID_10

• Description: Dekadal weather data (1989-present)
• Use: input for WSI calculations
• Type: View
• Remarks:

Structure

Attribute Constraint Type Unit Comment WEATHER_HRES_GRID_10_ID False NUMBER - Unique number to identify each record IDGRID_25KM False NUMBER - Unique id of the 25km grid to link weather and crop coefficients YEAR False NUMBER - Calendar year DEKAD_IN_YEAR False NUMBER - Dekad in the year TEMPERATURE_AVG False NUMBER degrees Celsius Average daily temperature TEMPERATURE_MAX False NUMBER degrees Celsius Maximum daily temperature TEMPERATURE_MIN False NUMBER degrees Celsius Minimum daily temperature PRECIPITATION False NUMBER mm Dekadal precipitation sum ET0 False NUMBER mm Dekadal evapotranspiration sum definition: select w.weather_hres_grid_10_id , w.idgrid IDGRID_25KM , w.year , w.dekad_in_year , w.temperature_avg , w.temperature_max , w.temperature_min , w.precipitation , w.et0 from weather_hres_grid_10@cgms14glo_glodas w