GEOG 427/527 EXERCISE 1: STREAM DISCHARGE AND HYDROLOGY

[Return to Geog 4/527 Exercise index page]

The purpose of this exercise is 1) to familiarize you with the use of the SSIL lab and Excel, and 2) to get a feel for spatial and temporal variations in discharge.

There are three data files needed for this exercise.
    x1partA.xls, x1partB.xls: available on the course web page, under Exercises, as an Excel 97 file; will also be available through SSIL
    You will download, from the USGS web page, the data file for your gaging station and year needed for Part B.

Part A. Calculating discharge from channel measurements

Discharge can be calculated from field measurements using the water continuity equation:
Q = w.d.v = a.v

The file X1partA.xls contains field data for discharge calculations at a channel cross-section site on the Middle Fork of the John Day River. Flow was measured with a current meter following the velocity-area method. For more information on this method, see p. 159-163 in Gordon. Fig. 5.18 shows the layout of the field measurements.

1. Copy or transfer the data from the X1partA.xls to your own file.

2. When entering data or doing other work on a spreadsheet, it is wise to save your file every few minutes. Save your spreadsheet using the File-Save command. Give it a name other than sheet1.xls (the default name Excel uses with a new spreadsheet). Pick a name that will remind you what the file is.

3. First calculate the area for each section, by multiplying width times depth. Start in the empty cell under Section area that corresponds to section 1. Enter the formula: = C11*D11 where C11 is the cell containing the section width, and D11 is the cell containing the section depth. Check to make sure this gives you the right answer.

4. When the have the formula correct in the first cell, enter similar fomulas in this column for each section. You can do this easily by copying down the formula from the first cell.

5. Next calculate the discharge for each section, by multiplying section area by velocity.

6. Now calculate the total discharge by summing the discharge for each section. Go to the bottom of the discharge column. In an empty cell at the bottom of this column, enter the formula = sum(range). (Use the correct range of cells within the parentheses.) Be sure to type a label in the cell beside this cell: Discharge, cms.

7. In the next empty cell below, convert the Q, cms values to Q, cfs values. Label this column Q, cfs. The conversion factors are:
1 cms = 35.32 cfs
1 cfs = 0.0283 cms.
Refer to the Handbook of Chemistry and Physics, in the Science Library reference section, for other conversion factors.

8. Don't forget to save your file.

Part A questions:
A-1. Where do the highest velocities occur in this cross section, and do the highest velocities correspond to the deepest sections?

A-2. A group of sections within the middle of the cross-section convey most of the discharge. Identify the small set of adjacent sections that conveys >= 50% of the discharge, and give their section numbers.

Also print out and turn in your completed spreadsheet with calculations for Part A.

Discharge at-a-station is quite variable seasonally, from day-to-day, and even from minute to minute during flood events. We will analyze some daily discharge at an Oregon gaging station. Data for Parts B and C are available on the Web and in the file X1partB.

1. Sign up for a gaging station and a year on the list in class. Be sure to write down the station name, station number and water year for which you are responsible. Also write down which group you are in and the names of the other stations. The list of stations is also available on the file x1partB.xls.

2. Get the daily discharge data for your station from the Web and transfer it onto an Excel spreadsheet. To do this, you will use a Web browser such as Microsoft Internet Explorer, or Netscape Navigator. Before getting the data, it is best to have Excel running in a window, with a new spreadsheet open, so you have somewhere to paste the data.

3. Now you are ready to get the daily discharge data, for one water year, for your station from the USGS Water Resources Information web pages. Go to the WRI home page, http://water.usgs.gov, go to Water Data, Historical (NWIS-W), then go to Oregon. Enter your station number, then Retrieve data. On the station page, under Data types select Historical streamflow daily values. On the Historical Streamflow daily values page, do the following:

a. Scroll down, then enter the dates for the water year that you will analyze. For example, if you are analyzing WY 1996, enter 10-01-95 to 9-30-96.

b. Under output format, select Tab delimited text data file, date format MM/DD/YYYY.

c. Retrieve data. You will see a number of lines of station information (mainly text with few numbers). By scrolling down you will see the actual discharge data.

d. Select the station information and the actual discharge data, down to the bottom of the page, then copy it using the edit-copy command on the web browser.

e. Then go to the Excel spreadsheet and edit-paste the station information and discharge data.

4. Check the data on the spreadsheet to make sure you got everything you need. If you do have everything, you can exit from the web browser. The data on the spreadsheet should appear with the date in the first column, discharge in the second column, and "flags" (notes on data quality) in the third column. If the data are not in separate columns on the spreadsheet, you will have to use a spreadsheet command to separate them into columns. On Excel this command is Data – Text to columns.

5. As you work on your data, be sure to save the file under a name reflecting your station and water year. Use a name like the following: UmpElk82.xls. Now is a good time to save.

