CE 397 Statistics in Water Resources

Exercise 7

SPAtially Referenced Regressions On Watershed Attributes (SPARROW)

by:

Maelle Limouzin, Kate Marney, Laura Read, and David Maidment

University of Texas at Austin

and Jonathan Goodall

University of South Carolina

March 2009

Contents

Introduction. 1

SPARROW Model 2

Background. 2

Recent Publications. 2

Goals of this Exercise. 2

Running the Model 4

Interpreting SPARROW Results. 8

Viewing the Results in ArcGIS. 10

Summary of items to be turned in: 20

Introduction

SPARROW is a water quality model developed by the USGS to predict mean annual loadings of nutrients (Nitrogen and Phosphorus) in a spatially distributed fashion across the continental United States. The model takes point and nonpoint sources of nitrogen on the landscape and sums them through the stream network, allowing for decay in concentrations as the nutrients flow downstream.  Sources of nutrients include nonpoint sources drawn from the landscape (fertilizers, atmospheric deposition) and point sources such as wastewater treatment plant effluents. SPARROW employs a statistically estimated nonlinear regression to predict these loads whose coefficients are determined in SAS.   SAS is also used to determine the resulting model loadings once the coefficients are found.  This exercise is just about computing the resulting model loadings.  For more information on SPARROW, see

http://water.usgs.gov/nawqa/sparrow/

SPARROW Model

Background

The SPARROW method spatially relates collected water quality data to the characteristics of a river basin.  The model has been used to trace total nitrogen and phosphorous levels through river basins across the U.S., based on sources of input (fertilizer uses, agricultural discharge, e.g.), physical characteristics of the watersheds (soil type, temperature, e.g.), and the aquatic system (channel depth, velocity, e.g.). By integrating spatial data (GIS) and statistical analysis (SAS), SPARROW is a powerful tool for modeling chemical transport through a basin.

Motivation for creating the SPARROW method began in 1972 with the Clean Water Act, which required states to measure and track water quality in rivers.  From there, growing concern about pollutant sources and ecological interactions has led to the development of the SPARROW model, which has some explanatory power in these areas.  By using regression to examine causal relationships, SPARROW addresses the challenges for understanding complex chemical transport and in-stream interactions.

The main goal of SPARROW is to reduce the three major issues that currently exist in regional water-quality assessments: sampling biases, a sparseness of monitoring stations over certain remote areas, and basin heterogeneity.

Recent Publications

A recent study on nitrogen transport used SPARROW to identify possible reasons for an increase in hypoxia and eutrophication at the Louisiana--Gulf of Mexico confluence.  The results from the regression statistics of SPARROW linked channel depth of the Mississippi River to nitrogen concentration, as well as correlated the nitrogen-source input variables with the average nitrogen flux.  This study is an example of SPARROW’s ability to compute a complicated regression on many input variables (watershed attributes, nitrogen sources, etc) and draw useful connections from the output to try to explain an occurring phenomenon.  How cool!

For further information on SPARROW, follow these links to publications:

http://water.usgs.gov/nawqa/sparrow/wrr97/results.html

http://water.usgs.gov/nawqa/sparrow/nature/nature.html

Goals of this Exercise

The goal of exercise is to run the SPARROW model to obtain predicted nitrogen loads on the watersheds in HUC region 12. The results of the regression will then be spatially referenced in GIS such that spatial patterns of nitrogen loading can be analyzed. Additionally, the uncertainty associated with the model will be examined.

Computer Requirements

This exercise is to be performed using SAS 9.1 and ESRI ArcGIS 9.3. Both of these programs are available in the LRC. SAS is only installed on the machines in ECJ 3.301.   The data file (50MB) for this exercise can be obtained at http://www.ce.utexas.edu/prof/maidment/STATWR2009/Ex7/Ex7Data.zip   The Sparrow program and its support files are also needed in this class and they can be obtained from:  http://water.usgs.gov/nawqa/sparrow/sparrow-mod/sparrow_package_v2_8.zip (70MB)  These files have been unzipped and are accessible at the LRC in the class directory \class_files\MAIDMENT\StatWR2009\Ex7.

