Published online August 10, 2007

Integration of a Cropping Systems Simulation Model and a Relational Database for Simple Farm-Scale Analyses Luca Bechini* and Claudio O. Sto¨ckle Wo¨sten et al., 1999; Ali et al., 2004), crop quality data (Haley et al., 1999), and pest and weed management information (Stigliani et al., 1996; Xia et al., 2003). Databases can also store model inputs and outputs, providing a useful support to organize the wide range of information needed to run simulation models and for the interpretation of simulation results. However, the integration of simulation models and relational databases has been explored only in limited cases (e.g., Caldeira and Pinto, 1998). The integration of databases and models provides the platform for conducting complex and comprehensive analyses of model inputs and outputs (e.g., scenario comparison, geographic information system applications, farming systems simulations, etc.), which cannot be easily done otherwise (e.g., using spreadsheets or ASCII files). In particular, relational databases can help organize and manage input data, relate input and output data, compare different scenarios for a cropping or farming system, locate output results in their environmental context (farm, field, landscape), integrate outputs with non-model-derived data (e.g., prices, cropped areas), and keep a history of the simulations. As an example, the integration of process simulation and data modeling may help create farm scale scenarios by managing different simulations for fields which differ in rotations applied and soil types (Fig. 1). The integrated system allows to sequentially run simulations for each field of a farm, and to aggregate the results by calculating farm-scale statistics (e.g., average crop yields, total biomass produced on-farm, irrigation water required by the farm, etc.). We integrated a dynamic simulation model of cropping systems (CropSystVB) with a relational database (Microsoft Access), resulting in a tool that can be used for a variety of applications. The objective of this article is to introduce the structure and functions of this integrated tool and to demonstrate its application. As an example for this communication, we integrated farmscale data to demonstrate how a cropping systems model can be applied to study nutrient flows. The open structure of this tool allows further development by the scientific community concerning process simulation, data modeling, and their integration.

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved.

ABSTRACT Agricultural simulation models are important tools in research, extension, policy formulation, and teaching. Their integration with relational databases allows carrying out complex and comprehensive data analyses of model inputs and outputs. In this article, we describe the integration of a dynamic cropping systems simulation model (CropSystVB) and a relational database (Microsoft Access), with the aim of developing a tool for simple applications at the farm scale. The data model, designed with the entity–relationship framework, represents the structure of the data and allows for organized and retrievable storage of inputs and outputs of the simulation model. In addition to standard CropSystVB’s inputs (soil, crop, weather, management events), the integrated tool also requires input data to describe the nutrients produced by livestock and allows for spatially variable fields, soils, and management options. Farm simulation scenarios can be defined, including the actual and alternative configurations of animal breeds, rotations, and crop management. One or more rotations can be simulated for each scenario; each rotation is located on a homogeneous area (a field, or part of it, or a group of fields); each area is described with relevant soil and weather inputs. The Microsoft Access implementation of the integrated tool (www.bsyse. wsu.edu/cropsyst; verified 15 June 2007) includes a Visual Basic for Applications version of the model and tables and forms to store and manage data. An example simulation in an animal farm with six scenarios, obtained by the combination of three animal loads and two irrigation systems (surface and sprinkler), is presented to illustrate its use.

A

