Updating ssurgo data , 16 August 2012

Table Description


I made a list of all the tables that we are using now and a brief description of them based on http://soildatamart.nrcs.usda.gov/documents/SSURGO%20Metadata%20-%20Tables%20and%20Columns%20Report.pdf
component
The Component table lists the map unit components identified in the referenced map unit, and selected properties of each component. If the Component % is greater than zero (low=65, RV=75, high=90) for a component, that component exists in every delineation of that mapunit. If the Component % includes zero (low=0, RV=50, high=90), the component may exist in some delineations, but not in others.
mapunit
The Mapunit table identifies the map units included in the referenced legend.
legend
The Legend table identifies the soil survey area that the legend is related to, and related information about that legend.
chorizon
The Horizon table lists the horizon(s) and related data for the referenced map unit component. If the horizon thickness is greater than zero (low=5, RV=8, high=12), the horizon exists everywhere this component occurs. If the horizon thickness includes zero (low=0, RV=1, high=3), the horizon may exist in some places, but not in other places. Horizons that have two distinct parts, such as E/B or E&Bt horizons, are recorded twice. Once for the characteristics of the first part; and again on another row, using the same depths and thicknesses, for the characteristics of the other part.
muaggatt
The Mapunit Aggregated Attribute table records a variety of soil attributes and interpretations that have been aggregated from the component level to a single value at the map unit level. They have been aggregated by one or more appropriate means in order to express a consolidated value or interpretation for the map unit as a whole.
chtexture
The Horizon Texture table lists the individual texture(s), or term(s) used in lieu of texture, for the referenced horizon. Only the unmodified texture terms are listed in the Horizon Texture table; modifiers are listed in the Horizon Texture Modifier table. For example, a gravelly loamy sand is shown as “GR-LS” in the Horizon Texture Group table, “ls” in the Horizon Texture table, and “gr” in the Horizon Texture Modifier table.
chtexturegrp
The Horizon Texture Group table lists the range of textures for the referenced horizon as a concatenation of horizon texture and texture modifier(s). For example, a horizon that is gravelly loamy sand in some places and gravelly loamy coarse sand in other places is shown as GR-LS on one row and GR-LCOS on another row in this table. The row with the typically occurring texture is identified as the RV row. Stratified textures are shown in one row. For example, a horizon that is stratified gravelly loamy fine sand and cobbly coarse sand is shown as SR- GR-LFS CB-COS on one row and the Stratified? column for that row is marked “yes”. If two or more textures always occur together but are not stratified, all of the textures are listed on one row and the Stratified? column for that row is marked “no”.
chtexturemod
The Horizon Texture Modifier table lists the texture modifier(s) for the referenced texture. For example, a gravelly loamy sand is shown as “GR-LS” in the Horizon Texture Group table, “ls” in the Horizon Texture table, and “gr” in this table.
chpores
The Horizon Pores table lists the voids for the referenced horizon. If the Quantity is greater than zero (low=2, RV=5, high=10) in a row, the voids in that row exist everywhere the horizon and component occur in the map unit. If the Quantity includes zero (low=0, RV=2, high=5), the voids may exist in some places, but not in others. More than one row can be marked as an RV row because a horizon may have more than one size or shape of void.
chstruct
The Horizon Structure table lists the individual soil structure size, grade, and shape terms for the referenced horizon. Terms in this table are assembled into a structure group string which is recorded in the Horizon Structure Group table.
chstructgrp
The Horizon Structure Group table lists the ranges of soil structure for the referenced horizon. The row with the typically occurring structure is marked as being representative. The entry in this table is based on grouping of entries in the Horizon Structure table.
chconsistence
The Horizon Consistence table contains descriptive terms of soil consistence – rupture resistance, plasticity, and stickiness – for the referenced horizon. One row in this table is marked as having the representative characteristics for the horizon.
chfrags
The Horizon Fragments table lists the mineral and organic fragments that generally occur in the referenced horizon. If the Volume % is greater than zero (low=5, RV=10, high=15) in a row, the kind and size of fragment in that row exists everywhere this horizon and component occur in the map unit. If the Volume % includes zero (low=0, RV=5, high=10), the kind and size of fragment may exist in some places, but not in others.
cocropyld
The Component Crop Yield table lists commonly grown crops and their expected range in yields when grown on the referenced map unit component. Yields for the map unit as a whole are given in the Mapunit Crop Yield table.

Notes about the Databases
  • There are two temporary tables that we don't need to keep the values of them. TComp2 and TComp3 are just storing more fields of component table rather than either TComp11, TComp12 and TComp13. Therefore we can add those fields to these three tables and therefore:
    • eliminate the process of making TComp2 and TComp3
    • make the LTC Query simpler.
  • In the final table(TTC) we store some of the features once for a map unit and once for the 3 most used components of that map unit. For instance, we can name areaname and the percentage of different kind of crops(Oats, Soybeans, Wheat,...).
The Essential Fields


So far we know the fields that we want on the final table from the following tables(we are trying to reduce the current final table by examining the essential columns to keep and drop the rest). Please note that the table is getting updated dynamically by information that we get from IEPA.

# Table Required Fields
component No Information Provided Yet
mapunit muhelcl, muname, musym, muacres, farmlndcl, invesintens
legend areasymbol
chorizon kffact
muaggatt drclassdcd, flodfreqdcd, hydclprs
chtexture No Information Provided Yet
chtexturegrp No Information Provided Yet
chtexturemod No Information Provided Yet
chpores No Information Provided Yet
chstruct No Information Provided Yet
chstructgrp No Information Provided Yet
chconsistence No Information Provided Yet
chfrags No Information Provided Yet
cocropyld No Information Provided Yet


The Store Procedures


  • The store procedures to merge the data from different ssurgo databases(based on the county) are on server 11 under a database called qmars. You can make the final file by running dbo.RUN_ALL.
Future challenges


  • Make the store procedures dedicated to the fields that are going to be determined by IEPA.
  • Investigate the process of converting initial access files to the SQL Server file by using FME provided technologies.

Migration to ADF 10.0