To get to the class directory while in the LRC, In Windows Explorer, use Tools/Map Network Drive to map to \\civil.ce.utexas.edu\class

Click connect using “different user name”  with your  LRC username preceded by ce-lrc\ as shown below

And navigate to:  \class_files\MAIDMENT\StatWR2009\Ex7

Where you will see the following file directories that contain the unzipped versions of these files.

Running the Model

This section describes how to obtain the SPARROW model yourself using the files accessible from the USGS web site. If you don’t have SAS available to you, or run into trouble executing this section of the exercise, just skip to the next section on Interpreting SPARROW Results and continue on from there.

A complete package of files for running SPARROW can be obtained from the USGS website http://water.usgs.gov/nawqa/sparrow/sparrow-mod.html

In this exercise, we are going to use Version 2.8.

Download this from http://water.usgs.gov/nawqa/sparrow/sparrow-mod/sparrow_package_v2_8.zip

These files are already available unzipped at \class_files\MAIDMENT\StatWR2009\Ex7\sparrow in the class directory in the LRC.

If you unzip this file, you will find 4 different folders: Master, Data, Results and GIS.

You can open the different folders to see how the model is organized. The following figure shows you the structure of the model. As you can see, the outputs files from model run will be put in the results folder where the “sparrow_control” file we will use to run the model is already located.

Copy the results folder and place it in your own directory, not in the class directory. This is so that each time the model is run, you generate your own results rather than generating results for the whole class.

Open SAS 9.1 (available in the LRC in Room 3.301) and using File/ Open Program,

navigate to the results folder you just copied to your own directory and open the File “sparrow_control_example.sas”.

Once the model is open, the first thing that you have to do is to tell the model where to find the data and where to put you results. In order to do that, you have to change some lines of the code.   Here is what they look like in the original results file prepared by Greg Schwarz of the USGS:

And here is how this file would be edited to work from z:\sparrow in the LRC:

Use File/Save in SAS to save the resulting amended file.

Now you can run the model by clicking the  button.

You’ll see a lot of stuff going by on the screen.  Its pretty amazing that this stuff all works.  You are computing flow and nutrient fluxes across the whole United States in just a couple of minutes!!

The results you get in SAS are difficult to interpret as they appear in SAS. We are going to use ArcGIS 9.3 to see them spatially.

Interpreting SPARROW Results

Open your results folder.  Several files have been created.

The one that we will use is the predict.txt  file. These results are available in a SAS file and in a text file, but in order to see them in ArcGIS 9.3, we need an Excel file.

Open the “predict.txt” file with Microsoft Office Excel (this file is in the zip file compiled for this exercise so if you don’t succeed in obtaining it yourself, you can just pick up the computed result and move on). You can see all the predicted Nitrogen values the model has calculated.

When you open this file, you’ll see a big table with more than 60,000 rows in it, each indexed by a River Reach (rr) – these are reaches in the USGS Enhanced River Reach file of the US http://water.usgs.gov/GIS/metadata/usgswrd/XML/erf1_2.xml   As you read across the headers in this file, you’ll see notations for each of the model variables, so each row in this table gives the SPARROW results for one River Reach.

ArcGIS 9.3 cannot handle Excel 2007 files so you need to save this file as an Excel 1997-2003 .xls file (not .xlsx).

Viewing the Results in ArcGIS

Now that the model has been run, we can view the results spatially by joining the ‘Predict’ table with a shapefile of the watersheds. For this exercise we will be looking at the results for HUC Region 12, which encompasses much of the great state of Texas.

The GIS data required for this portion of the exercise has been compiled and is available at http://www.ce.utexas.edu/prof/maidment/STATWR2009/Ex7/Ex7Data.zip

Unzip the GIS data to a directory of your choosing.  SPARROW.mdb  is a geodatabase  containing a line feature class of the HUC Region 12 river reaches the SPARROW model is based on (erf1_2_reaches), a polygon feature class of the corresponding watersheds (erf1_2_ws), and a polygon feature class of the state of Texas for reference.

Open ArcMap

and add these three feature classes by clicking on the  button and navigating to the location where the geodatabase is saved.

