Excel-Based Tool for Automatic Calibration of Urban Drainage System Models ^†

Abstract

Climate change and aging infrastructure are the main stressors for existing urban drainage systems (UDS), causing more frequent overloading. The locations of flooded areas and system behavior under different scenarios can be analyzed using calibrated SWMM5 models. This study presents the implementation of an automatic calibration module designed for SWMM5. This module can be utilized for different types of drainage systems, including those that contain only impervious subareas, a mix of impervious and pervious subareas, as well systems that include natural ditches or channels. The latter are the most complex because, in this case, it is necessary to include the inflow of groundwater to the model. A dynamic link library (DLL) created by the authors enables automatic calibration of 21 parameters in SWMM5 in previously defined limits. The module enables calibrating up to five parameters simultaneously. This study presents different strategies for calibration, describes their limitations and possible solutions.

1. Introduction

Climate change is having a considerable impact on urban areas [1,2], and it is predicted that many areas around the world will be subjected to more frequent weather extremes, including intense rainfall events [3]. Hundreds of large cities in Europe are already known to be extremely vulnerable to pluvial floods, but there could be thousands more, although this cannot be confirmed without undertaking further investigations and risk assessments [4]. As public awareness has grown, so has the political and technical pressure to implement various flood mitigation and adaption measures, such as integrated stormwater planning and management initiatives, smart and real-time controlled infrastructure, and nature-based solutions. However, the implementation of some of the more advanced stormwater management approaches requires an in-depth knowledge of system behavior, which is typically based on estimations and predictions obtained through analyzing hydraulic models.

One of the most commonly used models for analyzing the impact of changes in precipitation volume and intensity on the existing drainage systems is SWMM5 [5,6]. To utilize this model for decision making, a calibration is required. Depending on the location of the site of interest, this is not always easy because the data needed for model building and calibration may be inaccurate or lacking, or the collection of it may be time consuming and labor intensive. A typical model requires data on the catchment characteristics, environmental conditions, as well as the geometry and the condition of the stormwater system. Behrouz et al. [7] have summarized the three main data calibration approaches: the data forcing approach, the manual “trial-and-error” approach and the automatic calibration approach. The primary distinction between the approaches is that the data forcing and “trial-and-error” approaches rely heavily on the judgement and experience of the practitioner, whereas automatic calibration is designed to be an objective and reproducible procedure. Nonetheless, both manual and automatic calibration are capable of producing the best fit values for the model; the key difference is the reproducibility of the results and the speed with which they are obtained. The best fit is typically estimated using either single- or multi-objective functions, and the appropriateness of the results is assessed through RMSE, peak flow error or total flow volume error.

Due to the inherent benefits of automatic calibration, a number of modules have been developed over the years, some of which are based on changing the SWMM5 input file during the calibration steps, while others are based on creating a dynamic link library (DLL), which allows one to change parameters without creating and reading new input file after each step [7,8,9,10,11]. The latter is preferred, as it takes less time to calibrate the model. This methodology is implemented in this study.

The goal of the study is to explain in detail how to calibrate a hydraulic model of an urban drainage system by using the modified DLL and a simple user interface developed by the authors.

2. Materials and Methods

In practice, automatic calibration of the urban drainage system model in SWMM5 can be achieved in two ways—either by creating and reading an input file after each calculation step or by changing the parameter settings directly within the program without the need for any additional actions. If the latter approach is chosen for model calibration, it is necessary to create a DLL, which allows the parameters to be changed within SWMM5. McDonnell [8] developed one of these tools, and it was adapted by the authors to achieve greater flexibility in changing the calibration parameters during the calibration. This newly attained ability to adjust 21 parameters [11] was added to the SWMM5 source code [12].

The authors designed this DLL to be compatible with a variety of programs, including Microsoft Excel. The software was chosen because it is widely used, and it does not require specific programing language knowledge while also offering a variety of options for data analysis and visualization. The DLL created is compatible with both 32-bit and 64-bit software versions.

3. Results

In Visual Basic for Applications (VBA), two options for automatic calibration were created: Option A for calibrating one or two parameters using the trial-and-error approach and Option B for calibrating up to five parameters at the same time using the Levenberg–Marquardt algorithm. An informative worksheet (“paramID”) is dedicated for the parameters, which may be included in the automatic model calibration. Each parameter is equipped with an ID, description, and minimal and maximal values, which may be defined by the user.

Table 1. Parameters that may be calibrated.

