Creation of Johnson County SWAT SSURGO Soil Input Dataset:
Using the AvSwat Model with SSURGO Data in Johnson County, Iowa

Contents

• Introduction [1]
• Part I, Procedure for downloading Tabular and Cov.zip files from SSURGO web site
• Part II, Outline of the SSURGO tabular data and SWAT Soils Input format
• Part III, Conversion of the SSURGO data file, ia103_a to a grid file
• Part IV, Building the User Soils Table in ACCESS
• Part V, Running the ArcView Swat Interface

Introduction

This documentation is a supplement to The User’s Guide for ArcView Interface for SWAT 99.2, released by Blackland Research Center, Texas Agricultural Experiment Station (SWAT User’s Manual, 1999). The SWAT99.2 User's Guide discusses using use the State Soil Geographic Database (STATSGO) soil database as the input soil file to run SWAT model. The STATSGO data for SWAT for Texas is included in the SWAT installation file, and the remaining states STATSGO SWAT data is available from the SWAT website, http://www.brc.tamus.edu/swat/.

While using STATSGO soil data is legitimate in running SWAT in larger watersheds, some room for improvement remains on smaller watersheds. The Soil Survey Geographic Database (SSURGO) is the most detailed level of soil mapping done by NRCS, and each SSURGO mapping area (mostly on county boundaries) is put on NRCS web site, http://www.ftw.nrcs.usda.gov/ssur_data.html, as completed. Using SSURGO data to run SWAT enables users to generate better simulation results at the farm and field level.

In order to use SSURGO database in SWAT, several adjustments have to be made.

1. Direct conversion of SSURGO data to AV-SWAT STATSGO/MUUF input format is not compatible to current STATSGO AV-SWAT data.
2. Errors in categorizing fine sand soils between 0.08 mm and 0.05 mm as silt instead of very fine sands.
3. Substituting for the years of expert logic that went into the MUUF code.
4. Using simple averages of high and low values for some variables ended up with the results far off from the current STASGO/MUUF’s. Several options can be applied to tackle these inconsistencies, but we believe that the best alternative is to create a UserSoil file. In part, this option has already been built in AV-SWAT with a template showing the data structures. Nevertheless, creating a new user file to replace this template is still not that straight forward due to some procedures have to be strictly followed up.

Part I, Download the Tabular and Cov.zip files from SSURGO web site

1. Open Internet Explorer and search SUURGO web site using the address http://www.ftw.nrcs.usda.gov/ssur_ftp.html 
2. The National SSURGO Database Data Access window will be displayed.
3. Click Download SSURGO data.
4. Click Data folder.
5. Choose ia005 folder, which is the most recently modified SSURGO soil data for Iowa (i.e. it was modified on 5/7/99).
6. Double click tab.zip, and choose “Save the file to disk”.
7. Click OK.
8. Store tab.zip to C:\temp\ directory.
9. Then click Save. Window program starts download the file.
10. Repeat the same steps to download cov.zip file.

Part II, Outline of the SSURGO tabular data and SWAT Soils Input format

The tab.zip file includes the two files we need to work with in SSURGO data conversion process; “comp” and “layer.” The comp file covers general soil type information such as ID, muid, musym, stssaid, compname, seqnum, s5id and others reflecting soil characteristics. The layer file includes specific soil information by layer and has the most important data we need for generating soil input file in SWAT. Not only does the layer file include those variables similar to comp’s such as ID, stssaid, muid, seqnum, s5id, and layernum, it also contains more detail soil feature data such as albedo, clay, silt, sand and rock content in soil. Unfortunately, the variables defined in SSURGO are not consistent with those defined in the SWAT User Soil template. For instance, variables included in STATSGO (or in User Soil template) such as organic matter, albedo, bulk density, available water capacity, carbon, clay, silt, sand, rock and saturated conductivity do not exist in the layer table from SSURGO database. Although some of them are easily to be generated by doing some simple arithmetic calculations. For example “clay” variable for each later can be simply created by taking arithmetic mean between the high and the low contents recorded in the SSURGO layer table. However, variable such as saturated conductivity (K factor) has to be derived through a series of steps, in which some empirical engineering formula are applied in generating process. Since soil data used in SWAT are divided into 10 layers, this routine has to be strictly followed in the conversion. We built 10 queries in ACCESS, each query dealing with one soil layer. Then a joint query merged these 10 queries into one SWAT input soil table and name it as the USERSOIL table.

