CE 397 Statistics=
in
Water Resources
Exercise 7
SPA=
tially Referenced Regressions O<=
/u>n
Watershed Attributes (SPARROW)
by:
Maelle Limouzin, Kate Marney, Laura Read, and Da=
vid
Maidment
University of Texas at Austin
and Jonathan Goodall
University of South Carolina
March 2009
Contents
Introduction
SPARROW Model
Background
Recent
Publications
Goals of this Exercise.
Running the Model
Interpreting SPARROW Results.
Viewing the Results in ArcGIS.
Summary of items to be turned in:
Introduction
SPARROW is a water qualit= y 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 nut= rients 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 t= hese loads whose coefficients are determined in SAS. SAS is also used to determin= e the resulting model loadings once the coefficients are found. This exercise is just about comput= ing the resulting model loadings. For more information on SPARROW, see
http://water.usgs.gov/nawqa/s= parrow/
SPARROW Model
Background
The SPARROW method spatially relates collected water q= uality data to the characteristics of a river basin. The model has been used to trace t= otal 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 modeli= ng chemical transport through a basin.
Motivation for creating the SPARROW method began in 19= 72 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 t= hese 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 incr= ease in hypoxia and eutrophication at the Louisiana-= -Gulf of Mexico confluence. The res= ults 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 SPARRO= W’s ability to compute a complicated regression on many input variables (waters= hed attributes, nitrogen sources, etc) and draw useful connections from the out= put to try to explain an occurring phenomenon.= How cool!
For further information on SPARROW, follow these links= to publications:
http://wate= r.usgs.gov/nawqa/sparrow/wrr97/results.html
http://wate=
r.usgs.gov/nawqa/sparrow/nature/nature.html
Goals of this Exercise
The goal of exercise is to run the SPARROW model to ob= tain 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 patte= rns of nitrogen loading can be analyzed. Additionally, the uncertainty associat= ed with the model will be examined.
Computer Requirem=
ents
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 Win= dows Explorer, use Tools/Map Network Drive to map to \\civil.ce.utexas.edu\class
![](3D"Ex7_files/image002.jpg")
Click connect using “different user name” with your LRC username preceded by= ce-lrc\ as shown below
![](3D"Ex7_files/image004.jpg")
And navigate to:
\class=
_files\MAIDMENT\StatWR2009\Ex7
Where you will see the following f= ile directories that contain the unzipped versions of these files.
![](3D"Ex7_files/image006.jpg")
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̵= 7;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
![](3D"Ex7_files/image008.jpg")
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 fold= ers: Master, Data, Results and GIS.
![](3D"Ex7_files/image010.jpg")
You can open the diff=
erent
folders to see how the model is organized. The following figure shows you t=
he
structure of the model. As you can see, the outputs files from model run wi=
ll
be put in the results folder where the “sparrow_control” file we
will use to run the model is already located.
![](3D"Ex7_files/image012.gif")
Copy the = results folder and place it in yo= ur own directory, not in the class directory. This is so that each time the model = is run, you generate your own results rather than generat= ing results for the whole class.
Open SAS 9.1<= /b> (available in the LRC in Room 3.301) and using File/ Open Program,
![](3D"Ex7_files/image014.jpg")
navigate to the results fol= der you just copied to your own directory and open the File “sparrow_control_example.sas”.
![](3D"Ex7_files/image016.jpg")
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 i= n the original results file prepared by Greg Schwarz of the USGS:
![](3D"Ex7_files/image018.jpg")
And here is how this file would be edited to work from z:\sparrow in the LRC:
![](3D"Ex7_files/image020.jpg")
Use File/Save in SAS to save the resulting amended file.
Now you can run the model by clicking the ![](3D"Ex7_files/image022.jpg")
button.
![](3D"Ex7_files/image024.jpg")
You’ll see a lo= t of stuff going by on the screen. Its pretty amazing that this stuff all works.&= nbsp; You are computing flow and nutrient fluxes across the whole United States in just a couple of minutes!!
The resu= lts 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 res= ults folder. Several files have been creat= ed.
![](3D"Ex7_files/image026.jpg")
The one that we will use is the predict.txt file. These results are avai= lable in a SAS file and in a text file, but in order to see them in ArcGIS 9.3, we need an Excel file.
![](3D"Ex7_files/image028.jpg")
Open the “predict.txt” file with Microsoft Of= fice 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 comp= uted result and move on). You can see all the predicted Nitrogen values the model has calculated.
![](3D"Ex7_files/image030.jpg")
When you open this fi=
le,
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 Rea=
ch
file of the US http://w=
ater.usgs.gov/GIS/metadata/usgswrd/XML/erf1_2.xml As you read across the heade=
rs in
this file, you’ll see notations for each of the model variables, so e=
ach
row in this table gives the SPARROW results for one River Reach.
![](3D"Ex7_files/image032.jpg")
ArcGIS 9.3 cannot handle Excel 2007 files so you need = to save this file as an Excel 1997-20= 03 .xls file (not .xlsx).=
![](3D"Ex7_files/image034.jpg")
Viewing the Results in ArcGIS
Now that the model has been run, we can view the resul= ts spatially by joining the ‘Predict’ table with a shapefile of the watersheds. For this exercise we will be looking at the results for HUC Reg= ion 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
![](3D"Ex7_files/image036.jpg")
Unzip the GIS data to a directory of your choosing.
![](3D"Ex7_files/image038.jpg")
<=
/p>
Open ArcMap
![](3D"Ex7_files/image040.jpg")
and add these three feature=
classes
by clicking on the ![](3D"Ex7_files/image042.jpg")
button and navigating to the locati=
on
where the geodatabase is saved.
![](3D"Ex7_files/image044.jpg")
Change the symbology by right clicking on each data la= yer and selecting Properties, then selecting the Symbology tab or simply by double clicking the symbol displayed beneath the layer name.
![](3D"Ex7_files/image046.jpg")
Use File/Save= As to save your map file as Sparrow.m= xd
Next, add the table created from the predict.txt file.
Again, use the <=
span
style=3D'mso-spacerun:yes'> and navigate to the excel fi=
le Predict.xls and select the Predict$ worksheet.
![](3D"Ex7_files/image048.jpg")
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 click= ing the table name and selecting Open<= /b>. This table should appear exactly as it did in Excel.
![](3D"Ex7_files/image050.jpg")
![](3D"Ex7_files/image052.jpg")
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 a= nd choose Data/Export
![](3D"Ex7_files/image054.jpg")
Navigate to the SPARROW geodatabase and store the result as SASPredict as type File and Personal Geodatabase Tables.
![](3D"Ex7_files/image056.jpg")
Now we can connect the information in the prediction t= able 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
![](3D"Ex7_files/image058.jpg")
Now, lets join the predict= ed nutrient loadings to the mapped watersheds . Right click on erf1_2_ws and select Joins and Relates/Join
![](3D"Ex7_files/image060.jpg")
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 i= n the target table, and choose Yes f= or creating an index field in the additional dialog box below.
![](3D"Ex7_files/image062.jpg")
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 se= e 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 orde= r 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.
![](3D"Ex7_files/image064.jpg")
<=
/p>
The result is a map of the watersheds which is color c= oded to identify the magnitude of the predicted load. You can see that the key load= ings are coming down the main rivers, especially those in central and east Texas that drain agricultural lands, the Sabine, the Trinity and the Brazos River= s.
![](3D"Ex7_files/image066.jpg")
To be turned i= n: 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 clicki= ng 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 dis= play 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 dis= play the predicted point load for each watershed. Point loads come from industri= al and municipal sources. Notice= how different the spatial pattern is and how much more prominent is the impact = of the major cities of Texas.
![](3D"Ex7_files/image068.jpg")
Next, sy=
mbolize
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 ![](3D"Ex7_files/image070.jpg")
button in the standard Tools toolba=
r, you
can click on features and see their attributes. Select the feature class erf1_2_ws to Identify from and cl=
ick on
a catchment. You’ll see=
all
its characteristics:
![](3D"Ex7_files/image072.jpg")
This is the catchment with WaterI= D 41287 on the Trinity River near its outlet to Galveston Bay.
The definitions of each of these variables are contain= ed in the Sparrow documentation which can be accessed at: http://pubs.usgs.gov/tm= /2006/tm6b3/PDF.htm In part= icular, 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://p= ubs.usgs.gov/tm/2006/tm6b3/PDF/tm6b3_appendix.pdf
![](3D"Ex7_files/image074.jpg")
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 th= e catchment with WaterID 41287 on the Trinity River ne= ar Galveston Bay. How much = flow and nitrogen loading comes in from upstream and from what sources? How much is added in the local area?