STEPL 2'2: Spreadsheet Tool for Estimating Pollutant Load

1
STEPL 2.2 Spreadsheet Tool for Estimating
Pollutant Load

• US EPA Romell Nandi (Nandi.Romell_at_epamail.epa.gov
  )
• Tetra Tech Ting Dai (ting.dai_at_tetratech-ffx.com)
  and
• Henry Manguerra (henry.manguerra_at_tetratech-ffx.com
  )

Visit us at www.ttwater.com
2
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

3
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

4
Process
5
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
  users input expected default B type
• Soil N, P, and BOD pollutant concentrations

6
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

7
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

8
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

9
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.

10
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

11
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
12
STEPL Main Program

• Run STEPL executable program to create and
  customize spreadsheet dynamically

13
STEPL Spreadsheet
Composed of four worksheets
14
Input Worksheet

• Location of watershed (state, county) and weather
  station provide default values for rainfall and
  USLE parameters

15
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

16
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.
17
Input Worksheet, cont.

• Optional input tables default data are used

Initial estimate is available from the on-line
data server.
18
BMP Worksheet
Select BMP for each land use type from pull down
list
Default BMP efficiencies are automatically specifi
ed for each selected BMP
19
Total Load Worksheet
Each row of results corresponds to a different
watershed or project.
20
Graph Worksheet
21
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.

22
STEPL Online Input Data Server
23
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
24
STEPL Online Input Data Server Basic Report
Data is summarized by HUCO polygon
25
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
26
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
27
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.

28
STEPL BMP Calculator

• Describe schematically BMP configuration
• Number and linkages
• BMP type and efficiency
• Land use area
• Calculate combined
• efficiency

Delete Connection
Add BMP box
Draw Connection
Calculate combined efficiency
Move BMP box
29
STEPL BMP Calculator
Series
30
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

31
STEPL BMP Calculator
Combination
32
Summary

• 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

Demo

• STEPL program
• Distributed land use calculation system (based
  on MRLC land use map)

33
STEPL Additional Reference Slides
34
STEPL Components
Downloaded Zipped File (.zip)
Run one time
Unzip utility
Setup Program (setup.exe)
Run one time
Run whenever customizing a spreadsheet
Main Program (STEPL.exe)
Customized Excel spreadsheet (user selected
filename)
May reuse spreadsheet by saving it into a new file
35
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 gt Options
submenugt General tab
36
Hydrology Algorithm Summary

• Estimate average rainfall/event using corrected
  rainfall and number of rain days (days that rain
  amount gt 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

37
Nutrient Algorithm Summary

• Simple loading functions (concentration x runoff)
• Agricultural nutrient concentrations adjusted by
  animal density
• Population impact relates to septic tank failure
  rate.

38
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

39
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 Smal
l grains 25.0 5.00 Hay 36.0 8.70 a/. Dornbush et
al. (1974). b/. Edwards et al. (1972) c/.Gilbertso
n et al. (1979) manure left on soil surface.
40
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 gt 90 Forest 0.19 0.06 0.07
gt 75 Forest 0.23 0.10 0.07 gt 50
Forest 0.34 0.25 0.18 gt 50 Agriculture 1.08 0.6
5 0.83 gt 75 Agriculture 1.82 0.80 1.70 gt 90
Agriculture 5.04 0.77 0.71 Phosphorus /b gt
90 Forest 0.006 0.009 0.012 gt 75
Forest 0.007 0.012 0.015 gt 50
Forest 0.013 0.015 0.015 gt 50
Agriculture 0.029 0.055 0.083 gt 75
Agriculture 0.052 0.067 0.069 gt 90
Agriculture 0.067 0.085 0.104 a/. Measured as
total inorganic nitrogen. b/. Measured as total
ortho-phosphorus
41
BMP List
42
Add New Data to BMP List

• In STEPL customized menu, click View/Edit BMP
  List
• BMPList worksheet is shown, add or delete BMPs

43
STEPL Add New Data to BMP List
Update BMP button (BMPList worksheet)
New BMP added! (BMPs worksheet)

• 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)
• Cor D\Stepl\Support\AllBMPstepl.csv
• C or D\Stepl\Support\AllBMP.csv

44
STEPL Use the Results from BMP Calculator 2

• 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).

45
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.

46
STEPL BMP Calculator - Steps
47
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

48
Sample Problem Exercise 1

• Estimate total annual load for a HUCO which is in
  Lycoming County and HUC02050205 (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.

49
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

50
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.

51
Sample Problem Exercise 1

• Enter data in the Input Worksheet (numbers in red
  in spreadsheet), contd.
• 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.
52
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
53
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.
54
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.
55
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.

56
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

57
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.
58
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.
59
Sample Problem Exercise 3

• Estimate total annual load and load reduction for
  a watershed that lies across two HUCOS within
  HUC02050205 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.

60
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)

61
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.

62
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
  __________

63
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.
64
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 trains combined
  efficiency at the same time in the same window.
  You dont need to open a separate BMP window for
  each BMP train (see illustration below).