STEPL 2.2: Spreadsheet Tool for Estimating Pollutant Load : STEPL 2.2: Spreadsheet Tool for Estimating Pollutant Load US EPA: Romell Nandi (Nandi.Romell@epamail.epa.gov)
Tetra Tech: Ting Dai (ting.dai@tetratech-ffx.com) and
Henry Manguerra (henry.manguerra@tetratech-ffx.com) Visit us at: www.ttwater.com
What is STEPL? : What is STEPL? Calculates nutrient (N, P, and BOD pollutants) and sediment loads by land use type and aggregated by watershed
Calculates load reductions as a result of implementing BMPs
Data driven and highly empirical
A customized MS Excel spreadsheet model
Simple and easy to use
Formulas and default parameter values can be modified by users (optional) with no programming required
STEPL Users? : STEPL Users? Basic understanding of hydrology, erosion, and pollutant loading processes
Knowledge in the use and limitation of environmental data (e.g., land use, agricultural statistics, and BMP efficiencies)
Familiarity with MS Excel and Excel Formulas
Process : Process
Step 1 – Source representation : Land use area
Animal counts
Septic system failure
Precipitation
Soil characteristics
USLE or RUSLE parameters for soil erosion estimation
Soil hydrologic group for runoff estimation; user’s input expected; default = B type
Soil N, P, and BOD pollutant concentrations Step 1 – Source representation
Step 2 – System calculates annual load before BMP : Step 2 – System calculates annual load before BMP Runoff
Curve Number Method (all land uses except urban area)
Erosion and Sediment Delivered
Erosion = Universal Soil Loss Equation (USLE = RKLSCP)
Sediment = Erosion x Delivery Ratio
Concentration
Default (cropland, pasture, forest, feedlot)
Load
Export coefficient x Source area (urban)
Dissolved concentration x Runoff volume
Soil concentration x Sediment volume
Step 3 – Select BMP : Step 3 – Select BMP Select a BMP by land use
Initial set of BMPs for each land use type is included in the spreadsheet
Default N, P, BOD, Sediment removal efficiencies by BMP are included when available. User may change the default values.
Specify a BMP train by land use
Use BMP calculator to specify configuration of BMP train (multiple BMP implementations) and to calculate combined efficiencies
Step 4 – System calculates annual load after BMP : Step 4 – System calculates annual load after BMP Load reduction = Load before BMP x BMP efficiency
Load after BMP = Load before BMP – Load reduction
Loads are aggregated by watershed
Data Sources : Data Sources Spreadsheet Default Data
Rainfall by county: NOAA
Rainfall correction factors by station: NOAA
USLE values by county: NRCS 1997 Natural Resources Inventory (NRI)
Other: Literature values
Default pollutant concentrations in runoff and soil
BMP list and efficiencies
Household wastewater characteristics, etc.
Data Sources, cont. : Data Sources, cont. On-line Data Server
Land Use: NRCS 1997 NRI Database
Note: Other = Federal land use, misc. minor land uses, and other farmland/CRP land.
Animal Counts: USDA 1997 Census of Agriculture
Note: D or N = data withhold or not available.
Septic Systems: NESC 1992-1998 Summary of Onsite Waste Water Treatment System
Note: If the failure rates are reported as 0% or not available (e.g. DE, DC, HI, OK, PA, and SD), we recommend to use the U.S. average rate of 1%.
Soil hydrologic group: USDA STATSGO Soil Database
STEPL Web Site : STEPL Web Site Link to on-line
Data server Link to download
setup program to
install STEPL program and documents Temporary URL: http://it.tetratech-ffx.com/stepl until moved to EPA server
STEPL Main Program : STEPL Main Program Run STEPL executable program to create and customize spreadsheet dynamically
STEPL Spreadsheet : STEPL Spreadsheet Composed of four worksheets
Input Worksheet : Input Worksheet Location of watershed (state, county) and weather station provide default values for rainfall and USLE parameters
USLE Review : USLE Review For sheet and rill erosion (water):
A = R K LS C P
A = average annual soil loss (t/ac) due to sheet and rill erosion
R = rainfall erosivity factor
K = soil erodibility factor
LS = slope length and steepness factor
C = cover and management factor
P = support practice factor
Input Worksheet, cont. : Input Worksheet, cont. Values in red are required user input Use local and site-specific data as much as possible! Initial estimates based on national data are available from the on-line data server.
Input Worksheet, cont. : Input Worksheet, cont. Optional input tables – default data are used Initial estimate is available from the on-line data server.
BMP Worksheet : BMP Worksheet Select BMP for each land use type from pull
down list Default BMP efficiencies
are automatically
specified for each
selected BMP
Total Load Worksheet : Total Load Worksheet Each row of results corresponds to a different watershed or project.
Graph Worksheet : Graph Worksheet
STEPL Online Input Data Server : STEPL online input data server can be used to obtain preliminary values for STEPL input parameters
Land uses (in acres)
Animal types and numbers
Septic system numbers and failure rate
Soil hydrological group
Data sources include national databases. User should use local data when available.
STEPL Online Input Data Server
STEPL Online Input Data Server : STEPL Online Input Data Server
STEPL Online Input Data Server : STEPL Online Input Data Server Data is available at
HUC and county
intersection (HUCO
Polygon)
Generate data
summaries Note: Zoom in further to display polygon IDs
STEPL Online Input Data Server: Basic Report : STEPL Online Input Data Server: Basic Report Data is summarized by HUCO polygon
STEPL Online Input Data Server: Custom Report : STEPL Online Input Data Server: Custom Report Data is extrapolated for a given geographic area within a HUCO or spanning multiple HUCO polygons Watershed or area
of interest within
HUCO 5153
STEPL Online Input Data Server: Custom Report : STEPL Online Input Data Server: Custom Report Data is extrapolated for a given geographic area within a HUCO or spanning multiple HUCO polygons Watershed or area
of interest spans
HUCO 5153 and 4972
STEPL BMP Calculator : STEPL BMP Calculator Calculates combined efficiency of a BMP train for a given land use. The use of BMP calculator requires the understanding of BMPs and their placement in the watershed.
STEPL BMP Calculator : STEPL BMP Calculator Describe schematically BMP configuration
Number and linkages
BMP type and efficiency
Land use area
Calculate combined
efficiency Add BMP box Calculate combined efficiency Delete Connection Draw Connection Move BMP box
STEPL BMP Calculator : STEPL BMP Calculator Series
STEPL BMP Calculator : STEPL BMP Calculator Parallel Notes:
Three boxes representing BMPs are connected to a fourth box, which in this case represents the receiving stream
The area of the receiving stream box should be set to zero
STEPL BMP Calculator : STEPL BMP Calculator Combination
Summary : Summary Demo STEPL program: Generates STEPL spreadsheet
STEPL BMP calculator: Calculates combined efficiency of multiple BMPs
Optional STEPL data server: Gives initial input data for STEPL if the local data are not available STEPL program
Distributed land use calculation system (based on MRLC land use map)
STEPL Additional Reference Slides : STEPL Additional Reference Slides
STEPL Components : STEPL Components Setup Program
(setup.exe) Downloaded
Zipped File
(*.zip) Main Program
(STEPL.exe) Customized Excel
spreadsheet
(user selected filename) Run one time Run one time Run whenever
customizing a
spreadsheet May reuse spreadsheet
by saving it into a
new file Unzip utility
Customized Menu : Customized Menu Tip: To ensure that files are linked to the customized menu, set Excel default file location to C\STEPL or D:\STEPL
Step: Tools menu > Options submenu> General tab
Hydrology Algorithm Summary : Hydrology Algorithm Summary Estimate average rainfall/event using corrected rainfall and number of rain days (days that rain amount > 5 mm or 0.2 inch)
Use NRCS’ runoff formula with Curve Numbers
R is rainfall (cm), Q is daily runoff (cm), and CN is curve number
The initial rainfall abstraction coefficient could be much less than 0.2
Calculate total runoff for different land uses
Nutrient Algorithm Summary : Nutrient Algorithm Summary Simple loading functions (concentration x runoff)
Agricultural nutrient concentrations adjusted by animal density
Population impact relates to septic tank failure rate.
Sediment Algorithm Summary : Sediment Algorithm Summary USLE for rural land uses
USLE parameters by counties
Lookup tables for urban land uses
Area-based sediment delivery functions
Sediment reduction BMP efficiencies
Data Source: Nutrient Concentration in Runoff : Data Source: Nutrient Concentration in Runoff Modify default nutrient concentration in runoff Dissolved Nutrients in Agricultural Runoff
Land Use Nitrogen mg/l Phosphorus mg/l
-----------------------------------------------------------------------
Fallow /a 2.6 0.10
Corn /a 2.9 0.26
Small grains/a 1.8 0.30
Hay /a 2.8 0.15
Pasture /a 3.0 0.25
Barn yards /b 29.3 5.10
Snowmelt runoff from manured land /c:
Corn 12.2 1.90
Small grains 25.0 5.00
Hay 36.0 8.70
a/. Dornbush et al. (1974). b/. Edwards et al. (1972)
c/.Gilbertson et al. (1979); manure left on soil surface.
Data Source: Nutrient Concentration in Runoff : Data Source: Nutrient Concentration in Runoff Mean Dissolved Nutrients Measured in Streamflow by the National Eutrophication Survey (Omernik, 1977)
Watershed Concentrations (mg/l)
Type Eastern U.S. Central U.S. Western U.S.
---------------------------------------------------------------------------------------------------
Nitrogen /a:
>= 90% Forest 0.19 0.06 0.07
>= 75% Forest 0.23 0.10 0.07
>= 50% Forest 0.34 0.25 0.18
>= 50% Agriculture 1.08 0.65 0.83
>= 75% Agriculture 1.82 0.80 1.70
>= 90% Agriculture 5.04 0.77 0.71
Phosphorus /b
>= 90% Forest 0.006 0.009 0.012
>= 75% Forest 0.007 0.012 0.015
>= 50% Forest 0.013 0.015 0.015
>= 50% Agriculture 0.029 0.055 0.083
>= 75% Agriculture 0.052 0.067 0.069
>= 90% Agriculture 0.067 0.085 0.104
a/. Measured as total inorganic nitrogen. b/. Measured as total ortho-phosphorus
BMP List : BMP List
Add New Data to BMP List : Add New Data to BMP List In STEPL customized menu, click “View/Edit BMP List”
BMPList worksheet is shown, add or delete BMPs
STEPL: Add New Data to BMP List : STEPL: Add New Data to BMP List Click “Update BMP Data” button to update the BMP selections in the BMPs worksheet
Click “Save Updates” to save changes to text files (comma delimited)
C:or D:\Stepl\Support\AllBMPstepl.csv
C: or D:\Stepl\Support\AllBMP.csv New BMP added!
(BMPs worksheet) Update BMP button
(BMPList worksheet)
Slide44 : Type the combined BMP efficiency in Table 7 in the BMPs worksheet in STEPL
Select “Combined BMP-Calculated” for cropland, forest, and urban (Tables 1, 3, and 6 in the BMP worksheet). STEPL: Use the Results from BMP Calculator 2
STEPL BMP Calculator - Steps : STEPL BMP Calculator - Steps Add a BMP (box) by clicking the BMP icon shown in the tool bar. Repeat to add more BMPs.
Specify type of BMP (for each box) and affected area by double clicking the BMP box and display dialog box shown below. Repeat for all BMPs added. Specify connections between BMPs by drawing an “imaginary” line from the upstream BMP to the downstream BMP while holding the shift key. Start drawing the line from the very edge of the box!
To improve layout, you may move BMP boxes by holding shift key, clicking and dragging the BMP to its new position. Use delete tool to delete BMP connections.
Slide46 : STEPL BMP Calculator - Steps
Sample Problem Exercises : Sample Problem Exercises Exercise #1
Estimate total annual load for a specific HUCO area, and total load reduction resulting to implementation of a (single) BMP on croplands, and urban area
Use STEPL On-line Data Server Basic Report and STEPL Spreadsheet
Exercise #2
Similar to Exercise #1 but for a specific 8-digit watershed
Use STEPL On-line Data Server Custom Report and STEPL Spreadsheet
Exercise #3
Similar to Exercise #1 but for a smaller watershed that spans across two HUCO areas, and BMP trains implemented on croplands, and urban areas
Use STEPL On-line Data Server Custom Report, BMP Calculator, and STEPL Spreadsheet
Sample Problem Exercise #1 : Sample Problem Exercise #1 Estimate total annual load for a HUCO which is in Lycoming County and HUC=02050205 (Pine Watershed) in Pennsylvania
Get data using on-line data server
Using Internet Explorer, go to http://it.tetratech-ffx.com/stepl/
Click hyperlink “Access to STEPL database for Input Data” to display on-line data server
Follow steps outlined in the on-line data server
Select state = Pennsylvania
Select county = Lycoming or select HUC = 02050205: Pine (try both and check what happens)
If you are familiar with the area, you will probably know which polygon it is. If not, click the identify tool to activate it, and click one of the selected polygons in red to query which county and HUC it is located (i.e., results shown in the table shown near the bottom of the window. Note that the table also shows the HUCO polygon ID
You may skip step #3 shown in the interface if you do not want to manually include (or select) additional HUCO polygons
Click Basic report button to generate tables showing data for the selected HUCO polygons
Just in case you are interested in knowing where the rest of the HUCO polygons are located in the map, you may use the Identify tool to identify the other selected HUCO polygons in the map, or you may use the zoom-in tool just to zoom enough to display the HUCO Ids in the map.
Do not close the windows since we will be using them later on.
Sample Problem Exercise #1 : Sample Problem Exercise #1 Generate a new custom spreadsheet. Note that you may reuse a spreadsheet you created previously for a different project.
Click Start button (e.g., normally located at the Windows bottom left corner), then Program, STEPL, and STEPL to run the STEPL main executable program (stepl.exe in /STEPL folder) and display main interface
Select options. For Exercise #1, specify the following:
Specify number of watershed = 1
Check all landuses except for User-defined (default selection)
Check all animal types
Click ok to create new spreadsheet
When the new spreadsheet is opened, click Ok button to enable stored formulas/equations in the spreadsheet
Save the spreadsheet using a new file name
For this example, you may save it to exercise1.xls
Sample Problem Exercise #1 : Sample Problem Exercise #1 Enter data in the Input Worksheet (numbers in red in spreadsheet)
By default, optional tables are not shown. Click yes to show the optional tables (Table 5-8) with their default values. Click no to hide them.
Select state = Pennsylvania, and county = Lycoming. Notice that initial values for Annual Rainfall and Number of Rain Days are specified in Table 1 as you select a state or county.
Select a weather station = NY New York Central Part. Notice that correction factors change with the selected weather station.
In Table 1, enter the land use areas for your watershed by referring back to the reported values by the on-line data server. Note that you are assuming your entire HUCO (e.g. 5153) = watershed. You may cut and paste values from one window to another.
Also in Table 1, Select the feedlot percent paved assuming feedlot area is not zero. Default value = 0-24%.
Sample Problem Exercise #1 : Sample Problem Exercise #1 Enter data in the Input Worksheet (numbers in red in spreadsheet), cont’d.
In Table 2, enter the animal counts for your watershed by referring back to the reported values by the on-line data server. (Watch for missing data, all the data entered must be numeric values!)
Also in Table 2, select the average # of months manure is applied in croplands.
In Table 3, enter septic system data by referring back to the reported values by the on-line data server. (If septic failure rate is zero, enter 1 (percent) as the national average.
In Table 4, examine the initial USLE parameter values for each land use type which were automatically specified as you selected the state and county. You can always change the default and initial data when
local data is available.
Sample Problem Exercise #1 : Sample Problem Exercise #1 Examine estimated load in Total Load and Graph worksheets and enter the results below:
Total Annual N Load (lb): ________________
Total Annual P Load (lb): ________________
Total Annual Sediment Load (ton): _____________
Source with highest annual load contribution:
N load (lb): _________ What source: __________
P load (lb): _________ What source: __________
Sediment load (lb): _________ What source: __________
Note that load reduction = 0 since you have not specified
any BMP yet – see next slide
Sample Problem Exercise #1 : Sample Problem Exercise #1 For the same HUCO area, estimate total annual load reduction assuming reduced tillage is practiced in cropland areas, and porous pavement is used in urban areas
Enter BMP data in BMPs worksheet
In Table 1 which is for cropland areas, select Reduced Tillage System under BMP column. Note that initial values of BMP efficiencies are automatically specified with the selected BMP.
In Table 6 which is for urban areas, select Porous Pavement under BMP. You can always manually change the initial BMP efficiencies
if local data is available. If your BMP is not in the selection list, refer to the user manual on how to
add it.
Sample Problem Exercise #1 : Sample Problem Exercise #1 Examine estimated load reduction in Total Load and Graph worksheets and enter the results below:
Total Annual N Load Reduction (lb): ________________
Total Annual P Load Reduction (lb): ________________
Total Annual Sediment Load Reduction (ton): _____________
Source with highest annual load contribution:
N load (lb): _________ What source: __________
P load (lb): _________ What source: __________
Sediment load (lb): _________ What source: __________
End of Problem Exercise #1 – Try adjusting your input data and reexamine the results.
Sample Problem Exercise #2 : Sample Problem Exercise #2 Estimate total annual load for the entire 8-digit watershed, HUC =02050205 (Pine Watershed) in Pennsylvania
Get data using on-line data server
Instead of basic report, you will be using custom report since you want to get the data summed up for the entire 8-digit watershed
Hint: Entire area of each HUCO is within the 8-digit watershed.
Create a spreadsheet for this project or exercise.
Instead of generating a new custom spreadsheet using the STEPL main executable program, you will be using the spreadsheet in the previous exercise.
Save the spreadsheet used for Exercise #1 to save recent changes.
Save this spreadsheet with a new name (exercise2.xls, be sure to save the file as *.xls type). This new spreadsheet will be used for Exercise #2.
Sample Problem Exercise #2 : Sample Problem Exercise #2 Enter new data in the Input Worksheet
Note that some of the data entered in the previous spreadsheet are still valid (e.g., state, county, etc.)
In contrast to Exercise #1, you will be examining the data in the Optional tables so click Yes button to show the optional tables
Note that the on-line data server provides the average soil hydrologic group. Enter this value in Table 5.
You may change the rest of the default data especially if local data is available
Sample Problem Exercise #2 : Sample Problem Exercise #2 Examine estimated load in Total Load and Graph worksheets and enter the results below:
Total Annual N Load (lb): ________________
Total Annual P Load (lb): ________________
Total Annual Sediment Load (ton): _____________
Source with highest annual load contribution: (You may unselect the BMPs to obtain source load before the implementation of the BMPs)
N load (lb): _________ What source: __________
P load (lb): _________ What source: __________
Sediment load (lb): _________ What source: __________
Note that load reductions have been calculated since BMPs have been already specified in the previous exercise. For this exercise, assume that the same BMPs are installed for all cropland and urban areas in the 8-digit watershed.
Sample Problem Exercise #2 : Sample Problem Exercise #2 Examine estimated load reduction in Total Load and Graph worksheets and enter the results below:
Total Annual N Load Reduction (lb): ________________
Total Annual P Load Reduction (lb): ________________
Total Annual Sediment Load Reduction (ton): _____________
Source with highest annual load contribution:
N load (lb): _________ What source: __________
P load (lb): _________ What source: __________
Sediment load (lb): _________ What source: __________
End of Problem Exercise #2 – Try adjusting your input data and reexamine the results.
Sample Problem Exercise #3 : Sample Problem Exercise #3 Estimate total annual load and load reduction for a watershed that lies across two HUCOS within HUC=02050205 and where all croplands are practicing reduced tillage and filter strips (shown below):
Get data using on-line data server
Use custom report to get numbers for the entire watershed.
Hint: Compute the percent area of the HUCO that is within the watershed.
Sample Problem Exercise #3 : Sample Problem Exercise #3 Create a spreadsheet for this project or exercise.
Save the spreadsheet used in Exercise #2 to exercise3.xls.
Enter new data in the Input Worksheet
Enter BMP data in BMP worksheet
In Table 1, which is for cropland areas, select “Combined-BMP calculated” under BMP column to indicate that we have a “Reduced Tillage-Filter Strip” BMP train in croplands.
Note that the N, P, BOD, and Sediment BMP efficiencies remained zero. If you have the combined efficiency values for this particular BMP train, enter them in Table 7 (number in red). These values will be reflected in Table 1 and in other tables (i.e., if the same BMP train is implemented for other land uses).
If you do not have the values, you may use the BMP calculator (next step)
Sample Problem Exercise #3 : Sample Problem Exercise #3 Use BMP Calculator to estimate combined efficiencies of the BMP train
Run the BMP Calculator by selecting the STEPL/BMP Calculator menu of the STEPL spreadsheet. If the system cannot find the BMP Calculator program, navigate to /STEPL folder and select BMPCalculator.exe
Using the BMP Calculator interface, do the following (refer back to slide 33 for steps in using BMP Calculator):
Add two BMP boxes (one each for Reduced Tillage, and Filter Strip
Enter BMP information (type, area, etc.) for each BMP box by double-clicking the box (Question: What is the area associated with the filter strip)
Specify the connection between the two BMPs (Question: Which BMP should be upstream). You may move the boxes to make them more readable
Calculate the combined efficiencies for N, P, BOD, and Sediment.
Enter the combined efficiencies in Table 7 of STEPL spreadsheet. Note the efficiencies are reflected in Table 1.
Sample Problem Exercise #3 : Sample Problem Exercise #3 Examine estimated load in Total Load and Graph worksheets and enter the results below:
Total Annual N Load (lb): ________________
Total Annual P Load (lb): ________________
Total Annual Sediment Load (ton): _____________
Source with highest annual load contribution:
N load (lb): _________ What source: __________
P load (lb): _________ What source: __________
Sediment load (lb): _________ What source: __________
Sample Problem Exercise #3 : Sample Problem Exercise #3 Examine estimated load reduction in Total Load and Graph worksheets and enter the results below:
Total Annual N Load Reduction (lb): ________________
Total Annual P Load Reduction (lb): ________________
Total Annual Sediment Reduction (ton): _____________
Source with highest annual load contribution:
N load (lb): _________ What source: __________
P load (lb): _________ What source: __________
Sediment load (lb): _________ What source: __________
End of Problem Exercise #3 – Try adjusting your input data and reexamine the results.
More Exercises for BMP Calculator : More Exercises for BMP Calculator Try different BMP trains in the BMP Calculator. Note that you may define as many trains as you want and calculate each BMP train’s combined efficiency at the same time in the same window. You don’t need to open a separate BMP window for each BMP train (see illustration below).