## Advanced Addendum to Exercise 5. Working with Statsgo Component Tables in Arc/Info

### CE 397 GIS in Water Resources University of Texas at Austin Prepared by David R. Maidment

You will be using the mapunit, comp and layer tables, and the soilshed and covshd10 coverages from the main assignment. If you don't have them, get them from the anonymous ftp site: ftp.crwr.utexas.edu in directory pub/gisclass/ex5.

### 1. Creating New Mapunit Attributes from the Comp Table

What we want to do now, is to add new attributes to the map unit table which summarise for each map unit the percentage of that map unit occupied by soil groups A, B, C, D. To do this for each map unit we have to total up the values of comppct over all components having a particular soil group. This is easier said than done because of the one to many relationship between the map unit and component tables and because for each map unit there is a cumulative calculation involved of the values of Comppct based on the value of field Hydgrp. We are going to use a calcuation defined over a relation to accomplish this task.

In Arc/Info, adding attributes value is accomplished by first defining what the new attributes will look like when displayed in the map unit table and then by determining the value that is to be stored in each record of the attribute.

Arc: tables [this starts up the Tables system]

Arc: tables
Copyright (C) 1982-1995 Environmental Systems Research Institute, Inc.
TABLES Version 7.0.3 (Mon Mar 13 22:21:55 PST 1995)

First, lets check out what tables are available:

Enter Command: dir

``` TYPE NAME                            INTERNAL NAME    NO. RECS LENGTH EXTERNL
------------------------------------------------------------------------------
DF  COVSHD10.TIC                      ARC0000DAT           4      12    XX
DF  COVSHD10.BND                      ARC0001DAT           1      16    XX
DF  COVSHD10.PAT                      ARC0002DAT          67      20    XX
DF  COMP                              ARC0003DAT        5579     292
DF  MAPUNIT                           ARC0004DAT         631     142
DF  LAYER                             ARC0005DAT       17102     240
DF  SOILSHED.TIC                      ARC0006DAT        1343      12    XX
DF  SOILSHED.BND                      ARC0007DAT           1      16    XX
DF  SOILSHED.PAT                      ARC0008DAT          88      24    XX
DF  RF1SHED.TIC                       ARC0009DAT           4      12    XX
DF  RF1SHED.AAT                       ARC0010DAT          72     170    XX
DF  RF1SHED.BND                       ARC0011DAT           1      16    XX
```

The mapunit and comp tables are the ones that you need to work with next. take a look at the attributes (items) in each of the information tables that you copied:

Enter Command: select mapunit
631 Records Selected.
[there are 631 map units in Texas]

Enter Command: items

```COLUMN   ITEM NAME        WIDTH OUTPUT  TYPE N.DEC  ALTERNATE NAME     INDEXED?
1  STSSAID                5     5     C      -                        -
6  SSAID                  3     3     C      -                        -
9  MUSYM                  5     5     C      -                        -
14  MUID                   7     7     C      -                        -
21  MUNAME               109   109     C      -                        -
130  MUKIND                 1     1     C      -                        -
131  MLRA                   4     4     C      -                        -
135  PRIMFML                2     2     C      -                        -
137  MUACRES                6     6     I      -                        -
COLUMN   ITEM NAME WIDTH OUTPUT  TYPE N.DEC  ALTERNATE NAME
```

