GEOG 1011
LANDSCAPES
and WATER
Accessing Streamflow Data via the Worldwide Web
OBJECTIVES: 
(a) to use the worldwide web to access hydrologic data; 

(b) to compare precipitation/runoff characteristics in
different
regions. 
BACKGROUND: The National Weather Service (NWS), the US
Geological
Survey (USGS), and various other federal and state agencies in the US
share
the responsibility of collecting, reporting and maintaining hydrologic
data. There are literally thousands of precipitation and
streamflow measurement
stations across the country. Some of these stations have been in
operation
for over 100 years and they provide key information on longterm
changes in climate and surfacewater hydrology.
DATA: Web sites maintained by the USGS and the NWS provide
information on current conditions of precipitation and streamflow
throughout the country. Through these sites you can also access
historical records of precipitation and streamflow, and recently
published
reports.
ASSIGNMENT:
We suggest you open a WORD document, and use the copy/paste commands
to place
your graphs and results within that document. Clearly identifying
the question number, e.g. 1a) 1b)
.....
6a) 6b) and so on.
Be sure to include:
 your name
 your section & T.A.
SUGGESTION: Open TWO WINDOWS in the web
browser
of your choice. Keep the assignment on one page and use the
other
page as a base to access links. This will allow you to have the
questions
available as you look at the reference sites.
1. Open the USGS water resources web site http://water.usgs.gov/
a) Select the small map of the US labeled WaterWatch http://water.usgs.gov/waterwatch/
b) List two general regions of the country where streamflows are
presently
above / below average:
above average:
below average:
c) Click on one of the states in a region with above average flow, and
you should see a more detailed map showing the locations of individual
gaging stations. Click on one of the black dots.
You should see a
menu similar to the one shown below on the left panel.
Click on the tab labeled 'hydrograph' and you will see recent changes
in discharge, as shown in the middle panel. Click on the tab
labeled 'peak' and you will see peak discharges for each year in the
period of record.
The data presented in these graphs can be retrieved by selecting
different options, and if you are interested in doing further data
analysis, such as flood frequency analysis, you can download individual
data sets in different formats.
2. In addition to streamflow and water quality data, the USGS
provides
information on drainage basin characteristics for many streams and
rivers
(e.g. drainage area, average elevation, channel gradient, mean annual
precipitation,
percent forest cover, and so on). These data can be used to develop
relations
for streamflow and floods in drainage basins where there are no gaging
stations. To illustrate this approach we will
use data from 20 streams in the Colorado Front Range, and try to
correlate the mean annual flood (MAF) with relevant variables such as
drainage
area (DA), mean annual precipitation (MAP), and average basin slope
(SLOPE).
a) Open Microsoft EXCEL. Then, click on the link below. You
should see a table listing data for the individual drainage basins.
Table 1. Values of
drainage
area
(DA),
mean annual precipitation (MAP) and average basin slope (SLOPE) and
mean
annual flood (MAF), for 20
streams in the Colorado Front Range.
b) Highlight the values listed in the table, and use the Copy and Paste commands to paste the values
into
the EXCEL spreadsheet.
c) Using the chart options within EXCEL, construct three XY scatter
plots
showing relations between (i) MAF and DA; (ii) MAF and MAP; and MAF and
SLOPE. The procedures for making XY scatter plots are as follows:
 In newer versions of EXCEL:
 select Insert from the
menus at the top of the page, then click on Scatter Plot.
 click on the tab Select Data,
and
in the dialog box that appears, choose Add.
 enter the series name, e.g. MAF vs. DA, and select the range
of cells on the spreadsheet corresponding to the X values (DA), then
select the range of cells corresponding to the Y values (MAF).
 In older versions of EXCEL:
 select the Chart Wizard (Graph/Plot) icon from the toolbar,
then select Scatter Plot.
 click on the tab Series,
and
in the dialog box that appears, choose Add.
 enter the series name, e.g. MAF vs. DA, and select the range
of
cells on the spreadsheet corresponding to the X values (DA), then
select the range of cells corresponding to the Y values (MAF).
d) Add axis labels to your chart. In new versions of EXCEL,
the tab for axis titles can be
found under the layout
menu. In old versions of EXCEL, select Chart Options under the Chart menu.
e) Add linear trendlines to each of the plots.
 In newer versions of EXCEL:
 select Layout from the
menus at the top of the page, then click on Trendline.
 scroll to the bottom of this box and click on the tab More Trendline Options
 Among the trendline options, select Linear, and at the bottom select Display Equation and Display Rsquared value.
 In older versions of EXCEL:
 make sure the chart is selected, then, under the Chart menu at the top of the page,