Creation of each SWAT input variable per soils layer:

1. Organic matter OM1 = (oml+omh)/2.
2. Albedo: alb = (0.7/(Exp(0.5596*[avgOM])))^2
3. Z1: 25.4*[laydeph]
4. Bulk density: bd1= ([bdl]+[bdh])/2
5. Available water capacity: awc1= ([awcl]+[awch])/2
6. Carbon content: cbn1: [avgOM]/1.72
7. Total soil: Total Soil = ([no10l]+[no10h])/200, where no10l and no10h represent percent passing sieve number 10. But, the former represents the minimum value for the range in percent by weight of the soil material in a layer or horizon that is less than 3 inches and passes a No. 10 sieve. The later refers to the maximum value for the range in percent by weight of the soil material in a layer or horizon that is less than 3 inches and passes a No.10 sieve. This total soil definition was adopted by USDA (Soil Survey Staff, 19075). The total soil consists of clay, silt, and sand, i.e., the sum of these three components should be equal to 100.
8. Clay: Clay1= Int((([clayl]+[clayh])/2)/[Total Soil]*100)/100, where Int means integer. The reason of using Integer in front and post multiplication of 100/100 in this formula is to assure that the sum of clay silt and sand equals a 100. This is strictly required in SWAT. Notice that clayl is defined as the maximum value for the range in clay content of the soil layer or horizon, expressed as a percentage of the material less than 2 mm in size. Similarly, clayh is defined as minimum value of the range in clay content of the soil layer or horizon, expressed as a percentage of the material less than 2 mm in size.
9. Silt: Silt1= Int((((([no200l]+[no200h])/2)/[Total Soil])-[Clay1])*100)/100, where both no200l and no200h means percent passing sieve number 200. The former represents the minimum value for the range in percentage by weight of the soil material in a layer or horizon which is less than 3 inches and passes a No. 200 sieve. The later refers to the maximum value for the range in percentage by weight of the soil material in a layer or horizon which is less than 3 inches and passes a No. 200 sieve.
10. Sand: Sand1= ((([no10l]+[no10h])/2)/[Total Soil])-[Clay1]-[Silt1], where both no10l and no10h refer to percent passing sieve number 10. As before, the no10l represents the minimum value for the range in percent by weight of the soil material in a layer or horizon which is less than 3 inches and passes a No. 10 sieve. The later refers to the maximum value for the range in percent by weight of the soil material in a layer or horizon which is less than 3 inches and passes a No. 10 sieve.
11. NO31 refers to soil nitrate nitrogen content and it is set 0 as default.
12. Rock: rock1= 100-(([no10l]+[no10h])/2), where both no10l and no10h are defined in number 10 above. This is to adopt USDA’s rock definition. Intuitively, if we take whole soil material as a 100 and subtract those materials that can not pass through number 10 sieve, the remaining must be rock, assuming total soil material is composed of clay, silt, sand, and rock.
13. Finally, Saturated Conductivity (K1) variable is generated through a series steps using empirical engineer formulas listed as followings.

    BDSC1: (1.2+0.00445*[sand1]), where sand1 has been generated in step 10.
    BDSC2: (1.2+0.35+0.005*[sand1]).
    X2: Ln(0.01124*[BDSC1]), where Ln represents natural logarithm.
    B2: ([X2]-Log(8*[BDSC2]))/([BDSC1]-[BDSC2])
    B1: [x2]-[B2]*[BDSC1].
    F: [bd1]/([bd1]+Exp([B1]+[B2]*[bd1]))
    XC: 100-[clay1]
    SC1: 12.7*[XC]/([XC]+Exp(11.45-0.097*[XC]))+1
    K1: [F]*[SC1].

