Written By Chad Cooper
One of the things I love the most about my job as a Solutions Engineer with the GISinc, State and Local Government team, is that I never know what challenge is coming up next. Oftentimes these challenges involve non-spatial data that needs to be linked to GIS. When jobs like this come up, I turn to one of my favorite tools, Python.
Opelika Water Utilities came to us with one database table and two database views of water billing information, a point featureclass of water meters, and the question of “How can we use our billing data to determine if a meter is malfunctioning?”. If a meter is faulty, it does not provide a usage reading, thus, the customer does not get billed for water usage and the Utility loses revenue – potentially lots of revenue.
Using historic billing data, the goal was to find active water meters that consistently, over the course of several months, have readings of zero. These meters are flagged as malfunctioning, or “dead”, triggering a notification by email of new dead meters. Of course, everyone on the project team knew a Python script was the way to solve this problem. The resulting script, at around only 300 lines of code, uses many familiar Python standard library modules (smtplib, email for emailing) along with Esri’s arcpy for spatial data processing and the third-party module configobj; a GISinc favorite for parsing simple text-based configuration files. The process starts off by first reading a configuration file for input parameters such as featureclass, table, and view locations and names. Next, tables are queried and billing data is linked to water meters through a series of database joins. For speed and efficiency (the entire process runs in around 30 seconds on 15,000+ water meters and tens of thousands of billing records), Python dictionaries are utilized for interim data storage and comparison. Billing data is updated monthly, so for a given meter, if the current meter reading date is greater than the last run date of the script, that meter is processed. A minimum of three readings (current month plus previous two months) is required for a faulty status determination where if all three readings are zero, the meter is then deemed “dead”. Dead meters are added to a separate featureclass containing the full billing history. To finish off the process, if a meter is newly added to the dead meters layer, it is flagged as such, and at the end of the run, a list of the new dead meters is sent in an email to the proper Utility personnel for further investigation.
In addition to the Python script, GISinc configured an Operations Dashboard for personnel to investigate flagged meters. The script and dashboard together form a simple and useful solution for Opelika Water Utilities to stay informed of potential issues on their water network without assigning additional resources to the task while also serving as a firm foundation for future analytical processes that the Utility wishes to view spatially.