A Relational Data Model for Advancing Stormwater Infrastructure Management

Within the stormwater management community, there is an increasing movement toward municipalities having inspection or monitoring programs (Stormwater BMP Maintenance TC 2019), resulting in an abundance of observational data. For example, a simple monitoring program for inflow and outflow (depth, velocity, flow) at continuous 15-minute intervals and 12 discrete water quality grab samples at the influent and effluent for six analytes [total suspended solids (TSS), total dissolved solids (TDS), total nitrogen (TN), total phosphorus (TP), copper, and zinc] results in 210,384 observations per year at one site. A municipal program can have hundreds of sites. This rise in monitoring is to support the desire to have an improved understanding of how a specific SCM performs relative to planning-level or compliance expectations or how a suite of SCMs perform in a region. For example, as part of Measure W passed in 2019 in Los Angeles, California, monitoring is a funded item and is required by SCM projects receiving funding (Los Angeles County Flood District 2019; Storm Water Grant Program 2019). Further, monitoring can aid in operation and maintenance efforts and decision making through improved models and analysis (Wadzuk et al. 2021a). SCM monitoring can also facilitate deepening the community’s understanding of the unit processes driving performance over time, ultimately allowing for more efficient and effective designs. SCM monitoring is a resource-intensive activity, and the resulting data need an organized and efficient structure for the knowledge held within the monitoring data to be used in stormwater planning and management decision making.