have been developed and applied widely, and their importance today is well recognized (Donatelli et al., 2002; Sinclair and Seligman, 1996). In research, models help to organize knowledge and test scientific hypotheses; in extension and policy formulation, they help to explore alternative scenarios for the management of agricultural systems; in teaching, they are excellent tools for studying systems behavior and giving students the possibility of exploring system responses to variations of input parameters and variables. Relational databases are useful tools to organize and integrate data about specific aspects of reality (GarciaMolina et al., 2002). Examples of their application in agriculture include the storage and management of experimental data (e.g., van Evert et al., 1999a, 1999b; Scott and Lord, 2003; Zuliani et al., 2003), soil data (e.g., GRICULTURAL SIMULATION MODELS

MATERIALS AND METHODS

L. Bechini, Dep. of Crop Science, Univ. of Milano, Via Celoria 2, 20133 Milano, Italy; and C.O. Sto¨ckle, Dep. of Biological Systems Engineering, Washington State Univ., Pullman, WA. The research was funded by the Univ. of Milano with the “Visiting Scientist Program” and the “Scientific and Technological Research Program.” Received 31 Oct. 2006. *Corresponding author ([email protected]).

The Cropping Systems Simulation Model For the purpose of this implementation, a simplified version of the standard CropSyst model (Sto¨ckle et al., 1994, 2003) was used. The model, called CropSystVB (ver. 1.0), was

Published in Agron. J. 99:1226–1237 (2007). Integrated Agricultural Systems doi:10.2134/agronj2006.0305 ª American Society of Agronomy 677 S. Segoe Rd., Madison, WI 53711 USA

Abbreviations: CropSystVB, the Visual Basic for Applications version of the cropping systems simulation model CropSyst; GIS, Geographic Information System; RDBMS, Relational Database Management System; VBA, Visual Basic for Applications.

1226

1227

¨ CKLE: INTEGRATION OF SIMULATION MODEL & RELATIONAL DATABASE BECHINI & STO

IN

Field #1, Cornwheat, Loam soil

MODEL

IN

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved.

IN

OUT MODEL OUT

MODEL

Field #2, Barley, Sandy soil

OUT IN Field #3, Alfalfa, Clay soil

MODEL

Calculation of farm-scale statistics

OUT

Field #4, Corn, Sandy-loam soil

Fig. 1. Example of application of the integration of cropping systems simulation and data model for a farm, considering different fields, soil types, and rotations.

written using Visual Basic for Applications (VBA) and utilizing Microsoft Excel as user interface. CropSystVB is a multi-year, multi-crop, management-oriented process model designed to assess the productivity and environmental impact of cropping systems including either monocultures or complex crop rotations. Management options include irrigation, N fertilization (organic and inorganic sources), residue management, and tillage. CropSystVB can evaluate long-term cropping system scenarios spanning many decades (i.e., 50 or 100 yr) of weather, and many different soils and management conditions. CropSystVB provides open access to the source code, which is embedded in the Excel file and available to users by simply launching the VBA editor distributed with Excel. This version is mostly utilized for development and testing of new ideas for process modeling, and as a teaching tool for graduate students and advanced CropSyst users.

The Entity–Relationship Framework A data model is a representation of the structure of the data, that is, what information must be stored and its components’ relationships (Garcia-Molina et al., 2002). We have used the entity–relationship framework to design the data model (Garcia-Molina et al., 2002). In this framework, entity sets represent collections of homogeneous objects (or entities), where each set can be described with one or more attributes (properties). A relationship, connecting two or more entity sets, represents the association between different types of objects. For example, the entity sets “farmer” and “fields” are connected by a relationship “cultivate,” because a farmer cultivates one or more fields. If each member of the entity set A can be connected to only one member of the entity set B, and each member of B can be connected to many members of A, then we talk about a “many-to-one” relationship. An example of a “many-to-one” relationship is the one between the fields and the farmer cultivating them: each field can be cultivated by one farmer only, while a farmer may cultivate many fields. In a “many-to-many” relationship, an entity of either set can be connected to many entities of the other set. For example, the relationship between the farmers and the crops can be of “many-to-many” type (a farmer may cultivate one or more crops, while the same crop can be cultivated by more than one farmer). A key is a set of one or more attributes that uniquely identifies an entity within an entity set. For example, a maize (Zea mays L.) hybrid can be uniquely identified with its name. Keys are normally attributes of entity sets to which they

belong. Weak entity sets are those whose key is composed of attributes which totally or in part belong to another entity set. For example, a soil layer can be uniquely identified by the soil name and by the number describing its vertical position in the profile; the number alone would not be enough because two different soil layers of two different profiles may share the same vertical position. The data model for this simple example is represented in Fig. 2, which is used also to introduce the symbols proposed by Garcia-Molina et al. (2002) for entity– relationships data models. Two entities, “soil profiles” and “soil layers,” are represented with rectangles; a relationship (“soils have layers”) is represented with a line and a diamond. The relationship states that a soil may have one or more layers; the label “1..N” indicates the minimum (1) and maximum (N) multiplicity; also, a single layer belongs to one soil profile only (“1..1”: at least one and no more than one soil). The attributes of an entity set are represented with circles; the primary key is underlined (e.g., “id_soil”). The primary key of the weak entity set “soil layers” is composed of id_layer and id_soil, so that different soil profiles may share the same layer identifier (1, 2, etc.). Weak entity sets (whose keys include attributes of other entity sets) and their supporting relationships are marked with double borders.

The Data Model The objective of our data model is to store input data needed to run CropSystVB, including: (i) the nutrients produced by animals in addition to the standard soil, crop, and weather inputs; (ii) the spatial variability of fields, soils and related management, allowing different fields and soils to be described and used in the simulations, and (iii) the relationships among these elements. The entity–relationship diagram of our data model is presented in Fig. 3. A list of the most important attributes of each entity set is provided in Table 1. The central entity set in this data model is the farm, the level at which most management decisions are made. Each farm can be represented with at least one farm cultivation scenario. In the simplest case, there will be only one scenario, describing current farm practices. Alternative management scenarios can be added, including different rotations, crops, and crop managements. Alternative scenarios may include the reduction or optimization of fertilization, a different scheduling of irrigation water application, the adoption of minimum- or no-tillage practices or precision farming management. Central to the cropping system model used is the concept of rotation. Each simulation includes a rotation, defined as a sequence of crops in time, each crop being cultivated with a specific set of management operations. For the purpose of cropping systems modeling, a rotation can be a sequence of different crops, but can also be a sequence of the same crop (monosuccession or continuous cropping). The spatial unit on which a rotation is performed is the homogeneous area. The area is homogeneous in the horizontal space; this

soil profiles id_soil

1..1

have

1..N

soil layers

id_layer name

sand

clay

slope

Fig. 2. Example of symbols (proposed by Garcia-Molina et al., 2002) and concepts used for entity–relationship data models. For details see the text. This data model represents the fact that a soil profile may have one or more soil layers, while a single soil layer belongs to one soil profile only. The primary key of the soil profile (id_soil) is used to form the primary key of the weak entity set “soil layers.”

1..1

managed with

1..N

1..N

has

0..N

0..N

has

1..1

1..N

0..N

0..N

manures are described by

1..1

1..N

1..1

are related to

soils

1..1

1..N

1..1

1..1

lie on

fields

1..N

1..1

1..N

animals produce manures

simulations

1..N

are simulated

are made of

1..N

1..1

1..1

1..1

1..1

0..N

locations

1..N

daily outputs

annual outputs

1..N

soil layers initial conditions

1..N

have

have

have

have

soil layers

are located in

fields are composed of homog. areas (fields are grouped into homog. areas)

1..N

homogeneous areas

manures and org. fert.

fertilization events

irrigation events

1..N

1..1

have

are cropped with

growing season outputs

1..1

rotations

tillage events

has

0..N

0..N

manure application events

1..1

has

1..1 1..1 1..1

have

1..1

crops in rotation

are 1..N cropped with

are a sequence of

1..1

are represented with

1..1

farms

1..1

Fig. 3. The entity–relationship diagram of the data model. See the text and Fig. 2 for details about the symbols and concepts. The attributes and the keys of each entity set are not shown for clarity in the figure, but are reported in Table 1.

crop residues

1..1

crop produce residues

1..1

0..N

are described by

1..1

1..N

have

0..N

animal breeds

farm cultivation scenarios

1..1

1..1

management set

types of animal groups

1..1

belong to

1..N

animal groups

set of crop parameters

0..N

1..N

are composed by

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved. 1228 AGRONOMY JOURNAL, VOL. 99, SEPTEMBER–OCTOBER 2007

1229

¨ CKLE: INTEGRATION OF SIMULATION MODEL & RELATIONAL DATABASE BECHINI & STO

Table 1. Attributes of the entity sets of the data model represented in Fig. 3. Primary keys are underlined. Name of the attribute

Description

Units

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved.

Animal breeds id_animal_breed name id_scenario breed_type

A number identifying the animal breed within a farm scenario The name of the animal breed Foreign key (identifies the farm scenario to which the breed belongs) Type of animal breed (dairy, swine, etc.) Animal groups

– – – –

id_animal id_animal_breed id_scenario animal_type number weight id_manure annual_manure_production

A number identifying the animals within the breed/scenario Foreign key (identifies the animal breed to which the animals belong) Foreign key (identifies the farm scenario to which the breed belongs) Foreign key (identifies the type of animals) The number of animals of this type in this breed/scenario The average live weight of the animals of this type Foreign key (the type of manure produced by this group of animals) Amount of manure produced annually by each kg live weight of this animal group Animal groups types

– – – – – 21 kg head – 21 21 kg yr kg

id_animal_type name default_weight default_manure_type default_annual_manure_production

A number to identify the animal group A description of the type of animal The default weight of one animal belonging to this group Foreign key (the default type of manure produced by this animal group type) Default amount of manure produced annually by each kg live weight of this animal group Annual outputs†

– – 21 kg head – 21 21 kg yr kg

year id simulation ...

The year to which the annual results are related The simulation to which the annual results are related (several output variables generated by the simulation model on a yearly time step) Crop parameters set†

– – –

name scientific_name ...

Identifier of the crop (a string) The scientific name of the crop (crop parameters required by the model to simulate crop growth and development) Crop residues†

– – –

name ...

Name of crop producing this residue (parameters required by the model to simulate crop residue decomposition) Crops in rotation

– –

sowing_date id_rotation crop_name id_management

The sowing date of the crop within this rotation The rotation to which the crop belongs Foreign key (identifies the name of the crop, and its model parameters) Foreign key (identifies the management set used to manage the crop) Daily outputs†

– – – –

date id_simulation ...

The date to which the daily simulation results are related The simulation to which the daily results are related (several output variables generated by the simulation model on a daily time step) Farms

– – –

id_farm name

Farm identifier (a number) The name of the farm/farmer Farm cultivation scenarios

– –

id_scenario id_farm name

The identifier of the scenario within the farm Foreign key (identifies the farm) A description of the scenario Fertilization events

– – –

id_fertilization id_management date FertilizationNO3_N FertilizationNH4_N FertilizationSource FertilizationForm FertilizationMethod

A number identifying the fertilization event Foreign key (a number identifying the management set) The date in which the fertilization event occurs The total amount of NO3–N applied with fertilizer The total amount of NH4–N applied with fertilizer Type of fertilizer used (source) Form of fertilizer used (liquid or solid) Method of application of the fertilizer Fields

id_field id_farm name area

The identifier of the field within the farm (a number) Foreign key (identifies the farm) The name of the field The size (area) of the field Fields_homogeneous areas

– – – ha

id_field id_farm id_area percentage_area

Foreign key (identifies the field within the farm) Foreign key (identifies the farm) Foreign key (identifies the area) The percentage area of the field which belongs to the homogeneous area

– – – %

Continued on next page.

– – – 21 kg N ha 21 kg N ha – – –

1230

AGRONOMY JOURNAL, VOL. 99, SEPTEMBER–OCTOBER 2007

Table 1. Continued. Name of the attribute

Description

Units

Growing season outputs† id_rotation sowing_date

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved.

id_simulation ... id_area name id_polygon location_name soil_name

Foreign key (The rotation to which the growing season outputs are related) Foreign key (The sowing date of the crop within this rotation to which the growing season outputs are related) Foreign key (the simulation to which the growing season outputs are related) (several output variables generated by the simulation model for the growing season) Homogeneous areas

– –

The identifier of the homogeneous area The name of the homogeneous area The identifier of the corresponding polygon in a GIS Foreign key (identifies the location, i.e. the source of weather data for this homogeneous area) Foreign key (identifies the soil, i.e. the source of pedological data for this homogeneous area) Irrigation events

– – – –

– –

–

id_irrigation id_management date Irrigation_Amount Irrigation_NO3_N Irrigation_NH4_N

A number identifying the irrigation event Foreign key (a number identifying the management set) The date in which the irrigation event occurs The amount of irrrigation water applied The amount of nitrate N applied with the irrigation The amount of ammonium N applied with the irrigation Locations

– – – mm 21 kg N ha 21 kg N ha

name latitude longitude altitude screening_height weather_file_path weather_file_prefix yearly_phase Annual_Average_Temperature Annual_Temperature_Amplitude

The identifier of the location (its name) Latitude Longitude Altitude Wind Screening Height Weather File Directory Path Weather File Prefix Phase of Sine Temperature Function Annual Average Temperature Half Annual Temperature Amplitude Management set

– degrees degrees m m – – d °C °C

id_management name Fraction_Of_Biomass_To_Standing_Residue Fraction_Of_Biomass_To_Surface_Residue

A number identifying the management A name describing the management set Standing Stubble Mass Fraction of Unharvested Biomass Surface Residue Mass Fraction of Unharvested Biomass Manure applications events

– – – –

id_manure id_management date ManureAmount ManureType ManureApplicationMethod

A number identifying the manure application event Foreign key (a number identifying the management set) The date in which the manure application event occurs Amount of applied manure mass Foreign key (the identifier of the manure type applied in this event) Manure application method Manures and organic fertilizers†

– – –

id_manure name ...

A number identifying the manure A description of the manure (parameters required by the model to simulate decomposition of manures and organic fertilizers) Rotations

–

id_rotation name id_scenario id_farm id_area

Identifier of the rotation (a number) A description of the rotation Foreign key (identifies the scenario within the farm) Foreign key (identifies the farm) Foreign key (identifies the area) Simulations†

– – – – –

id_simulation name id_rotation to_be_simulated

Identifies the simulation (a number) Simulation name Foreign key (identifies the rotation which is simulated) Indicates whether the user wants this simulation to be run (or not) the next time the simulation procedure will be started Starting date for the simulation Ending date for the simulation Indicates if daily crop outputs are requested or not Indicates if growing season output are requested or not Indicates if annual output are requested or not (attributes describing initial conditions of the soil) Soil layers†

starting_date ending_date Daily_Output Growing_Season_Output Annual_Output ... soil_name id_layer ... Continued on next page.

Foreign key (identifies the soil name to whom the layers belong) The identifier of the soil layer (a number) (soil parameters for each layer)

kg ha

21

– –

–

– – – true or false – – true or false true or false true or false – – – –

1231

¨ CKLE: INTEGRATION OF SIMULATION MODEL & RELATIONAL DATABASE BECHINI & STO

Table 1. Continued. Name of the attribute

Description

Units

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved.

Soil layers initial conditions soil_name id_layer id_simulation ...

Foreign key (identifies the soil) Foreign key (identifies the soil layer) Foreign key (identifies the simulation which will use these initial conditions) (initial values of state variables for each soil layer) Soils†

– – – –

name description ...

The identifier of the soil (its name) Soil description (attributes describing soil profile) Tillage events

– – –

id_tillage id_management date TillageSurfaceFraction TillageSoilLayer1Fraction TillageSoilLayer2Fraction TillageSoilLayer3Fraction TillageSoilLayer4Fraction TillageIntensity

A number identifying the tillage event Foreign key (a number identifying the management set) The date in which the tillage event occurs The fraction of existing surface residue remaining on soil surface after tillage The fraction of existing surface residue being transferred to soil layer 1 The fraction of existing surface residue being transferred to soil layer 2 The fraction of existing surface residue being transferred to soil layer 3 The fraction of existing surface residue being transferred to soil layer 4 Tillage intensity

– – – – – – – – –

† For simplicity, entity sets containing many attributes (initial values of state variables; soil, crop, residue, and manure parameters; outputs of the simulation model) are described only with the key(s). The complete list of their attributes can be obtained by downloading the software (see the text for details).

means that across the area soil and weather are homogeneous and management is applied homogeneously: the same operations were and will be applied all over the area in the same way, and the same crop rotation was and will be performed on the area. This assumption can hold for a large or a small portion of farmland, depending on the modeling objectives and on data availability. If the user has a precision agriculture approach in mind, the homogeneous area can be a small part of the field: the user knows that soil properties and management of this portion of the field make it different from other parts of the same field. The other way round, if soil variability is low (or can be considered low in the simplistic model application) and crop management is homogeneous across the farm, then the user may assume that a group of fields can all be simulated using the same soil parameters and the same crop management. Therefore, a homogeneous area may represent an entire field, a portion of a field, or a group of similar fields. The percentage of the field area included in the homogeneous area is indicated. For each farm cultivation scenario, more than one rotation can be described, each applied to a different homogeneous area. A homogeneous area can be georeferenced. For each homogeneous area, a soil (made of one or more layers) and a representative weather station (“location,” with daily weather data) need to be specified. A soil and a weather station can be common to more than one homogeneous area, but a single homogeneous area cannot be assigned more than one soil and one weather station. A rotation belongs to a farm cultivation scenario. In a rotation, the crops are characterized by a specific sowing date (specified in the “crops in rotation” entity set) and are described with a set of morpho-physiological parameters used in the simulation model. Each crop is managed with a set of management operations, which is a sequence of irrigation, fertilization, manure application and tillage events, each occurring on a specific date. For each type of event, specific parameters are listed (e.g., the amount of water applied for irrigation). Crop residues, manures, and other organic fertilizers (e.g., compost, sludge) can be related to the crop or to the group of animals producing them (respectively as crop residues or animal manures), or not being related to any of them (organic fertilizers produced outside the farm). For each simulation, annual, growing season, and daily outputs are recorded. Annual and daily outputs have a relationship with the simulation, while grow-

ing season outputs are also related to the crop; they are actually the attributes of the many-to-many relationship between crops-in-rotation and simulations (Fig. 3). Finally, for each farm, one or more animal breeds can be described, with the purpose of calculating the animal nutrient load; it is then possible to partition the nutrient emissions from animals over the crops cultivated on farmland. Each animal breed is composed by one or more animal groups, each characterized by an average weight, number of heads, and manure production coefficients. We emphasize that this is only an example of a data model that can be used to simulate a farming system. It has the advantage of allowing the description at farm level of fields and animal breeds, and it can be customized by adding or removing elements to fit specific needs. For example, the farm entity set may have attributes indicating the address, the owner of the farm and a telephone number, and the soil entity set may include the soil classification and the date of the soil survey. Also, the entity sets describing animals can be better detailed or eliminated, depending on the purpose of the application. Other options might include the addition of a pesticide simulator, together with specific entity sets describing the management of chemicals and their properties.

Implementation of the Data Model We implemented the data model in Microsoft Access 2000 (CropSystVB-Access, ver. 1.0). The reasons for choosing this relational database management system (RDBMS) are: (i) possibility of re-using most of the VBA code of CropSystVB; (ii) availability of this product on most computers; (iii) facility of creating tables, queries, and forms. We created all the tables needed to represent the entity sets and their relationships, and created several input forms to facilitate the process of inserting new data (e.g., Fig. 4). In a typical operating session, the user opens the Access file and employs the forms available to input a new farm, one or more farm scenarios, one or more animal breeds and related animal groups, soil and weather data, fields and homogenous areas, rotations and associated management event(s), and one or more simulations associated with each rotation. Other forms allow the user to run one or more simulations and to access all the output variables simulated by the model. The

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved.

1232

AGRONOMY JOURNAL, VOL. 99, SEPTEMBER–OCTOBER 2007

outputs can be copied and pasted in other Windows applications (e.g., a spreadsheet). In addition, our Access implementation includes the connection between the simulation model and the input data: when the user requires to run one or more simulations, a specific Access-VBA code loads all the relevant inputs, runs the simulation(s) using the same classes available in the Excel version, and writes the results in the output tables. Therefore, once all input data are specified, the simulation process is completely automated, not requiring user’s intervention. Our implementation consists of a single Microsoft Access file (»7 Mbytes), running under Windows 2000 (or XP) with Microsoft Access 2000 (or above); no other supporting software is needed. The minimum hardware requirements are a Pentium III 500 MHz processor, a RAM memory of 128 Mbytes, and 50 Mbytes disk space. No particular requirements exist for graphics or other hardware. No input or output files are involved, as all input and output data are managed inside the database file. This means that the size of the database file increases as simulations are added to it: for example, after setting up and running the 27 simulations of the example application presented below, the size of the Access file was 16 Mbytes. As in the Excel version, the Access-VBA code is fully available to users through the VBA editor. No manual exists, but explanations on how to use the program are provided on the forms.

Example Application To illustrate the use of the integrated cropping systems simulation and data model for the analysis of farming systems,

we present a simple example concerning the study of nitrate leaching for a pig farm located at Tribiano, province of Milano, northern Italy (45j24¶ N, 9j22¶ E). The farm has one field cropped with grain corn (Zea mays L.). The soil is a Dystric Eutrochrept, coarse-loamy over sandy, mixed, mesic (Soil Survey Staff, 1998); groundwater is always deeper than 2 to 3 m; soil is moderately deep (1.5 m), has a loam texture and is well drained; soil profile is homogeneous across the field. The slope of the field is 0.3%. We have simulated six different scenarios, obtained by combining three animal loads (low, medium, and high, giving 150, 300, and 450 kg animal-N ha21, respectively) and two different irrigation systems (surface vs. sprinkler irrigation). By interviewing the farmer, we have obtained average management practices for the high animal load–surface irrigation system situation; we have derived other input data from existing databases and literature. For the surface irrigation system, we identified eight homogeneous areas within the field, each located at a different distance from the water inflow. These areas differ in the amount of water infiltrating during each irrigation event, this quantity being highest close to the inlet and lowest toward the outlet (Fig. 5a), with an average value for the entire field of 119 mm, typical of the practice of farmers in this area for fields with a length of about 40 m (Galbiati, 1979). For the sprinkler irrigation system, the amount of water infiltrating into the soil was considered uniform across the entire field, and was calculated as F/e, with F (mm) 5 the amount of water needed to refill the soil to field capacity, and e 5 efficiency, set at 0.85. The farmer has a fixed schedule for irrigation because water is available strictly every 20 d. To mimic this

Fig. 4. Example of the forms developed to input data in the database and to run the simulation model.

1233

¨ CKLE: INTEGRATION OF SIMULATION MODEL & RELATIONAL DATABASE BECHINI & STO

(a)

(b) 120

Crop yield (t DM ha-1)

Water infiltrated (mm)

12.60

100 80 60 40

Low Medium High

12.50

12.40

12.30

20 12.20

0 0

5

10

15

20

25

30

35

0

40

5

10

15

20

25

30

35

40

30

35

40

Distance from the inlet (m)

Distance from the inlet (m)

(c)

(d) 480

280 Nitrate leaching (kg N ha -1)

Soil water drainage (mm)

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved.

140

460 440 420 400

260 240 220 200 180 160 140 120

380 0

5

10

15

20

25

30

35

Distance from the inlet (m)

40

0

5

10

15

20

25

Distance from the inlet (m)

Fig. 5. Simulation of surface-irrigated continuous grain corn in Tribiano, northern Italy (45°24¶ N, 9°22¶ E) at different levels of manure N fertilization (high 5 450, medium 5 300, low 5 150 kg N ha21, integrated with 0, 90, and 180 kg urea N ha21, respectively) and at different distances from the inlet; (a) amount of water infiltrated, used as input for the multi-annual simulations; (b) simulated average grain yields; (c) simulated average annual soil water drainage; (d) simulated average annual nitrate leaching.

situation, we applied irrigation water every year at fixed dates on 15 June, 5 July, and 25 July. For both irrigation systems, nutrient management was homogeneous for the entire field. Pig slurry was applied every year in autumn (two-thirds) and winter (one-third). Urea was applied before sowing and around the development stage V6 (Ritchie et al., 1996), with a total for the season of 0, 90, and 180 kg N ha21 for the high, medium, and low animal loads, respectively. Daily weather data were from the station of Milano Linate (45j27¶ N, 9j17¶ E, 107 m asl) for the period 1951–2000. Each simulation was run for a period of 50 yr; the first 20 yr allowed equilibrating soil organic matter pools; summary statistics of simulation results were calculated on the remaining 30 yr, providing an evaluation of the effects of different weather patterns.

RESULTS Implementation of the Example Application In our data model, we created one farm with one field and six scenarios; each scenario describes different combinations of animal loads (“Low,” “Medium,” and “High”) and irrigation systems (“Surface” and “Sprinkler”): “Low-Surface,” “Medium-Surface,” “High-Surface,” “Low-Sprinkler,” “Medium-Sprinkler,” “High-Sprinkler.” A scenario represents a set of options for farm cultivation; in our example, the scenarios differ for the amount of manure N applied on the field and for the irrigation

system used: therefore, animal breeds of different load and crop rotations with different crop management were associated with each scenario. The farm has only one field, which is used in all the scenarios. The field was either divided in eight homogeneous areas (surface irrigation), or was represented as a uniform area (sprinkler irrigation); as the field is small and homogeneous, all the areas were characterized by the same soil type and the same weather location. In every scenario the farmer cultivates corn and therefore crop rotation was always continuous corn. However, this rotation was named differently in different scenarios and homogeneous areas, because the rotation is linked to crop management, and crop management varies according to the scenario and to the homogeneous area. Examples of rotation names are “A1-High-Surface,” which indicates the rotation applied to the area closest to the inlet (“A1”), fertilized under the high animal load, and irrigated with the surface system; and “High-Sprinkler,” which indicates the rotation applied to the entire field, homogeneously irrigated with the sprinkler system under the high animal load. Each rotation was implemented by creating 50 entities in the “crops in rotation” entity set; each entity includes the sowing date (beginning of April, from 1951 to 2000) and the crop management adopted in that scenario. We finally associated a simulation to each rotation.

1234

AGRONOMY JOURNAL, VOL. 99, SEPTEMBER–OCTOBER 2007

In total, we created 27 rotations, 27 simulations, and 27 crop management sets (3 surface irrigation scenarios 3 8 areas 1 3 sprinkler irrigation scenarios 3 1 area).

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved.

Examples of Results Simulated crop yields in the surface-irrigated scenarios were relatively high and increased linearly over the slope (Fig. 5b), with the lowest yields at the inlet. There was no interaction between the position in the field and the animal load: at every distance from the inlet, the highest yields were produced with the medium and low animal loads, and the lowest with the high load. This apparently strange result is due to different efficiencies of manure N and urea N: at the low and medium animal loads, the application of urea at V6 ensures that higher concentrations of soil mineral N are maintained until flowering. This allows higher N uptake and higher crop growth rates in the low and medium animal load scenarios (data not shown) compared with the high animal load scenarios (no urea applied). The amount of simulated annual water drainage (Fig. 5c) was large as a result of three heavy irrigations of 119 mm each, applied based on a fixed schedule and not on crop water needs, and complemented with significant amount of rainfall outside the growing season. Drainage was larger close to the water inlet, where the application depth was maximum, and smaller at the bottom of the field receiving less irrigation, but nevertheless substantial at all positions in the field, with no differences resulting from the levels of animal load. The spatial variability of simulated nitrate leaching (Fig. 5d) had a similar pattern: leaching decreased slightly from the inlet toward the opposite side of the field, and was lowest where the amount of infiltrated water was smallest. Most of the variability of leaching is due to the N load, while the contribution of the position in the field was minimum (the average difference of leaching between the opposite sides of the field was 11 kg N ha21). When the two irrigation systems were compared using the average results at field level (Table 2), it was observed that higher crop yields (10.3 Mg DM ha21 on average), lower nitrate leaching (218 kg N ha21), higher crop N uptake (130 kg N ha21), higher net N mineralization (112 kg N ha21), and lower water application (285 mm) were obtained with the sprinkler irrigation method. This was due to reduced drainage and smaller leaching events during summer with the sprinkler system. A possible drawback of the sprinkler system are the higher amounts of soil mineral N left in the soil at crop harvest (117 kg N ha21), which can also be interpreted as a potential for reduced N fertilization, and the higher nitrate concentration (16 mg N L21) of drainage water, due to the smaller quantity of water drained.

DISCUSSION Compared with data management based on a file system, the use of a RDBMS provides the flexibility in manipulating complex data structures, and the possibility of managing large quantities of data (Garcia-Molina

et al., 2002). In particular, the integration of a simulation model with a relational database allows automating, improving and speeding up the tasks of preparation, generation and storage of input and output data of the simulation model, of execution of the simulations, and of analysis of the results. The 27 management sets, rotations, and simulations in the example were automatically generated with a simple VBA program; the program created new records of the tables “Rotations,” “Crops in rotation,” “Simulations,” “Management sets,” “Irrigation events,” “Fertilization events,” “Manure applications events,” and “Tillage events” by writing in the tables the relevant values for each simulation and establishing the proper relationships. This process saved time and reduced the likelihood of errors in the long and tedious work of filling the specific tables with the user interface. Similar applications would allow users to create simulations by deriving the data from external databases (describing farming systems for a region or cropping systems for a farm, as in the case of precision farming), or to generate simulations that represent the variability of the use of a specific production factor, as in the example of surface irrigation. Once input data are stored in the database, simple queries allow analyzing and comparing them for different scenarios. For example, one could automatically compare the amounts of production factors (e.g., water, fertilizers), soil characteristics (e.g., soil depth, texture) or initial conditions (e.g., organic matter content) of different simulations. As an example of a query on production factors, we show the variability (among different simulations) of the amounts of N applied with urea and pig slurry, and the amounts of irrigation water applied (Fig. 6): the database allowed to quickly listing production factors used and summing up the amounts applied on different dates, yielding the total for various simulations. One of the advantages of this integrated tool is that rotations are simulated independently: the model is run Table 2. Simulated variables (annual average of multi-annual simulations) for continuous corn, irrigated with a surface or a sprinkler irrigation system in northern Italy (45°24¶ N, 9°22¶ E). Variable Crop yield, Mg DM ha

21

Irrigation, mm Soil water drainage, mm 21

N uptake, kg N ha

21

Net N mineralization, kg N ha

Soil mineral N at crop harvest, kg N ha21 21 Nitrate leaching, kg N ha Nitrate concentration of drainage water, mg N L21

Animal load †

Irrigation system

High

Medium

Low

sprinkler surface sprinkler surface sprinkler surface sprinkler surface sprinkler surface sprinkler surface sprinkler surface sprinkler surface

12.7 12.3 271 356 352 438 304 271 292 279 199 176 233 253 66 58

12.7 12.5 271 356 352 438 298 269 232 220 171 154 186 203 53 47

12.8 12.5 271 356 352 438 288 261 169 158 142 131 141 157 40 36

† High, medium, and low animal (pig) loads provide 450, 300, and 150 kg N ha21, respectively, and are integrated with 0, 90, and 180 kg N ha21 with urea, respectively.

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved.

¨ CKLE: INTEGRATION OF SIMULATION MODEL & RELATIONAL DATABASE BECHINI & STO

for each rotation, stores the output results in the database, and is reinitialized for the next rotation. This allows handling spatially explicit data and simulations. In principle, the same concept could be applied to individual animals or other independent simulation entities or objects. Also, the user intervention is not required at the transition between one simulation and the next, and therefore the entire simulation process proceeds smoothly. Another important benefit from the use of the integrated tool is the help in the analysis of model outputs. The program stores all the results in the output tables of the Access file; their relationships with the simulation and crops-in-rotations entity sets allow making a connection with input data. Simple queries can be easily prepared to compare the results of different simulations, aggregate outputs in time and space, and calculate derived variables. All the results presented in this study were obtained with such queries. Also, the queries are dynamic instruments that allow the user to recalculate output statistics after new results are written by the model in the output tables; this means that the calculations are formalized only once by the user and can be automatically re-run when more results become available. Let us now compare this implementation with a model that does not use a database. We may suppose that the simulation model uses specific input and output files (ASCII or binary), coded with its own format. The inputs could be prepared with a similar procedure to

1235

that used in our implementation, that is, using a program that automatically writes 27 input files. However, these 27 input files would be kept separated and no simple possibility would exist of obtaining an integrated view of the simulations available (e.g., a list of the simulation names, the range of fertilizer application rates, and so on). Also, if the model has no “batch run” capabilities, the user would have to manually run 27 separate simulations. Finally, the user would obtain 27 different output files, one per simulation; each file would contain 50 values (one each simulation year) for each output variable (grain yield, soil water drainage, nitrate leaching, etc.). All this amount of data should be aggregated and processed (manually or using an ad hoc program) to calculate summary statistics. This means that there are several operational advantages with RDBMS implementation. However, it should be recalled that a disadvantage is the reduced speed of data storage compared with a dedicated model output format (e.g., binary). Another disadvantage of the present implementation is the difficulty of importing/exporting simulation parameters: as the parameters are embedded in specific records of numerous tables, they cannot be easily exchanged among users. Also, the release of a new version of the Access implementation (which would be provided if, for example, more parameters are required by a modified version of the simulation model) would require the users to manually copy all her/his old simulations into the

Fig. 6. Simulation of surface-irrigated continuous grain corn in Tribiano, northern Italy (45°24¶ N, 9°22¶ E): graphic result of a query of the database, with the amounts of production factors (N and irrigation water) used in each simulation. Nitrogen was applied with urea (“N min fert”), with the inorganic fraction of pig slurry (“N NH4 slurry”), and with the organic fraction of pig slurry (“N org slurry”).

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved.

1236

AGRONOMY JOURNAL, VOL. 99, SEPTEMBER–OCTOBER 2007

new Access file, as an automatic tool to do this is not available in our implementation. Several examples in the literature support our conclusions that the use of relational databases facilitates the management of agronomic data, in particular of input data for agricultural simulation models. Applications exist that make use of models and link them to relational databases for regional scale studies, generally using a Geographic Information System (GIS) environment: for example, Vaughan and Corwin (1994) applied a vertical solute and water transport model coupled to a RDBMS to study solute loading to groundwater; Bechini et al. (2003) mapped irrigation requirements for a region by interfacing a cropping systems simulation model and a GIS through a RDBMS. Other works do not propose a full model and database integration, but deal with the use of database to facilitate models’ use: Caldeira and Pinto (1998), for example, used a relational database to organize the data describing field experiments (plots, treatments, cultivars, weather data, etc.); these data can be used to automatically create input files for the DSSAT simulation models. Their work shows that the database allows to more explicitly represent relationships among variables (that are implicitly represented when ASCII files are used, as in the case of DSSAT), and facilitates the work of the user when preparing model input files. Wo¨sten et al. (1999) have developed a relational database to store soil hydraulic data from several sites in Europe: the collation of several data into a single database allowed integrated analyses of the entire data set, in particular the estimation of parameters for moisture retention and hydraulic conductivity functions. Van Evert et al. (1999a, 1999b) proposed a very flexible data model for storing data about field experiments, allowing exploring the relationships among different variables measured on different objects at different space and time scales.

CONCLUSIONS With the purpose of improving the capabilities of the cropping systems simulation model CropSystVB (developed in Microsoft Excel using Visual Basic for Applications), we integrated it with a relational database. The data model developed for this purpose contains the inputs needed by CropSystVB (describing the soil–crop system), and locates them into the bigger framework of a farm. The concepts of farm, farm scenario, animal breed, field, and homogeneous area were therefore included in the data model, together with model outputs. We implemented the data model using Microsoft Access, by creating tables, relationships, and forms to facilitate data input and analysis of the outputs. The VBA code was adapted from the Excel version to read inputs and write outputs in the Access environment. The integrated tool allows automatically running and analyzing the results of one or more simulations. To evaluate its capabilities, we simulated six different scenarios for a farm, obtained by the combination of three animal loads by two irrigation systems (surface and sprinkler). The database was very useful to rep-

resent the variability (over the length of the field, for the surface irrigation scenarios) of infiltrated water and of corresponding simulated variables, and to make comparisons with the sprinkler irrigation scenarios. The integrated tool allows users a large degree of control over the modeling process, including full access to the code of the process simulation model and the flexibility to generate inputs and outputs stored in a database, data that can be retrieved for later use and combined with data external to the modeling activity to create a large array of analytical options. This flexibility allows users to represent many types of farming systems and to modify the processes in the simulation model. It requires, however, a degree of familiarity with VBA and the use of Microsoft Access for full realization of these capabilities. The software for the Access and Excel implementation of the software is available at no cost at www.bsyse.wsu.edu/cropsyst/ (verified 15 June 2007). ACKNOWLEDGMENTS We would like to thank Josep Maria Villar (University of Lleida, Spain) and Francesc Ferrer Alegre (Lab-Ferrer, Spain) for organizing the course where we first developed the software described here.

REFERENCES Ali, I., F.D. Whisler, J. Iqbal, J.N. Jenkins, and J.M. Mckinion. 2004. Soil physical properties web database for GOSSYM and GLYCIM crop simulation models. Agron. J. 96:1706–1710. Bechini, L., S. Bocchi, and T. Maggiore. 2003. Spatial interpolation of soil physical properties for irrigation planning. A simulation study in northern Italy. Eur. J. Agron. 19:1–14. Caldeira, C.P., and P.A. Pinto. 1998. Linking DSSAT V3 to a relational database: The AGROSYS-DSSAT interface. Comput. Electron. Agric. 21:69–77. Donatelli, M., M.K. Van Ittersum, M. Bindi, and J.R. Porter. 2002. Modelling cropping systems: Highlights of the symposium and preface to the special issues. Eur. J. Agron. 18:1–11. Galbiati, G.L. 1979. Sperimentazione irrigua pluriennale su una coltura continua di mais da granella in semina primaverile nel cremonese (fase conclusiva). (In Italian, with English abstract.). L’irrigazione 6:5–36. Garcia-Molina, H., J.D. Ullman, and J.D. Widom. 2002. Database systems: The complete book. Prentice Hall, Upper Saddle River, NJ. Haley, S.D., R.D. May, B.W. Seabourn, and O.K. Chung. 1999. Relational database system for summarization and interpretation of hard winter wheat regional quality data. Crop Sci. 39:309–315. Ritchie, S.W., J.J. Hanway, and G.O. Benson. 1996. How a corn plant develops. Rev. ed. Spec. Rep. 48. Iowa State Univ. Coop. Ext. Serv., Ames. Scott, J.M., and C.J. Lord. 2003. SGS database: Use of relational databases to enhance data management for multi-site experiments. (Sustainable grazing systems: Building financial, social and natural capital for livestock producers). Aust. J. Exp. Agric. 43:729–743. Sinclair, T.R., and N.G. Seligman. 1996. Crop modeling: From infancy to maturity. Agron. J. 88:698–704. Soil Survey Staff. 1998. Keys to soil taxonomy. 8th ed. U.S. Gov. Print. Office, Washington, DC. Stigliani, L., G. Santospirito, N. Cardinale, and C. Resina. 1996. A relational database as decision support system in chemical weed control. Weed Technol. 10:781–794. Sto¨ckle, C.O., M. Donatelli, and R. Nelson. 2003. CropSyst, a cropping systems simulation model. Eur. J. Agron. 18:289–307. Sto¨ckle, C.O., S.A. Martin, and G.S. Campbell. 1994. CropSyst, a cropping systems simulation model: Water/nitrogen budgets and crop yield. Agric. Syst. 46:335–359.

¨ CKLE: INTEGRATION OF SIMULATION MODEL & RELATIONAL DATABASE BECHINI & STO

Reproduced from Agronomy Journal. Published by American Society of Agronomy. All copyrights reserved.

van Evert, F.K., E.J.A. Spaans, S.D. Krieger, J.V. Carlis, and J.M. Baker. 1999a. A database for agroecological research data: I. Data model. Agron. J. 91:54–62. van Evert, F.K., E.J.A. Spaans, S.D. Krieger, J.V. Carlis, and J.M. Baker. 1999b. A database for agroecological research data: II. A relational implementation. Agron. J. 91:62–71. Vaughan, P.J., and D.L. Corwin. 1994. A method of modeling vertical fluid flow and solute transport in a GIS context. Geoderma 64:139–154. Wo¨sten, J.H.M., A. Lilly, A. Nemes, and C. Le Bas. 1999. Development

1237

and use of a database of hydraulic properties of European soils. Geoderma 90:169–185. Xia, Y.L., R.E. Stinner, D. Brinkman, and N. Bennett. 2003. Agricultural chemicals use data access using COLDFUSION markup language and a relational database. Comput. Electron. Agric. 38:217–225. Zuliani, M., A. Peressotti, G. Zerbi, G. Zuliani, G. Delle Vedove, and F. Danuso. 2003. ClimagriLT: A relational meta-database for data management of long-term agronomic experiments. Ital. J. Agron. 7:137–143.