Where are the Hotspots? How this Sparked Interdepartmental Success
by Empowering Users with GIS
Application for the G. Herbert Stout Award
For Visionary Use of GIS by Local Government in North Carolina
TOWN OF MOORESVILLE, NORTH CAROLINA
T. Alan Saine, PE, GISP
Civil Engineer for Development Services
Contents
Purpose ......................................................................................................................................................... 2
Implementation ............................................................................................................................................ 2
Motivation for Creating CCTV Cleaning Data ............................................................................................ 2
Why was it Developed .............................................................................................................................. 2
What Problem Was Intended To Be Solved .............................................................................................. 2
What Problem(s) Were Solved.................................................................................................................. 2
Implementation Phases ............................................................................................................................ 2
Phase I- Project Origination .................................................................................................................. 2
Phase II- Data Acquisition & Examination ............................................................................................. 3
Phase III – Project Planning & Defining Scope ...................................................................................... 3
Phase IV – Data Creation & Integration ................................................................................................ 3
Phase V –Deploy Map ........................................................................................................................... 3
Phase 6- Ongoing Changes & Modifications ......................................................................................... 4
Organization Impact.................................................................................................................................. 4
How is it Applied within the Organization ............................................................................................ 4
Ultimately What Decision/Operation are affected ............................................................................... 4
Who are the Users ................................................................................................................................ 5
What Effect Has There Been On Productivity And/Or Decision Making............................................... 5
Graphic Examples...................................................................................................................................... 6
Screen Captures .................................................................................................................................... 6
Maps...................................................................................................................................................... 6
Appendix ..................................................................................................................................................... 14
1
Purpose
The Town of Mooresville GIS program had just begun in 2009 and the Engineering staff was looking to make an impact
and get other departments to buy-in to utilizing GIS in their daily workflows. This project was the turning point in which
GIS was embraced in nearly every department in the Town of Mooresville Public Services Division. GIS continues to be a
valuable tool in everyday decision making and has allowed several departments to operate more efficiently.
Implementation
Motivation for Creating CCTV Cleaning Data
The Town of Mooresville Water Sewer Maintenance Department requested assistance to help with a rise in sanitary
sewer overflows and sewer stoppages. The goal was to use work orders, Excel files, python scripts and GIS analysis tools
to determine where the sewer cleaning Hotspots were in the Collection system.
In 2010, the Town of Mooresville, North Carolina began experiencing a rise in the number of sanitary overflows and
sewer stoppages. The reason for the problem was evident aging infrastructure. The Town of Mooresville has over 6000
manholes and 250 miles of sanitary-sewer gravity-main lines; however only 30 percent of that infrastructure has been
constructed in the last 20 years. In addition to aging infrastructure, the Town of Mooresville population has tripled in the
last 20 years, thus putting additional strain on the significantly aged sanitary sewer system.
Why was it Developed
Mooresville, like most municipalities, have permit requirements that are regulated by the Division of Water Quality, a
division of the North Carolina Department of Environment and Natural Resources. One of the requirements is the
permittee shall assess all cleaning needs, and develop and implement a program appropriately cleaning all sewer lines.
The Mooresville Water Sewer Maintenance Department (WSMD) manages this requirement on a day-to-day basis. The
WSMD sewer cleaning crew was almost completely reactionary and at times would only clean lines that had a history of
backups or were located in older parts of the system.
What Problem Was Intended To Be Solved
The WSMD staff knew that there had to be a more productive way to determine which sewers need to be cleaned in
order to prevent future sewer backups. So, WSMD requested the Town’s Engineering Department to help them
determine first where all the distressed spots, or “hotspots,” in the system were located and then to help them create a
plan for more strategically and effectively cleaning and maintaining the system.
What Problem(s) Were Solved
The sewer cleaning crew can now view all the sewer mains they have cleaned, see where any hotspots are occurring,
and notify the appropriate staff to investigate the situation further. Also, now any Town of Mooresville employee can
view this data on a desktop computer, tablet, or other mobile devices.
Implementation Phases
Phase I- Project Origination
The Town of Mooresville Water Sewer Maintenance Department had just recently started to use the Town’s new GIS
and this raised their curiosity on if they could do more with these maps. So they requested help of the Engineering staff
to see if it was possible to map sewer stoppages and problem areas.
2
Phase II- Data Acquisition & Examination
One of the first things the Engineering staff did was evaluate the data the WSMD staff had already created. The WSMD
staff had work orders containing address information of what sewers they cleaned in paper format. It was quickly
decided WSMD staff needed to be collecting the cleaning data electronically and begin using MHID’s as the unique
identifier.
Phase III – Project Planning & Defining Scope
The main goal from the beginning was to identify problem areas in the sanitary sewer system. Cleaning data appeared to
be a very reasonable way to help locate these areas and after reviewing the data, the Engineering staff knew if they
integrated spatial cleaning data that this would facilitate a way find the sewer cleaning hotspots.
Phase IV – Data Creation & Integration
The next item was to determine how to most effectively collect the information off the hundreds of work orders, which
were in paper format, and connect that data to the equivalent sewer segment based on physical addresses. With the
help of WSMD staff monthly spreadsheets were developed for tabulating past data. As this process was occurring the
cleaning crew began recording the MHID’s for every sewer segment they cleaned in a monthly spreadsheet. The
cleaning data was created by using a join between the MHID in the Excel file and the MHID in the Town’s ArcGIS SDE
database. A copy of cleaned sewer lines and cleaned manholes were selected and copied. Simple reports were also
created utilizing the Arcpy function in Python. Creating the Cleaning Data would require the repetition of several steps;
this process was simplified by using Python. The Python script was developed, so that the user would have the Excel file
in a folder on their computer if they wanted to extract the month and MHIDs from it and then perform several analyses.
The Python script was also saved into a custom ArcToolbox so that any user could access it using ArcMap.
An example of this, the ArcGIS Analyst Frequency Tool was used to calculate the number of sewer mains and their
length that were cleaned that month. Once the cleaned sewer mains were created, the process was repeated for the
next month’s data. After the monthly data was created, the data was merged together into one layer and the frequency
of how often each sewer line had been cleaned for the designated time period was recorded.
Once the initial data was created an analysis was done with the hopes of answering the following questions:



