Appendix V
The CGMS Database
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
| Description: | calculated/estimated potential evaporation and global radiation per station. |
| Use: | output table for additional weather calculation, input for grid weather |
| Remarks: | none |
CROP
| 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
| 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
| 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
| 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
| 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
| 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
| 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
| 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
| 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
| 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
| 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
| 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
| Description: | contains system parameters |
| Use: | upon system initialisation |
| Remarks: | user defined |
VARIETY_PARAMETER
| 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
hosted by Sudus Internet | powered by cmsGear
copyright 2025 Supit.net