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:

  • CALCULATED_WEATHER
  • CROP_YIELD_X
  • GRID_WEATHER
  • REFERENCE_WEATHER
  • STATIONS_PER_GRID

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 2018 Supit.net