Parameter ID	Action	Minimum	Maximum
0	Changes area using coefficient	0.1	6
1	Changes width using coefficient	0.1	3
2	Changes slope using coefficient	0.1	2
3	Changes impervious percent	0	100
4	Changes Dstore impervious	0.254	2.54
5	Changes Dstore pervious	5.08	10.16
6	Changes N impervious	0.01	0.3
7	Changes N pervious	0.03	0.8
8	Changes max. infiltration rate/Suction head	8/260	49/321
9	Changes min. infiltration rate/conductivity	0.2/120	0.2/120
10	Changes decay constant/initial deficit	2/7	0.09/0.38
11	Changes roughness of pipes	0.01	0.3
12	Conductivity [mm/h]	0.2	130
13	Conductivity slope [mm/h]	2	60
14	Aquifer bottom elevation [m]	−0.3	0.3
15	Ground water elevation [m]	−0.3	0.3
16	Groundwater flow coefficient A1	0.2	0.9
17	Groundwater flow exponent B1	0.5	1.5
18	Surface water flow coefficient A2	0.2	0.9
19	Surface water flow exponent B2	0.5	1.5
20	Surface water and groundwater interaction coefficient A3	0.2	0.9

lists all of the parameters that can be calibrated with the developed tool.

If the user intends to investigate the sensitivity of different model parameters, either individually or in relation to other parameters, the first option should be chosen (A). If, on the other hand, the user had already defined the most sensitive set of parameters to be included in the calibration, the second option may be used (B). It is important to note that Option A (Figure 1) allows choosing between different variants for calculations (from 1 value for one parameter up to 21 values for both parameters), and Option B (Figure 2) allows calibrating up to five parameters at the same time. In the workbook, Option A may be found under worksheet “Surf”, and Option B may be found under worksheet “roughLM”.

In addition to the previously shown sheets, the workbook contains many others that are either directly used for or supplementary to the calibration. For instance, the supplementary sheets include lists of errors (“err_list”) and the information on various limits of parameters (“limits”). One of the most important sheets for calibration is the sheet “measured”, where the user needs to add measured data, which will be used in the model calibration. The measured data can include flow rates or water depths measured at selected conduits or nodes, and it is used to calculate the objective function.

The calibration results can be found in the worksheet “out_data”, which displays the final parameter values, as well as the difference between the measured and the modeled results. This sheet also includes information on the number of iterations, the number of function calls and the finish code, all of which indicate potential errors during the calibration. To learn more about the error message, the user can compare the error code in the sheet with the codes listed in the worksheet “error list”.

The user interface currently lacks the ability to save the calibration results directly to the SWMM5 input file, but this capability can easily be added through MS Excel.

4. Discussion

The authors have tested the automatic calibration module on several different UDS models and configurations. During the analysis, a number of problems and limitations emerged. First of all, it is necessary to mention some limitations regarding the SWMM5. If the modeled system only contains pipes, the excess water that infiltrates into the soil within the pervious areas is not included in the runoff calculations; typically, this is not an issue. This becomes a problem once there are ditches in the model into which the water exfiltrates from the ground. For such cases, it is necessary to include aquifers in the model and connect them to some junctions, similarly to surface catchments. The area of such catchment is usually estimated using surface elevation models, and therefore, it often does not coincide with the area of the groundwater basin (for example, part of precipitation that infiltrated into the soil in other catchments may exfiltrate into a ditch within another catchment). Therefore, it is suggested to adjust the size of the aquifers during the calibration. As such adjustment changes the size of the catchments in the model, it is evident that the modeler must be extra diligent when assessing and ensuring the model performance during different scenarios and flow regimes.

The second calibration option (B) contains five parameters, which may be calibrated simultaneously. It is possible to increase this number in the future, but a large number of parameters often give unrealistic results, especially in the case of a low number of measurements. Additionally, the addition of parameters may increase the number of errors in the simulations, as in some cases, the SWMM stops the calculation during calibration. The termination of the process of calculation is typically related to the selected calibration parameters, and it refers to conflicts between different parameters and their limits. In such cases, it is suggested to exclude some of the calibration parameters and perform an initial sensitivity study to analyze the impact of different parameters on the calibration and SWMM performance in general. The initial feedback on the calibration is provided in the MS Excel UI through the process finish code. The error messages are given through eight different states. These states give an instant overview to the user on the background of calibration failure (e.g., if the input parameters were improperly selected or if the selected precision was too small) or success (e.g., giving an indication on the lowest relative error). Additionally, as a source of reference and self-help, a worksheet containing a list of SWMM errors with their short descriptions was added to the Excel workbook. The addition of this worksheet is supposed to provide a quick and low-effort means of looking up the potential reasons why the program does not work as expected.

5. Conclusions

• A dynamic link library was created for the automatic calibration of urban drainage system models in SWMM5. It is possible to use the DLL in MS Excel, as well as in other computer programs. In this study, the user interface for the automatic calibration module was created in MS Excel.
• MS Excel was selected because it has several built-in analysis and visualization capabilities, and it is a familiar software for the potential users.
• The initial testing of the calibration module pointed out several limitations both in the SWMM5 and the module itself. These limitations must be considered and addressed during the calibration and usage of the hydraulic model. The reduction in the limitations is an on-going task, as is the creation of a dedicated help file and user manual to support the potential users of the module.
• Automatic model calibration may still be arduous and time consuming, as the number of parameters that must be included in the calibration can be high. For such cases, parallel computation may be used.
• Upon request, the automatic calibration module is freely available to all interested parties.