In this description, Width is the number of characters assigned to the field, Output is the number of characters used to display the field, Type N.Dec is the type of data (C = character, I = integer, N = floating point, .dec = number of decimal places, Alternate name is an alias for the field. Notice that Muname has a very wide field to accomodate the grouping of component names to form a map unit name.

Enter Command: select comp

5579 Records Selected. [there are 5579 components in Texas; 5579/631 ~ 9 components per map unit]

Enter Command: items

```COLUMN   ITEM NAME        WIDTH OUTPUT  TYPE N.DEC  ALTERNATE NAME     INDEXED?
1  STSSAID                5     5     C      -                        -
6  MUID                   7     7     C      -                        -
13  SEQNUM                 2     2     I      -                        -
15  MUSYM                  5     5     C      -                        -
20  COMPNAME              30    30     C      -                        -
50  S5ID                   6     6     C      -                        -
56  COMPPCT                3     3     I      -                        -
59  SLOPEL                 2     2     I      -                        -
61  SLOPEH                 2     2     I      -                        -
63  SURFTEX                8     8     C      -                        -
71  OTHERPH               40    40     C      -                        -
111  COMPKIND               1     1     C      -                        -
112  COMPACRE               6     6     I      -                        -
118  CLASCODE              20    20     C      -                        -
138  ANFLOOD                5     5     C      -                        -
143  ANFLODUR              12    12     C      -                        -
155  ANFLOBEG               3     3     C      -                        -
158  ANFLOEND               3     3     C      -                        -
161  GSFLOOD                5     5     C      -                        -
166  GSFLODUR              12    12     C      -                        -
178  GSFLOBEG               3     3     C      -                        -
181  GSFLOEND               3     3     C      -                        -
184  WTDEPL                 4     4     N      1                        -
188  WTDEPH                 4     4     N      1                        -
192  WTKIND                 5     5     C      -                        -
197  WTBEG                  3     3     C      -                        -
200  WTEND                  3     3     C      -                        -
203  PNDDEPL                4     4     N      1                        -
207  PNDDEPH                4     4     N      1                        -
211  PNDDUR                10    10     C      -                        -
221  PNDBEG                 3     3     C      -                        -
224  PNDEND                 3     3     C      -                        -
227  ROCKDEPL               2     2     I      -                        -
229  ROCKDEPH               2     2     I      -                        -
231  ROCKHARD               4     4     C      -                        -
235  PANDEPL                2     2     I      -                        -
237  PANDEPH                2     2     I      -                        -
239  PANHARD                5     5     C      -                        -
244  SUBINITL               2     2     I      -                        -
246  SUBINITH               2     2     I      -                        -
248  SUBTOTL                2     2     I      -                        -
250  SUBTOTH                2     2     I      -                        -
252  HYDGRP                 3     3     C      -                        -
255  FROSTACT               8     8     C      -                        -
263  DRAINAGE               5     5     C      -                        -
268  HYDRIC                 1     1     C      -                        -
269  CORCON                 8     8     C      -                        -
277  CORSTEEL               8     8     C      -                        -
285  CLNIRR                 1     1     C      -                        -
286  CLIRR                  1     1     C      -                        -
287  SCLNIRR                2     2     C      -                        -
289  SCLIRR                 2     2     C      -                        -
291  PRIMFML                2     2     C      -                        -
**  REDEFINED ITEMS  **
6  MUSEQ                  9     9     C      -                        -
```

You'll notice that you need to "select" the table you want to investigate prior to looking at the items in that table. (In the COMP table, note that the items beginning at columns 56 and 252 are COMPPCT and HYDGRP. You'll use these items a little later)

Now you are going to add hydrologic soil group data to the Mapunit table. There are 7 established STATSGO soil group types for Texas: A, B, C, D, A/D, B/D, and C/D. The latter three types are soils which are D soils unless they are drained, in which case they become types A, B, C, respectively. There are very few instances of these types of soils in Texas, so we will not deal with them in this exercise.

For each STATSGO polygon, you want to know what percentage of the polygon makes up each of these soil group types. First, using the additem command, add fields to the Mapunit table for the percentage of each soil group type:

Enter Command: select mapunit

631 Records Selected.

Enter Command: additem mapunit A_pct 3 3 i

Cannot add items to an open file.

With this command you are literally saying: add to the table called mapunit an item called A_pct that will be 3 characters wide, will need 3 characters of display width, and is an integer. However, Arc/Info will not let you modify a table that you have "selected". If you select another Table (e.g. Comp), then you will be able to modify Mapunit (strange but true). Or, you can leave Tables and come back in and modify Mapunit without selecting any table:

Enter Command: quit

Leaving TABLES...

Arc: tables

Now, lets add the items for the percentage of each map unit occupied by A, B, C, D:

Enter Command: additem mapunit A_pct 3 3 i

Enter Command: additem mapunit B_pct 3 3 i

Enter Command: additem mapunit C_pct 3 3 i

Enter Command: additem mapunit D_pct 3 3 i

Enter Command: select mapunit

631 Records Selected.

Enter Command: items

