Published in Agron J 99:1226-1237 (2007)
DOI: 10.2134/agronj2006.0305
© 2007 American Society of Agronomy
677 S. Segoe Rd., Madison, WI 53711 USA
Integrated Agricultural Systems
Integration of a Cropping Systems Simulation Model and a Relational Database for Simple Farm-Scale Analyses
Luca Bechinia,* and
Claudio O. Stöckleb
a Dep. of Crop Science, Univ. of Milano, Via Celoria 2, 20133 Milano, Italy
b Dep. of Biological Systems Engineering, Washington State Univ., Pullman, WA
* Corresponding author (luca.bechini{at}unimi.it)
Received for publication October 31, 2006.
 |
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.
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
|
INTRODUCTION
|
|---|
AGRICULTURAL SIMULATION MODELS 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 (Garcia-Molina 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., Wö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.).
View larger version (24K):
[in this window]
[in a new window]
|
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.
|
|
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 farm-scale 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.
|
MATERIALS AND METHODS
|
|---|
The Cropping Systems Simulation Model
For the purpose of this implementation, a simplified version of the standard CropSyst model (Stöckle et al., 1994, 2003) was used. The model, called CropSystVB (ver. 1.0), was 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.
View larger version (11K):
[in this window]
[in a new window]
|
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."
|
|
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 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.
View larger version (40K):
[in this window]
[in a new window]
|
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.
|
|
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 growing 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 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 (45°24' N, 9°22' 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 ha–1, 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) = the amount of water needed to refill the soil to field capacity, and e = efficiency, set at 0.85. The farmer has a fixed schedule for irrigation because water is available strictly every 20 d. To mimic this 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 ha–1 for the high, medium, and low animal loads, respectively. Daily weather data were from the station of Milano Linate (45°27' N, 9°17' 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.
View larger version (16K):
[in this window]
[in a new window]
|
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 = 450, medium = 300, low = 150 kg N ha–1, integrated with 0, 90, and 180 kg urea N ha–1, 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.
|
|
|
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. In total, we created 27 rotations, 27 simulations, and 27 crop management sets (3 surface irrigation scenarios x 8 areas + 3 sprinkler irrigation scenarios x 1 area).
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 ha–1).
When the two irrigation systems were compared using the average results at field level (Table 2), it was observed that higher crop yields (+0.3 Mg DM ha–1 on average), lower nitrate leaching (–18 kg N ha–1), higher crop N uptake (+30 kg N ha–1), higher net N mineralization (+12 kg N ha–1), and lower water application (–85 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 (+17 kg N ha–1), which can also be interpreted as a potential for reduced N fertilization, and the higher nitrate concentration (+6 mg N L–1) of drainage water, due to the smaller quantity of water drained.
View this table:
[in this window]
[in a new window]
|
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).
|
|
|
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.
View larger version (34K):
[in this window]
[in a new window]
|
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").
|
|
One of the advantages of this integrated tool is that rotations are simulated independently: the model is run 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 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 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. Wö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 represent 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.
|
NOTES
|
|---|
The research was funded by the Univ. of Milano with the "Visiting Scientist Program" and the "Scientific and Technological Research Program."
|
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.[Abstract/Free Full Text]
- 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.[Medline]
- 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.[Abstract/Free Full Text]
- 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.[Abstract/Free Full Text]
- 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.
- Stöckle, C.O., M. Donatelli, and R. Nelson. 2003. CropSyst, a cropping systems simulation model. Eur. J. Agron. 18:289–307.[CrossRef]
- Stö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.[Web of Science]
- 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.[Abstract/Free Full Text]
- 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.[Abstract/Free Full Text]
- 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.[Web of Science]
- Wösten, J.H.M., A. Lilly, A. Nemes, and C. Le Bas. 1999. Development and use of a database of hydraulic properties of European soils. Geoderma 90:169–185.[CrossRef][Web of Science]
- 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.
TOP
ABSTRACT
INTRODUCTION