Note on the K1 factor. This calculation is an expansion of a routine in the EPIC source code using the percentages of sand, silt, and clay. It is similar, but simpler than the MUUF methods, providing similar results in the range of 3.5 to 12 mm/hr. An alternative straightforward method would be 25.4*((PERMH + PERML)/2), averaging the high and low permeability rate for the soil layer or horizon, expressed as inches/hour, and converting it to mm/hr. This method results range from 3 to 500 mm/hr. The modelers recommended using this set, questioning the accuracy of physical measurements in the SSURGO data. Most NRCS soils scientists asked, recommended using the measured values. The only middle ground was offered by Ray Sinclair, who recommended using the modelers calculations, since the model was developed with that range in mind. If the runoff is too high on major storms, Susan Neitsch, SWAT technical support, recommended adjusting the K1 factor up to increase infiltration. The ranges for this variable for particular soils are adjustable.

Now, all new variables needed for soil layer 1 are created. Comparing this table to the User Soils table structure, it is clear that every column stored in the User Soil table is also stored in comp and layer 1 ACCESS query tables, implying that the data sources we have now are adequate for building the User Soils table.

Part III, Conversion of the SSURGO data file, ia103_a to a grid file

1. Download the Johnson County data (FIPS 103) from the SSURGO website, http://www.ftw.nrcs.usda.gov/ssur_data.html
   
2. Start ArcView
3. Load the Spatial Analysis extension (from the File menu choose Extensions).
4. Click in the check box labeled Spatial Analyst, then press the OK button on the Extensions dialog to load the extension.

1. Choose Views at the left of the project window.
2. Click New to open a new view.

1. Press the add theme button.
2. Navigate to the directory of c:\avswat\johnson_iasoil\cov\cov. Double click on ia103_a.
3. Click View.

1. Click View in menu bar.
2. Click Properties.
3. Click Projection.
4. Choose Standard.
5. Choose UTM-1983.
6. Choose Zone 15.
7. Click OK.

1. Double click a color legend box in the view.
2. Choose Unique Value in Legend Type box.
3. Choose Musym in Values Filed box.
4. Click Apply.
5. Click X box on upper right corner to quit this set up window.

1. Click Theme in menu bar.
2. Click Convert to Grid.
3. Locate directory where you want to put new grid and give the grid a name (For example: johnson_ia103).
4. Click OK. Will see Conversion Extent: ia103_a window.

1. Enter 10 meters in Output Grid Cell Size. 30 meters would match the detail used in the DEM and Landuse layers but 10 meters makes the soils layer more accurate. The 10-meter grid increases the directory size from 43MB to 57MB, with similar slower calculation speeds. Most errors in the 30-meter soils grid would occur in narrow strips of a soil type, as along ridges or streams. These narrow buffer strips may be important in your analysis.
2. Click OK
3. Click MuSys for the conversion field
4. Wait. This conversion takes time.
5. Click Join feature attributes to grid.
6. After the grid is created, select the new grid and open it by clicking the small square.
7. Click Alt-Theme, then click Table to pull up the attribute table for the grid.
8. Click Alt-File, Export, to export the Table as a Dbase file. We saved the example file as ia103_30mtable.dbf
9. Open this ia103_30mtable.dbf file for editing. This data file is needed to link the grid with the usersoil.dbf file above. The Value column maintains a link to the soils grid file. Create a Name column by modifying the S_value column to make it identical with the SNAM column in usersoil.dbf. Make the same modifications to add a letter to the front, limit the total number of characters to 5, and make sure every soils has a unique entry. See the usersoil.dbf steps 13 and 14 above for details.

Part IV, Building the User Soils Table in ACCESS

