The CGMS database
The database is the core of the CGMS system and contains all input, output and system definition data.
Overview#
The database is the core of the CGMS system and contains all input, output and system definition data. Although it is a ‘normal’ database in the sense that all tables and all data can be manipulated with standard database tools, some of the operational procedures rely on exclusive use of the tables. Most tables form a permanent part of the database, but some tables are created and deleted (dropped) by the executable program. Because of the naming scheme used for these temporary tables, it is not possible to run more than one CGMS program at the same time on the same database. Another a-typical restriction is that the table definition for some of the tables is fixed. It is not normally important in which order the columns in a table are defined, as long as the column names correspond to the expected names. However, because of implementation details, the order in which the column names are defined should not be changed for the following tables:
Table by table#
Permanent tables#
CALCULATED_WEATHER
fixed order- Description
- calculated/estimated potential evaporation and global radiation per station.
- Use
- output table for additional weather calculation, input for grid weather
- Remarks
- none
CROP
user defined- Description
- crop name, number and associated
CROP_GROUP. - Use
- reference table, establishes association between crop and
CROP_GROUP, used during system initialisation and the crop growth simulation - Remarks
- user defined
CROP_CALENDAR
user defined driving- Description
- crop, crop variety and growing season per grid
- Use
- determines which crops are simulated for a grid, and simulation period
- Remarks
- user defined, one of the ‘driving’ tables of the system
CROP_PARAMETER_VALUE
- Description
- crop characteristics as understood by WOFOST
- Use
- input table for the crop simulation process
- Remarks
- none
CROP_YIELD_X
fixed order- Description
- results of the crop simulation per EMU (=level 2 output)
- Use
- output table for the crop simulation process
- Remarks
- do not change the layout of this table
ELEMENTARY_MAPPING_UNIT
user defined- Description
- intersection of the soil map, administrative units and the grid
- Use
- input/reference table for the crop simulation process
- Remarks
- user defined, derived from GIS, provides the link to the GIS
GRID
user defined- Description
- grid system, expressing position and reference altitude for simulation. Weather is considered to be homogeneous for the grid. The grid table drives crop simulation, and the simulation strategy is linked to the grid system
- Use
- input/reference table for both level 1 and level 2
- Remarks
- user defined, derived from GIS, provides the link to the GIS
GRID_WEATHER
fixed order- Description
- the interpolated weather data per grid per day
- Use
- output for level 1, input for level 2
- Remarks
- do not change the layout of this table
METDATA
user defined- Description
- contains the station based daily meteorological data
- Use
- input for level 1
- Remarks
- user defined
PARAMETER_DESCRIPTION
- Description
- describes the use and the type of parameter in
CROP_PARAMETER_VALUEandVARIETY_PARAMETER_VALUEtables - Use
- used during system initialisation to correctly interpret the parameter tables
- Remarks
- do not change this table
REFERENCE_WEATHER
fixed order- Description
- contains the long term average weather data per station per day
- Use
- substitute data in case of missing data during level 1 processing
- Remarks
- filled by utility program ReferenceWeather. Do not change the layout of this table
ROOTING_DEPTH
- Description
- the rooting depth classes and associated rooting depth limits
- Use
- reference table used in crop simulation
- Remarks
- part of the soil model, can be changed or extended if necessary
SIMULATION_UNIT
- Description
- contains the unique combinations of RD and SPG for each grid and each crop to be simulated
- Use
- lookup table used during crop simulation
- Remarks
- the content of this table is based on the evaluation of the soil information with respect to the different crops, and partly derived from the SUITABILITY table
SITE
- Description
- contains some soil model parameters on a system wide basis
- Use
- input table for the crop simulation process
- Remarks
- supplied by the user, but rarely changed
SMU_SUITABILITY
- Description
- a derived table containing the sum of the suitable area for a SMU for a particular
CROP_GROUP - Use
- used during the aggregation from the simulation unit results to the EMU results, and later (outside the CGMS executable) for the aggregation to NUTS results
- Remarks
- this table needs to be recalculated whenever the
SIMULATION_UNITtable changes
SOIL_ASSOCIATION_COMPOSITION
- Description
- described the percentage area for each STU in each SMU. It is assumed that it therefore also describes the percentage area for each STU in each EMU
- Use
- used to calculate the weighing factor to be applied to the
SIMULATION_UNITresults - Remarks
- derived from the soil map
SOIL_PHYSICAL_GROUP
user defined- Description
- contains the physical parameters that describe the soil groups used by CGMS
- Use
- used by the crop growth model
- Remarks
- user defined, derived from the soil map
SOIL_TYPOLOGIC_UNIT
user defined- Description
- the rooting depth (RD) and soil physical group (SPG) combination associated with every STU
- Use
- used by the crop growth model
- Remarks
- user defined, derived from the soil map
STATIONS_PER_GRID
fixed order- Description
- stations used for grid weather interpolation, by year. Note that only the rain station is stored that is actually used, not the possible seven that were considered
- Use
- output table for the level 1 grid weather calculation
- Remarks
- will only contain the last information produced. Two runs for two different periods in the same year will cause the table to be overwritten for that year. Do not change the layout of this table
SUITABILITY
user defined- Description
- the suitable STUs for each
CROP_GROUP - Use
- used to derive the
SIMULATION_UNITtable - Remarks
- user defined, derived from the soil map
SUPIT_CONSTANTS
- Description
- the regression constants per weather station used for the estimation of global radiation for the station
- Use
- used by the additional weather process
- Remarks
- recalculate this table when new stations are added to the
WEATHER_STATIONtable. If a newSUPIT_REFERENCE_STATIONStable is supplied, then this table should also be recalculated. Note however that in that case the historical data needs to be reprocessed.
SUPIT_REFERENCE_STATIONS
- Description
- contains calculated regression constants, derived from measured radiation data, for a number of weather stations covering a large part of Europe
- Use
- used by SupitConstants to derive the regression constants for the weather stations used (and listed in the
WEATHER_STATIONtable) - Remarks
- not normally changed by the user but supplied by the JRC
SYSCON
user defined- Description
- contains system parameters
- Use
- upon system initialisation
- Remarks
- user defined
VARIETY_PARAMETER
user defined- Description
- see
CROP_PARAMETER_VALUE - Use
- used by the crop growth model
- Remarks
- user defined, to express the deviation from the main crop parameters for a variety
WEATHER_DATA_AVAILABILITY
- Description
- the type of data that can be obtained from a station, per station per year
- Use
- output for the weather data availability calculation, used by the grid weather interpolation
- Remarks
- none
WEATHER_STATION
- Description
- weather stations and weather station data to be used by the system
- Use
- used by all of the level 1 steps (additional weather, availability, grid weather)
- Remarks
- when changing this table,
REFERENCE_WEATHERandSUPIT_CONSTANTSmay have to be updated
Temporary tables#
| CW: | temporary storage for the additional weather calculation |
| CY: | temporary storage for the crop yield calculation |
| GW: | temporary storage for the grid weather calculation |
The system creates these tables for the current run, and writes the output to these tables. The tables are created without an index, and are therefore fast when adding data. At the end of the run, the content of the temporary tables is inserted in the main tables (which are indexed). This is done as a single database transaction, and in a single operation. This allows the database to optimise the recreation of the index.
Tables per process#
This section describes the tables that are used by each processing step.
System Initialisation
CROP
SYSCON
Additional weather calculation
CALCULATED_WEATHER
CW
METDATA
SUPIT_CONSTANTS
WEATHER_STATION
Weather data availability calculation
CALCULATED_WEATHER
METDATA
REFERENCE_WEATHER
WEATHER_DATA_AVAILABILITY
WEATHER_STATION
Grid weather interpolation
CALCULATED_WEATHER
GRID
GRID_WEATHER
GW
METDATA
REFERENCE_WEATHER
STATION_PER_GRID
SYSCON
WEATHER_DATA_AVAILABILITY
WEATHER_STATION
Crop growth simulation
CROP
CROP_CALENDAR
CROP_PARAMETER_VALUE
CROP_YIELD
CY
ELEMENTARY_MAPPING_UNIT
GRID
GRID_WEATHER
PARAMETER_DESCRIPTION
ROOTING_DEPTH
SIMULATION_UNIT
SITE
SMU_SUITABILITY
SOIL_ASSOCIATION_COMPOSITION
SOIL_PHYSICAL_GROUP
SOIL_TYPOLOGIC_UNIT
SYSCON
VARIETY_PARAMETER_VALUE
Reference Weather calculation
CALCULATED_WEATHER
METDATA
REFERENCE_WEATHER
WEATHER_STATION
Supit Constants calculation
SUPIT_CONSTANTS
SUPIT_REFERENCE_STATIONS
WEATHET_STATION