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


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


SOILSdatasets
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 m1 (%) 
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*(botdeptopdep))/sum(botdeptopdep) 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 broadscale 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.dm3 
Mean bulk density

BULK_STD 
True 
NUMBER 
kg.dm3 
Standard deviation bulk density

TAWC 
True 
NUMBER 
cm.m1 
Mean available water capacity (33 to 1500 kPa, USDA standard)

TAWC_STD 
True 
NUMBER 
cm.m1 
Standard deviation available water capacity (33 to 1500 kPa, USDA standard)

ORGC 
True 
NUMBER 
g.kg1 
Mean organic carbon content

ORGC_STD 
True 
NUMBER 
g.kg1 
Standard deviation organic carbon content

TOTN 
True 
NUMBER 
g.kg1 
Mean total nitrogen

TOTN_STD 
True 
NUMBER 
g.kg1 
Standard deviation total nitrogen

CNRT 
True 
NUMBER 
 
Mean C/N ratio

CNRT_STD 
True 
NUMBER 
 
Standard deviation C/N ratio

CECS 
True 
NUMBER 
cmolc.kg1 
Mean cation exchange capacity of fine earth fraction

CECS_STD 
True 
NUMBER 
cmolc.kg1 
Standard deviation cation exchange capacity of fine earth fraction

CECC 
True 
NUMBER 
cmolc.kg1 
Mean CECclay, corrected for contribution of organic matter

CECC_STD 
True 
NUMBER 
cmolc.kg1 
Standard deviation CECclay, corrected for contribution of organic matter

ECEC 
True 
NUMBER 
cmolc.kg1 
Mean effective CEC

ECEC_STD 
True 
NUMBER 
cmolc.kg1 
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 kg1 
Mean total carbonate equivalent

TCEQ_STD 
True 
NUMBER 
g C kg1 
Standard deviation total carbonate equivalent

GYPS 
True 
NUMBER 
g.kg1 
Mean gypsum content

GYPS_STD 
True 
NUMBER 
g.kg1 
Standard deviation gypsum content

ELCO 
True 
NUMBER 
dS.m1 
Mean electrical conductivity

ELCO_STD 
True 
NUMBER 
dS.m1 
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 ‘soilclimate’ 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
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 (1989present)
 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