Change the symbology by right clicking on each data layer and selecting Properties, then selecting the Symbology tab or simply by double clicking the symbol displayed beneath the layer name.

Use File/Save As to save your map file as Sparrow.mxd

Next, add the table created from the predict.txt file. Again, use the  and navigate to the excel file Predict.xls and select the Predict$ worksheet.

Once this table is added, it will show up in the ‘Source’ window on the left hand side of the ArcMap window. This table can be opened by right clicking the table name and selecting Open. This table should appear exactly as it did in Excel.

Lets put this table in the Geodatabase so that we’ll be able to join it to the spatial data easily.  Right click on the Predict$ file and choose Data/Export

Navigate to the SPARROW geodatabase and store the result as SASPredict as type File and Personal Geodatabase Tables.

Now we can connect the information in the prediction table to the watersheds by creating a join based on the waterid. Right click on the watershed polygon erf1_2_ws and select Joins and Relates à Join to build this relationship.  Be patient –this export takes about 5 minutes.  Say Yes to add the resulting table to the map

Now, lets join the predicted nutrient loadings to the mapped watersheds .  Right click on erf1_2_ws and  select Joins and Relates/Join

The join will be based on GRIDCODE in the watersheds polygon layer and will be joined to the waterid in the Predict table. Keep all records in the target table, and choose Yes for creating an index field in the additional dialog box below.

Resave your map file as Sparrow.mxd so that if you have a crash later, you can restart from this point.

After completing the join, open the attribute table of the watersheds polygon by right clicking on that layer and selecting Open Attribute Table. You will see that the entries from the prediction table have been attached to each watershed. Now we can symbolize the watersheds according to their nitrogen loading in order to understand the spatial patterns.

Right click on the watershed layer erf1_2_ws and select Properties. Select the Symbology tab then select Quantities à Graduated Colors. Select PLoad_Total as the Value and change the number of classes to 15 so that we have a greater degree of graduation. The classification type, Natural Breaks, picks class breaks that best group similar data values while maximizing the differences between classes.

The result is a map of the watersheds which is color coded to identify the magnitude of the predicted load.   You can see that the key loadings are coming down the main rivers, especially those in central and east Texas that drain agricultural lands, the Sabine, the Trinity and the Brazos Rivers.

To be turned in: A map of the total predicted load for each watershed. Identify the spatial trend for the data.

This symbology can be saved as a layer by right clicking and selecting Save as Layer File… Name this layer accordingly, in this case pload_total. By doing this for each change in symbology, we can go back and add each layer file to the display so that the different attributes can be viewed more easily.

Repeat this process, but change the value field from PLOAD_TOTAL to PLOAD_Point. This will display the predicted point load for each watershed. Point loads come from industrial and municipal sources.  Notice how different the spatial pattern is and how much more prominent is the impact of the major cities of Texas.

Next, symbolize the predicted load for atmospheric deposition, fertilizer, waste (from livestock) and non agricultural by changing the value field of the graduated symbology to PLOAD_ATMDEP, PLOAD_FERILIZER, PLOAD_WASTE, and PLOAD_NONAGR respectively.

If you use the  button in the standard Tools toolbar, you can click on features and see their attributes.   Select the feature class erf1_2_ws to Identify from and click on a catchment.  You’ll see all its characteristics:

This is the catchment with WaterID 41287 on the Trinity River near its outlet to Galveston Bay.

The definitions of each of these variables are contained in the Sparrow documentation which can be accessed at:  http://pubs.usgs.gov/tm/2006/tm6b3/PDF.htm  In particular, in Appendix D, section 6, there is a definition of all prediction variables and an explanation of what they mean.  Appendix D is obtained at http://pubs.usgs.gov/tm/2006/tm6b3/PDF/tm6b3_appendix.pdf

Summary of items to be turned in:

A map of each different predicted loading. Discuss the results. Which sources contribute the greatest amount of nitrogen to the overall load? How are these loads distributed spatially (i.e. are they concentrated in certain regions, along certain rivers, do the different sources follow different patterns)?

“Tell the story “ of the catchment with WaterID 41287 on the Trinity River near Galveston Bay.  How much flow and nitrogen loading comes in from upstream and from what sources?  How much is added in the local area?