Where are our trouble areas (hotspots)?
How many miles are we cleaning?
How often are we cleaning the same sewer line segment?
With the help of frequency analysis, calculating geometry, and the ability to spatially locate the sewer mains we were
not only able to answer all the above questions, but determine solutions for the problems that were revealed by our
evaluation.
Phase V –Deploy Map
The Engineering staff created the first sewer cleaning map (paper copy) and displayed the hotspots using a red-yellowgreen scheme. Hotspots, or areas that had been cleaned 5-6 or more times in that year (2011), and these were displayed
as red or yellow based on the frequency range. This was done so it would be evident to anyone looking at this map
where there were issues. Not only did we display the hotspots but we wanted to figure out how much time we were
wasting addressing these hotspots. It was determined that during 2010 we were cleaning our hotspot areas 74% of the
total cleaning time, which was very inefficient. This was an eye opening situation, not only for the Engineering staff but
the WSMD also. Both groups agreed that immediate action needed to occur to fix this issue because it was not only
wasting staff time but also costing the Town a tremendous amount of money.
3
The first thing the Engineering staff did is discuss each hotspot area with the sewer cleaning crew members and
obtained their local knowledge and their hypothesis of why they were cleaning each of those hotspot areas so
frequently. In some instances the culprits were easy to determine, such as roots or grease. This information became a
catalyst for improved interdepartmental communication between the WSMD and the Town of Mooresville Fat Oils
Grease Department (F.O.G.). Other locations were more difficult to determine the reasoning of why the cleaning had to
occur on such a regular basis. Several methods were used to investigate these areas, such as closed-circuit television
(CCTV) of the sewer mains and inspecting all the oil/water separators in the vicinity of the sewer cleaning hotspot.
Several problems were fixed by simple root control treatments and locating grease violations by local restaurants.
Phase 6- Ongoing Changes & Modifications
Once the hotspots were located and the WSMD had success in fixing several areas the decision was made to continue to
refine our process of automating the data entry and creation as much as possible and provide this data to more Town
employees. This included giving the cleaning crew mobile access to this data. Today the Town continues to map the
sewer cleaning, but using ESRI ArcGIS app, workers can now use iPads to log cleaning data directly into the system from
the field (instead of keeping track of Excel spreadsheets). The sewer cleaning crew can instantly view where the cleaning
truck has been in the past 18 months and be strategic in cleaning by maximizing the route of the cleaning crew and
migrating to areas that need attention. Additionally, they can easily identify hotspots which are displayed in red on the
map and communicate where customer intervention may be needed.
As a result of the changes the way sewer cleaning data is entered and the asset identifier has been modified. When the
sewer cleaning data was created in 2011-2012 we used the MHID as the reference. It occurred to us that the best way to
identify a sewer line would be to give all the sewer mains their own unique identifier (SLID) and we use that as the
reference in the work order. Today, all sewer cleaning data is now entered by the sewer cleaning crew via iPad using
our asset management program. That asset management program is used to report the sewer cleaning data in excel
format and then a similar process described in Phase IV is used to create the sewer cleaning data.
Organization Impact
How is it Applied within the Organization
This project created and continues to provide a model to the entire Public Services Division of how through the use of
technology can save both time and money. This proactive approach to maintaining the Town sewer system has
enhanced interdepartmental communication and several other applications have been developed since this project
started. Prior to 2010, the Water/Sewer Department, F.O.G., Water Treatment, Wastewater Treatment, Fire
Department, Police Department, and Streets Department combined had 5 users that knew or had access to GIS. Now,
GIS is widely used in the Public Services Division and we continue to find new uses for GIS technology every year.
Ultimately What Decision/Operation are affected
With the mobile access to GIS Utility data our utility crews have the ability to view real time data out in the field and
they are able to use this tool to help them perform tasks, such as; valve isolation, location of work orders, location of
assets (water valves, storm outlets, sewer manholes, etc.). All work orders are referenced by the asset type and the
number associated with that asset, for example; if a crew were to work on a water main leak, that work order would
reference that water main by the ID number it is on our GIS system and the Engineering Department can use this work
order to map water leaks and determine what trends are occurring in water leaks (Appendix). This table summarizes the
size of water lines vs. how old the line is, what season that line broke, and how much it costs to repair those breaks. This
table became crucial in determining what lines the Town of Mooresville wants to move forward with replacing. It was
4
determined that 2” water, galvanize water mains, and 6” water mains older than 66 years old should be the first to be
replaced and be included in our CIP planning.
The success of this project was a catalyst to move forward with other mapping projects with data analysis. Some of
those projects include water leaks hotspots and creating spatial data of the CCTV camera crew inspections and their
code repairs , which are based on sewer main ID numbers. These repairs are then linear referenced into our GIS
enterprise database. The Town uses the water leak and the sewer repair, and street condition to help prioritize which
street repairs need to occur first. Prior to this implementation the Street Department would often determine the list of
streets to be paved solely based on street condition with no consideration given for the buried utilities.
Over the past two years the Town of Mooresville has facilitated an action plan to minimize or completely eliminate
utility cuts in newly rehabilitated streets. This was a huge concern for the staff based on the additional cost to repair a
newly paved street as well as the citizen perception that there was a lack of coordination between Town departments.
We decided that a process needed to be put into place to coordinate utility work with annual paving plans. Early on we
developed a method to use the most recent ITRE street survey completed in 2011, GIS data, and asset management
software (IWORQ) to pinpoint problem areas. This street survey prioritizes road repairs based on their current condition.
We utilized this to set our annual rehab schedule. Our goal was to only rehabilitate streets after we had evaluated and
fixed everything possible issue from a utility standpoint (sanitary sewer issues, old 2” water lines , valve repairs, etc.).
We involved all departments in our Public Services Facility to produce the best process possible. Using all of that
institutional knowledge, we created a compiled but simple, color‐coded (green for ready to go, yellow for lack of data
and red for definite fixes) priority list. Utility work orders are issued based on the priority list with specific deadlines to
ensure work is completed in the proper order. As these work orders are completed, the list and GIS data are updated.
Once everything under the streets is fixed or inspected and found in good condition, that street moves up on the priority
list (from either red or yellow to green) for paving. We are already starting to see drops in our utility cuts and will
continue to monitor this for the future. This is just another example of how to apply GIS data and increase the
coordination and efficiency of Public Services Division at the Town of Mooresville.
Who are the Users
Any Town of Mooresville employee has access to any GIS data. This open data culture has paid dividends to boost
interdepartmental communication and collaboration.
What Effect Has There Been On Productivity And/Or Decision Making
There have been numerous ways that this project has been the catalyst for increased productivity and altered how the
Town of Mooresville makes decisions. Throughout the years of collecting sewer cleaning data there has been an increase
in the total footage cleaned and a decrease in the total repeats (cleaned more than 2 times) per year. Below is the
actual data for the last five years.
Year
Total (ft.)
>2 Times (ft.)
292,716.77
215,312.95
434,063.05
211,305.77
449,789.85
84,642.65
355,469.03
2,650.00
2010
% > 2 Times
74%
2011
49%
2012
19%
2013
1%
2014
1%
417,909.36
5,914.94
5
As you can see when the sewer cleaning program was first evaluated the sewer crews were cleaning sewer main
segments which had been previously cleaned more than twice that year, 74% of the time. The following year (2011) we
began to, coordinate with other department resources and knowledge, investigate the hotspots, and fix some of these
hotspot areas. Each year the hotspot areas decreased dramatically and we are now only cleaning repeat hotspot areas
1% of the time.
Not only has the Town seen an increase in productivity but also a decrease in total worker overtime and pump station
maintenance. These are in direct correlation with increase efficiency of our sewer cleaning and inflow & infiltration (I &
I) program. This proactive approach has led to fewer blockages, sanitary sewer spills, and less I & I to treat at the Waste
Water Treatment Plant. Below is a table that shows the pump maintenance budget and overtime budget for the sewer
crews at the Town of Mooresville over the past 5 years.
Year
Maintenance Budget
2009 $
283,719.30
2010 $
218,471.17
2011 $
204,589.22
2012 $
127,826.11
2013 $
128,453.85
2014 $
153,725.02
Overtime
$
29,738.72
$
17,138.43
$
13,530.21
$
8,112.82
$
7,373.70
$
6,070.95
The pump maintenance budget has decreased a total of 46% and the sewer crew’s overtime budget has decreased by
80%.
Graphic Examples
Screen Captures
This screenshot shows ESRI ArcMap application and shows the November 2010 Sewer cleaning data (brown) and the
2010 cleaned lines and their frequency of cleaning for that year. (see next page)
6
Below is a screen shot of the ESRI iOS application on an iPad which shows the Manhole numbers and the cleaning data
frequency (green,yellow, red)
7
Below is a screen shot of the ESRI iOS application on an iPad which shows the Sewer Main ID’s and Manhole numbers
and the cleaning data frequency (green,yellow, and red)
8
Maps
2011 Sewer Cleaning Map – contains line work of spatial location of all sewer mains cleaned
9
2012 Sewer Cleaning Map – contains line work of spatial location of all sewer mains cleaned
10
2013 Sewer Cleaning Map – contains line work of spatial location of all sewer mains cleaned
11
2014 Sewer Cleaning Map – contains line work of spatial location of all sewer mains cleaned
12
Aug. 2012 –Aug. 2013 Water Leak Map- contains all the spatial location of water leaks
13
Appendix
Fall
Water
Main
Size
3/4"
Sum
Breaks
Spring
Total Cost
Sum
Breaks
Summer
Total Cost
Sum
Breaks
Winter
Total Cost
49 Years
1"
3
$
734.95
1
$
217.21
28 Years
89 Years
2"
13
$ 9,095.73
100
Years
11 Years
2
1399.36
2
$
2
765.27
2
765.27
3
$
7
$ 1,897.75
1
456.98
1
456.98
765.27
945.59
765.27
217.21
2
488.61
6
1440.77
17
$ 8,809.33
13
$ 9,973.52
11
$ 6,437.36
54
$34,315.93
2
1399.36
1
242.42
242.42
1
1
1
4
148.54
1
203.07
1
203.07
1
735.505
2
884.045
1
425.68
1
764.36
7
3564.51
1
332.47
425.68
47 Years
2393.86
$
Total Cost
1
18 Years
2
2
Sum
Breaks
734.95
14 Years
50 Years
Total Cost
3
1
44 Years
Total
Sum
Breaks
764.36
809.54
54 Years
1
1
361.11
332.47
57 Years
1
723.69
1
723.69
65 Years
1
573.75
1
573.75
5
2992.23
30
21347.315
1
848
1
848
5
3007.26
67 Years
6
3777.68
10
6367.385
9
8210.02
7 Years
89 Years
3
1524.83
1
964.3
1
518.13
6"
17
$24,034.17
6
$ 4,206.72
5
$ 4,349.25
15
$20,033.17
43
$52,623.31
1
498.45
1
563.26
2
1061.71
1
784.3
42 Years
1
1077.16
1
1077.16
54 Years
1
763.81
1
763.81
1
329.96
1
403.24
4
1377.23
2
2337.73
3
3461.12
100
Years
41 Years
55 Years
1
1
784.3
329.96
64 Years
3
973.99
65 Years
1
1123.39
67 Years
12
20424.19
1
2079.95
2
1181.025
8
12887.91
23
36573.075
89 Years
2
1372.33
2
1152.78
2
2669.77
1
2000.06
7
7194.94
8"
1
$ 1,246.45
2
$
2
$ 1,777.14
5
$ 3,986.45
1
573.11
1
573.11
1
1246.45
2
962.86
1204.03
962.86
14 Years
15 Years
1
1246.45
3 Years
2
67 Years
12"
2
$ 2,343.62
100
1
1708.895
962.86
1
1204.03
1
2
$ 4,175.25
4
$ 6,518.87
1
1708.895
14
Years
16 Years
1
634.72
1
634.72
45 Years
1
2613.55
1
2613.55
67 Years
1
1561.7
1
1561.7
16"
1
$ 5,152.01
1
$ 5,152.01
53 Years
1
5152.01
1
5152.01
29
$19,865.87
116
$105,259.58
Grand
Total
33
$36,719.97
19
$14,539.98
35
$34,133.78
15