Lead-Free Water Challenge: What We Learned About Data Management and Lead Service Line Inventories

By John Paul Miller, Associate Director, Center for Geospatial Solutions

Note: On August 4th, the US EPA released its Guidance for Developing and Maintaining a Service Line Inventory to support water systems with their efforts to develop inventories and to provide states with information for oversight and reporting. EPIC’s statement on this guidance is here.

Nearly one year has passed since the launch of the Lead-Free Water Challenge, EPIC’s technical assistance program seeking to boost lead service line replacement programs in six municipalities in partnership with Center for Geospatial Solutions, Blue Conduit, and WaterPIO. Below, EPIC’s data management partner and Associate Director of Center for Geospatial Solutions, John Paul (JP) Miller, spotlights some of the best practices on data management, ranging from building a single source of truth to digitizing records and outlining requirements for effective data management. 

—

Data Management Best Practices Summary

Many communities face challenges creating and maintaining a lead service line (LSL) inventory. The good news is that there is a straightforward approach that all municipalities can take to make progress getting the lead out. This approach involves the following steps and is outlined in more detail in the accompanying process diagram. 

How to Get Started Creating a Lead Service Line Inventory

Creating a lead service line (LSL) inventory can be a daunting challenge for municipalities of any size. Just figuring out where to start can be difficult. Data is often missing, incomplete, siloed across different departments, or stored in a format that makes it difficult to use. As such, most municipalities only have a partial understanding of their relevant data.

It is important for every municipality to have a complete picture of their data. That data may be incomplete or inadequate, but understanding what gaps exist is the critical first step in addressing this challenge. As such, every municipality should conduct a lead service line data audit. The purpose of the audit is twofold: 1) determine who has what in terms of data; and 2) create a “single source of truth.” The audit should include stakeholders from potentially relevant departments and focus on finding the following information:

A matrix should be created documenting who (row) has what (column). The above information may be stored on paper forms or in a digital format (below). It is important to flag any physical records, as they will need to be converted to a digital format.

• GIS (parcels, any drinking water infrastructure, etc.)

• CAD (same as GIS, plus as-builts)

• Spreadsheets (ex. billing roster)

• Scanned forms

• PDFs

The single most important of these items is the billing roster. It forms the backbone of the inventory data and can be used to create an initial lead service inventory map. Billing rosters typically contain address information and some type of unique ID for each account. These two fields can be used in a process called “geocoding.” Geocoding is the process of turning addresses into latitude and longitude locations on a map (i.e. GIS data). This can be done using proprietary products (e.g. ESRI in ArcGIS) or through free and open-source tools (e.g. OpenStreetMap Nominatum). Alternatively, billing roster addresses can be joined in GIS with parcel data. The geocoded billing roster serves as the base for the “single source of truth.”

Supplemental data can be used to refine the geocoded billing roster, such as a field inspection report detailing the pipe material. Care should be taken to document how the supplemental data is used to modify the geocoded billing roster. One simple way is to turn on editor tracking in GIS, which records who last edited the data and when. It’s also helpful if the source of the changes can be documented (e.g. new column listing supplemental data source such as field inspection report). Once all supplemental data has been used to update the geocoded billing roster, the municipality 1) has determined who has what and; 2) created a single source of truth. The single source of truth may have data gaps or inaccuracies, but the municipality now has a complete picture of their available data. 

Switching from Physical to Digital Records

One of the biggest challenges for municipalities, particularly under-resourced smaller and medium-sized ones, is converting physical records to digital records. Transitioning to digital records can be time-consuming and resource-intensive; however, there are enormous rewards for organizations that are able to make the transition. Many municipalities find that the transition is cost-neutral or even revenue-positive. 

Switching from physical to digital records is a journey and does not happen overnight. This seemingly overwhelming task can be broken down into two main areas: 1) making records accessible; and 2) modernizing data infrastructure. 

Making Records Accessible

Paper and digital records both require an organizational system to make information accessible and discoverable. However, a digital system offers advantages in terms of discoverability, security, compliance, transparency, and convenience. Municipalities looking to transition to digital records need to take two steps:

• Digitize existing records – All relevant information should be gathered in the “Who Has What” stage of the lead service line data audit, including any physical records. These physical records need to be scanned. Most scanning services have options for odd-sized physical records (engineering plans, tap cards, etc.). It is recommended to have a third party provide this service, especially since they have expertise in the next step.

• Apply metadata and indexing – Creating metadata for digital records is really what separates digital organizational systems from physical ones. It allows records to be searchable and discoverable. Optical character recognition (OCR) expands on typical metadata (address or name) and allows for full-text indexing. Records management systems with full-text indexing are not constrained by predefined searches.  

Modernizing Data Infrastructure

What do we mean by “modern” data infrastructure? Modern data infrastructure allows records to be findable/discoverable, accessible, interoperable, and re-usable. This should occur across departments and systems. The infrastructure can be local or in the cloud and can scale with different types and amounts of data. It should support data collection, ingestion, storage, transformation, querying, processing, analysis, and output.