Our goal is to build a new usersoils.dbf table to replace the table with the same name that is already embedded in AVSWAT’s subdirectory called AVSWAWTDB. The usersoils.dbf includes several major variables, which are used as joining attributes for the soil data layer. These include S5ID, Soil Name (SNAME), MUID, SEQN, etc. Although those variables are included in comp and layer tables, the variable names may not be directly matched. Therefore, to make a new user soils table to replace usersoils.dbf, we have to build several new queries in ACCESS.

Twelve queries are needed in building user soil table in ACCESS; comp, ten layers queries, and the ‘max layers query’ where the max layer query is directly made from the original layer table by selecting muid, sequent number (seqnum), and layernum (nlayers) in which “group by” is selected for muid and seqnum, and “max” is chosen for nlayers in the Total row under the design grid.

1. Launch Access and open the AvSWAT SSURGO Conversion database. Click on the Query tab. Click on the New button to start a new query. Add the comp to the query. Close the Show Table list box.
2. Click Show Table button again.
3. Click Query button and then make selections on layer1, layer2, layer3, …, through layer10, and finally also make selection on Max layers query table.
4. Make links from comp to each layer query table and max layer table by ‘muid’ and ‘seqnum.’
5. Click Query, then New, then Append query.
6. Make selection on Current Database and click down arrow in Table Name box.
7. Select usersoil, and click OK. Notice that usersoil table here was directly copied from the usersoil table structure stored in directory of AVSWATDB.
8. Select following fields from comp table by double click each field name in the table: compname, seqnum, muid, s5id, comppct, hydgrp. And select nlayers from Max layers query table, and create a new field CRK with default 0.
9. Select following fields from layer1 query table: alb, kfact, z1, bd1, awc1, k1, CNB1, clay1, silt1, sand1, rock1, No31. And select same fields of z2, bd2, awc2, …, through No32 from layer2 query table. Make the similar selections of z3 through No33 from layer3 query table. And repeat the same selections from layer4 through layer 10 query tables.
10. Click first cell correspondent to compname column. Fields stored in Usersoil table are displayed because it was specified in step 9.
11. Double click muid making it stay in the same column as compname, which means that filed of compname originated from comp table will be replaced with muid when ACCESS runs this append query. Double click SEQN from usersoil table, and make it locate at same column as seqnum. Double click SNAME from usersoil table and make it stay at same column as muid. Double click S5ID, and CMPPCT,etc until the last field No310 was selected. Notice that after running this Append Query, compname seqnum muid comppct and kfact selected from comp table will be respectively replaced by MUID SEQN SNAME CMMPPCT and USLEK. The remaining fields stored in both comp and usersoil are generally identical and they are also in the same order. Therefore, the rest fields can be selected in sequence from usersoil table by double click on each field.
12. Run this Append Query. You will be told that you are about to append 181 rows.

1. Click on the New button to start a new query, then click Design View.
2. Add the usersoil to the query. Close the Show Table list box.
3. Click Query, then select Delete Query.
4. Double click * in the top row of usersoil table to select all fields.
5. Run this Delete Query to delete those records previously stored in usersoil table.

1. Run Delete usersoil Query.
2. Run conversion user soil append query.
3. Click usersoil table and this is our finale usersoil table used in AVSWAT.

1. Launch Access and open the AvSWAT SSURGO Conversion database.
2. Right mouse click on usersoil in AvSwat SSURGO conversion database.
3. Click on Save as/Export.
4. Click on To an External File or Database.
5. Click OK.
6. Select dBASE IV (*.dbf).
7. Name it as usersoil.dbf.
8. Click on Export.
9. Go to Exploring c:\AVSWAT\AvSwatDB\ directory.
10. Double click usersoil.dbf.
11. Click Replace button.
12. Export usersoil.dbf table to EXEL.
13. Add a character in front of the digital number entry of each soil in SNAM column since AVSWAT requires that the SNAM entry must start with a character not a number. We used J for Johnson Co., and would use C for the adjacent Cedar Co. soils if we study a watershed crossing the county line.
14. Use the Access/Excel text functions, (Left, Mid, Right, Length) to cut the SNAM column to a unique set limited to five characters each, including the J.
15. Now, usersoil.dbf is ready for SWAT to use.

