Term Project Report

CE 394K GIS in Water Resources
Bacteria Loading in the Trask River
By: Sarah R. Lindsay

slindsay@mail.utexas.edu

Outline:

• Objectives
• Background Information
• GIS Procedures
• Obtaining Data
• Clipping the Grids
• Clipping the Coverages
• Editing an Attribute Table
• Adding Data to an Attribute Table
• Converting from Coverages to Grids
• Working with the Landuse Coverage
• Conclusion
• Future Work
• References
• Data Dictionary

Objectives

The purpose of this project is to use GIS to estimate bacteria loads into the Trask River based on land use, dairy farm locations and sewage treatment plant locations.  Once average bacteria concentrations in the river are determined, they can be compared to real data obtained from sampling for bacteria in the river.   Significant effort was devoted to managing the database so that it could be analyzed for the project.  Bacteria concentrations for urban, industrial and residential landuse was not obtained in the time allotted for the project.   Therefore, the final grid calculations could not be completed.  Hence, this page discusses editing the coverages and making calculations that are required for later steps in the project.

Background Information

The Trask River is part of the Tillamook Bay drainage basin.  Tillamook Bay is located in Tillamook county in the northwestern portion of the state of Oregon.

Click on the image to view the location map for the Trask River Basin.

Tillamook Bay is part of the National Estuary Program (NEP) .  The purpose of the NEP is "to protect and restore the health of estuaries while supporting economic and recreational activities. Local NEP's are created that develop partnerships between government agencies that oversee estuarine resources and the people who depend on the estuaries for their livelihood and quality of life." [1]

The Tillamook Bay National Estuary Project (TBNEP) was established in 1992.  Tillamook Bay is approximately 6.2 miles long and 2.1 miles wide.  The bay has a total area of 13 square miles and an average depth of 6.6 feet.  The area of the Tillamook Bay drainage basin is 1,428 square kilometers.  Annual precipitation in the region is approximately 100 inches.  The highest amounts of precipitation occur in the months of November, December and January [2].  The area of the basin and precipitation in the basin are key factors in determining the bacteria loading to the bay.

The high levels of bacteria in Tillamook Bay have been identified as a priority problem by the TBNEP.  The sources of the bacteria are Confined Animal Feeding Operations (CAFOs), urban runoff, sewage treatment plants, and onsite septic tanks.  These sources are concentrated mainly in the lowland areas of the Tillamook Bay drainage basin.  The upland regions of the basin are covered by forests which do not contribute significant amounts of bacteria to the rivers.   The Trask, Tillamook and Wilson Rivers contribute the highest amounts of bacteria to Tillamook Bay.  Extensive water sampling for bacteria has been performed in the Trask River basin.   The availability of this "real" bacteria concentration data and the fact that the Trask River is a major contributor of bacteria to the bay made the Trask River basin ideal for this project. 

  Click on the image to view the streams, rivers and CAFOs of the Trask River basin.

GIS Procedures

The coverages for the project were obtained from the Tillamook Bay National Estuary Project's September 1997 CD-ROM (TBNEP CD).  Files can also be downloaded directly from TBNEP's web page [3].   The coverages used included those of: 1) land use, 2) dairy farm locations, 3) major rivers, 4) streams and 5) sewage treatment plants.   The grids of runoff, precipitation, flow accumulation, and flow direction for the Tillamook Bay Basin were obtained from Patrice Melancon.  The Trask River watershed grid was delineated and created by Patrice.  Refer to the data dictionary for a complete listing of the coverages and grids.

Clipping the Grids

The runoff grid, elevation grid, flow accumulation grid and flow direction grid for the entire Tillamook Bay basin needed to be clipped to the extent of the Trask River basin.  First, the Trask River watershed grid was converted to a polygon coverage so that it could be used as the clipping coverage.  This was done in ArcInfo with the following command:

Arc:  GRIDPOLY <in_grid> <out cover>

Arc:  GRIDPOLY traskws trpoly

The clipping of the grids was done in the Grid section of ArcInfo.  The following command was used:

Arc:  grid

Grid:  GRIDCLIP <input dem> <output dem> cover <clip cover>

For example, the runoff grid was clipped by the following command:

Grid:  gridclip runoff2 trskrun cover trpoly

  Click on the image to view the original runoff grid and the clipped grid.

This command takes the runoff grid for the Tillamook Bay watershed, runoff2, and clips it with the polygon coverage of just the Trask River basin, trpoly, resulting in a runoff grid for the Trask River basin only, trskrun.   The GRIDCLIP command was used to make grids of elevation, flow direction, flow accumulation and runoff for the Trask River Basin.

  Click on the image to view the other clipped grids.

Clipping the Coverages

The coverages from the TBNEP CD were of the entire Tillamook Bay system.   The original coverages were clipped to the same extent as the Trask River polygon coverage.  The following command was used:

Arc:  CLIP <in cover> <clip cover> <out cover> {POLY, LINE, POINT}

For example, the CAFO point coverage was clipped with the following command:

Arc:  CLIP cafo trpoly trcafo point

Translated, this means that the original cafo coverage was clipped with the Trask River watershed coverage, trpoly, to create a new point coverage of CAFOs, trcafo, located in just the Trask River basin.

  Click on the image to view the CAFO point coverages.

The same CLIP command was used on all the coverages in this project to clip them to the extent of the Trask River basin.

Editing an Attribute Table

The coverage of CAFOs in the Trask River Basin needed adjustment.  Some of the CAFOs are no longer in operation but were still on the coverage.  Therefore, a coverage of only the existing CAFOs needed to be created.  Then, bacteria concentration data could be added to each existing CAFO.  ArcView does not allow some attribute tables to be edited and the attribute table of trcafo was one of those.  Therefore, the coverage trcafo was converted to a shapefile called Trcafo1.  This was done in ArcView by making the trcafo theme active and selecting Theme/Convert to Shapefile. 

The attribute table of Trcafo1 was able to be edited in ArcView.   To do this, the Trcafo1 theme was made the active theme in the View window.  Next, its associated attribute table was opened by clicking on the button.  Then, Start Editing was selected from the Table menu.  The records that needed to be deleted were selected with the button, which highlighted the records in yellow.  Finally, Delete Records was selected from the Edit menu.   This procedure deleted the records in the attribute table and it deleted the corresponding points from the coverage.  

The edited coverage, Trcafo1 was renamed Trskcaf and now contained only CAFOs in operation.

Adding Data to an Attribute Table

An Excel file listing the number of cows located at each CAFO was obtained from Bruce Follansbee of the TBNEP.  This Excel file contained a lot of information, including the Cafo Id number and the number of thousands pounds of cows at each CAFO. The following conversions and calculations were performed to determine the concentration of fecal coliforms at each CAFO site.

1, 000 pounds of dairy cows = 82 pounds of manure per day [3]

1 gram of dairy cow manure = 870, 000 fecal coliforms [4]

lbs of dairy cows in thousands * (82 lbs of manure/day) / 1, 000 lbs of dairy cows = lbs of manure/day

lbs of manure/day * .45 = kg of manure/day

kg of manure/day * 1, 000 = g of manure/day

g of manure/day * 870, 000 fecal coliforms/g of dairy cow manure = fecal coliforms/day

Example: 111, 000 pounds of cow

111, 000 lbs of cow * (82 lbs of manure/day) / 1, 000 lbs of cow = 9, 102 lbs of manure/day

9, 102 lbs of manure/day * .45 = 4096 kg of manure/day

4, 096 kg of manure/day * 1,000 = 4, 096, 000 g of manure/day

4, 096, 000 g of manure/day * 870, 000 coliforms/gram of dairy cow manure = 3.56 X 10¹² coliforms/day at one site!

The final Excel file contained the CAFO Id number and the number of fecal coliforms/day at each CAFO site.  The file was saved as a DBASE file named coliforms.dbf.  DBASE files can be read by ArcView by selecting the Tables icon and Add from the project window.  The coliforms.dbf table needed to be joined to the attribute table of the CAFO coverage, trskcaf.  The coliforms.dbf table was opened by the above mentioned procedure.  Next, the attribute table of the CAFO coverage (trskcaf) was opened by making that theme active and selecting the button.  The Trcafo2_Id field on the imported DBASE file (coliform.dbf) table was depressed and then the Trcafo2_Id field on the Attributes of Trskcaf table was depressed.  Finally, Table/Join was selected. 

Now the trskcaf attribute table has an additional field listing coliform concentrations per day for each CAFO.

Converting from Coverages to Grids

To do the calculations to determine the bacteria loading to the Trask River, all the coverages with coliform data needed to be in grid form.  Coverages and shapefiles can be converted to grids by making the desired theme active and selecting Theme/Convert to Grid. Dialog boxes appear asking for output information.  The following is an example of the conversion of the trskcaf shapefile to a grid:

The trskcaf point coverage has a coliform number associated with each point.  This conversion to a grid used the coliforms field of the attribute table as the value for the cells in the grid.  All the other cells were assigned "no data" as a value.  For a point coverage, each point is represented in a grid by one cell.  Therefore the CAFO grid is not too exciting to look at.

  Click on the image to view an enlargement of one section of the CAFO grid.

