IPMDSS Weed Model System Documentation
By Ole Qvist Bøjer and Per Rydahl, DIAS version 13.08.2003
Ole Qvist Bøjer is the contact person regarding installation and technical issues.
Per Rydahl is the contact person regarding data and agronomical issues. Once the application has been installed, most problems are due to misssing and erroneous data.
1. Operating system and software
The weed applications are compatible to Windows NT 4.0, Windows 2000, and Windows XP (Professional and Server versions). The webserver is Internet Information Server (IIS 5) and Jscript version installed with Internet Explorer 5.5 or later (Important!!). The database server used is SQL Server 2000.
2. Installation
The installation files is included in the zip-file, Weeds.zip. Unzip the files in a website folder.
Option 1:The installation will be completed most smoothly if installed from scratch. Therefore it is recommended that you take a backup of existing files and databases to ensure that you can quickly restore all files and databases if you run into problems. If the directories exist, then back them up: weeds, graphics, seasonplan and <% windir %>\java\trustlib\cpweed. Also backup the databases: cpWeed, cpCommon, and Graphics or rename them (e.g. sp_renamedb ‘’cpWeed’, ‘cpWeed_backup’ in Query Analyzer). Option 2: If you wish to keep your existing installation running e.g. if you are in the middle of the season running trials, you can also install a parallel installation. The best way to do this is on a test server and afterwards copy to your master server. Option 3: A third way is to use different directories and database names. The latter however requires some insight and skills and is not recommended.
3. Installation of databases
1)In SQL Server Enterprise Manager create a user named ‘webuser’ or likewise if it is not already there.
2)In 'DBInstall'\Install_4_cpWeed_StoredProc.sql search for 'COLLATE Danish_Norwegian_CI_AS' and change to your local database collation (you can check which collation you are using by right clicking on cpCommon or cpWeed and click on properties in SQL Enterprise Manager). Note that only Case Insensitive (CI) collations will work!
3)Open SQL Query Analyzer and run the scripts no. 1 to 6 from directory 'Weeds\DBInstall' one at a time in the numbered order (fig. 1). Remember to change the setup as stated in the top of each of these files if you want different database names or ‘webuser’ names. For a successful installation, make sure that no errors are produced during execution of any of the scripts. If there are errors you should solve them before proceeding.
4)Version 13.08.2003: Upgrade of cpCommon: Run the scripts no. 1 and 2 from directory 'cpCommon\DBInstall\' in Query Analyzer
5)The database installation should now be complete.
Figure 1To create a new weed model database, run the script, ‘Install.sql’ from SQL Query Analyzer
Countries using Treatment Frequency Index (TFI)
For Baltic and Polish distribution package disregard this information as this package is setup with no TFI as a standard! Continue with paragraph 4.
Depending on where to fetch product information (whether the ‘Product’ database is present or not) or depending on if TFI is used or not, WdsQry_Product and WdsQry_ProductCrop should refer to the proper source of herbicides (table 1).
no. / Views (default setup) / Herbicide + Adjuvant database / Product data / FixMix data1 / WdsQry_Product / cpWeed / Wds_XMLProducts / Wds_FixMix
Wds_FixMixComponent
Wds_XMLProducts
2 / WdsQry_Product_Crop / cpWeed / Wds_XMLProductCrop
Com_CropTFICrop / Wds_FixMix_Crop
3 / WdsQry_Product_Prd / Product / Product.Product
Product.ProductName
Product.Registration
Product.Price
Product.ProductAttribute
Common.Unit / Wds_FixMix
Wds_FixMixComponent
4 / WdsQry_Product_Crop_Prd_NO_TFI / Product / Wds_HerbicideCropParam
Wds_HerbicideCropParamFixMix / Wds_FixMix_Crop
5 / WdsQry_Product_Crop_Prd_TFI / Product / Wds_HerbicideCropParam
Wds_HerbicideCropParamFixMix Com_CropTFICrop
Product.TFICrop
Product.TFIDosage
Product.ActiveIngredient_Chemical
Product.ActiveIngredient
Product.Chemical
Product.Product
Common.Unit / Wds_FixMix_Crop
Table 1. Which views to use to retrieve product data
Rename the views as needed (externally (rename) and internally in the view properties (‘CREATE VIEW Wds…’).
Examples:
1) The product database is present and TFI is used:
Rename WdsQry_Product_Prd to WdsQry_Product
Rename WdsQry_Product_Crop_Prd_TFI to WdsQry_Product_Crop
2) The product database is present and TFI is not used:
Rename WdsQry_Product_Prd to WdsQry_Product
Rename WdsQry_Product_Crop_Prd_NO_TFI to WdsQry_Product_Crop
3) The product database is not present and TFI is used:
Keep WdsQry_Product
Keep WdsQry_Product_Crop
4) The product database is not present and TFI is not used:
Do like in 3)
Note: when renaming, remember to save or rename WdsQry_Product and WdsQry_Product_Crop first
4. Installation of web pages
1)Copy the files from Windows_java_trustlib_cpWeed to <% Windir %>\Java\Trustlib\cpWeed\ (backup first!)
2)Unzip Weeds.zip in a webiste directory e.g. Weeds.
3)Edit the config.asp file with language= (en, da, et, lv, lt, pl), and the connection information (username, password) for the ‘webuser’ of the SQL databases. Example: Edit the’ weed/include/config.asp’ so that
ServerName = "999.999.999.999"; // replace with the IP number of your database server
UserID = "xxx"; // replace with the ‘wwwuser’ as described above
Password = "xxxx"; // replace with the password of ‘wwwuser’
Language = en; // set the default language. Language codes are defined in table, LanguageDefinitions
LoginMode = false; // When true, log the website user in table Wds_LogUser. P.t. only used in Denmark. URL’s to problems, growth stages and products can also be changed in config.asp.
4)Also edit the URL's config.asp to reflect your own directory configuration
5)If all necessary data have been imported (see section 5) you can now run weeds/Parameters.asp, weeds/EfficacyProfile.asp, weeds/EfficacyMix.asp. Note that asp-pages now needs the language parameter like this: "language=en" if you want to change language. (before it was language=’an integer’) Example: Try to start the application in your web browser (Internet Explorer). Use the URL: If the page does not show, you can ask for support in DIAS or try to solve the problem yourself..
Language adaptation: Make the constant labels of the web pages local to your country by copying records and translating the records in the table, Wds_text. To change the dynamic content of the webpages, translate the records in tables, Wds_Season_Translate, Wds_WeedWaterStress, Com_Crop_Translate, Com_Problem_Translate, Com_Scale_Item_Translate, Com_Unit_Translate, Com_Text, and Com_SeasonPlan. After making your own translated records, you can change the default language variable in ‘include/config.asp’ from Language=en to e.g. Language=lv
Switching between different languages can be done using the standard URL with a parameter, e.g. The language codes are defined in table, LanguageDefinitions’
5. Dataentry
Data entry is done most easily using an Access 2000 frontend. A more cumbersome way is to use the SQL Server Management Console directly. You make the Access frontend this way:
1)Make sure that minimum either SQL Server Client tools or SQL Server Connectivity has been installed (from the SQL Server CD)
2)Open Access 2000, then select "Access database wizards, pages and
projects".
3)Select the "General" tab and double click "Project (existing database) (figure 3)
4)Give the database a name and create it (figure 4)
5)In Data Link Properties window type in the SQL Server IP address, the
username and password and select the database (figure 5). Usually the user will only need read and write permission. This is most safe for security reasons.
6)Test connection.
7)Click OK and you are connected (figure 6)
Figure 3
Figure 4
Figure 5
Figure 6
1)If you are upgrading, you can copy data from your old cpWeed database (if any) to the new one. For the webpages to view properly you must additionally enter data in Wds_Text, Wds_Season_Translate, and Wds_WeedWaterStress_Translate. Use the data from the Excel spreadsheet, TblData.xls in the DBInstall\Data directory and enter your own language code (et, lv, lt, pl). If you want to, you can import the english records in the table as well.
2)Enter the relevant herbicides in Product database (previously product data were in table wds_product wds_product_crop)
Hints for dataentry (Per)
…..
Hints for testing and debugging (Per)
…..
Other efficacy versions
When the standard efficacy version is working with all data, you can make other efficacy versions. One way is to create a new database (e.g. ‘cpWeedHigh’, stands for high efficacy), then 1) restore a backup of ‘cpWeed’ into ‘cpWeedHigh’ or 2) do the ‘Installation of database’ step again, and afterwards copy data from the tables in ‘cpWeed’ into tables of ‘cpWeedHigh’. For low efficacy versions same procedure can be followed. When the new databases have been created you can change the efficacy values in table, Wds_ProblemEfficacyTarget. Another way is to use the season field in all the tables (Per Rydahl can explain how)
Testing the efficacy versions can be done using the standard URL with a parameter, e.g.
Switching between efficacy versions can be done on the fly.
6. Model documentation
Documentation of the model will only be described in the outline to be of help when navigating the weed model. When in need of details, scrutinizing the code is necessary.
Files (before 1/10-2002)
Figure 7Overview of files (created with SmartDraw) used in the weed model and in which files to find the different classes shown in figure 8
Files after 1/10-2002
Figure 8Overview of files (created with SmartDraw) used in the weed model and in which files to find the different classes shown in figure 8
Roadmap to Model calculations
Calculation / getModelData (SP in cpWeed) / Recommend.java / Classes.aspDosage (single herbicide) / x
Dosage (ADM) / x / x
Efficacy (single herbicide) / x
Efficacy (ADM) / x
Adjuvant (single herbicide) / x / x
Adjuvant (ADM) / x
Rounding / x / x
ADM calculation / x
Discarding insignificant doses / x
Figure 9Class overview of weed model (JPG from WithClass 2000)
The purpose of the classes is to provide data to the two Active Server Pages (ASP), Parameters.as and Treatments.asp. Parameters.asp utilize methods of WeedReg and Treatments.asp utilize methods of Model to get data from the database, cpWeed.
The WeedReg class has look-a-like methods that get new Database objects and returns the appropriate resultset in the form of a collection of objects to be presented in the comboboxes of Parameters.asp. The Model class has similar methods that returns collections of objects to Treatments.asp.
The Database class is an interface to the database. This class is used by both the WeedReg and Model class. It has methods that calls specific stored procedures on the database server as shown in figures 9 and 10 (red boxes). The resulting collections of objects (AdjuvantRec, ProductRec, ParamRec, LookUpRec, ModelSetupRec, Treatment) for the ASP-pages are generated in the methods of the Database class.
Classes beginning with “J” are Java classes, and classes without are ASP classes in Jscript. Java-files can be compiled into bytecode (the java executables) using Sun JDK 1.3.1_02 or MS JDK 4.0 or later. The J-classes are used specifically for calculating optimal mixture rates for ADM herbicide mixtures. The main class for this is JRecommend, which arrange data for the optimizing algorithm, JSimplex. The input for Jrecommend is a collection of JADMRecord objects which is a copy of the ADMRec collection from the Model class. After optimization in Jrecommend, the optimal mixtures are returned in a collection of JTreatment objects. In the Model class, method Calculate the Java JTreatment collection is copied to the Jscript Treatment collection. Finally, the Treatment collection is formatted as a list of recommendations in the Treatment.asp page.
Stored Procedures
All stored procedures, returning data are executed from the Database class. Figures 9 and 10 show which stored procedures are used in the weed model web pages.
Figure 10Scematic overview of relationship between comboxes in Parameters.asp and the corresponding stored procedures in cpWeed (SmartDraw).
Figure 11Scematic overview of relationship between recommendations in Treatments.asp and the corresponding stored procedures in cpWeed (SmartDraw).
Stored Procedure: getModelData
Procedure getModelData (figure 11) plays the most important role in the weed control application. It selects all data needed for single mixtures, fixed rate two-component mixtures, and ADM mixtures. The procedure consists of a chain of select statements, each delivering data to other select statements in getModelData. The last two statements in getModelData each deliver a result set to be used in the asp-pages. The transfer of data from one select statement to another is done using temporary tables (hash tables (#)). The temporary tables are unique for each request. From getModelData one can derive how data in the model tables are related, in what form records should be entered, and how table data should be interpreted. In figure 11 the dataflow in getModelData is shown.
Figure 12Dataflow of resultsets in stored procedure, getModelData (SmartDraw)
Rules for dosage calculations
Calculation of the rate of herbicide with which to treat and individual weed species:
RateEfficacyTarget = 10 exp( (((- 0.5 * Log2 (CultivarCorrection_par * 100 - 100 + EfficacyTarget) / (100 - EfficacyTarget)) - a_displacement_par) / b_slope_par)
+ Log10 (WeedGSCorrection_par * TempCorrection_par * WeedWaterStressCorrection_par))
RateLeastAcceptEfficacy = 10 exp( (((- 0.5 * Log2 (CultivarCorrection_par * 100 - 100 + LeastAcceptEfficacy) / (100 - LeastAcceptEfficacy)) - a_displacement_par) / b_slope_par)
+ Log10 (WeedGSCorrection_par * TempCorrection_par * WeedWaterStressCorrection_par))
When no correction parameter for weed growth stage, temperature, water stress or cultivar is found in the correction tables – or the corresponding parameter in table Wds_CropModelSetup is false - a neutral correction parameter value of 1 is inserted instead.
Each weed species has its own specific herbicide rate when it is the only weed to be controlled. When several weed species has to be controlled at the same time, the highest RateEfficacyTarget of each herbicide in a collection of weeds has to be used (RateMaxOnWeed)
A collection of weed species can only be controlled if a hebicide has an a-parameter for each of the weed species.
Dosages are rounded according to MaxRatePerApplication_par. If less than 0.2 then 3 decimals are used. If between 0.2 and 2.0 then 2 decimals are used. If between 2.0 and 20 then 1 decimal is used. If greater than 20 then 0 decimals are used.
Single herbicide or fixed rate mixtures:
When a RateEfficacyTarget exceeds the MaxRatePerApplication_par, the MaxRatePerApplication_par is used, and the solution is marked as a low effect solution
A herbicide will only be selected if it has no soil action or if it has soil action but use is still recommended.
Efficacy of a herbicide in the calculated dosage:
EffectWeedActualRate = CultivarCorrection_par - (1 / (1 + Exp(- 2 * ([a_displacement_par]
+ [b_slope_par] * Log10(RateActual] / ([WeedGSCorrection_par]
* [TempCorrection_par] * [WeedWaterStressCorrection_par])))))))
If the herbicide is a component of a standard tank mixture, RateActual is replaced by RateMaxOnWeed in the efficacy calculation.
ADM Mixtures:
An ADM mixture can be optimized according to price or treatment frequency per unit of herbicide.
The optimization of an ADM mixture is done using linear optimization or linear programming (the name of a mathematical method)
For a two component mixture of herbicides, h1 and h2 meant to control weed species w1, w2, and w3, let us assume:
P1, P2 are the known prices of product 1 and 2 per unit.
D1, D2 are the doseages i.e. the variables we want to find.
The objective function is to find the minimum for P1*D1 + P2*D2 = Min!
Constraints:
D1 <= MaxRatePerApplication_par[h1]
D2 <= MaxRatePerApplication_par[h2]
1/RateEfficacyTarget[w1h1]*D1 + 1/RateEfficacyTarget[w1h2]*D2 > 1
1/RateEfficacyTarget[w2h1]*D1 + 1/RateEfficacyTarget[w2h2]*D2 > 1
1/RateEfficacyTarget[w3h1]*D1 + 1/RateEfficacyTarget[w3h2]*D2 > 1
To ensure that a solution will always be found, RateEfficacyTarget is always converted to a unique value by differentiating the outmost decimals within a calculation (the linear problem)
In the program, a Java class is used to perform the optimization (Simplex). This calculating code is taken from Numerical Recipés, converted from C to Java and wrapped into a Java class.
Solutions, where one of the herbicide component doseages are less than 5% of MaxRatePerApplication_par are discarded.
The doseages, that have been found are rounded according to the rules above. After rounding, the efficacy of the herbicide doseages on the individual weed species are calculated.The rounding of dosages sometimes causes the efficacy to be slightly below the target efficacy (1 – 3 %). Efficacy of ADM mixtures are calculated using iterative numeric approximation (Jens Erik Jensen, Landbrugets Rådgivningscenter (LR))
The price of a solution is calculated as the sum of each of the doseages after rounding - times the herbicide price per unit.
Fixed rate mixtures in ADM mixtures:
Fixed rate mixtures are treated as single herbicides in ADM optimization, just using a relative dose. Not until after optimization, the fixed rate mixtures are split into the two herbicide components i.e. the actual doseages, rounding, prices and TFI’s calculated. Rate = ADMRate * FixMixMaxrate_par
Adjuvants
Adjuvants used with the herbicides are selected after all calculations have been done. Adjuvant has to be added if RateStartAdjuvant > 0 and RateStartAdjuvant >= Rate/MaxRateApplication
Sorting:
Recommended solutions are sorted ascendingly according to solution price or Treatment frequency ndex (TFI) depending on the user selection of optimization criteria in Parameters.asp. Normal effect solutions are listed first and low effect solutions are listed last although the latter solutions may be cheaper.
Add-on tools for Weed Model
The two tools, ”Efficacy profile of one herbicide” and ”Efficacy of own mixtures” consist of five additional ASP-files, EffficacyProfile.asp, EfficacyResult.asp, EfficacyMix.asp, EfficacyMixResult.asp, and EfficacyCommon.asp besides the files of the “Problem solving” application. Include statements in the four files refer to asp-files shared by several asp-files. The stored procedures can be installed in cpWeed on SQL Server using the script, InstallEfficacy.sql. Like in the problem solving application, to check the algorithms, scrutinising the program code or SQL Server stored procedures is necessary. Translation of the labels in the asp-files is done in the table, Wds_Text. Translation of the dynamic data is done using the “_Translate”-tables in cpCommon and cpWeed if they have not been translated already.
1. Efficacy profile of one herbicide
A. Efficacyprofile.asp
Conditions for calculation:Crop
Crop1) / / Undersown2) /
Season3) /
Cultivar4) / / Expected yield5) /
Growth stage6) / /
Growth conditions
Soil type7) / / Min.temp8). / / Max.temp.9) /
Weeds
Growth stage10) / / Water stress11) /
Treatment options
Product12) / / / Show all weed species13) /
Files, form elements and stored procedures