While there are numerous steps to modernize an organization’s data infrastructure, the most important is a dedication to change. Management needs to require all new and existing systems to be vetted against a modern data infrastructure framework. IT professionals and business analysts can provide the subject matter expertise to ensure the correct infrastructure solutions are chosen and implemented. 

Making Do with Limited Resources

Smaller and medium-sized municipalities often struggle to have the resources and/or staff to fulfill all their obligations. Budgets shrink and swell, staff come and go, and new responsibilities/requirements arise. The need to replace lead service lines is just one example of this phenomenon. Therefore, municipalities must plan for ways to maximize their existing resources. This starts with a commitment to maintenance and innovation.

Maintenance

There is often a bias in American culture in favor of innovation over maintenance; however, it is impossible for innovation alone to solve municipalities’ responsibilities. Many of the most critical services that municipalities provide are inherently maintenance-focused tasks (trash, water, sewer, etc.). Maintaining and updating a lead service line inventory is just as important, if not more so, than creating one in the first place.

To ensure the inventory is kept up to date, a municipality needs to develop a formal LSL action plan and standard operating procedure (SOP) detailing how the inventory should be updated. The plan should outline goals, actions, responsibilities, and record keeping expectations. Additionally, the action plan should outline which data gaps exist and how they will be addressed. 

Lastly, it is imperative that there remains a “single source of truth” for the LSL inventory. That can only exist by knowing who has done what, and when. A SOP detailing how the inventory data should be updated ensures that the “single source of truth” persists, even when staff change.

Innovation Opportunities

While maintenance is a prerequisite for developing a LSL inventory, innovation is necessary to effectively do maintenance. There are several areas where innovation is particularly helpful:

• Mobile Data Field Inspections – Often the most reliable way to verify LSLs is to inspect a residence in the field. Field inspections are traditionally done with paper forms and some type of camera. Information from these forms needs to be manually transcribed to update the “single source of truth”, which is problematic for several reasons. Mobile data field applications, such as ESRI’s Field Maps or Fulcrum, make this process nearly seamless. Data validation can be used to standardize data collection, updated information can be directly connected to the “single source of truth,” timestamps, and editor fields can record when and who made changes, and photos can be associated with an asset. This can dramatically increase the effectiveness and efficiency of field inspections, which are one of a municipality’s most valuable tools.

• Community Outreach – It is very important for municipalities to engage with their communities and provide outreach regarding decisions and progress to help build trust from the public. This can be accomplished in many ways and a public-facing website with maps and data is particularly helpful. ArcGIS Hub can provide a public place for community members to access data and mapping products. It also provides a forum for LSL replacement feedback and concerns. The site can even be configured in a way that allows users to create events, a calendar, discussion forums, and be notified by updates from the municipality.

• Citizen Science – Citizen science is a rapidly expanding field that enables everyday citizens in communities to act as force multipliers for various initiatives. Municipalities with limited resources can leverage those in their community to gather data for them. This can be achieved using digital surveys apps, like ESRI’s Survey123 or Microsoft’s Power Apps, that can be accessed via a QR code and record data like location, photos, and pre-set conditional logic questions. Survey results can be directly linked to a dashboard, so municipalities can see responses in real time – including which areas in the municipality have low response rates and are candidates for additional outreach. 

• Progress Dashboards – It can be helpful for those managing the LSL inventory and replacement to know the current state of the municipality’s progress. A dashboard can provide key performance indicators (KPIs) that at quick glance show whether progress is on track, in real-time. Dashboards can be created using a variety of software Microsoft Power BI, Tableau, or ArcGIS Dashboards; however, it should always be linked to the “single source of truth”.

• LSL Prediction – While field inspections are the most reliable way to determine the number of LSLs in a community, it may not be feasible or practical to inspect every residence. Machine learning and artificial intelligence can be used to predict where LSLs are likely to exist based on other field inspection data and neighborhood information (ex. building date and type). Companies such as 120Water and BlueConduit offer expertise in this type of prediction. Results can help municipalities estimate their level of effort and where to target field inspection.

• Capacity Building Grants – While most of the other innovation topics have focused on specific technological solutions, the truth is most municipalities with limited resources need significant help building internal capacity, understanding what resources/best practices are available to them, and just figuring out how to get started. In this spirit, EPIC’s Lead-Free Water Challenge recognized that communities need access to funding, service providers, and technologies. Additional capacity building grants are necessary to help get the lead out.

—

The Environmental Policy Innovation Center (EPIC) would like to thank John Paul Miller, Jeff Allenby, and the amazing team at the Center for Geospatial Solutions for their collaboration on the Lead-Free Water Challenge and their partnership on the broader goal of replacing toxic lead pipes faster, more efficiently, and equitably across the country.