Appendix V

The CGMS database

The database is the core of the CGMS system and contains all input, output and system definition data.

On this page

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_VALUE and VARIETY_PARAMETER_VALUE tables
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_UNIT table 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_UNIT results
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_UNIT table
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_STATION table. If a new SUPIT_REFERENCE_STATIONS table 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_STATION table)
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_WEATHER and SUPIT_CONSTANTS may 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