Geomorphologists are interested in flooding because large hydrologic events are associated with significant geomorphic change. Whether or not large flood events are responsible for more geomorphic change than frequently occuring smaller discharge events is an important theoretical consideration of geomorphologists, and related to how landforms change. Flooding is only a problem when humans are impacted. During eary July of 2002 the Texas Hill Country received an unusually large amount of rain, which generated basin scale flooding in the rivers draining the eastern Edwards Plateau. This region, the Texas Hill Country, is poised for catastrophic flooding because of the physical setting, steep slopes and thin soils, and the abundant source of moisture from the Gulf of Mexico. Although such flooding was viewed as catastrophic, it is important to understand the magnitude of the event within the context of the historical data, which pertains to the frequency with which such events occur. The question is: How large was the flood, and how common (frequency) do floods of this size (magnitude) occur? In order to address this question you will perform common hydrological analysis, including hydrographs, flood recurrence intervals, and flow duration curves.

• the lab is due Thursday, October 15th.
• you will need a Zip disk and access to the Internet
• you will be downloading streamflow data from a Guadlupe River gauging station
• you will work with a partner in this lab
• be sure to hand in a diskette with your data (streamflow data with only the correct graphs) in Excel format, a hard copy of the plots, and a table with the discharge and duration values (see bottom of this page for details)

I. Dowload data into Excel

