Sewershed Cleaning Prioritization Dashboard for the City of Columbus, OH

Introduction The city of Columbus, Ohio is serviced by a vast network of sanitary pipes with a combined length of more than 2800 linear miles that have been constructed as far back as the 1800s. The extensive size of this network makes maintenance challenging and requires effective prioritization of critical assets. This study presents the development of a user-configurable, easy-to-use dashboard to support the prioritization and maintenance of the city's sewer systems. The dashboard assigns an aggregate score to each asset based on seven criteria, enabling targeted prioritization and optimized resource allocation for maintenance activities. Background and objectives Historically, sewershed cleaning in the city of Columbus was conducted empirically by targeting areas with prior user complaints and manhole floodings. This approach resulted in a disproportionate focus on certain problematic locations while making it difficult to assess overall cleaning and maintenance needs across the city. Additionally, the city was divided into 143 basins, each with a mean area of approximately 3,000 acres. The large size of these basins made it challenging to identify and monitor smaller areas with concentrated issues. The last city-wide cleaning effort occurred in 2005, underscoring the need for an updated and comprehensive cleaning strategy. To address these challenges, a user-friendly management dashboard was developed to assist in the prioritization and maintenance of Columbus's sewer network. The dashboard incorporates intrinsic network data and expert input, and is designed for ease of use to support more effective decision-making. Methodology As a first step, larger sewersheds were subdivided to prevent large, low-priority regions from skewing the prioritization results. The delineation of smaller sewersheds considered network concentration to maintain a uniform maintenance workload. The newly defined sewersheds range from 200 to 500 acres in size and total approximately 1,000 areas. The prioritization process applies weighted scores to seven criteria to optimize resource allocation for maintenance: 1- Pipe material, 2- Fat, Oil, and Grease (FOG) potential, 3- Number of historical Water-in-Basement (WIB), SSOs, and manhole floodings, 4- Pipe construction and rehabilitation dates, 5- Pipe sizes. Each factor is scored for every pipe, ranging from 0 (minimal risk of blockage) to 10 (elevated risk). Conduit-level scores are calculated for each pipe, then aggregated at the sewershed level based on pipe density in each area. Final aggregate scores are determined by applying weights that reflect each category's perceived impact on sewershed performance. Baseline weights were established through a survey of eight experts from consulting firms and municipalities across Ohio (see Table 1 for a summary). With criteria weights determined, aggregate scores were calculated for each sewershed, and sewersheds were ranked by percentile to indicate their criticality in the maintenance schedule. Finally, a recurring maintenance program was proposed based on the city's maintenance and sewershed priority level. The process described above was implemented in a dashboard interface using Microsoft Power BI, enabling quick adjustment of user-defined weights and visualization of sewershed priority levels. The dashboard presents users with a list of sewersheds and detailed information for each. Users can modify criteria weights as needed, and the updated scores are reflected in the maintenance priority ranking. These results are displayed on an interactive map, allowing users to easily identify high-priority areas. Additionally, all dashboard information is available for export in spreadsheet format. Overall, the platform provides an accessible, user-friendly way to interact with sewershed maintenance data without requiring technical expertise. Figure 1 presents an overview of the dashboard interface. Conclusion and lessons learned This study proposes an improved maintenance schedule for the city of Columbus, Ohio. Unlike previous methods that relied solely on historic manhole flooding records, the current approach incorporates additional criteria for a more comprehensive ranking of sewershed priorities. A comparison with historical records demonstrates that critical areas are still being identified; however, the use of smaller sewershed units enables more detailed categorization of the sewer network and allows for focused maintenance efforts. Figure 2 illustrates the differences between the new and legacy sewershed classifications, highlighting how critical areas are more precisely identified. Results of these analyses were validated against historical power cleaning data. Figure 3 shows that most of the locations with a historical need for cleaning are identified as high priority sewersheds using the updated ranking approach. This approach leverages inherent network properties and expert input from the region, making it both robust and repeatable. Finally, the dashboard interface facilitates easy data manipulation and provides a user-friendly platform for visualizing results.