Long-Term Modeling of Structural BMPs using STORM within a Spreadsheet

Abstract

The Storage-Treatment-Overflow-Runoff Model, STORM, was developed for the US Army Corps of Engineers in the 1970s. The model applies the rational method at an hourly timestep to compute stormwater runoff to a storage-treatment control structure. A fixed treatment rate is used to compute outflow, utilization of storage, and overflow. The model provides a simple but useful paradigm for diverse applications. One of its original uses was estimating combined sewer overflow for entire cities. Today one of its most important uses is for sizing stormwater detention basins (a.k.a. structural BMPs). The use of STORM for this purpose is described in the California Stormwater Best Management Practice Handbooks (California Stormwater Quality Association, 2003). STORM’s core algorithms are incorporated into the NetSTORM program (Heineman, 2004), which also includes functionality for linking multiple control structures within a STORM framework, and processing and statistical analysis of precipitation data. The STORM component of NetSTORM is now incorporated into a dynamic-link library file (DLL) which can be referenced as a function in a spreadsheet program. This allows direct calling of STORM from a single cell in a spreadsheet. The present project differs from work previously described by Sample, Heaney, Wright, Fan, Lai, and Field (ASCE Journal of Water Resources Planning and Management, January 2003) where the STORM algorithms were coded in Visual Basic for Applications (VBA) and interpreted by the spreadsheet program. Because the STORM DLL references compiled Fortran code, it can analyze a 50-year precipitation record in less than one-quarter second on a typical PC, making it practical for a variety of applications. The STORM DLL can be flexibly deployed within a spreadsheet for applications such as multi-objective optimization and development of usercustomized tables of capture efficiency, overflow volume, and overflow frequency with minimal constraint on the length of the precipitation record that can be reasonably analyzed. The STORM DLL has been used in several studies. An example spreadsheet has been developed demonstrating its use for optimizing cost of a stormwater treatment facility based on unit costs for treatment rate and storage tank size and constraints on overflow frequency. A second example demonstrates its use for sizing a dry detention pond where specified annual capture efficiency and overflow frequency is sought with constraints on drawdown time and basin volume. The STORM DLL is available free of charge to the public.