select trendline.
 in the box that appears, select linear, then options; at the bottom of the next
box, select Display Equation
and Display Rsquared value.
f) Repeat steps c)  e) to construct the two additional plots, MAF vs.
MAP, and MAF
vs.
SLOPE.
g) Select each of the plots individually, copy them, and paste them
into your WORD document. Perhaps resize them so they fit on one
page.
h) Answer the following question:
Which of the three variables DA,
MAP, or SLOPE
is better for predicting the MAF? Explain.
NOTE: SAVE YOUR WORD
DOCMUENT PERIODICALLY IN CASE YOUR COMPUTER CRASHES!!
3. Climate observations and data characterizing trends in
precipitation, temperature, snow accumulation, etc. are available
through various federal and state agencies, including the National
Weather
Service, the Natural Resources Conservation Service, the US Forest
Service and state climatology offices. For this next exercise, we
will use NWS data to evaluate the significance of longterm trends in
precipitation at two locations in the USA, Burlington, VT, and Boulder,
CO.
a) Using your web browser, go to the NWS web site for Burlington, VT:
http://www.nws.noaa.gov/climate/index.php?wfo=btv
b) Select the tab Local
Data/Records, and click on the link to Monthly
Temperature/Precipitation/Snowfall
Averages & Totals.
Click on the link to Precipitation.
You
should see a table of monthly and annual precipitation, starting in
1884.
c) Open a new spreadsheet in
Microsoft EXCEL. Highlight all the data from 18842008, and use
the
Copy and Paste commands to paste the values
into
the EXCEL spreadsheet.
d) If the data fall nicely into 14 columns, go to the next step;
otherwise, go to the Data menu
at the top of the page and select Text
to
Columns. Select Fixed
Width, then Finish.
e) Delete the columns corresponding monthly precipitation
(JANDEC). To do this, highlight columns BM and select delete from the edit menu. You
should now have just two columns corresponding to the year and the
annual precipitation, in inches.
f) Using the chart options within EXCEL construct a scatter plot of
annual precip vs. year. Label the axes accordingly and fit a
linear trendline to the data.
g) What would you say just looking at the data... is the trend
significant? Sometimes it's hard to tell. We can, however,
conduct a statistical test of the slope of the trendline, m, to determine whether it is (or
is not) significantly different from zero. The procedure is as follows:
 EXCEL includes many mathematical and statistical functions,
including the LINEST function for doing simple linear regression.
To use this function, move to an open area of the spreadsheet below the
data, and highlight a contiguous 2 x 5 block of cells (2 columns and 5
rows). Enter the LINEST function in the upper left cell.
This function is somewhat elaborate:
=LINEST(known_y's,known_x's,const,stats)
 Enter the range of cells corresponding to the Y values
(PRECIP), then the range of cells corresponding to the X values (YEAR),
leave ‘constant’ blank, and enter 1 for stats. To enter the
function, press shft+ctrl, then enter (if you own a Mac, press
shift+command, then enter). You should see a 2 x 5 matrix of
numbers like those in the table below. These numbers correspond
to statistical parameters as follows:
slope of the regression line, m

0.045

54.54

intercept, b

standard error of m, SEm

0.013

25.150

standard error of b

coefficient of determination

0.091

5.213

standard error of y

F statistic

12.257

123

degrees of freedom

regression sum of squares

333.1

3342.4

residual sum of squares 
 In conducting statistical tests we must first state a
hypothesis. In this case our hypothesis is that the slope of the
trendline is 0, meaning there is no significant relation between X and
Y. We state the hypothesis as follows:
Ho : m = 0
H1: m ≠ 0
 To evaluate this hypothesis we use the t statistic, t = m/SEm,
where
m is the slope of the
regression line, and SEm is
the standard error of m.
These
values are located in the first two cells of the first column of
numbers. You can enter a formula below the 2 x 5 matrix to
calculate the value of t (call it the calculated t). Below that
you can enter the critical value of t using another EXCEL function:
=TINV(p,
df), where p = 0.05, and df is the degrees of freedom, located in the
4th row of the second column of numbers (123 in this case). If
the absolute value of
the calculated t is greater
than the critical value of t, then you reject Ho, and conclude that
the slope of the line is significantly different from 0.
h) Copy and paste the plot of PRECIP vs. YEAR into your Word
document. Also copy the table of parameter values and results
of the t test. What do you conclude about trends in annual
preciptation in Burlington, VT?
i) Conduct a similar test of trends in annual precipitation in Boulder,
CO for the period 18942008. To access these data, click on the
link to Table 2.