ODNR Division of Soil and Water Resources - Access Templates

CONTACTS

Mailing Address:
Division of Soil & Water Conservation
2045 Morse Rd
Building B-3
Columbus, OH 43229
Phone (614) 265-6610
FAX: (614) 262-2064

Location Map

For general information about the Division of Soil & Water Conservation, e-mail your questions here.


SWCD logo

 

Download Access Templates

without custom queries

with custom queries
 
Select Template Database as OH Access ****
(dependent on your version of Access)
 
Another place to find soil Access templates
ODNR GIMS - soil data is already loaded in the template upon download. (information may not be as current as data downloaded from the Soil Data Mart)
 
 
 
 
 
Use these Access templates as they are or use them as a guide for creating your own templates.
 
After loading these templates import the tabular data or import the query into your existing template.
 
The Access Templates with customized queries were modified from templates created by the USDA-NRCS. Use these as they are or use them as a guide for creating your own templates.
 
The customized queries in these templates only pull out the most dominant condition for each map unit.
 
These templates must be used with Access 2002 or newer.
 
Why Access templates with customized queries?
 
It eliminates the need to create a Query.  These templates have a custom query included.  Simply load the county soil information and export the information in preparation for joining to the county soil layer in a GIS software application.
 
Already have an Access template with soil data already loaded?
 
Simply import the queries from these templates.
Below are the soil properties and column headings within each customized query. All the soil properties in the queries are for the dominate condition for each mapping unit. 
 

 
Click here for more on this.

Access Templates

Soil_Attributes
Query: 1_Soil_Attributes

Soil Attributes

Column Heading
This Access Template will allow you to symbolize the following soil properties and interpretations in your GIS.

Slope
Hydric Rating
Drainage Class
Hydrologic Group
Control section particle size class
Farmland Class
Highly Erodible Class
Flood Frequency
Bedrock Depth (min)
Water Table Depth (min)

 Slope_r
hydricating
drainagecl
hydgrp
taxpartsize
farmlndcl
muhelcl
floodfreqcl
brockdepmin
wtdepannim
Surface_Texture_OM
Query: 1_Surface_Texture_OM 

Soil Attributes

Column Heading
This template allows you to symbolize soil properties of the surface layer. Clay Percentage
Texture
Organic Matter Percentage		 claytotal_r
texture
om_r
Interpretations
Query: 1_Interpretations

Soil Attributes

Column Heading
  Dwelling with basement suitability
Dwelling without basement suitability
Septic tank absorption field suitability
Sewage lagoon suitability
Local roads/streets suitability
Source for sand for construction material
Path and trail suitability
Forestry, potential erosion hazard (road/trail)		 engdwobdcd
engdwbdcd

engstafdcd

engsldcd
englrsdcd
engcmssdcd

urbrecptdcd
forpehrtdcp
Interp_Log_Landings    
Query: 1_Interp_Log_Landings

Soil Attributes

Column Heading
This query should be used to not only pull out the log landing suitability soil interpretations but should be used as a guide. Number associated with rule
Name of rule
Log landing limitation value
Log Landing limitation

rulekey
rulename
interphr
interhrc 
     
If modified, this query can pull out other interpretations.  To modify this query you must be in the design view and you will need to modify the rule key criteria in order to pull out other interpretations.  See the cointerp table for the reference relating rulekey to its rulename.
Which templates should I download?
  • Soil Data Mart - will have to import data into the template after downloading
  • Soil Data Mart - can download Access template, spatial information and tabular data separately or together
  • ODNR GIMS - soil data is already loaded in the template upon download
  • Customized Queries - queries do not need to be created.

Instructions
Links to detailed instructions
for displaying soil properties
  • Create or Import an Access Query to Obtain Soil Attribute Data
 

screen shot of foldersImport Digital Soils Information into Access

  • First get the Digital Soils information
  • Unzip the downloaded information (if zipped) and you will see a folder called soil_oh*** (*** is the three digit county code) containing the subfolders and files shown to the right.
  • Unzip the soildb_OH_2002.zip file (It may not be 2002 depending on what version was downloaded).
  • Open the Access template called soildb_OH_2002.mdb (or it may be 2000 or 1998 depending on the version you downloaded, which should be the same as the Access version that you will be using) in the soil_oh*** folder.
  • It will prompt you to import data by specifying the directory
  • Go to the directory where the tabular folder resides ( by opening the Tabular folder) and copy the location.
  • Paste the directory in the import window and click OK.
  • It will take awhile for the data to load.
  • After loading, the Soil Reports window will appear.
  • Close the Report and close the Soil Reports window.
 

