GEOG
2043
ENVIRONMENTAL FIELD TECHNIQUES
Lab Exercise 1
Retrieving Streamflow Data:
An Introduction to the Web and Excel
OBJECTIVES
-
To navigate the web, download and manipulate data from the USGS Water Resources
Site
-
To use a spreadsheet program to analyze streamflow measurements
BACKGROUND
The U.S. Geological Survey (USGS) maintains an extensive data
base characterizing hydrologic conditions thoughout the US. The data base
includes historical flow and water quality measurements from thousands
of gaging stations. Some of these stations have been in operation for over
100 years, while others were abandoned long ago. The existing network cosists
of roughly 7000 gages, many of which are capable of trasmitting streamflow
data in approximately real time. To access the USGS site, click on the
following link:
http://water.usgs.gov/
Note that, in addition to water data, you can retrieve publications,
and information on various programs, equipment and techniques.
TASKS AND QUESTIONS
Retrieving Real-Time Data
1. Click on the link to real time data.
The map shows current streamflow conditions in the US.
a) Which region(s) of the country is (are) particularly dry at the moment?
b) Which region(s) of the country is (are) particularly wet at the moment?
c) Click on the state of Texas, and you will see a list of all the gaging
stations in Texas. Scroll through the list to find station 08111500
the Brazos River nr. Hempstead, TX. Click on the station number.
You should see a plot showing variations in the discharge of the Brazos
River over the last 7 days.
-
Change the number of days to 31 and click on the box labeled "get data".
You should now see that there was a relatively large flood on the Brazos
River in mid-August.
-
List the approximate value of the peak flow on Aug. 16, 2002.
Return to the main USGS page (http://water.usgs.gov), and click on the
link to NWIS Web.
-
Scroll through the options and select the link to PEAKS. You'll
see a screen that allows you to choose sites, using various criteria.
-
Toggle that box labeled site number, then hit "submit".
-
Enter the site number for the Brazos River nr. Hempstead, TX (08111500);
hit "submit".
-
Click on the site number. You will see a graph of the peak flow for each
year of record. This graph is useful for visualizing the data but not particularly
useful for doing any analyses.
-
To see a table listing the data, select "tab-separated" file under the
output formats.
d) Compared to other floods on this river, how would you rate the Aug.
16, 2002 flood?
Retrieving Historical Data
Return to the main USGS page (http://water.usgs.gov), and click on the
link to Surface Water.
-
Select Colorado in the box labeled Geographic Area, and hit "go"
-
Scroll through the list of options and select the link to MEASUREMENTS.
You'll see the same screen, allowing you to choose sites, using various
criteria. Suppose you don't know the site number of the gage? However,
perhaps you know the county where's it's located.
-
Toggle that box labeled county, then hit "submit".
-
Scroll through the list until you come to Eagle County; hit "submit".
-
Select Eagle R bl Wastewater Treatment Plant at Avon, CO, site number 09067020
-
You will see a table listing the data and comments from actual stream discharge
measurements.
These measurements can be quite useful, although not in this particular
format. It is easier to work with the data using a spreadsheet software
program such as EXCEL. To create a file that EXCEL can read,
-
select tab-separated file as the output
option;
-
save the file to the D drive of your computer.
The remainder of the lab focuses on basic data manipulation and graphical
presentation of results using a spreadsheet software program (EXCEL). For
basic tips on using EXCEL follow the link at the bottom of the page.
Using EXCEL
EXCEL can be located in the directory of "programs" on most PCs on campus.
Start the program by clicking twice on the icon. To open the file saved
in the steps above, you must first navigate to the D drive and find the
file. Once you've found the file follow these steps:
-
Select open from the file menu. A dialogue box will prompt
you for information about the format of the file. In many cases the data
are delimited by some character (tab, space, or comma), so you should select
this option (note: in other cases the data may be formatted in fixed-width
columns, thus you might want to check the other option....however, not
in this case).
-
The next dialog box asks for more specific information about the delimeter;
in this case, you should select only tab, since that's how the USGS
data are formatted. A spreadsheet should appear with the data aligned in
columns.
ADDITIONAL TASKS AND QUESTIONS
1. Make a plot of discharge vs. gage height. If you don't know
how to do this, follow these steps:
-
Select the CHART WIZARD from the tools at the top of the spreadsheet.
The chart wizard will prompt you for the data range.
-
Select the columns corresponding to gage height and discharge; these are,
respectively, the x an y variables on your plot.
-
Step forward through the various plot options: -> scatter plot -> format
-> titles, axis labels, etc.
-
If you failed to enter the axis labels, or you don't like the appearance
of the plot you can change many things. Commands to make changes are access
via the menus at the top.
2. Fit a trendline through the data. The command to do this can
be found under the ?? menu. When you select trendline, you will be presented
with options on the type of line.
a) Which option should you select?
b) Note the tab to select additional options; select this and check
the box display equation on chart.
c) Write the equation here:
d) Rearrange the equation and express the gage height as a function
of discharge:
e) In simple stream channels the depth should vary as a function of
four things: the discharge, the roughness, the width, and the slope, all
raised to the 0.6 power (see class lecture notes). How does the exponent
in the relation for the Eagle River compare? Can you offer an explantion
for the difference?
Help with Web Operations and EXCEL