**National Soil Information System**

**WIN-PST Data Generation**

**As-Built Documentation**

######### June 2001

United States Department of Agriculture

Natural Resources Conservation Service

Information Technology Center

2150 Centre Avenue, Building A

Fort Collins, CO 80526-1891

\(970\) 295-5464

######### NASIS Project

Project Manager: Ken Harward

Soil Survey Business Area Specialist: Terry Aho

Technical Project Manager: Gary Spivak

Systems Analyst: Dorn Egley

[Introduction 5](#introduction)

[MS Access Soil Database WIN-PST Interface and Processes
5](#ms-access-soil-database-win-pst-interface-and-processes)

[WIN-PST Create Data Macro/Process 6](#win-pst-create-data-macroprocess)

[WIN-PST Export Data Macro/Process 6](#win-pst-export-data-macroprocess)

[WIN-PST Data Attributes and Derivations
6](#win-pst-data-attributes-and-derivations)

[Known Potential FOCS Export/WIN-PST Value Inconsistencies
6](#known-potential-focs-exportwin-pst-value-inconsistencies)

[State and Soil Survey Area ID 6](#state-and-soil-survey-area-id)

[Component Sequence Number 7](#component-sequence-number)

[Surface Horizon 7](#surface-horizon)

[Surface Texture 7](#surface-texture)

[Rock Depth Related Attributes 7](#rock-depth-related-attributes)

[Water Table Related Attributes 8](#water-table-related-attributes)

[WIN-PST Attribute Details 8](#win-pst-attribute-details)

# Introduction

In June of 2001 the ITC NASIS Development Staff was instructed to
implement a procedure that would create a set of data to be used as
input to the WIN-PST application. The WIN-PST application is being
developed under the auspices of Eric Hesketh of the NRCS National Water
and Climate Center. We agreed that this data set would be generated by
functions that would reside in the same MS Access soil database that is
used by the NRCS Soil Data Viewer application. In order for data to
ultimately be available to WIN-PST, a SSURGO export would have to be
generated, and that data would then have to be imported into the
template MS Access soil database that is used in an NRCS field offices.
Subsequent to that, the functions that generate the WIN-PST data would
then be executed.

Exactly what data should be included in this data set was negotiated
between Eric Hesketh and members of the ITC NASIS Development staff and
members of the National Soil Survey Center Interpretations staff. Eric
provided a file in DBF format that represented the set of attributes
that WIN-PST required. The majority of these attributes had direct
counterparts in NASIS. After negotiations, some of the attributes
requested by Eric were not included because it made more sense for those
attributes to be created in the WIN-PST application.

The format of the data needed by the WIN-PST application corresponds to
the obsolete SSSD data model. The NASIS data model superseded the SSSD
data model. Most of the attributes needed by the WIN-PST application
exist in the SSSD "comp" table. NASIS does include a FOCS export process
that produces an export corresponding to the obsolete SSSD format. The
existing FOCS export process could not be used to fulfill the WIN-PST
needs for several reasons. The primary reason is that it would not be
efficient to ask a soil data quality specialist to generate both a FOCS
export and a SSURGO export for each NRCS field office or service center
in their domain.

Since the data needed by the WIN-PST application is in SSSD format, this
means that the functions added to the MS Access soil database in order
to generate this data are going to be performing many of the same
transformations performed by the NASIS FOCS export process. To the
degree possible, the new functions have been designed to generate the
same values that would be generated by the FOCS export process.
Ultimately, compromises had to be made. Details about why data generated
by the new functions may not be consistent with data generated by a FOCS
export will be provided in later sections.

# MS Access Soil Database WIN-PST Interface and Processes

Functions were added to the MS Access soil database to generate, or
generate and export, WIN-PST data on demand. Another option would have
been to generate WIN-PST data whenever data is imported into the
database, and then export that data on demand. This option was rejected
due to concerns about the total amount of time that would be required to
import and generate data in a single step.

To facilitate the creation of data for WIN-PST, a new table was added to
the database: "SYSTEM -- WIN-PST Data". The reason for the prefix
"SYSTEM -- " in the table name is due to a convention used to
distinguish tables that are part of the SSURGO standard from other
database tables. Whenever the user initiates the generation of WIN-PST
data, the current contents of this table is deleted, and this table is
then repopulated based on ALL data that currently resides in the
database.

The user cannot select which legends or components should be included.
Eric indicated that including components of minor extent probably wasn't
very useful, but he seemed content with letting the person who
originally created the SSURGO export decide which components should be
included.

WIN-PST data includes data for all components that were included in a
SSURGO export. A FOCS export may include no more than three components
per map unit. In addition, there is a complex algorithm for deciding
which components should be included in a FOCS export. This algorithm is
based on the sequence number column in the component table. The value of
this column is not even included in a SSURGO export. All of this means
that WIN-PST data may include components not found in a corresponding
FOCS export, and vice versa.

## WIN-PST Create Data Macro/Process

In the Access soil database, the macro named "WIN-PST Create Data"
deletes any existing records from the table named "SYSTEM -- WIN-PST
Data", and then regenerates the WIN-PST data based on all legends and
components that currently reside in the database. This macro invokes an
underlying function by the name of "WinPst_Create_Data". The WIN-PST
Create Data macro does not ask the user for any input.

## WIN-PST Export Data Macro/Process

In the Access soil database, the macro named "WIN-PST Export Data" first
generates WIN-PST data, and then exports that data to the directory
specified by the user. The export currently creates both an ASCII pipe
delimited text file and a file in dBase IV format (actually two files,
one data file and one index file, at least I think the second file is an
index file). The ASCII pipe delimited file is named "wpsoidat.txt". The
dBase IV files are named "WPSOIDAT.DBF" nad "WPSOIDAT.MDX"

Invoking this macro immediately displays a form where the user is asked
to specify the name of the directory in which the export file should be
created. At this time, the user must manually enter a complete directory
specification. This is a bit of a pain, but the developer doesn't know
how to present a directory selection widget in Access.

After the user enters a directory specification and then clicks the OK
button, an underlying function by the name of "WinPst_Export" is
invoked. This function ensures that a valid, writeable directory
specification was entered. If the directory specification is valid, the
form is closed and data is then generated and exported. If the directory
specification is invalid, an error dialog is displayed. The form is left
open for the user to reenter the directory specification after the error
dialog is closed.

# WIN-PST Data Attributes and Derivations

All attributes required for WIN-PST are contained in a single record. A
WIN-PST data record logically corresponds to a map unit component. While
a WIN-PST record corresponds to a map unit component, that record does
include surface horizon attributes.

There is one idiosyncrasy concerning hydrologic groups. If a source
component does not have a dual hydrologic group, a single WIN-PST record
is created. If a source component has a dual hydrologic group, two
WIN-PST records are created, one for each hydrologic group. When two
records are created for the same component, due to a dual hydrologic
group, those records will also have the same value for "seqnum"
(component sequence number).

## Known Potential FOCS Export/WIN-PST Value Inconsistencies

To the degree possible, the WIN-PST data generation was designed to
produce values consistent with those generated by a FOCS export of the
same underlying data. Complicating this goal is the fact that the new
data generation functions are operating on data that has already been
exported from NASIS, and not everything that was available to the FOCS
export process is still available.

### State and Soil Survey Area ID

The FOCS export is restricted to soil survey areas that represent a
traditional non-MLRA soil survey area. The WIN-PST data generation
functions in the MS Access soil database will create data regardless of
the type of underlying soil survey area. However, if the underlying soil
survey area is not a traditional non-MLRA soil survey area, "state" will
be set to "??"and "ssaid" will be set to "???".

### Component Sequence Number

The FOCS export process uses the sequence number column in the component
table for both selecting which components should be included in the
export, and for providing the value for the column "seqnum". When no
sequence numbers are available in NASIS, the FOCS export process creates
them. The data in the MS Access soil database is imported from a SSURGO
export. A SSURGO export does not include a component sequence number
column. The new WIN-PST data generation functions must always generate a
value for "seqnum", and there is no way that the values can be made
consistent with those found in a FOCS export of the same underlying
data.

### Surface Horizon

The WIN-PST data contains a number of attributes that represent
attributes of the surface horizon. The surface horizon is defined as a
horizon record where RV depth to top is 0. If more than one such record
is found for a component, no surface horizon attribute values are
returned, but the rest of the WIN-PST data is computed. The FOCS export
fails completely if more than one horizon record has an RV depth to top
of 0. In fact, the FOCS export fails under any of the following
conditions:

1\) horizon sequence number are used but they are not in sequence
starting with 1.

2\) the RV depth to top of the first horizon is not 0.

3\) for any horizon, RV depth to top is greater than RV depth to bottom.

4\) any two horizons overlap, based on RV depth to top and bottom.

5\) there is a gap between any two horizons, based on RV depth to top
and bottom

The only check performed by the WIN-PST data generation function is
whether or not there is only one horizon whose RV depth to top is 0, and
even if that test fails, the function will create a record for that
component. It just won't contain any surface horizon attribute values.

Terry Aho reminds me that neither the FOCS export nor the proposed
procedures for generating data for WIN-PST, address the issue of organic
surface horizons. In both cases, the surface horizon is defined as the
horizon whose RV depth to top is 0, regardless of whether or not that
horizon is an organic horizon.

### Surface Texture

In determining the surface texture, the FOCS export selects the "first"
texture group record where the RV flag is set, or in lieu of no RV flags
being set, selects the "first" texture group record, period. In either
case no "order by" clause is used when selecting the data. Therefore
when the same data is selected in the MS Access soil database, there is
no guarantee that the same "first" record will be selected, since
"first" is merely the first record that happens to be returned by the
underlying query. In the MS Access soil database, the record with the
minimum key value is always selected, but this doesn't address the FOCS
consistency issue. It merely ensures that the same texture group record,
for a given component of a given SSURGO export, will always be selected.

### Rock Depth Related Attributes

In determining rock depth, the FOCS export favors component restriction
sequence numbers over actual depth to top low values. The SSURGO data
does not include any of the generic table sequence number columns that
exist in NASIS. The new WIN-PST data generation functions determine rock
depth based exclusively on depth to top low values, which must be not
null in order for a record to even be considered.

### Water Table Related Attributes

Transforming the NASIS view of water table related attributes back into
the SSSD view of these same attributes is the single most complicated
aspect of reverse engineering data for WIN-PST. As always, to the degree
possible, every attempt was made to be consistent with how things were
done in the FOCS export. The problem is that this particular section of
FOCS export perl code is so intensive, that the confidence that things
were done in exactly the same manner is lower than usual. Areas where we
are not sure that absolute consistency was achieved have to do with what
happens when there are multiple records for a given month (a data
problem that should be prevented in NASIS but is not) and what period is
selected when there is more than one longest period of the same length.
See the WIN-PST Attribute details section for the detailed logic on how
the water table information was reverse engineered.

One area where we made a conscious decision to be different has to do
when the longest period with a water table has a mixture of perched and
apparent water tables. As near as I can tell, when this occurs in a FOCS
export, "perched" is returned. Terry Aho and Eric Hesketh concur that
when this same situation occurs when generating data for WIN-PST,
"apparent" should be returned. They explain that this choice represents
the more restrictive selection, as far as the intention of WIN-PST is
concerned.

## WIN-PST Attribute Details

The following table describes the WIN-PST attributes that are generated,
in detail. Below is a definition of the attributes associated with each
of the WIN-PST attributes that are generated. The name of the table used
to record the WIN-PST attributes is "SYSTEM -- WIN-PST Data". This table
has no unique constraints or primary key.

Access Column Sequence -- the sequence of a column in the WIN-PST data
table in the MS Access database.

Access Column Name -- the name of a column in the WIN-PST data table in
the MS Access database.

Access Data Type -- the physical data type of a column in the WIN-PST
data table in the MS Access database.

Domain -- the valid value choices for a column in the WIN-PST data table
in the MS Access database.

Source NASIS Table -- the physical name of the NASIS table containing
the underlying data used to derive the value of this attribute.

Source NASIS Column -- the physical name of the column or columns used
to derive the value of this attribute.

Derivation -- the algorithm used to derive the value of this attribute.

Unit of Measure -- the unit of measure associated with this attribute,
if any.

+----+----+------+------+-----+---------+-----------------------+----+
| Ac | Ac | Ac   | Do   | Sou | Source  | Derivation            | Un |
| ce | ce | cess | main | rce | NASIS   |                       | it |
| ss | ss | Data |      | NA  | Column  |                       | of |
| Co | Co | Type |      | SIS |         |                       | M  |
| lu | lu |      |      | Ta  |         |                       | ea |
| mn | mn |      |      | ble |         |                       | su |
| Se | Na |      |      |     |         |                       | re |
| qu | me |      |      |     |         |                       |    |
| en |    |      |      |     |         |                       |    |
| ce |    |      |      |     |         |                       |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 1  | s  | tex  |      | a   | are     | If the legend         |    |
|    | ta | t(2) |      | rea | asymbol | represents a          |    |
|    | te |      |      |     |         | traditional non-MLRA  |    |
|    |    |      |      |     |         | soil suvery area, set |    |
|    |    |      |      |     |         | to the first two      |    |
|    |    |      |      |     |         | characters of the     |    |
|    |    |      |      |     |         | area symbol,          |    |
|    |    |      |      |     |         | otherwise set to      |    |
|    |    |      |      |     |         | \"??\".               |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 2  | s  | tex  |      | a   | are     | If the legend         |    |
|    | sa | t(3) |      | rea | asymbol | represents a          |    |
|    | id |      |      |     |         | traditional non-MLRA  |    |
|    |    |      |      |     |         | soil suvery area, set |    |
|    |    |      |      |     |         | to the last three     |    |
|    |    |      |      |     |         | characters of the     |    |
|    |    |      |      |     |         | area symbol,          |    |
|    |    |      |      |     |         | otherwise set to      |    |
|    |    |      |      |     |         | \"???\".              |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 3  | s  | t    |      | a   | a       | Set to soil survey    |    |
|    | sa | ext( |      | rea | reaname | area name.            |    |
|    | na | 135) |      |     |         |                       |    |
|    | me |      |      |     |         |                       |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 4  | m  | tex  |      | m   | musym   | Set to map unit       |    |
|    | us | t(6) |      | apu |         | symbol.               |    |
|    | ym |      |      | nit |         |                       |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 5  | se | num  |      |     |         | A map unit\'s         |    |
|    | qn | ber( |      |     |         | components are sorted |    |
|    | um | inte |      |     |         | by RV percent         |    |
|    |    | ger) |      |     |         | composition           |    |
|    |    |      |      |     |         | (descending) and      |    |
|    |    |      |      |     |         | component name        |    |
|    |    |      |      |     |         | (ascending). Sequence |    |
|    |    |      |      |     |         | number is assigned    |    |
|    |    |      |      |     |         | sequentially starting |    |
|    |    |      |      |     |         | with one. The         |    |
|    |    |      |      |     |         | sequence numbers that |    |
|    |    |      |      |     |         | exist in NASIS are    |    |
|    |    |      |      |     |         | not included in a     |    |
|    |    |      |      |     |         | SSURGO export,        |    |
|    |    |      |      |     |         | therefore they must   |    |
|    |    |      |      |     |         | be reassigned from    |    |
|    |    |      |      |     |         | scratch, which means  |    |
|    |    |      |      |     |         | that the numbers      |    |
|    |    |      |      |     |         | assigned by the       |    |
|    |    |      |      |     |         | WIN-PST export        |    |
|    |    |      |      |     |         | process may not       |    |
|    |    |      |      |     |         | correspond to the     |    |
|    |    |      |      |     |         | values found in       |    |
|    |    |      |      |     |         | NASIS.                |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | When a component has  |    |
|    |    |      |      |     |         | a dual hydrologic     |    |
|    |    |      |      |     |         | group, 2 records are  |    |
|    |    |      |      |     |         | created, one for each |    |
|    |    |      |      |     |         | hydrologic group.     |    |
|    |    |      |      |     |         | These two records     |    |
|    |    |      |      |     |         | share the same value  |    |
|    |    |      |      |     |         | for \"seqnum\".       |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 6  | co | text |      | com | c       | Set to component      |    |
|    | mp | (60) |      | pon | ompname | name.                 |    |
|    | na |      |      | ent |         |                       |    |
|    | me |      |      |     |         |                       |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 7  | s  | text | Re   | cht | texture | If there are no       |    |
|    | ur | (14) | pres | ext |         | horizon texture group |    |
|    | ft |      | ents | ure |         | records, set to null. |    |
|    | ex |      | p    | grp |         |                       |    |
|    |    |      | arts |     |         | If there are any      |    |
|    |    |      | from |     |         | horizon texture group |    |
|    |    |      | 3    |     |         | records where the RV  |    |
|    |    |      | doma |     |         | flag is set, set to   |    |
|    |    |      | ins, |     |         | the first texture     |    |
|    |    |      | soil |     |         | (and associated       |    |
|    |    |      | tex  |     |         | modifier(s)) in the   |    |
|    |    |      | ture |     |         | concatenated texture  |    |
|    |    |      | cl   |     |         | string of the record  |    |
|    |    |      | ass, |     |         | with the lowest key   |    |
|    |    |      | t    |     |         | value.                |    |
|    |    |      | erms |     |         |                       |    |
|    |    |      | used |     |         | If there are no       |    |
|    |    |      | in   |     |         | horizon texture group |    |
|    |    |      | lieu |     |         | records where the RV  |    |
|    |    |      | of   |     |         | flag is set, set to   |    |
|    |    |      | tex  |     |         | the first texture     |    |
|    |    |      | ture |     |         | (and associated       |    |
|    |    |      | and  |     |         | modifier(s)) in the   |    |
|    |    |      | tex  |     |         | concatenated texture  |    |
|    |    |      | ture |     |         | string of the record  |    |
|    |    |      | mo   |     |         | with the lowest key   |    |
|    |    |      | difi |     |         | value.                |    |
|    |    |      | ers. |     |         |                       |    |
|    |    |      | Full |     |         | When necessary, the   |    |
|    |    |      | dom  |     |         | stratified prefix     |    |
|    |    |      | ains |     |         | (SR-) is ignored.     |    |
|    |    |      | are  |     |         |                       |    |
|    |    |      | too  |     |         | Note that this is an  |    |
|    |    |      | e    |     |         | instance where        |    |
|    |    |      | xten |     |         | results may not be    |    |
|    |    |      | sive |     |         | consistent with those |    |
|    |    |      | to   |     |         | seen in a FOCS export |    |
|    |    |      | list |     |         | of the same data,     |    |
|    |    |      | h    |     |         | since the FOCS export |    |
|    |    |      | ere. |     |         | uses no Order By      |    |
|    |    |      |      |     |         | clause in retrieving  |    |
|    |    |      |      |     |         | horizon texture group |    |
|    |    |      |      |     |         | records.              |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 8  | c  | num  |      | com | co      | Set to RV percent     | p  |
|    | om | ber( |      | pon | mppct_r | composition. Note     | er |
|    | pp | inte |      | ent |         | that an RV percent    | ce |
|    | ct | ger) |      |     |         | composition IS NOT    | nt |
|    |    |      |      |     |         | computed when the     |    |
|    |    |      |      |     |         | existing value is     |    |
|    |    |      |      |     |         | null, due in part to  |    |
|    |    |      |      |     |         | the fact that which   |    |
|    |    |      |      |     |         | components are        |    |
|    |    |      |      |     |         | selected for          |    |
|    |    |      |      |     |         | inclusion in the      |    |
|    |    |      |      |     |         | export are based on   |    |
|    |    |      |      |     |         | RV percent            |    |
|    |    |      |      |     |         | compostion, to begin  |    |
|    |    |      |      |     |         | with. This is         |    |
|    |    |      |      |     |         | consistent with the   |    |
|    |    |      |      |     |         | logic used by the     |    |
|    |    |      |      |     |         | FOCS export process.  |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 9  | sl | num  |      | com | slope_l | Set to slope gradient | p  |
|    | op | ber( |      | pon |         | low.                  | er |
|    | el | inte |      | ent |         |                       | ce |
|    |    | ger) |      |     |         |                       | nt |
+----+----+------+------+-----+---------+-----------------------+----+
| 10 | sl | num  |      | com | slope_h | Set to slope gradient | p  |
|    | op | ber( |      | pon |         | high.                 | er |
|    | eh | inte |      | ent |         |                       | ce |
|    |    | ger) |      |     |         |                       | nt |
+----+----+------+------+-----+---------+-----------------------+----+
| 11 | wt | nu   |      | co  | soimois | Set water table depth | fe |
|    | de | mber |      | soi | tdept_l | low to the minimum    | et |
|    | pl | (sin |      | lmo |         | soil moisture depth   |    |
|    |    | gle) |      | ist |         | to top (low) / 30.48  |    |
|    |    |      |      |     |         | (cm to ft) in the     |    |
|    |    |      |      |     |         | selected longest      |    |
|    |    |      |      |     |         | period.               |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 12 | wt | nu   |      | co  | soimois | Set water table depth | fe |
|    | de | mber |      | soi | tdept_h | high to the maximum   | et |
|    | ph | (sin |      | lmo |         | soil moisture depth   |    |
|    |    | gle) |      | ist |         | to top (high) / 30.48 |    |
|    |    |      |      |     |         | (cm to ft) in the     |    |
|    |    |      |      |     |         | selected longest      |    |
|    |    |      |      |     |         | period.               |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 13 | wt | tex  | AP   | co  | soimo   | If all months in the  |    |
|    | ki | t(5) | PAR, | soi | iststat | selected longest      |    |
|    | nd | (c   | P    | lmo |         | period have a perched |    |
|    |    | ode) | ERCH | ist |         | water table, set      |    |
|    |    |      |      |     |         | water table kind to   |    |
|    |    |      |      |     |         | "PERCH" (perched),    |    |
|    |    |      |      |     |         | otherwise set water   |    |
|    |    |      |      |     |         | table kind to "APPAR" |    |
|    |    |      |      |     |         | (apparent). A month   |    |
|    |    |      |      |     |         | has a perched water   |    |
|    |    |      |      |     |         | table when a soil     |    |
|    |    |      |      |     |         | moisture layer whose  |    |
|    |    |      |      |     |         | status IS NOT either  |    |
|    |    |      |      |     |         | "Wet" or "Saturation  |    |
|    |    |      |      |     |         | from capillary        |    |
|    |    |      |      |     |         | fringe" exists under  |    |
|    |    |      |      |     |         | a soil moisture layer |    |
|    |    |      |      |     |         | whose status IS "Wet" |    |
|    |    |      |      |     |         | or "Saturation from   |    |
|    |    |      |      |     |         | capillary fringe",    |    |
|    |    |      |      |     |         | based solely on RV    |    |
|    |    |      |      |     |         | soil moisture depth   |    |
|    |    |      |      |     |         | to top. Note that, as |    |
|    |    |      |      |     |         | far as I can tell,    |    |
|    |    |      |      |     |         | this differs from the |    |
|    |    |      |      |     |         | FOCS export in that   |    |
|    |    |      |      |     |         | when the FOCS export  |    |
|    |    |      |      |     |         | finds a longest       |    |
|    |    |      |      |     |         | period with a mixed   |    |
|    |    |      |      |     |         | kind of water table,  |    |
|    |    |      |      |     |         | it returns "perched"  |    |
|    |    |      |      |     |         | rather than           |    |
|    |    |      |      |     |         | "apparent". Both      |    |
|    |    |      |      |     |         | Terry Aho and Eric    |    |
|    |    |      |      |     |         | Hesketh concur that   |    |
|    |    |      |      |     |         | in such a case, as    |    |
|    |    |      |      |     |         | far as WIN-PST is     |    |
|    |    |      |      |     |         | concerned, it makes   |    |
|    |    |      |      |     |         | more sense to return  |    |
|    |    |      |      |     |         | "apparent", since     |    |
|    |    |      |      |     |         | that represents the   |    |
|    |    |      |      |     |         | more restrictive case |    |
|    |    |      |      |     |         | in regards to         |    |
|    |    |      |      |     |         | pesticide             |    |
|    |    |      |      |     |         | application.          |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | Note that in          |    |
|    |    |      |      |     |         | determining water     |    |
|    |    |      |      |     |         | table kind, only      |    |
|    |    |      |      |     |         | records where both    |    |
|    |    |      |      |     |         | water table depth to  |    |
|    |    |      |      |     |         | top low and RV water  |    |
|    |    |      |      |     |         | table depth to top    |    |
|    |    |      |      |     |         | are not null, are     |    |
|    |    |      |      |     |         | considered.           |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 14 | w  | tex  | JAN, | c   | month   | The beginning and     |    |
|    | tb | t(3) | FEB, | omo |         | ending water table    |    |
|    | eg | (c   | MAR, | nth |         | period is determined  |    |
|    |    | ode) | APR, |     |         | as follows.           |    |
|    |    |      | MAY, |     |         |                       |    |
|    |    |      | JUN, |     |         | 1\. Identify all      |    |
|    |    |      | JUL, |     |         | periods of contiguous |    |
|    |    |      | AUG, |     |         | months with a water   |    |
|    |    |      | SEP, |     |         | table, i.e. where     |    |
|    |    |      | OCT, |     |         | there is a soil       |    |
|    |    |      | NOV, |     |         | moisture record where |    |
|    |    |      | DEC  |     |         | moisture status is    |    |
|    |    |      |      |     |         | either "Wet" or       |    |
|    |    |      |      |     |         | "Saturation from      |    |
|    |    |      |      |     |         | capillary fringe". If |    |
|    |    |      |      |     |         | multiple records for  |    |
|    |    |      |      |     |         | the same month are    |    |
|    |    |      |      |     |         | encountered, use the  |    |
|    |    |      |      |     |         | record with the       |    |
|    |    |      |      |     |         | minimum key value.    |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | 2\. Select the period |    |
|    |    |      |      |     |         | of longest length.    |    |
|    |    |      |      |     |         | Set water table       |    |
|    |    |      |      |     |         | begins to the first   |    |
|    |    |      |      |     |         | month in that period, |    |
|    |    |      |      |     |         | and set water table   |    |
|    |    |      |      |     |         | ends to the last      |    |
|    |    |      |      |     |         | month in that period. |    |
|    |    |      |      |     |         | If more than one      |    |
|    |    |      |      |     |         | period of the same    |    |
|    |    |      |      |     |         | longest length in     |    |
|    |    |      |      |     |         | months is found,      |    |
|    |    |      |      |     |         | select the period     |    |
|    |    |      |      |     |         | that begins closest   |    |
|    |    |      |      |     |         | to January. If a      |    |
|    |    |      |      |     |         | period spans December |    |
|    |    |      |      |     |         | and January, the      |    |
|    |    |      |      |     |         | start of that period  |    |
|    |    |      |      |     |         | is considered to be   |    |
|    |    |      |      |     |         | the first month in    |    |
|    |    |      |      |     |         | that period earlier   |    |
|    |    |      |      |     |         | than December. If a   |    |
|    |    |      |      |     |         | water table occurs    |    |
|    |    |      |      |     |         | for all 12 months,    |    |
|    |    |      |      |     |         | set water table       |    |
|    |    |      |      |     |         | begins to "JAN" and   |    |
|    |    |      |      |     |         | water table ends to   |    |
|    |    |      |      |     |         | "DEC".                |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | Note that in          |    |
|    |    |      |      |     |         | determining which     |    |
|    |    |      |      |     |         | months have a water   |    |
|    |    |      |      |     |         | table, only records   |    |
|    |    |      |      |     |         | where both water      |    |
|    |    |      |      |     |         | table depth to top    |    |
|    |    |      |      |     |         | low and RV water      |    |
|    |    |      |      |     |         | table depth to top    |    |
|    |    |      |      |     |         | are not null, are     |    |
|    |    |      |      |     |         | considered.           |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 15 | w  | tex  | JAN, | c   | month   | The beginning and     |    |
|    | te | t(3) | FEB, | omo |         | ending water table    |    |
|    | nd | (c   | MAR, | nth |         | period is determined  |    |
|    |    | ode) | APR, |     |         | as follows.           |    |
|    |    |      | MAY, |     |         |                       |    |
|    |    |      | JUN, |     |         | 1\. Identify all      |    |
|    |    |      | JUL, |     |         | periods of contiguous |    |
|    |    |      | AUG, |     |         | months with a water   |    |
|    |    |      | SEP, |     |         | table, i.e. where     |    |
|    |    |      | OCT, |     |         | there is a soil       |    |
|    |    |      | NOV, |     |         | moisture record where |    |
|    |    |      | DEC  |     |         | moisture status is    |    |
|    |    |      |      |     |         | either "Wet" or       |    |
|    |    |      |      |     |         | "Saturation from      |    |
|    |    |      |      |     |         | capillary fringe". If |    |
|    |    |      |      |     |         | multiple records for  |    |
|    |    |      |      |     |         | the same month are    |    |
|    |    |      |      |     |         | encountered, use the  |    |
|    |    |      |      |     |         | record with the       |    |
|    |    |      |      |     |         | minimum key value.    |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | 2\. Select the period |    |
|    |    |      |      |     |         | of longest length.    |    |
|    |    |      |      |     |         | Set water table       |    |
|    |    |      |      |     |         | begins to the first   |    |
|    |    |      |      |     |         | month in that period, |    |
|    |    |      |      |     |         | and set water table   |    |
|    |    |      |      |     |         | ends to the last      |    |
|    |    |      |      |     |         | month in that period. |    |
|    |    |      |      |     |         | If more than one      |    |
|    |    |      |      |     |         | period of the same    |    |
|    |    |      |      |     |         | longest length in     |    |
|    |    |      |      |     |         | months is found,      |    |
|    |    |      |      |     |         | select the period     |    |
|    |    |      |      |     |         | that begins closest   |    |
|    |    |      |      |     |         | to January. If a      |    |
|    |    |      |      |     |         | period spans December |    |
|    |    |      |      |     |         | and January, the      |    |
|    |    |      |      |     |         | start of that period  |    |
|    |    |      |      |     |         | is considered to be   |    |
|    |    |      |      |     |         | the first month in    |    |
|    |    |      |      |     |         | that period earlier   |    |
|    |    |      |      |     |         | than December. If a   |    |
|    |    |      |      |     |         | water table occurs    |    |
|    |    |      |      |     |         | for all 12 months,    |    |
|    |    |      |      |     |         | set water table       |    |
|    |    |      |      |     |         | begins to "JAN" and   |    |
|    |    |      |      |     |         | water table ends to   |    |
|    |    |      |      |     |         | "DEC".                |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | Note that in          |    |
|    |    |      |      |     |         | determining which     |    |
|    |    |      |      |     |         | months have a water   |    |
|    |    |      |      |     |         | table, only records   |    |
|    |    |      |      |     |         | where both water      |    |
|    |    |      |      |     |         | table depth to top    |    |
|    |    |      |      |     |         | low and RV water      |    |
|    |    |      |      |     |         | table depth to top    |    |
|    |    |      |      |     |         | are not null, are     |    |
|    |    |      |      |     |         | considered.           |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 16 | hy | tex  | A,   | com | hydgrp  | If a component does   |    |
|    | dg | t(1) | B,   | pon |         | not have a dual       |    |
|    | rp | (c   | C, D | ent |         | hydrologic group, set |    |
|    |    | ode) |      |     |         | to the single         |    |
|    |    |      |      |     |         | hydrologic group. If  |    |
|    |    |      |      |     |         | a component has a     |    |
|    |    |      |      |     |         | dual hydrologic       |    |
|    |    |      |      |     |         | group, create one     |    |
|    |    |      |      |     |         | record for each of    |    |
|    |    |      |      |     |         | the two hydrologic    |    |
|    |    |      |      |     |         | groups, where each    |    |
|    |    |      |      |     |         | record has a          |    |
|    |    |      |      |     |         | different component   |    |
|    |    |      |      |     |         | sequence number.      |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 17 | l  | num  |      | ch  | h       | Set to RV horizon     | in |
|    | ay | ber( |      | ori | zdepb_r | depth to bottom /     | ch |
|    | de | inte |      | zon |         | 2.54 (cm to in). Note | es |
|    | ph | ger) |      |     |         | that an RV horizon    |    |
|    |    |      |      |     |         | depth to bottom IS    |    |
|    |    |      |      |     |         | NOT computed when the |    |
|    |    |      |      |     |         | existing value is     |    |
|    |    |      |      |     |         | null. This is         |    |
|    |    |      |      |     |         | consistent with other |    |
|    |    |      |      |     |         | cases where only an   |    |
|    |    |      |      |     |         | RV value is provided, |    |
|    |    |      |      |     |         | although the reason   |    |
|    |    |      |      |     |         | used in those other   |    |
|    |    |      |      |     |         | cases does not        |    |
|    |    |      |      |     |         | pertain here.         |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 18 | k  | nu   | .02, | ch  | kwfact  | Set to whole soil K   |    |
|    | fa | mber | .05, | ori |         | factor.               |    |
|    | ct | (sin | .10, | zon |         |                       |    |
|    |    | gle) | .15, |     |         |                       |    |
|    |    |      | .17, |     |         |                       |    |
|    |    |      | .20, |     |         |                       |    |
|    |    |      | .24, |     |         |                       |    |
|    |    |      | .28, |     |         |                       |    |
|    |    |      | .32, |     |         |                       |    |
|    |    |      | .37, |     |         |                       |    |
|    |    |      | .43, |     |         |                       |    |
|    |    |      | .49, |     |         |                       |    |
|    |    |      | .55, |     |         |                       |    |
|    |    |      | .64  |     |         |                       |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 19 | o  | nu   |      | ch  | om_l    | Set to organic matter | p  |
|    | ml | mber |      | ori |         | low.                  | er |
|    |    | (sin |      | zon |         |                       | ce |
|    |    | gle) |      |     |         |                       | nt |
|    |    |      |      |     |         |                       | by |
|    |    |      |      |     |         |                       | we |
|    |    |      |      |     |         |                       | ig |
|    |    |      |      |     |         |                       | ht |
|    |    |      |      |     |         |                       | \< |
|    |    |      |      |     |         |                       | 2  |
|    |    |      |      |     |         |                       | mm |
+----+----+------+------+-----+---------+-----------------------+----+
| 20 | o  | nu   |      | ch  | om_r    | Set to RV organic     | p  |
|    | mr | mber |      | ori |         | matter.               | er |
|    |    | (sin |      | zon |         |                       | ce |
|    |    | gle) |      |     |         |                       | nt |
|    |    |      |      |     |         |                       | by |
|    |    |      |      |     |         |                       | we |
|    |    |      |      |     |         |                       | ig |
|    |    |      |      |     |         |                       | ht |
|    |    |      |      |     |         |                       | \< |
|    |    |      |      |     |         |                       | 2  |
|    |    |      |      |     |         |                       | mm |
+----+----+------+------+-----+---------+-----------------------+----+
| 21 | o  | nu   |      | ch  | om_h    | Set to organic matter | p  |
|    | mh | mber |      | ori |         | high.                 | er |
|    |    | (sin |      | zon |         |                       | ce |
|    |    | gle) |      |     |         |                       | nt |
|    |    |      |      |     |         |                       | by |
|    |    |      |      |     |         |                       | we |
|    |    |      |      |     |         |                       | ig |
|    |    |      |      |     |         |                       | ht |
|    |    |      |      |     |         |                       | \< |
|    |    |      |      |     |         |                       | 2  |
|    |    |      |      |     |         |                       | mm |
+----+----+------+------+-----+---------+-----------------------+----+
| 22 | ro | num  |      | co  | re      | Set to resdept_l /    | in |
|    | ck | ber( |      | res | sdept_l | 2.54 (cm to in) for   | ch |
|    | de | inte |      | tri |         | the first record      | es |
|    | pl | ger) |      | cti |         | retrieved from the    |    |
|    |    |      |      | ons |         | component restriction |    |
|    |    |      |      |     |         | table where records   |    |
|    |    |      |      |     |         | are sorted by         |    |
|    |    |      |      |     |         | restriction kind      |    |
|    |    |      |      |     |         | (ascending) and       |    |
|    |    |      |      |     |         | restriction depth to  |    |
|    |    |      |      |     |         | top low (ascending),  |    |
|    |    |      |      |     |         | where restriction     |    |
|    |    |      |      |     |         | kind is \"Lithic\" or |    |
|    |    |      |      |     |         | \"Paralithic\". If a  |    |
|    |    |      |      |     |         | profile has a         |    |
|    |    |      |      |     |         | paralithic layer over |    |
|    |    |      |      |     |         | a lithic layer, the   |    |
|    |    |      |      |     |         | depth to top of the   |    |
|    |    |      |      |     |         | lithic layer will be  |    |
|    |    |      |      |     |         | returned.             |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | Note that in          |    |
|    |    |      |      |     |         | determining rock      |    |
|    |    |      |      |     |         | depth low, only       |    |
|    |    |      |      |     |         | records where depth   |    |
|    |    |      |      |     |         | to restriction top    |    |
|    |    |      |      |     |         | low is not null, are  |    |
|    |    |      |      |     |         | considered.           |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 23 | ro | num  |      | co  | re      | Set to resdept_h /    | in |
|    | ck | ber( |      | res | sdept_h | 2.54 (cm to in) for   | ch |
|    | de | inte |      | tri |         | the first record      | es |
|    | ph | ger) |      | cti |         | retrieved from the    |    |
|    |    |      |      | ons |         | component restriction |    |
|    |    |      |      |     |         | table where records   |    |
|    |    |      |      |     |         | are sorted by         |    |
|    |    |      |      |     |         | restriction kind      |    |
|    |    |      |      |     |         | (ascending) and       |    |
|    |    |      |      |     |         | restriction depth to  |    |
|    |    |      |      |     |         | top low (ascending),  |    |
|    |    |      |      |     |         | where restriction     |    |
|    |    |      |      |     |         | kind is \"Lithic\" or |    |
|    |    |      |      |     |         | \"Paralithic\". If a  |    |
|    |    |      |      |     |         | profile has a         |    |
|    |    |      |      |     |         | paralithic layer over |    |
|    |    |      |      |     |         | a lithic layer, the   |    |
|    |    |      |      |     |         | depth to top of the   |    |
|    |    |      |      |     |         | lithic layer will be  |    |
|    |    |      |      |     |         | returned. This logic  |    |
|    |    |      |      |     |         | is consistent with    |    |
|    |    |      |      |     |         | that used by the FOCS |    |
|    |    |      |      |     |         | export.               |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | Note that in          |    |
|    |    |      |      |     |         | determining rock      |    |
|    |    |      |      |     |         | depth high, only      |    |
|    |    |      |      |     |         | records where depth   |    |
|    |    |      |      |     |         | to restriction top    |    |
|    |    |      |      |     |         | high is not null, are |    |
|    |    |      |      |     |         | considered.           |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 24 | p  | nu   |      | ch  | ph1to1  | If calcium chloride   |    |
|    | hl | mber |      | ori | h2o/ph0 | ph low is not null    |    |
|    |    | (sin |      | zon | 1mcacl2 | and ph 1:1 water low  |    |
|    |    | gle) |      |     |         | is null, or both      |    |
|    |    |      |      |     |         | calcium chloride ph   |    |
|    |    |      |      |     |         | low and ph 1:1 water  |    |
|    |    |      |      |     |         | low are not null but  |    |
|    |    |      |      |     |         | taxonomic order is    |    |
|    |    |      |      |     |         | \"Histosols\", set to |    |
|    |    |      |      |     |         | calcium chloride ph   |    |
|    |    |      |      |     |         | low, else set to ph   |    |
|    |    |      |      |     |         | 1:1 water low. Note   |    |
|    |    |      |      |     |         | that the same test is |    |
|    |    |      |      |     |         | used to set both ph   |    |
|    |    |      |      |     |         | low and ph high, so   |    |
|    |    |      |      |     |         | that calcium chloride |    |
|    |    |      |      |     |         | ph isn\'t used for    |    |
|    |    |      |      |     |         | one value while ph    |    |
|    |    |      |      |     |         | 1:1 water is used for |    |
|    |    |      |      |     |         | the other value,      |    |
|    |    |      |      |     |         | which can happen with |    |
|    |    |      |      |     |         | our current FOCS      |    |
|    |    |      |      |     |         | export.               |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 25 | p  | nu   |      | ch  | ph1to1  | If calcium chloride   |    |
|    | hh | mber |      | ori | h2o/ph0 | ph low is not null    |    |
|    |    | (sin |      | zon | 1mcacl2 | and ph 1:1 water low  |    |
|    |    | gle) |      |     |         | is null, or both      |    |
|    |    |      |      |     |         | calcium chloride ph   |    |
|    |    |      |      |     |         | low and ph 1:1 water  |    |
|    |    |      |      |     |         | low are not null but  |    |
|    |    |      |      |     |         | taxonomic order is    |    |
|    |    |      |      |     |         | \"Histosols\", set to |    |
|    |    |      |      |     |         | calcium chloride ph   |    |
|    |    |      |      |     |         | hig, else set to ph   |    |
|    |    |      |      |     |         | 1:1 water high. Note  |    |
|    |    |      |      |     |         | that the same test is |    |
|    |    |      |      |     |         | used to set both ph   |    |
|    |    |      |      |     |         | low and ph high, so   |    |
|    |    |      |      |     |         | that calcium chloride |    |
|    |    |      |      |     |         | ph isn\'t used for    |    |
|    |    |      |      |     |         | one value while ph    |    |
|    |    |      |      |     |         | 1:1 water is used for |    |
|    |    |      |      |     |         | the other value,      |    |
|    |    |      |      |     |         | which can happen with |    |
|    |    |      |      |     |         | our current FOCS      |    |
|    |    |      |      |     |         | export.               |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 26 | sh | tex  | LOW, | ch  | lep_r   | Set to null if RV LEP |    |
|    | ri | t(9) | M    | ori |         | is null, else set as  |    |
|    | nk | code | ODER | zon |         | follows:              |    |
|    | sw |      | ATE, |     |         |                       |    |
|    |    |      | H    |     |         | RV LEP \>= 0.0 and    |    |
|    |    |      | IGH, |     |         | \<= 2.9 -\> LOW       |    |
|    |    |      | VERY |     |         |                       |    |
|    |    |      | HIGH |     |         | RV LEP \> 2.9 and \<= |    |
|    |    |      |      |     |         | 5.9 -\> MODERATE      |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | RV LEP \> 5.9 and \<= |    |
|    |    |      |      |     |         | 8.9 -\> HIGH          |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | RV LEP \> 8.9 -\>     |    |
|    |    |      |      |     |         | VERY HIGH             |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | This is the same      |    |
|    |    |      |      |     |         | logic used by the     |    |
|    |    |      |      |     |         | FOCS export.          |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 27 | s  | num  |      | ch  | s       | Set to true if either |    |
|    | lo | ber( |      | ori | lope_l/ | slope low or slope    |    |
|    | pe | inte |      | zon | slope_h | high is greater than  |    |
|    | gt | ger) |      |     |         | 15%                   |    |
|    | 15 |      |      |     |         |                       |    |
|    |    |      |      |     |         | False = 0             |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | True = 1              |    |
+----+----+------+------+-----+---------+-----------------------+----+
| 28 | h  | num  |      | co  | s       | Set to true if water  |    |
|    | wt | ber( |      | soi | oimoist | table depth to top    |    |
|    | lt | inte |      | lmo | dept_l/ | low or water table    |    |
|    | e2 | ger) |      | ist | soimois | depth to top high is  |    |
|    |    |      |      |     | tdept_h | less then or equal to |    |
|    |    |      |      |     |         | two feet.             |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | False = 0             |    |
|    |    |      |      |     |         |                       |    |
|    |    |      |      |     |         | True = 1              |    |
+----+----+------+------+-----+---------+-----------------------+----+