6. Calculate some statistics for your daily discharge data. Go to the bottom of the data, and in an empty cell at the bottom of the discharge column, calculate the mean discharge. Do this by entering a formula in the cell: =average(range). Fill in the range by selecting all of the cells that contain the daily discharge values. In another empty cell at the bottom, calculate the standard deviation of the daily discharge values. Select the empty cell. Insert-Function to get the Paste function window. Then select Statistical. Then scroll down and select STDEV, and hit OK. Excel may be able to select the correct range of cells, or you may have to move the cursor into the parentheses and select the cells yourself. You want to select the whole column of daily discharge values, but not any cells with text.

7. Make an annual hydrograph for your data. This is an XY chart with date on the horizontal axis and discharge on the vertical axis. Do it in the following way:

a. Select the cells with date and discharge and Edit copy.

b. Make a chart using the Insert-Chart command. Step 1 of 4: Select XY (scatter) chart, then select the chart picture with lines but not dots, and the one with straight angular lines, not the smoothed line. Next.

c. Step 2 of 4: Your chart should look like an annual hydrograph. The data should be represented by a line, rather than dots or squares for each data point. Next.

d. Step 3 of 4: Label the x-axis day of water year. Label the Y-axis Q, cfs. The chart title should the station name and the water year. Next.

e. Step 4 of 4: Save the chart as a new sheet within the file, named annhydro or something like that.

f. Use Chart-Options if you want to change things on the chart. You can also select part of the chart, such as an axis, and format the font size (14 point looks better than 10 point for chart axes and labels). Or select the line and change its weight or style. There are lots of other ways to edit your chart. You don’t need a legend since you have only one data series on the chart. The annual hydrograph chart should be wider than it is high – it will probably appear this way by default, but if it does not, you can change the way it fills the page. On the Page-Setup command, for example, you can change the orientation between portrait and landscape (landscape is best for annual hydrographs). You can adjust the way your chart fills the page using File-Page Setup-Chart and File-Page Setup-Margins.

g. When your chart looks good, print it chart using the File-print command. Always preview before you print, to be sure it looks the way you want it to look.

h. Make sure you can read the dates on your annual hydrograph, for interpreting the hydrograph. Your chart may print with numbers rather than dates on the x-axis. By experimenting with the formatting options you may be able to get the dates to print. If this is not successful, you can make a column of day numbers in your spreadsheet that will allow you to read the date from the number on the chart. On your spreadsheet, insert a new column beside the date column. In this column, fill in numbers from 1 (=10/1, 1st day of the water year) to 365 (=9/30, last day of the water year). There are several ways to do this. For example, you can type in 1, then in the next cell type in a formula equal to the first cell plus 1. Then copy this formula down through the rest of the cells in the column to the last day of the water year.

8. Be sure to save your spreadsheet from Part B.

9. Compare the standard flows for your station to values plotted on your annual hydrograph. For each station, peak discharges for standard recurrence intervals – Q_2yr (considered approximately equal to bankfull discharge), Q_10yr, Q_50yr, and Q_100yr -- are provided in the file x1partB.xls. Also, the discharge for a standard low flow is given – the 7-day, 10-yr low flow. This is the lowest discharge (calculated as average discharge over a 7-day period) expected to be observed once every 10 years on average. These peak discharge and low flow values were calculated using the full period of record available for the gaging station (50 years or more at most of these stations). These data are taken from Wellman, R.E., and others, 1993. Statistical Summaries of Streamflow data in Oregon: Volume 2 – Annual low and high flow, and instantaneous peak flow, U.S. Geological Survey Open-file report 93-63.

10. At some time, you may wish to go back to http://water/usgs.gov and explore around the web page. Look at Get help with terms used on these pages. Explore around, and you will find other interesting things on the USGS web page.

Part B question:
B-1. Write a paragraph describing the discharge variation that you see in your annual hydrograph. Address the following issues: range of discharge over the year, seasons of high flows and low flows, how discharges in this water year compare to the Q_1.25, Q₁₀, Q₅₀, and Q₁₀₀, and 7-day, 10-yr low flow.

Part C: Comparison across water years and stations.

1. Bring your annual hydrograph to class, labelled with the station, year and your name. They will be placed in a notebook in the Map Libary for the class members to use.

2. Look at the other hydrographs in your group (i.e., A1 or A2, etc.). Identify differences in the annual hydrographs.

Part C questions:
C-1. How does the pattern of discharge vary between the wet year (WY 1982) and the dry year (WY 1990) at your station?

C-2. How does the pattern of discharge vary among the three stations in your group? You might want to look at maps to get an idea of how the drainage basins are different. In discussing the differences, you might address issues such as mean discharge, seasonality of high and low flows, number and frequency of high flow events, and interannual variability.

Turn in your spreadsheet from Part A, your chart from Part B, and your written answers from A, B and C, all stapled together.

[Return to Geog 4/527 Exercise index page]

last update:  02/12/07 10:45 PM
Department of Geography, University of Oregon, Eugene OR, 97403-1251