Create an Access Query to Obtain Soil Attribute Data

  1. In the project window Click on Tables.
  2. Take a look at the databases. (Just to familiarize yourself with them.)
  3. Open the map unit and table and record the number of records that appear (the number will display at the bottom of the window. This number is important! This will be the number of musym you need to have before joining to the spatial layer.
  4. In the project window Click on Queries.
  5. Click on Create Query in Design View.
  6. Next go on to one of the next two sections, depending if you would like to spatially symbolize component and mapunit attribute information or if you would like to symbolize horizon information

For Symbolizing Component and Mapunit Soil Attributes

  1. It will prompt you to add tables, add the component and mapunit tables
  2. Choose the following fields in the component table (by double clicking on the field names): compname and comppct_r, and in the Mapunit table choose musym. These fields are the fields that should always be chosen when querying soil data. The muagg table is another useful table if you are interested in symbolizing certain soil ratings and limitations.
  3. Choose the soil attributes you would like symbolized on a map (there are many properties and interpretations that can be symbolized) some of the more common attributes to be symbolized are the drainagecl, slope_r, and hydricrating fields which can be chosen from the Component table (other tables may need to be added to get other attributes).
  4. Important: under comppct_r in the criteria row type >39. This may need to be modified if the major component comprises of <40 percent of the mapping unit. Check to make sure all mapunit symbols are included after creating the query if not then modify the comppct_r criteria (Hint refer to step 3 under Create an Access Query to Obtain Soil Attribute Data above). 

    The number of records (mapunits) from the mapunit table is the number you need.  If there are to many  mapunits you will have to remove the duplicates and/or change the compct_r column in the query.  Likewise, if there are too few you will have to change the compct_r criteria in the query to capture them all.
  5. Go to the file drop down and save query and specify a name. After saving close the design view window.
  6. You will then see the query among the list of the other queries.
  7. Find and open the added query by double clicking on the newly added query. Go to the File drop down and choose Export. Save in a directory of choice as an Excel file.
  8. Then go to the Preparing Soil Data in Excel for Import Into ArcMap PDF for further directions.

For Symbolizing Horizon Soil Attributes

  1. It will prompt you to add tables add the component, mapunit and chorizon tables. At this point other horizon (ch*) tables can be added such as chashto and chunified as desired.
  2. Choose the following fields in the component table (by double clicking on the field names): compname and comppct_r, and in the Mapunit table choose musym. These fields are the fields that should always be chosen when querying soil data. Then choose the desired fields in the other horizon tables that were added.
  3. Choose the soil attributes (by double clicking an attribute that appears in the list for each table) you would like symbolized on a map (there are many properties and interpretations that can be symbolized)
  4. Important: under comppct_r in the criteria row type >39. This may need to be modified if the major component comprises of <40 percent of the mapping unit. Check to make sure all mapunit symbols are included after creating the query if not then modify the comppct_r criteria (Hint refer to step 3 under Create an Access Query to Obtain Soil Attribute Data above). 

    The number of records (mapunits) from the mapunit table is the number you need.  If there are to many  mapunits you will have to remove the duplicates and/or change the compct_r column in the query.  Likewise, if there are too few you will have to change the compct_r criteria in the query to capture them all.
  5. Go to the file drop down and save query and specify a name. After saving close the design view window.
  6. You will then see the query among the list of the other queries.
  7. Find and open the added query by double clicking on the newly added query. Go to the File drop down and choose Export. Save in a directory of choice as an Excel file.
  8. Then go to the Preparing Soil Data in Excel for Import Into ArcMap PDF for further directions.
 

screen shot of access fileImport an Access Query to Utilize Soil Information

 
Follow these directions after completing the Access Import and when there is a known query that you would like to import.
  • Open the soil Access template. Double click the soil_d_oh***.mdb file Microsoft Access will open.
  • The Soil Reports window will appear, minimize this window.
  • In the project window click on Queries
  • Click the File dropdown and choose Get Externa lData and then click Import.
  • In the Import window browse to the Access file that contains the needed query and double click the file.
  • An Import Objects window will appear, click on the Queries tab.
  • Scroll through the list and find the desired query. Highlight the query, click OK. The query has now been added.
  • Find and open the added query by double clicking on the newly added query.
  • Check the musym column and make sure all the mapunit symbols in the county are present. If they are not you must modify the query in the design view and decrease the percentage criteria in the comppct_r column.
  • Go to the File drop down and choose Export. Save in a directory of choice as an Excel file.
  • Close Access and rename the Access database (so you will know which county database it is).
 

Prepare Soil Data in Excel for Import Into ArcMap

In this section you will have to remove any duplicate mapunit symbols and will check to see if all mapunits are in the database. It will help to look at the mapunit table in the county Access database to get the actual number of map units in the county. Note: If a query is developed that specifies that a mapunit must have an attribute then all mapunits may not be displayed in the query. For example, if you specify that a mapunit must have the first horizon in a query (H1) then all the Udorthents and urbanland will not be displayed. (They do not have an H1.)
  1. Open excel and open the file that was just created in the previous section.
  2. Highlight the musym column. Go to the Data drop down and choose Sort. A Sort Warning window will appear make sure Expand Selection is selected. Click Sort.
  3. Make sure that the musym field is selected under Sort by and Ascending is checked. Click OK. In the figure above, the duplicate entries that are to be deleted are highlighted.
  4. Look at the musym (map unit symbol) column, anywhere there are two or more musyms that are the same, one of the musyms needs to be deleted. Delete the duplicate musym that makes up the lesser component of the mapunit or delete the second musym by selecting its row (by right clicking and deleting). Save file as a .dbf file in the same directory and name the file.
screen shot of excel spreadsheet
 

Add and Join Soil Attribute Information in Arc Map

  1. Open ArcMap click the Add button and navigate to the .dbf file that was created in excel consisting of the mapunits and some of their properties.
  2. Right click the Soil layer and go to Joins and Relates and click on Join.
  3. Select Join attributes from a table and choose the attribute in the table that the join will be based on, which will be MUSYM. Next choose the table that will be joined which will be (the .dbf created and added earlier) and then choose the field in this table that the join will be based on musym. Click OK.
  4. A Create Index window will appear click Yes.
  5. Open the attribute table of soilmu_a_oh*** (soil map unit layer) and you will see that the new information from the table was added.
  6. To open the attribute table Right click the layer and click Open Attribute Table.