```COLUMN   ITEM NAME        WIDTH OUTPUT  TYPE N.DEC  ALTERNATE NAME     INDEXED?
1  STSSAID                5     5     C      -                        -
6  SSAID                  3     3     C      -                        -
9  MUSYM                  5     5     C      -                        -
14  MUID                   7     7     C      -                        -
21  MUNAME               109   109     C      -                        -
130  MUKIND                 1     1     C      -                        -
131  MLRA                   4     4     C      -                        -
135  PRIMFML                2     2     C      -                        -
137  MUACRES                6     6     I      -                        -
143  A_PCT                  3     3     I      -                        -
146  B_PCT                  3     3     I      -                        -
149  C_PCT                  3     3     I      -                        -
152  D_PCT                  3     3     I      -                        -
```

Now you can see that the 4 items added to the mapunit table actually do show up. Next you'll want to relate the component table (Comp) to the mapunit table (i.e associate attributes from Comp to Mapunit). We are here doing in Arc/Info the same kind of thing that you did earlier in Arcview when linking the two tables (Comp is the "Source" table and Mapunit is the "Destination" table).

Enter Command: select comp

5579 Records Selected.

Enter Command: relate add
Relation Name: mu
Table Identifier: mapunit
Database Name: info
INFO Item: muid
Relate Column: muid
Relate Type: ordered
Relate Access: rw
Relation Name: Hit Return key here

Once you've narrowed down the selected table (Comp) , you will sum the percentages (comppct) for all records associated with a specific map unit. This is a bit tedious and you need to be careful with your typing!

Enter Command: reselect hydgrp = 'A'

[this is a reduce/select command that narrows our selected table (Comp) down to only the records that have a value of 'A' for the item hydgrp]

285 Records Selected.

Enter Command: calc mu//A_pct = mu//A_pct + comppct

Be sure to leave spaces on each side of the = sign and the + sign or the calculation won't work.