(Note:  The afore-mentioned procedure was followed for the trout coverage.  The trout ended up being a single point that represents the Tillamook City sewage treatment plant.  Samples have been taken from the plant's effluent to determine bacteria levels.  An average bacteria concentration number calculated from the samples was used as the cell value in the grid (trskout) of the trout coverage.)

Working with the Landuse Coverage

The original land use coverage from the TBNEP was called low_poly5.   It is a polygon coverage of landuse in the Tillamook Bay area for the lowland regions.  The previously mentioned procedure for clipping coverages (see Clipping the Coverages) was used to create a coverage of landuse in the Trask River Basin area called landuse.

  Click on the image to view the landuse coverage for the Trask River basin.

  Click on this image to view a close-up of the landuse coverage.

As you can see, this is not very informative.  The polygons are all coded the same color.  The legend needed to be changed so that each landuse type had a different color associated with it.  This was done in ArcView by double-clicking on the color box in the landuse theme button.  This activated the Legend Editor.  "Unique Value"  was chosen for the Legend Type.  Next, a Values Field needed to be selected.  As you can see in the image below, there were two different "types" of landuse that could be selected as a Values Field;  "Type" or "D_Type":  

  Click on the image to view the "Type" landuse type coverage for the Trask River Basin.

  Click on the image to view the "D_Type" landuse type coverage for the Trask River Basin.

In the Legend Editor, you can only choose one value for the Values Field.  However, it would be better to view all the landuse types together on one map.   Therefore, the landuse coverage for the Trask River basin was converted into a shapefile by selecting Theme/Convert to Shapefile.  Then, "D_Type" was selected for the Values Field in the Legend Editor of the shapefile and "Type" was selected for the Values Field of the original Trask River basin landuse coverage.  The colors and labels were changed in the Legend Editor to loosely resemble the color scheme of the Anderson Landuse classification.
(Note:  The original landuse coverage created by the TBNEP did not use the Anderson Landuse classification.  Therefore, the color schemes and codes are different in the coverages for this project than from maps that use the Anderson classification.)

Now, both the "Type" and "D_Type" themes could be added to the same view to produce one map of landuse:

  Click on the image to view the combined landuse map.

This looks much better and conveys more information!

Conclusion

GIS has been an extremely useful tool in creating items to be used in modeling bacteria loads to the Trask River.  This was achieved by altering the original coverages to a single river basin extent and converting them into grids in the ArcView and ArcInfo environments.  Overall, the project greatly increased my GIS skills as well as my web page and presentation skills.

Future Work

For the bacteria loading model to be complete, a few more steps are required.  First, estimates of bacteria concentrations for each different type of landuse (other than the CAFOs) need to be determined or estimated from the literature.   Those numbers could then be added to the attribute table of the landuse coverage.   The coverage could be converted to a grid with cell values based on the bacteria concentration numbers.  A connection between bacteria concentration per area would need to be determined so that individual values in each grid cell will be accurate.

The highland region of the Trask River basin is mainly forest land.   Though the forest area does not contribute a significant amount of bacteria to the river, it should still be included in the model.  The TBNEP CD did not have a coverage of landuse or land cover that included the entire forest area.  However, an assumption could be made that the area not covered by the lowland landuse coverage is all highland forest area.

  Click on the image to view the estimated forest region of the Trask River basin.

The Trask River basin landuse coverage and the Trask River basin coverage could both be converted to grids.  Then, the landuse grid could be subtracted from the basin grid which would result in a grid of the forest area of the Trask River basin.   The TBNEP literature contains bacteria concentration data from samples that were collected from the Trask River in a forested area.  This number could be used to back-calculate the number of coliforms per area of forest per year.

The four grids (CAFO, landuse, sewage treatment plant and forest area) would all have cell values of coliform concentration.  The grids could be added together to determine the amount of bacteria in the entire Trask River basin.  Ann Quenzer's script, which was used in the GIS class Exercise 6, could then be altered to calculate mean annual loading of bacteria into the system.   The bacteria concentrations in the Trask River as estimated from the GIS model could then be compared with the actual data obtained from water sampling in the Trask River.  If the match is close, the bacteria concentrations could be altered in the grids to determine what needs to be done in the model to reduce bacteria loading to the river.

Finally, the bacteria loading model could be applied to the entire Tillamook Bay drainage basin to determine the collective amount of loading to the bay.

References

[1]  http://www.epa.gov/OWOW/estuaries/nep.html

[2]  Tillamook Bay National Estuary Project.  September 1997. "Tillamook Bay Environmental Characterization:  A Scientific and Technical Summary."

[3]  http://www.orst.edu/dept/tbaynep/nephome.html

[4]  Crane, S.R., Moore, J.A., Grismer, M.E. and J.R. Miner.  1983.   "Bacterial Pollution from Agricultural Sources:  A Review".  Transactions of the ASAE.  pp. 858 - 872.

Data Dictionary

All coverages and grids were in the Oregon Lambert projection.  The parameters are as follows:

Go to the top of the page
Go to Sarah's page
Go to the Dept. of Geology's page