Part V, Running the ArcView Swat Interface

1. From the File menu choose extension
2. Click in the check box labeled SWAT model, then press the OK. The main interface screen of the SWAT will show up now.
   Please notice that before the user begins to generate a new SWAT project, all of the ArcView map themes and database tables needed by the interface should be created and stored in one directory. These include DEM, Land Cover and Land Use, Soil layer, Weather, and Management files, etc.

1. Click the New Project button on main interface.
2. Navigate a directory tree and find the directory where all themes and tables used in this project are stored, then give this new project a name (for example: johnson_ia103.swa).
3. Click OK.

1. On the main interface, click the button beside Edit SWAT Databases.
2. Click User Soils in the dialog box. The data of SNAM column in Usersoil.dbf will be displayed in the window.
3. Use arrow key to point each soil name and will see this soil feature data at left panel.
4. Move arrow key through each soil to verify whether SWAT is able to read the Usersoil.dbf table correctly. SWAT will show error message when it finds some soil entry has problem.
5. Click Exit when through.

1. Click Define data projection on the Watershed menu.
2. Click OK. (Notice that if the projection was previously defined, a prompt box will be displayed showing the existing settings.
3. Click NO if the projection is correct. Click Yes if the projection is not correct and continue with step 4.
4. Click Custom projection. Choose Standard, then choose UTM-1983 on Category box.
5. Choose Zone 15 on Type box.
6. Click OK.

1. Click Watershed Delineation from the Watershed menu. The dialog box for DEM processing will be shown up.
2. Click icon beside the DEM grid text box to load the DEM grid.
3. Choose DEM map grid from the project directory.
4. Click OK. The DEM grid will be displayed on the screen.
5. Click Apply next to Preprocessing of the DEM.
6. Click OK when the map preprocessing is complete.
7. Enter a number of hectares in Threshold area box (for example 500 in here). The smaller the number of hectares specified, the more detailed the drainage network delineated by the interface.
8. Click Apply.

1. Click Add button. The DEM dialog box will be minimized.
2. Move the mouse pointer to the desired location on the map and click the left mouse button. The interface will automatically snap the added outlet to the closest stream line.
3. Click ArcView icon in the bottom.
4. Click Restore to recover outlet definition window.
5. Click Select from outlet definition window.
6. Move mouse pointer to the edge of the selected outlet dot, then hold mouse and drag a square surrounding the outlet Dot. Release the mouse button.
7. Click OK when a message saying one outlet has been selected.
8. Click Apply. When the run is finished, SWAT-ArcView indicates: “Completed the watershed delineation and stream-subbasin parameter calculation.
9. Click OK. SWAT will asks you “Do you need to insert Reservoirs or point source discharges into your watershed configuration?”
10. Click NO if you don’t need. Otherwise, click Yes.

1. Click Yes when asked “Do you want to proceed with LandUse-soil data definition?”
2. Click Load land use grid icon from Land Use Data Layer. Navigate the project directory to find land use data layer table (92cover in this example and it stores land use information).
3. Click 92cover. The 92cover will be displayed in Grid name box.
4. Click OK.
5. Click Yes when SWAT “Do you want to proceed with the LandUse-soil data definition?”
6. Click icon at left of table grid valueàSwat land cover classes. When asked “Load LULC USGS àSwat Land Cover table? Click No.
7. Click OK when *.dbf file (string fields) is shown in the box.
8. Navigate the directory tree c:\Avswat\David\luclass (notice that luclass stores land cover information.) Click OK.
9. Click Apply button next to Reclassify and Clip.

1. Click icon of Soil Grid.
2. Navigate a directory finding the Grid file. Here it is stored at c:\avswat\johnson_iasoil\ia103_30m.
3. Click ia103_30m which stores soil grid information).
4. Click icon of Table Grid Values àSoils attributes.
5. See the next section of this paper on creating the Soils attribute for SSURGO.
6. Navigate the directory tree till find soils attribute file. Here it is located at c:\avswat\johnson_iasoil\ia103_30mtable.dbf.
7. Click ia103_30mtable.dbf, which stores the soil attributes information).
8. Make selection on Name.
9. Click Apply.
10. Click Overlay.

