Tributary Load
Calculations
File:
Features
Concentration and flow data are retrieved from the access database.
Output is summarized on an average, yearly, and monthly basis within the workbook.
Produces a daily output file that can be appended to the historical loading database and subsequently analyzed.
See general
instructions for installation
and the user interface.
Source
Data
Data are retrieved from the 'master-filtered' query of the access database and
paired with the corresponding daily flow retrieved from the 'flows' table. The linkage is defined in the source
index sheet of load_calculations.xls.
As specified in that query, records with fatal flags (sample, result, or outier) are excluded (Flag code = 2) and records with provisional flags (Flag code = 1) are included. The query can be changed to limit the data to specific sites, date ranges, etc.
Sample concentrations are averaged by date before being used in the calculations. Future enhancements to the software could provide flow-weighting of concentrations within days for more accurate processing of storm event samples. This would require compilation of short interval (15-60 minute) flow record from each tributary.
Run for a Single
Site & Variable
Review the site and variable index sheets. Follow instructions there.
Select a site and a water quality variable from the menu window.
Set date range for calibration of the regression model (recommend at least 5 years). The tributary database starts in January 1985.
Set date range for computation & output of loads. Typically, this would be most the recent 2-3 years to prepare annual reports and update the historical tributary loading database with final USGS flow data (see updating procedure for historical loads database). The entire calibration period can also be selected.
Select computation method (default = regression with interpolation)
Click the button labeled 'Run for Selected Site & Variable'.
Review output pages & graphs selected from the sheets
menu.
Run for Multiple
Sites & Variables
Select sites by entering '1' in the 'Batch' column of the site_index sheet.
Select variables by entering '1' in the 'Batch' column of the variable_index sheet.
In the 'Input Parameters' section below the menu, enter the
database subdirectory
for publishing batch output. If the 'Publish Batch Output' box on the
menu screen is checked, a snapshot of
the ''Charts" sheet
for each variable & site will be stored there in .gif format (see
Output Storage & Retrieval). Do not specify
the 'web/demo' subdirectory that is used for documentation files.
In the 'Input Parameters' section, enter a filename for storing daily output in
ASCII format. The file should have a '.txt' extension. This file can
be uploaded and appended to the historical
loading database.
Click the button labeled 'Run for All Sites & Variables'.
Review batch output pages accessed from the menu.
Click 'Save Batch Results' to store output tables and charts in a separate workbook for future reference or further analysis.
Computation
Methods
Select default method on the menu:
1. Flow-wtd-mean conc, 1 flow stratum
2. Flow-wtd-mean conc, 2 flow strata (~AUTOFLUX method)
3. Interpolation (concentration vs. date)
4. Multiple regression vs. flow, season, & date
5. Method 4 with
residual interpolation (recommended)
Each method uses only those samples collected on
dates with positive flow.
Methods 3 -5 require at least 20 samples, otherwise Method 2 is used.
Method 2, in turn, requires at least 2 samples per flow stratum, otherwise Method 1 used.
Mean loads computed using each method are displayed on the menu sheet.
Time series results are
provided only for the selected method.
Regression
Algorithm
Adapted from: Walker & Havens,
2003 See
Equations.
Steps:
1 Retrieve Daily Flows for Desired Period and Site from Access Database
2 Retrieve Concentrations for Desired Period, Site, & Parameter from Database
3 Average Sample Concentrations by Day
4 Pair Mean Daily Flow with Mean Daily Sample Concentrations
5 Calibrate Regression Model Relating Concentration to Flow, Season, & Trend
6 Apply Regression Model to Each Sampled Date
7 Compute Residual for Each Sampled Date = Ln ( Observed / Predicted Conc)
8 Compute Residual for Each Day by Interpolating between Sampling Events
9 Apply Regression Model to Each Day in Period
10 Combine Predicted Concentrations & Interpolated Residuals on Each Day
11 Multiply Concentration by Mean Daily Flow to Compute Mean Daily Load
12 Store Daily Results in a Text File for Later Uploading to the Historical Loading Database
13 Summarize Results on Monthly, & Yearly Time Intervals
14 Compute Standard Error of Mean Load Estimate for Each Year and the Entire P
