Merging Snap-Plus Farm Databases into a Single Watershed Database

The instructions below describe how to knit any number of Snap-Plus version 1 farm databases (composed of FF2 files) into a single SQLite database that can be queried.  Note that when Snap-Plus version 2 is released (currently scheduled June, 2012), Snap-Plus databases will be in a single file format and this merging will be an easier process. Check this website for the latest updates.

Getting Ready: Put a copy of all of the farm databases you want to knit together into a single file folder. Make sure that all of these databases have been run with all calculations completed in the current version of Snap-Plus, v. 1.132. If these are older databases or if you do not know, this can be accomplished by opening each database in Snap-Plus v. 1.132 and selecting Update all fields from the Tools menu.

Download the MergeFarms software from: http://www.snapplus.net/dev/SetupMergeFarms.12.03.19.msi
This will install MergeFarms on your computer. MergeFarms has three components and will install an icon for each one on your desktop: SnapFF2reader, FF2toSQlite, MergeFarms.  Each of these components will need to be run in sequence to create the merged database.

Merging:

Step 1.

• Select the SnapFF2Reader icon  on your desktop.  (Note that if you cannot locate the icon on your desktop, you can find the program under MergeFarms in Start, All Programs.)
• Click on the yellow folder to browse and select the file folder where you have put the farm files you want to merge.
• Click Convert databases. The conversion log will tell you when this is finished and you can click on Done with step 1.

Step 2.

• Open FF2TOSQLite by clicking on the icon on your desktop.
• Click on the yellowish folder to the right under FF2_farm(s) to  import (.sqlite). The browser should open to the Conversions folder. (Note: If it does not, locate the folder in Windows XP, C:\Documents and Settings\All Users\Application      Data\UWSoils\MergeFarms\Conversions or in Vista/Win7, C:\ProgramData\UWSoils\MergeFarms\Conversions.) The databases you converted in step 1 will be in a subfolder with the date that you made the conversion. Select all of the farm databases you want to  join together.
• Click on the yellowish folder to the right under Destination folder for imported farms to browse to the directory location you want the files to be in. Select Make New Folder and rename the new folder to your preferred  name.
• Click on Convert data. A conversion error log will appear. Close it and click on Done.

Step 3.

• Open MergeFarms from the desktop icon.
• Click on the yellowish folder to the right under Source folder for merging farms and browse to locate the new folder you made in step 2.
• Click on the yellowish folder to the right under Destination folder for the merged farms database to browse to the directory location you want the files to be in. Select Make New Folder and rename the new folder to your preferred name.
• Type in a name for the merged database in the cell next to Merged filename (the default is MergedFarms.sqlite)
• Select Merge These Farms.
• Click on the X in the upper right hand corner to close when the time stops advancing, indicating that the merge is completed. You are done. You have created a  single SQLite database with all of the Snap-Plus information for all of   the farms.

 Using the Merged SQLite Database:
Software designed specifically for querying merged Snap-Plus databases is under construction. The following instructions allow you to do some analysis of merged databases in the interim. These instructions assume you are not familiar with SQLite, so don’t worry.

First, you need some software that reads SQLite. Download SQLite Studio fromhttp://sqlitestudio.one.pl/
The download is an executable file. If you save it to your desktop, it will create a feather-shaped icon that will open SQLiteStudio when you click on it.

Open SQlite studio.

From the top menu bar under Databases, select Add database.  This will bring up an Add database dialog box. Under Database file: browse for and open the merged file you created in step 3 above. Select Ok. Your merged sqlite database will appear in the left hand column.

Under Tools, select Open SQl Query editor.

Make sure your database shows up in the window to the right of the menu bar.

Copy the following query and paste it in to the window under the SQL query tab (Hint: right clicking in the window will bring up a menu bar that includes paste) or type it in directly.

Select Farms.name, Fields.name,
M.FilterStrip, M.Contour, M.TillageCode, M.RotCode, M.P_SoilTest, M.ConsSoilLoss, M.AvgPI from Farms, Fields, FieldPlans as M
where Farms.ID = Fields.farmID and
      Farms.ID = M.farmID and
      Fields.ID = M.fieldID
order by Farms.name, Fields.name;

Select the lightning bolt icon above the SQL query tab to execute this query. Under the Results tab, you should find a table showing the Farm name, Field name, Filter strip presence or absence, Contouring (0=no contour, 1 = contoured, 2 = stripcropped), the tillage across the rotation, the crops across the rotation, soil test P, rotational soil loss, and rotation average PI.

The above query is for a very limited subset of the information in Snap-Plus databases. Any information in the databases can be retrieved, including P Index and Soil Loss values for individual years.  However, if you are not familiar with SQLite and cannot wait for the new query builder, you may want to contact the Snap-Plus development team for assistance with the query.

Now you can export this table so you can use it in a spreadsheet. Go to the symbol that shows a spreadsheet with a lightning bolt (it’s the fourth symbol after the lightning bolt).  This will open an Export data dialogue box. Browse to the location you want the file to go and enter a name.  Leave the export format as CSV and select Configure and check Column names as first row, then ok to close the configure box. Now select Export. You have now created a comma separated text file that can be opened in Excel or other spreadsheet program. (Hint: open Excel first and then open the file from within Excel to get the Text Import Wizard dialogue box and in step 2 select Comma under Delimiters.)