1. Click Watershed in menu bar.
2. Click HRUs Distribution in the pull down menu.
3. Make selection on Multiple Hydrologic Response Units window.
4. Set up 20 in Land Use [%] over Subbasin Area and 10 in Soil Class [%] over Land Use Area.
5. Click OK.

1. Click Watershed in top menu bar.
2. Make selection on Weather Stations from the pull down menu.
3. Select Simulation for both Rainfall Data and Temperature Data. Of course, real rainfall and temperature data are valid options if they are available.
4. Select US data base in Weather simulation data section.
5. Click icon after US data base.
6. Click OK.

1. Click Watershed in top menu bar.
2. Make selection on Ground Water Theme.
3. Select Use Default Values from Definition of the ground water layer panel.
4. Click OK.

1. Click Input Files on top menu bar.
2. Click Watershed configuration file [.fig].
3. Repeat step 1 and click on Soil Data [.sol].
4. Weather Generator data [.wgn]. If Swat complains it could not find IOWAWGN.dbf file, click X on upright conor of watershed window to close swat watershed window. Johnson1021.apr window will pop up. Click tables to list all data tables used in this project. Double click statewgn.dbf and nevigate on column of Station of Statewgn.dbf to find "Iowa city IA" and change it to "IOWA." Now, quit statewgn.dbf windows by click x on up right connor. (This is a local bug, perhaps due to also having a nearby Iowa Falls station.)
5. Click View, then click Watershed and click Weather Generator data again from input file penal.
6. Continue the same steps until all input files have been finished run procedures.

1. Click Edit Input in top menu bar.
2. Select .mgt from the list.
3. Make input on crop budget information following the steps.

After each input file run is finished, Swat is ready to run the simulation. Simply click Simulation on menu bar, click Run Swat. Swat will begin to run until it finishes.

Click Simulation on top menu bar and then click Read Results. Swat will pop up four windows including Output map, Bsb, Rch, and *.apr. If so, this implies that SWAT has run through the simulation successfully. Otherwise, it will report some error messages. For example, in this Johnson county example, SWAT reports following messages after clicking the Read Results from Simulation panel: "Error in Swat1_ReadBsb Monthly at 5396. A(n) Nil Object does not recognize request middle." As a result of this error, SWAT will not be able to generate any output file correctly. To identify what this mistake is about, we recommend users run the simulation model through DOS instead of Interface because very often under DOS environment, SWAT can diagnose some errors and report them on the computer screen, which give you some clues how to tackle the errors.

Type the path of the project such as c:\Avswat992\johnson1118 and then hit Enter key. Then type c:\avswat992\avswatpr\swat992.exe, press Enter. Swat will run the model again through DOS environment. When it finishes, Swat reports following information: "c:\Swat99.2_Av\main.f(46): run-time error F6130: READ(000100005.sol)-invalid REAL Execution trace back: from main program in c:\Swat99.2_Av\main.f(46)." This message shows that the error comes from soil file named "000100005.sol." To fix it, we must first open this file and see what the problem is. Use EDIT command to open this soil file in DOS. We will see that "Number Null" is displayed at the intersection cells between columns 2 and 3, which represents the number of soil layer and row of organic carbon %. SWAT discerns this as an error because the entry should be a number not characters. Give your best estimate for this number and type it in. Then go back to run the model in DOS again. SWAT may report the similar error messages related to the other soils again. If so, make the correction using the same procedures as just discussed. Please notice that, normally, we should not foresee many error messages like this because most soil types stored in usersoil.dbf should be in a right format and provides complete information after the conversion is finished. Thus, if there are many alike soil errors, the user should go back and check the original usersoil.dbf file and the problems may be found over there.

[1] Written by Dr. Erda Wang, Blackland Research Center, Texas A&M; and David Buland, USDA/NRCS/NRIAI

 Back to Top