Due in part to the lack of a comprehensive national database of assets, the stormwater sector in the US is relatively data poor, inhibiting the ability of some municipalities to meet stormwater regulatory requirements (ASCE 2021). In personal communication by the authors with stormwater program managers in Austin, Texas; Philadelphia, Pennsylvania; and Portland, Oregon, we found all three municipal stormwater utilities use different data management platforms that do not facilitate easy data sharing outside their organization (Wadzuk et al. 2021a). However, there is an expressed need and desire for open knowledge sharing across municipalities. This is also evident through the breadth of municipal membership in the Green Infrastructure Leadership Exchange (https://giexchange.org/). The Exchange is “a highly connected peer learning network that offers a platform for practitioners to share experiences, circulate ideas, and solve problems together toward finding more sustainable water infrastructure solutions,” yet it does not have a mechanism to share explicit green stormwater infrastructure (GSI) function data. Some examples of questions the Exchange and others in the stormwater community are asking include:

To answer these types of questions either by one organization or across organizations, a novel data model specific to stormwater was required. Stormwater data include continuously monitored data, storm sampled data, and model-produced data, and ultimately, this SIDM answers a community need to store and retrieve disparate types of data beyond what is currently available. Without a model that enables efficient management and access, the data are simply not available for research or management, greatly inhibiting advancement of our knowledge base. In designing this data model, the type of metadata required to interpret data, data retrieval speeds, and data integration was also considered. Although urban stormwater data collection is increasing, there are few databases that span beyond a municipality. Often data are siloed by department, limiting the ability to extract or transfer knowledge. One exception to this is the International Stormwater Best Management Practices Database (BMP Database). The BMP Database is a collection of stormwater data from institutions around the world (Clary et al. 2018) that does have a common data standard but relies on users to convert data into the dictated format and voluntarily upload data. The BMP Database is a repository of SCM field studies that provides access to monitoring data, as well as analysis tools and performance summaries. Over time, the BMP Database was expanded for land use and contains six different databases (urban stormwater BMP performance, agricultural BMPs, stream restoration BMPs, National Stormwater Quality Runoff Database, Department of Transportation Portal to BMP Database, and Urban BMP Cost Database). The database provides a wealth of SCM data, including information about watersheds, design, monitoring, and costs (National LID Clearinghouse 2021). The power of the BMP Database is that it is not managed by one municipality but rather jointly supported by nonprofit organizations (i.e., American Society of Civil Engineers and Water Research Foundation) such that the database is open. However, these data are largely lumped discrete event-specific data, matching how data are stored in disparate, and often sparse, state and municipal repositories. This information provides performance data for a wealth of SCMs (Clary et al. 2018). However, the limited data records, inconsistent vocabulary, and output format make it challenging to access the data in a seamless fashion or apply them to more advanced continuous simulations. Further, submitting data to the BMP Database makes them publicly available for analysis, but it does not offer a platform to enable in-house data management and analysis across all assets a municipality or organization has.

To further SCM design outcomes and acquire transferable insight to SCM function requires stormwater databases for a range of spatial scales and system complexity (Choat et al. 2022; Roy et al. 2008; Taira et al. 2018; WEF and ASCE-EWRI 2012). For example, a municipality may want to quantify individual SCM performance within a neighborhood and also know how all the SCMs in a neighborhood are collectively meeting stormwater goals, such as combined sewer overflow reduction at an outfall. Additionally, understanding the performance of an individual SCM requires merging of event and continuous data. Although SCM performance is tied to a storm event, the continual hydrological processes within SCMs dictate performance capability, such as antecedent conditions, groundwater levels, evapotranspiration, and sediment influx (Homet et al. 2022; Hess et al. 2017).

By linking continuous and event-based data, these performance indicators can be identified and described. For example, there may be higher influent pollutant concentrations after long periods of dry time that can be readily discerned with linked data in the SIDM, versus previous data sets, storage, or structures that simply had discrete event concentrations without the larger environment of data and therefore no evident reason for abnormally high concentrations. Also, by better linking multiple processes in a SCM, the scientific community will be able to more precisely identify and quantify the ecosystem services provided by SCMs on local environments (Parr et al. 2016; Vogel et al. 2015). To meet these challenges, a more robust, but efficient, data model that provides access to event and continuous data through controlled vocabulary to stormwater capable of incorporating multiple sites is needed.

The SIDM design, based on CUAHSI’s ODM1, consists of a set of relational tables. The ODM specifications define a framework for recording the date, spatial location, and variable for a datapoint with its associated metadata detailing how it was collected (Horsburgh et al. 2008). The SIDM implements and builds on these specifications to pivot the database specifically toward stormwater infrastructure data. The SIDM collects additional metadata specifying the sublocation where a datapoint was collected, type of storm event that occurred during sample collection, and associated detection limit. The SIDM collects metadata for sampling sites in a portable document format (PDF) with information about the site’s location, SCM type, construction, monitoring history, site description, location and purpose of sample location, and names of samples and variables collected. Documentation is associated with a site at its creation in the SIDM and is accessible through the SIDM web application’s map interface.

The SIDM is implemented using 44 individual data tables stored within the MySQL relational database management system. Fig. 1 delineates the SIDM relational diagram. Thirty-seven of the 44 tables store SIDM data (e.g., data values, site information, and basic codes and definitions), whereas the remaining seven are used throughout the web application to manage data logs, file uploads, notes, sharing permissions, and users. Additional tables define controlled vocabulary for the codes used throughout the SIDM, which are derived from CUASHI’s ODM guidelines (http://his.cuahsi.org/mastercvreg/cv11.aspx). In Fig. 1, the title of each table in the diagram specifies the type of data stored in each SIDM table and lists the attributes stored in those tables. Cardinality is defined using the links between tables and shows the number of instances of other tables that must be created or referenced by a parent table to retrieve the information it stores. Data storage by the SIDM is accomplished by creating DataValues (center of the diagram) for all observations. Metadata for the attributes in each DataValue is fetched in a key–value fashion from other tables in the SIDM using the identifiers (IDs) specified in DataValues.

One major departure from the ODM1 is the introduction of the Datalog table, which is a record of all data added to or removed from the database. This can be done in bulk or through single value uploads. To implement this feature, a new LogID field is added to all data values during the data upload process. This field is indexed by an incremental sequence for each upload event. Although it marginally increases the size of the database, this new LogID field improves performance for updating the Series Catalog table and deleting large amounts of data at once.

Each table uses the InnoDB storage engine. InnoDB was chosen to ensure atomicity, consistency, isolation, durability (ACID)–compliant transactions and to allow for foreign key constraints. These constraints enforce strict relationships between each table and prevent many common data insertion or deletion errors.

The Datalog table also stores all the uploaded comma-separated values (CSV) file information, related via a child FileUploads table. By using the Datalog and the associated FileUploads table, a SIDM database administrator can completely recreate the underlying MySQL database in the unlikely event of database corruption. Likewise, accidental deletion of data can be easily remedied because the Datalog stores both the deletion event and the underlying file that was deleted. The SIDM adds three additional tables to the ODM1 structure to describe the observational metadata, which is key for stormwater infrastructure observations. Although the latter metadata is more nuanced specific to stormwater infrastructure data interpretation, they are key parameters related to the instrumentation used for stormwater quality (Fig. 2). These include the following:

Throughout the development process, the SIDM was designed to align with principles of findability, accessibility, interoperability, and reusability (FAIR) data (Wilkinson et al. 2016). The SIDM aims to create a data model structure that meets these criteria to enable more robust and sustainable study of the data. The SIDM makes finding data easy through a graphical user interface (GUI) search page with filters for each type of metadata compared to other stormwater databases. The effective interface for building queries to search the database enables authorized users to retrieve data values and metadata. The SIDM and interface were developed using multiple iterations of user feedback to ensure that all required variables and parameters were collected to accurately describe the observations. Focus was also given to user input to ensure that the process was streamlined to minimize user input error (e.g., leaving out a critical variable accidentally because it was not required). The data model is reusable because any given combination of space (site, sample location), time, and variable codes uniquely identifies a data point and can be used as a reference for further analysis. Metadata associated with every value is stored in data logs and gives future users knowledge about how and under what conditions the data were collected.

This paper describes an application of the SIDM to data collected by the Villanova Center for Resilient Water Systems (VCRWS). VCRWS has collected nearly 20 years of SCM data (from 2003 onward) across 21 different sites and 124 sample locations (the SIDM defines sample locations as locations where observation data are collected). Fig. 3 provides an example of one of the study sites, which has 16 sample locations. Differentiating between individual sample locations on a specific site is key for analysis (e.g., comparing inflow to outflow) both for aggregation at a specific site or for comparisons across multiple SCM sites.

Data are collected through continuous sampling sensors, grab samples, autosamplers, and field and lab measured data. The variables measured include discrete data [pH, total suspended solids, total dissolved solids, total kjeldahl nitrogen [TKN], nitrite $[{\mathrm{NO}}_2]$, orthophosphate [PO4-], total phosphorous, soluble phosphate, chloride, nitrite + nitrate [NOx], ammonia $[{\mathrm{NH}}_3]$, suspended and dissolved metals (zinc, lead, cadmium, chromium, copper)] and continuous data (electrical conductivity, temperature, dissolved oxygen, turbidity, sun elevation, total rainfall, flow depth, velocity, ponding depth, wind speed, wind direction, soil moisture, soil conductivity, soil temperature, relative humidity, air pressure, and solar radiation). Additionally, modeled variables, such as recession rate and outflow, are included in the database because these are key performance indicators. The collection method or processing of the laboratory samples for a specific variable may differ between sites or through time as laboratory equipment is updated, which is described by the Method Code. Metadata necessary for data interpretation is collected, such as detection limit, battery voltage, and system history. The large volume of continuous water quality and quantity measurements greatly exceeds any known green stormwater infrastructure database (for comparison, the International Best Management Practice Database has 5,041 TDS from across the world, whereas the SIDM has 4,561 measurements for the VCRWS sites).

Stormwater observations data, like many raw data sources, are inherently messy in its original format. The data output by time series dataloggers and lab instrumentation are in formats not conducive to direct loading into database systems. Both formats contain most or all of the appropriate metadata. Although these formats are easy to read and facilitate quality assurance reviews, they are not flexible enough to store and query quickly in a database. Unlike other stormwater databases, we merged continuous and discrete data to describe the infrastructure performance and function from a quantitative and qualitative approach.

The user interface, the SIDM web application, allows users to view and query data, export data to a downloadable graph or file, view the database code dictionary, and add data to the database. Additionally, database administrators can add to or edit the code dictionary, access data logs for the database, manage users, and change access permissions for restricted users. The SIDM software stack consists of a PHP instance running on an Apache webserver plus an instance of MySQL for data storage. The web-based interface is accessible by all modern browsers and requires no software installation by the user.

The initial interface screen shows a spatial representation of all VCRWS sampling sites available [Fig. 3(a)]. Users can click any labeled sampling sites on the map or in the menu to load data available, which can be viewed graphically. Site summary pages highlight the site history and available data. To retrieve data, users query data using the Query Builder, which contains filters, allowing for data retrieval for a combination of sites, sampling locations, variables, collection methods, event types, sample types, quality control levels, detection limits, or date ranges. Query results appear in tabular form for dynamic comparison with the included plotting interface. Options to change the amount of data viewed on one page, page through the results, change the applied filters on the query, and export the results to a CSV file are available.

For performance reasons, the output files are split into 1 million–row chunks to allow faster upload and processing by the server. A corresponding file index is appended to the file name to allow for distinct files when running this process on large data sets that can produce many output files. This method is preferable when performing the operation on a large data set, such as the historical data set for many sites.

Data importing is the most processing-intensive aspect of the SIDM. Workbooks containing raw data are cleaned, leaving just sampling dates and their data points. The SIDM data loader requires each data point to be parsed into one row of a CSV file (containing the following metadata: Datetime, DataValue, DetectionLimit, DetectionLimitCode, SiteCode, SampleLocationCode, VariableCode, MethodCode, EventTypeCode, SampleTypeCode, OffsetTypeCode, OffsetTypeValue). The SiteCode, SampleTypeCode, OffsetTypeCode, and OffsetValue are the same for all values in an input file and are statically defined. Metadata specific to each row in the input file (the Datetime and consequential EventTypeCode) and the metadata specific to each data point in the input file (DataValue, DetectionLimit, DetectionLimitCode, VariableCode, and MethodCode) are retrieved while iterating over each of the values in the Input file. SampleLocationCodes are retrieved using a combination of the SiteCode, VariableCode, and MethodCode.

Formatted files are uploaded to the VCRWS database using the data loader. Output files are uploaded directly to the database in portions smaller than 100 Mb. Upon upload, files are checked to ensure proper format and that timestamps are in the appropriate MySQL datetime format. To speed up this process, local file importing takes an input created by the upload process and directly injects data to the database. If there is an error with the uploaded file, an error code identifying the issue and where it is in the file is returned. The validation algorithm for processing uploaded values demonstrates linear complexity (also known as linear Big O notation). Therefore, adding many rows of data does not add extra computational time relative to how long a smaller number of rows would take to validate. Because the limit for upload is 100 Mb, validation is typically achieved in less than 5 minutes, ensuring that the uploaded data fit what is already in the database.

Storing data in “long” format, where a value must be specified for each piece of metadata in a DataValues row, is preferable for flexibility and query purposes because of the ability to add variables, sites, sample locations, and methods dynamically. The appropriate metadata can be added in the corresponding relational tables, and values can immediately be added to the data value storage. Similarly, query performance is increased due to the use of indexes within the data values table. Instead of adding a new index for each new variable, adding new data causes existing indices to update. Specifically, covering indexes, a SQL concept that uniquely identifies records based on a joint set of columns pertaining to a specific query, drastically increase performance by allowing the SQL engine to efficiently find and return query results using “where” statements to filter results based on those indices rather than directly searching the entire data record. There is an additional storage cost associated with creating and maintaining each index, but because storage is inexpensive and processing time remains the largest overhead to serving data, this tradeoff is acceptable.

Table 1 describes the complexity of searches possible with the Query Builder. The simplest search returns a single data point. The next most complex search involves the same spatial parameters but occurs over a series of timestamps. A single variable in a time series or at a timestamp can be queried for multiple site sampling locations collecting the specified variable. This process can be repeated at multiple sites. In the most complex search, any combination of one or more sites and one or more site sampling locations can be performed in a time series or at a timestamp for multiple variables.

Despite the simple nature of data storage and retrieval, the SIDM’s relational nature has the capacity for powerful data analysis. Given that each datapoint has an associated spatial and temporal value, single datapoints at different spatial locations or different points in time can easily be related to one another. The SIDM’s strict formatting requirements will create an output file from a query of variables in the same format. When combined with other sampling locations from the same site, one could quickly produce a comprehensive analysis of variable trends through time. These examples highlight the features and function of the SIDM in the storage of different types of stormwater observations; the intention of these examples is not to provide specific analytical methods or findings regarding the performance of any GSI.

The example applies the SIDM structure at a single site, the rain garden (Fig. 3). The rain garden is a SCM built at Villanova University in 2003 that collects runoff from a $\mathrm{5,140}\text{-}{\mathrm{m}}^2$ drainage area with 50% impervious surfaces. The rain garden diverts runoff through an infiltration basin with amended soils and vegetation to reduce downstream peak flow, stream bank erosion, and nonpoint source pollution. As runoff travels through the system, a series of sensors collect data on the water’s physical and chemical properties [Fig. 3(b)].

Queried data are retrieved from the tables storing their respective information in the SIDM. All Nitrite + Nitrate (${\mathrm{NO}}_{\mathrm{X}}$) data values collected and measured from samples collected at the composite autosampler [ASC1, Fig. 3(b)] that were above the detection limit for individual storm events have been specified from the Query Builder’s filter menu and then visualized from the raw export without the need for translation or reformatting (Fig. 4).

Each of these filters has a numerical identification that can be used as a key to retrieve the metadata from other tables associated with it. The SiteCode has a SiteCodeID of 4. The Query Builder receives the SiteCodeID 4 and finds all DataValues in the DataValues table of the SIDM with the same SiteCodeID. Fig. 5 demonstrates how the same usage of keys and values is reflected in the way the SIDM stores and retrieves metadata for values in the DataValues table. Each entry in a table containing codes for metadata has a unique ID code associated with it, which is used by values in the DataValues table to reference metadata associated with that ID in its respective table. For instance, an entry in the DataValues table with a SiteCodeID of 15 and SampleLocationID of 4 would be a piece of data from the BTI at ASC1. Some of the metadata tables contains references to other tables in the same way. The table pointing from ${\mathrm{NO}}_{\mathrm{X}}$ in Fig. 2 shows how a row in the SIDM’s Variables table for ${\mathrm{NO}}_{\mathrm{X}}$ specifies the units. The $\mathrm{MetadataID}=10$ stored in the row of the Sources table specifies that data from this source are collected from VCRWS-related projects.

To demonstrate the SIDM’s capability for relating data values both spatially and temporally, this example considers a constructed stormwater wetland (CSW) on the western portion of the Villanova campus. The Villanova CSW was built in 1999 with the purpose of capturing runoff from $\sim 18\mathrm{ha}$ of the university’s campus before it enters Mill Creek. Captured runoff enters at the confluence of two inlets (Fig. 6, inlet) and passes through a series of three meanders (Fig. 4, M1, M2, and M3), which were added in a second iteration in 2010 for increased hydraulic residence time and vegetation interaction to potentially improve nutrient extraction, before reaching the outlet (Fig. 6, outlet). The inlet and outlet of the Villanova CSW are equipped with flow meters to measure continuous, 5-minute discharge readings. These locations undergo water quality sampling after storm events and periodically during baseflow conditions using both autosampler and grab samples. All these data are stored in the SIDM discretized by sampling location and time of collection with respective variable codes.

As an example of how pollutant load may be analyzed, this example shows how data are queried and extracted from the SIDM for calculation and analysis that would be done outside of the SIDM. The query tool enables the user to extract just the data they need to directly perform their analysis without further data processing. Total suspended and dissolved solids are measured in a laboratory setting using the autosampler and grab samples collected from the inlet and outlet of the CSW (Fig. 7, SiteID 16) after storm events and intermittently during periods of baseflow. Querying the SIDM for TSS and TDS (Fig. 7, VariableIDs 6 and 7) values and continuous discharge (Fig. 8, VariableID 108) from the inlet and outlet (Fig. 8, SampleLocationIDs 129 and 130 for the two discharge sensors at the inlet, SampleLocationID 124 for water quality samples taken from the inlet, and SampleLocationID 128 for the discharge sensor and water quality samples from the outlet), one can construct an event-total mass load for each nutrient passing through the CSW for water quality sampling events. This would be accomplished by multiplying each nutrient’s measured concentration by the event-total discharge volume of the inlet and outlet with appropriate unit (Fig. 7, UnitsID 199 for TSS and TDS in $\mathrm{mg}/\mathrm{L}$ and UnitsID 35 for discharge in cfs) conversions. During baseflow conditions, grab samples are collected from all five of the sampling locations of the CSW. A distribution of event-total mass loads for TN and TP, in addition to TDS and TSS, at all five sampling locations during baseflow conditions can be constructed using event-duration integrated discharge volumes from 5-minute discharge observations (identified in postprocessing using rainfall data to contextualize periods of dry weather) and average nutrient concentrations (also calculated in postprocessing procedure grouping by date of event) from nutrient data with the EventTypeCode for baseflow stored in the SIDM (Fig. 7). In this example, detailing seasonal total mass loads for individual events between 2012 and 2019, inlet discharge was supplemented for sample locations not directly measuring discharge (M1, M2, and M3). The DateTime stored with each water quality observation allows for contextualization of event-total mass loads of these nutrients by season and serves as a basis by which discrete and continuous data, both indexed by the date and time of their observation, can be related to each other in postprocessing procedures as facilitated by the structure of the SIDM. Ultimately, this facilitates the analysis and comparison of a wide range of parameters.

The collection of metadata for each DataValue entered in the SIDM facilitates this task with almost no need for data translation beyond unit conversion for relating the quantities of units between VariableCodes. The Datetime and SampleLocationCodes of each DataValue for the VariableCodes Discharge, TN, TP, TSS, and TDS within the SiteCode CSW organizes these values relationally, spatially, and temporally (Fig. 8). Additional metadata for the type of event the nutrient values from (EventTypeCode) and the type of sample the nutrient values were extracted from (SampleTypeCode) allows investigators to add extra facets to analysis of the spatial and temporal mass loading performance of their systems.