[this "calculate" command says "For each map unit in the mapunit table, determine the value of the total percentage of STATSGO soil type A, by summing all of the component percentages for each component with a particular map unit id". The // nomenclature means that the value of A_pct is in the related destination table while the comppct comes directly from the selected table]

Enter Command: select comp

Enter Command: reselect hydgrp = 'B'

1792 Records Selected.

Enter Command: calc mu//B_pct = mu//B_pct + comppct

Enter Command: select comp

Enter Command: reselect hydgrp = 'C'

1539 Records Selected.

Enter Command: calc mu//C_pct = mu//C_pct + comppct

Enter Command: select comp

Enter Command: reselect hydgrp = 'D'

1944 Records Selected.

Enter Command: calc mu//D_pct = mu//D_pct + comppct

Now you have specified the formulas for all of the new items that you've added to mapunit. You may now look at some representative values of the different STATSGO hydrologic soil type percentages:

Enter Command: select mapunit

631 Records Selected.

Enter Command: list muid A_pct B_pct C_pct D_pct

[this command says "For mapunit, list the map unit id and all soil type percentages"]

```
Record  muid    A_pct B_pct C_pct D_pct
1  TXW       0     0     0     0
2  TX001     0     0     0     0
3  TX002     0    85    10     5
4  TX003     0    89    11     0
5  TX004     0    50     9    41
6  TX005     0    54    46     0
7  TX006     0    15    36    49
8  TX007     0     0    33    67
9  TX008     0     8    66    26
10  TX009     0    53    47     0
11  TX010     0     5     0    95
12  TX011     0    40    45    15
13  TX012    84     1    13     2
14  TX013     0    95     5     0
15  TX014     0    96     4     0
16  TX015     0    40    30    30
17  TX016     3    93     2     2
18  TX017     0    11    89     0
19  TX018     0    18    82     0
20  TX019     0     4    93     3
21  TX020     0    14    70    16
22  TX021     0     0    94     6
Continue?
```
(type y to see more data or n for no or (return) to look at the next block of data. If you type y you will see all 631 mapunit results!) Here is the result for mapunit TX560 that you examined earlier:

```   556  TX560     0    28     3    69
```

Each row of numbers should add up to 100 %. If so, then you have successfully appended your mapunit table with soil type group data!!

Enter Command: quit

Leaving TABLES...

### 2. Adding Attributes to the Watershed Soil Polygons

At the beginning of the exercise, you used a polygon coverage called soilshed clipped from the statsgo and covshd10 polygon coverages. All polygon coverages have polygon attribute tables (pat). Let's take a look at the items in the attribute table for soilshed:

Arc: items soilshed.pat

```COLUMN   ITEM NAME        WIDTH OUTPUT  TYPE N.DEC  ALTERNATE NAME     INDEXED?
1  AREA                   4    12     F      3                        -
5  PERIMETER              4    12     F      3                        -
9  SOILSHED#              4     5     B      -                        -
13  SOILSHED-ID            4     5     B      -                        -
17  MUID                   7     7     C      -                        -
**  REDEFINED ITEMS  **
19  IDS                    3     3     C      -                        -
```
Next you'll want to take the data from mapunit and attach it to this Polygon Attribute Table:

Arc: joinitem soilshed.pat mapunit soilshed.pat muid muid
Joining soilshed.pat and mapunit to create soilshed.pat

[this command says "attach to soilshed.pat the items from mapunit and call the resulting output soilshed.pat (i.e. redefine soilshed.pat). Associate the items based on the relate item muid. Place these new items immediately following the item, muid, in soilshed.pat]

Arc: items soilshed.pat

```COLUMN   ITEM NAME        WIDTH OUTPUT  TYPE N.DEC  ALTERNATE NAME     INDEXED?
1  AREA                   4    12     F      3                        -
5  PERIMETER              4    12     F      3                        -
9  SOILSHED#              4     5     B      -                        -
13  SOILSHED-ID            4     5     B      -                        -
17  MUID                   7     7     C      -                        -
24  STSSAID                5     5     C      -                        -
29  SSAID                  3     3     C      -                        -
32  MUSYM                  5     5     C      -                        -
37  MUNAME               109   109     C      -                        -
146  MUKIND                 1     1     C      -                        -
147  MLRA                   4     4     C      -                        -
151  PRIMFML                2     2     C      -                        -
153  MUACRES                6     6     I      -                        -
159  A_PCT                  3     3     I      -                        -
162  B_PCT                  3     3     I      -                        -
165  C_PCT                  3     3     I      -                        -
168  D_PCT                  3     3     I      -                        -
**  REDEFINED ITEMS  **
19  IDS                    3     3     C      -                        -
```

You'll notice that the items from the mapunit table are located immediately following the item item muid in the soilshed.pat.

Lets check out what soil polygons we have with soil TX560 in them:

Arc: tables
Copyright (C) 1982-1995 Environmental Systems Research Institute, Inc.
TABLES Version 7.0.3 (Mon Mar 13 22:21:55 PST 1995)

Enter Command: select soilshed.pat
88 Records Selected.

Enter Command: reselect muid = 'TX560'
5 Records Selected. [There are 5 soil polygons in this area containing a soil association described by map unit TX560]

Enter Command: list

```              5
AREA                       = ************
PERIMETER                  =   408095.281
SOILSHED#                  =     5
SOILSHED-ID                = 12570
MUID                       = TX560
STSSAID                    =
SSAID                      =
MUSYM                      =
MUNAME                     = TINN-PURSLEY-GOWEN (TX560)
MUKIND                     =
MLRA                       = 86A
PRIMFML                    =
MUACRES                    =      0
A_PCT                      =   0
B_PCT                      =  28
C_PCT                      =   3
D_PCT                      =  69
8
AREA                       =      648.901
PERIMETER                  =      123.008
SOILSHED#                  =     8
SOILSHED-ID                = 12570
MUID                       = TX560
STSSAID                    =
SSAID                      =
MUSYM                      =
MUNAME                     = TINN-PURSLEY-GOWEN (TX560)
MUKIND                     =
MLRA                       = 86A
PRIMFML                    =
MUACRES                    =      0
A_PCT                      =   0
B_PCT                      =  28
C_PCT                      =   3
D_PCT                      =  69

```

Look at a few different records in soilshed.pat. Many of the items in each record consistently have no entered values (not even zero!). Since there is no real value added from these items, you may choose to eliminate them from the table. Quit out of Tables and then restart Tables again.

Enter Command: quit
Leaving TABLES...

Arc: tables

Enter Command: dropitem soilshed.pat stssaid

Enter Command: dropitem soilshed.pat ssaid

Enter Command: dropitem soilshed.pat musym

Enter Command: dropitem soilshed.pat mukind

Enter Command: dropitem soilshed.pat primfml

Enter Command: dropitem soilshed.pat muacres

Enter Command: quit

Arc: items soilshed.pat

```COLUMN   ITEM NAME        WIDTH OUTPUT  TYPE N.DEC  ALTERNATE NAME     INDEXED?
1  AREA                   4    12     F      3                        -
5  PERIMETER              4    12     F      3                        -
9  SOILSHED#              4     5     B      -                        -
13  SOILSHED-ID            4     5     B      -                        -
17  MUID                   7     7     C      -                        -
24  MUNAME               109   109     C      -                        -
133  MLRA                   4     4     C      -                        -
137  A_PCT                  3     3     I      -                        -
140  B_PCT                  3     3     I      -                        -
143  C_PCT                  3     3     I      -                        -
146  D_PCT                  3     3     I      -                        -
**  REDEFINED ITEMS  **
19  IDS                    3     3     C      -                        -
```

You can now see that the pat for soilshed has been modified and includes only those items that have numerical values.

The coverage soilshed includes all of the STATSGO polygons within the COVSHD10 watershed perimeter. Soilshed does not, however, contain the borders between subwatersheds. To get that data included all in one coverage, you'll need to do the following:

Arc: intersect soilshed covshd10 soilstat

[this command says to merge the attributes of soilshed and covshd10 (including the

ID #'s) and place them in a new coverage called soilstat. You may choose to just merge the two coverages back into soilshed, in which case the final argument in the command line would be soilshed instead of soilstat]

Intersecting soilshed with covshd10 to create soilstat

``` Intersecting soilshed with covshd10 to create soilstat
Sorting...
Intersecting...
Assembling polygons...
Creating new labels...
Creating soilstat.PAT...
** Item "AREA" duplicated, Join File version dropped **

** Item "PERIMETER" duplicated, Join File version dropped **

** Item "AREA" duplicated, Join File version dropped **

** Item "PERIMETER" duplicated, Join File version dropped **
```

Ok! Now you've got three coverages: soilshed, covshd10 and their intersection soilstat

\$Arc: quit

To be turned in: List the item fields in the .pat tables of each of the three coverages. How many fields and records does each coverage have? What happens when you intersect soilshed and covshd10 to produce Soilstat? How many new fields are created? How many new records are created?

### 3. Relating the Comp and Layer Tables

In the above exercise, you compiled STATSGO soil properties from the comp table to the statsgo polygon attribute table and determined their values for a particular watershed. To get properties from the Layer tables, you have first to transfer them to the Comp table and then to the Mapunit table. Because it takes two relate items (muid and seqnum) to uniquely specify a soil layer, there is no single item in the Comp table which can be used as a relational link to the Layer table. This problem can be overcome by defining a new item in both the comp and layer tables which is a concatenation of muid and seqnum. This done in the following way:

In Arc go to tables:

Arc: tables

Select a table to work on:

Enter Command: sel comp

5579 Records selected

Enter Command: items [lists the items in the comp table]

```COLUMN   ITEM NAME        WIDTH OUTPUT  TYPE N.DEC  ALTERNATE NAME     INDEXED?
1  STSSAID                5     5     C      -                        -
6  MUID                   7     7     C      -                        -
13  SEQNUM                 2     2     I      -                        -
15  MUSYM                  5     5     C      -                        -
20  COMPNAME              30    30     C      -                        -
50  S5ID                   6     6     C      -                        -
56  COMPPCT                3     3     I      -                        -
59  SLOPEL                 2     2     I      -                        -
61  SLOPEH                 2     2     I      -                        -
63  SURFTEX                8     8     C      -                        -
71  OTHERPH               40    40     C      -                        -
111  COMPKIND               1     1     C      -                        -
112  COMPACRE               6     6     I      -                        -
118  CLASCODE              20    20     C      -                        -
138  ANFLOOD                5     5     C      -                        -
143  ANFLODUR              12    12     C      -                        -
155  ANFLOBEG               3     3     C      -                        -
158  ANFLOEND               3     3     C      -                        -
161  GSFLOOD                5     5     C      -                        -
166  GSFLODUR              12    12     C      -                        -
178  GSFLOBEG               3     3     C      -                        -
```
Continue? n

Enter Command: redefine [indicates the creation of a new item from existing ones]

Enter Starting Column: 6 [this begins the new item at column 6 of the comp table, at the beginning of the MUID field]

Item Name: museqnum [a temporary name]

Item width: 9 [this takes in the 7 characters of MUID and the two numbers of SEQNUM treated as character text]

Item output width: 9 [display width]

Item type: c [character display]

Enter starting column: [just hit return to end the dialog on redefine]

Enter Command: sel [close comp]

File Comp is now closed

Enter Command: additem comp mucomp 9 9 c seqnum

This adds a new item to the comp table called mucomp located after the item seqnum in the table.

Enter Command: sel comp [select the comp table again]

5579 Records selected

Enter Command: move museqnum to mucomp

This puts the values defined earlier in the temporary item museq into the new item mucomp. The new item can be viewed by using:

Enter command: list muid seqnum mucomp

```Record  muid    seqnum mucomp
1  TXW      1     TXW     1
2  TX001    1     TX001   1
3  TX001    2     TX001   2
4  TX001    3     TX001   3
5  TX001    4     TX001   4
6  TX002    1     TX002   1
7  TX002    2     TX002   2
8  TX002    3     TX002   3
9  TX002    4     TX002   4
10  TX002    5     TX002   5
11  TX002    6     TX002   6
12  TX003    1     TX003   1
13  TX003    2     TX003   2
14  TX003    3     TX003   3
15  TX003    4     TX003   4
16  TX003    5     TX003   5
17  TX003    6     TX003   6
18  TX003    7     TX003   7
19  TX003    8     TX003   8
20  TX003    9     TX003   9
21  TX003   10     TX003  10
22  TX003   11     TX003  11

```

Enter Command: sel layer

and repeat the above operation on the layer table to add mucomp to the layers.

Here is the dialog that accomplishes the addition of the new item to the layer table:

```
Enter Command: items

COLUMN   ITEM NAME        WIDTH OUTPUT  TYPE N.DEC  ALTERNATE NAME     INDEXED?
1  STSSAID                5     5     C      -                        -
6  MUID                   7     7     C      -                        -
13  SEQNUM                 2     2     I      -                        -
15  S5ID                   6     6     C      -                        -
21  LAYERNUM               1     1     I      -                        -
22  LAYERID                2     2     I      -                        -
24  LAYDEPL                2     2     I      -                        -
26  LAYDEPH                2     2     I      -                        -

Continue? n

Enter Command: redefine
Enter starting column: 6
Item name: museqnum
Item width: 9
Item output width: 9
Item type: c

Enter starting column:

Enter Command: sel
File LAYER is now closed.

Enter Command: additem layer mucomp 9 9 c seqnum

Enter Command: sel layer
17102 Records Selected.

Enter Command: move museqnum to mucomp

Enter Command: list muid seqnum mucomp
Record  muid    seqnum mucomp
1  TX001    1     TX001   1
2  TX001    1     TX001   1
3  TX001    1     TX001   1
4  TX001    1     TX001   1
5  TX001    2     TX001   2
6  TX001    2     TX001   2
7  TX001    2     TX001   2
8  TX001    3     TX001   3
9  TX001    3     TX001   3
10  TX001    3     TX001   3
11  TX001    4     TX001   4
12  TX001    4     TX001   4
13  TX001    4     TX001   4
14  TX002    1     TX002   1
15  TX002    1     TX002   1
16  TX002    1     TX002   1
17  TX002    2     TX002   2
18  TX002    2     TX002   2
19  TX002    2     TX002   2
20  TX002    3     TX002   3
21  TX002    3     TX002   3
22  TX002    3     TX002   3
Continue? n
```
Then you can relate the two mucomp items between the comp and layer table and perform vertical integration of soil properties across layers.
Go to
Main Statsgo Exercise