• Select a gauging station along the Guadalupe River (e.g. New Braunfels, Gonzales, Cuero, Victoria, etc...)
  • you will be downloading two types of data
    • 1. daily values for the period of record (through 9/30/2002)
    • 2. annual peak series (the maximum annual discharge event)
      • You should also be aware that this data is published in a bound format, referred to as the USGS Water Resources Data. These volumes are available in the Geology Library (call #: GB 1225 T4 A44).
• *unfortunately, the 2002 hydrologic year data is not yet included with the rest of the streamflow data, for this reason you will need to download this data seperately, and append it to the larger record within a spreadsheet (Excel)

• Procedure for downloading USGS streamflow data and importing into Excel

• from the USGS Water site, click on Surface Water
• choose the state (see image)
• click on Streamflow
• click Site Name
• finally enter the river name (ex: Guadalupe), and select Site Name,, and submit to obtain a list of stations for download
• click on the station to download
  • select a single station from the list of Guadalupe stations
• examine the options in the available data pull-down menu
  • this is where you will identify the type of data to download
    • you will download all of the "recent daily" (daily values from 10/01/01 to 09/30/02), "daily streamflow (usually eary 1960s to 09/30/01)", and "peak streamflow (early 1960s to 2001)"
• Station Information at the top of the screen. This provides useful information pertaining to the gauging station (history of station, elevation of gauge, basin area above gauge, etc...), and record of streamflow data.

• Choose the range of data in the Dates to Retrieve box. Be sure your data begins and ends with the limits of a U.S. hydrologic year, 10/01 and ends with 09/30, respectively
• Select Tab-delimited text data file as the output format and then select the date format you prefer (tab seperated is a good option)
• Click on the Retrieve Data button.

• repeat the above steps for each data set
• Examine your data. In the left hand column is the date pertaining to each daily discharge record, and in the right hand column is the discharge data in cubic feet per second (cfs, or, ft^3/s). Read the heading for notes and information on the format of the data.
  • Often this will provide information as to the number of missing records, whether the gauging station has been relocated, elevation of the stream gauge, drainage area, etc....
• Copy the data by selecting the data and dragging the cursor, or, press Ctrl / A on your keyboard

Next…

• In Windows, execute Notepad in the Accessories menu from the Start button
• Using the right-click button on the mouse, paste your data into Notepad and save the file to your disk.
  •     This will save the data in ASCII format
• Execute Excel
• Excel will detect the text or ASCII format, so you will have to define the data to Excel with the Import Wizard
• Save the data in an Excel spreadsheet format
• Clean-up the data by removing the header information (some of this information is useful, you may want to save it in a different spreadsheet) so that you have two columns, date and discharge

• In Excel, append the "recent daily" streamflow data for hydrologic year 2002 (10/01/2001 - 09/30/2002) with your "daily streamflow" record so that you have a continuous column of data
  • also, using the "recent daily" data, find the peak event from this past summer and append to your "peak streamflow" record

• you should now have two data sets ending in hydrologic year 2002
  • annual peak Q
  • daily Q

II-A: Gauging station information

USGS Gauging Stations

• Answer the following questions about the gauging station
  • what is the elevation of the gauging station?
  • what is the location, latitude - longitude and description (i.e. bridge of 183, south of Dallas)
  • how long has the gauging station been in operation?
  • has the station been moved?
  • are there any special consideration with respect to the data?
  • what other types of data is available?

II-B:

Daily Q:

• Compute the average daily Q for your station using the entire period of record

• at the cell beneath the last discharge value (09/30/02), enter the following formula
  • =average(a1:a5000)
  • note: the cell address and range will vary for each station and spreadsheet

 

Annual peak Q:

• using similar procedures, compute the average annual peak discharge for your period of record

III-A: Construct a Flow Duration Curve

Flow duration curves are especially useful for examining daily Q values over the entire period of record. The flow duration is the % of time a Q event of a given magnitude is equaled or exceeded. Thus, a Q of 12,000 cfs with a duration of 30% implies that 70% of the time the daily streamflow is less than 12,000 cfs. Conversely, a very high duration, for example 95%, means that the streamflow of that duration was equaled or exceeded almost every day (95% of the time). Stream ecologists often use flow duration curves to determine the frequency of low flows, which is an important consideration for many aquatic species that depend on a specific range of flow conditions.

• Plot a flow duration curve using your total discharge record
• make a copy of your discharge data and paste as a new column
• sort the data in descending order (from highest to lowest discharge)
• determine the N for each discharge value
  • N = rank of a particular event, with 1 = to the largest daily discharge event during record
    • if you have 30 years of data, your smallest daily Q will have an N of 10,950 (30*365)
  • the column should begin with 1 and end with the largest N corresponding to the lowest discharge (at the bottom of the column)
    • you will need to use Excel to fill a series of data from 1 to ... to make the N column
      • insert a # 1 in the first cell of the column to be filled, and then block the column
      • from the menu, select Edit / Fill / Series, and define the stop value (the number of values in your data set), and click on the trend box
      • this should have created a new column of data (the N values)

• compute T by entering the following spreadsheet formula into the cell adjacent your N column

• the actual cell address (b1) will vary
• this will compute T, % of time that a daily discharge event is equaled or exceeded
  • T=N/n+1, by mulitplying by 100 time is converted to a %
  • n = # days of daily data
  • b1 is the cell substitute for N, the rank of a particular daily discharge, with 1 = to the largest daily discharge during record

• your spreadsheet columns should look like this
  • note: the Q column (discharge) has been moved to the right for the purpose of plotting

III-B: Plot the data in Excel

• block the two columns, with % time on the left, and Q as the column on the right

• hold the shift button and scroll (block) to the bottom of the columns (i.e. columns H and I in the example)

• click on the Chart  icon on the menu bar (above your spreadhseet cells)
• select XY (scatter)
  • example
• Finish


Format your chart

• The range for the X axis should be from 0 – 100 (using divisions of 10)
• The Y axis should range from the minimum to the maximum discharge value so that the entire variability of your data is displayed
• Make the Y axis a logarithmic scale
•     double click on the Y axis to activate the axis options
  • select logarithmic
  • note that you can also change the font properties (style, color, size, etc...), line style (to remove symbols), rang, etc...
    • spend some time experimenting with the different options for both the X and Y axes
      • the X axis must not be logarithmic
• the completed graph should display a curvilinear pattern

IV-A: Determine the Recurrence Interval (RI) for the recent July flood event

Flood recurrence intervals (RI) are a very useful flood index. The RI is the average number of years between floods of equal or greater magnitude. It does not mean that a flood of a specific magnitude will occur every x years. For example, for a 100 year period of record the two largest floods may occur in consecutive years. Indeed, rivers do not understand statistics. However, because this statistic is one of the most commonly reported hydrological indices, it is important for you to understand how it is derived. Although there are more sophisticated procedures for computing RI, the technique outlined below has long been considered the standard in the hydrological sciences. Other techniques, such as Log-Pearson III, may be more appropriate considering the skewed distribution of most hydrographic data sets, particularly in south-central Texas, but the Gumbel method should suffice for our purposes. Geomorphologists are interested in the RI of floods because they relate to channel pattern and geomorphic change. Determining the flood recurrence interval for a gauging station is commonly done by engineers to understand the percentage of time a discharge of a given magnitude (or threshold) will occur. Such analysis are also useful in the consideration and design of engineering structures, such as a bridge or dam.

• use the annual peak Q data set

• Plot a flood recurrence interval curve
• make a copy of the peak discharge data and paste as a new column
• sort the data in descending order (from highest to lowest discharge)
  • use the sort command in Excel
    • Excel menu: data / sort
• determine the N for each discharge value
  • N = rank of a particular flood, with 1 = to the largest flood of record
  • rank the peak discharge values from 1 - … highest N in the column adjacent to your sorted peak flow data
    • the column should begin with 1 and end with the largest N corresponding to the lowest discharge (at the bottom of the column)
      • thus, if you have 50 years of data, the smallest annual Q would have an N of 50

      • you will need to use Excel to fill a series of data from 1 to ... to make the N column

        • insert a "1" in the first cell of the column to be filled, and then block the column

          • from the menu, select Edit / Fill / Series, and define the stop value (the number of values in your data set), and click on the trend box

            • this should have created a new column of data (the N values)

• compute the recurrence interval (RI = n+1/N)
  • the spreadsheet formula should be entered into the cell adjacent your N column and will appear as:

• substitute the spreadsheet cell address (ex: b1) for N
• this will compute RI, the recurrence interval (in years) of a given flood event, or, the average # of years between events equaling or exceeding a flood of a given magnitude
• n = # years
• N = rank of an annual flood event, with 1 = to the largest flood event on record

• your spreadsheet columns should look like this
  • note: the Q column (discharge) has been moved to the right for the purpose of plotting

IV-B: Plot the data in Excel

• block the two columns, with Q on the left, and T as the column on the right

• hold the shift button and scroll (block) to the bottom of the columns (i.e. columns H and I in the example)

• click on the Chart  icon on the menu bar (above your spreadhseet cells)
• select XY (scatter)
  • example
• Finish


Format your chart

• The range for the X axis should be from 0 – 100 (using divisions of 10)
  • Make the X axis a logarithmic scale
    • double click on the X axis to activate the axis options
      • select logarithmic
• The Y axis should range from 0 to the maximum discharge value (an even #) so that the entire variability of your data is displayed
  • example: if the maximum value is 89,451 cfs, make the upper limit 100,000
• do not make the Y axis logarithmic
• format your figure
  • edit the font properties (style, color, size, etc...), line style (to remove symbols), rang, etc...
• spend some time experimenting with the different options for both the X and Y axes
• print the plot and draw a trend line through the data, with the exception of the largest event

Plot your orinal time-ordereed data in Excel to construct an annual and single event hydrograph for the streamflow data for hydrologic year 2002, and a single event hydrograph for this summer's big flood event
• Use your original daily values (not the sorted and ranked data)
• Annual hydrograph (for a single water year)
•     beginning with 10/01 and ending 9/30
• Single flood event hydrograph
•     identify the flood event in the data record and plot the appropriate range of data (Q vs. time)
• For the single flood event hydrographs, identify the following:
•     peak Q (cfs)
•     identification of the different components of a flood hydrograph (runoff, baseflow, rising limb, receding limb)
• you may plot these after you have printed the graph, or within Excel using the Draw toolbar

VI: Magnitude - frequency analysis of discharge events

• From the RI curve, determine the following statistics. Use a ruler to draw lines on your plot for the statistics below. You will also report them on a separate sheet of paper to hand in with the data and plots.
  1. RI of average annual peak Q
  2. RI from this summer's Q event
  3. Q of 10 yr flood
  4. Q of 25 yr flood
  5. Q of 100 yr flood

 

• From the duration curve, determine the following statistics. Again, use a ruler to plot arrows, and report statistics on a seperate sheet of paper
  1. % occurrence of average daily Q
  2. % occurrence of the Q from this summer's flood event
  3. Q = or > 90% of time
  4. Q = or > 50% of time (median Q)
  5. Q = or > 10% of time

VII: What to hand in

• Zip disk with spreadsheets organized and cleaned up
  • spreadsheets should include RI and duration curves, and also hydrographs (annual and single event)
• plots of RI and duration curves
  • should include arrows, etc... pertaining to relevant statistics
• Data sheet of statistics, organized for the RI and duration curves
• plots of annual and single event hydrographs
  • should be appropriately labeled

• Make sure the graphs are neat and tidy. They should have consistent font sizes, scales, and colors (black and white is preferred). See the online examples of what your figures should look like

Created by pfh on 9/15/98, last modified by pfh on 09/30/02