Developing a GIS Database for a Rural Public Water System

Developing a GIS Database for a Rural Public Water System

An Example from Piedmont North Carolina

Submitted by

Michael Bacon and Joseph T. Forrest

Introduction

According to the NC Rural Economic Development Center there are approximately 659 rural water delivery systems in 75 counties in North Carolina.[1] Very few, if any, of these systems have implemented a GIS database, despite the fact that GIS offers an ideal technology to maintain and utilize their operational information. The original purpose of this project therefore was to develop a simple prototype system to (1) demonstrate to the feasibility of applying GIS in this niche industry and (2) to have a working model to test the interest of utilities in the concept. With GIS a water system can view its delivery network and its relationship to land ownership, easily find the location of its lines and their attributes (diameters, lengths, etc.), view service locations, and evaluate areas for possible expansion, as well as numerous other functions. As a test case we chose the Orange-Alamance Water System (OAWS), which serves an area of northern Orange and eastern Alamance Counties in Piedmont North Carolina (Map 1). We chose this particular system principally because of access to the data necessary to implement the prototype. We feel, as well, that OAWS is probably typical of rural water utilities in North Carolina and so forms an excellent model for implementing the concept.

Background on OAWS

The Orange-Alamance Water System was founded in 1967 as a non-profit corporation to supply drinking water to areas of Orange and Alamance Counties. Prior to its establishment, all water in the area was supplied by wells drilled and maintained by property owners. Over the years OAWS prospered and built up a customer base of over 3600 users. At this level of service the system nearly exceeded its capacity to produce and deliver water. During years of drought, OAWS’s capacity to deliver was seriously jeopardized. In 2003 the NC Department of Environment and Natural Resources (NCDENR) informed OAWS that it could no longer add customers until its delivery capacity was increased, either by purchase contracts from other utilities, by drilling additional deep wells, or by expansion of it reservoir capacity. Because its system network is in an area of high growth OAWS felt the necessity of alleviating its restrictions as quickly as possible, in order to start adding new customers. In 2004 OAWS concluded its service to the town of Swepsonville,[2] resulting in a net loss of 600 customers. The system then made an agreement with Burlington to purchase water and have it delivered through a new interconnect with the Haw River system. Since the loss of the Swepsonville customers at Swepsonville, OAWS has added only a fraction of the anticipated customers that it felt would justify the system's commitment to purchasing water from Burlington. OAWS needs, therefore, to add to its customer base. It is anticipated that the implementation of the GIS database could assist OAWS in closely maintaining the operational data necessary to operate most efficiently, in identifying and contacting non-customers within its existing network and in making strategic decisions concerning future expansion beyond its present service area.

Project Objectives

The project had three objectives:

1. The first and most important objective of the project was to bring data together that shows the delivery networks and all land tracts and attributes around the system. This was a fairly simple objective to fulfill. It involved creating a shapefile of the system’s delivery lines and obtaining shapefiles of land parcels in the service area. Implementation of this objective is discussed in a following section.

2. A second objective was to link the system’s delivery network with the actual properties it serves. This will give users the ability to see the delivery network in relationship to property boundaries and to tie customer information to those properties. OAWS staff could easily find and view a customer’s property and all information about that parcel, such as service address, mailing address of the customer, property area (size in acres), location of the property in relationship to the road and street network, parcel ID number as assigned by the county tax department, or other relevant information as needed by the utility’s management. Field personnel who need to make emergency calls for line repairs can easily retrieve service locations and produce a map showing the best route to the location.

A secondary advantage of being able to view the customer base with their properties is strategic. In cases in which a system is attempting to grow and expand, there may be properties within the system that are not customers that could be added with proper incentives. The GIS implementation is an excellent way to see exactly where present customers are located and where properties are located that are not subscribers. A marketing effort can then be mounted to sign up additional subscribers. This very situation has become an issue for OAWS, as the system lost 600 customers in 2004 and is trying to add additional ones now to make up lost revenue.

3.  An additional issue facing OAWS is not only identifying potential customer within the existing network, but also in identifying areas of strategic expansion not presently served. The OAWS network is in an area of rapid growth. Hillsborough, located to the east, and Mebane, to the west, are both competing with OAWS for new customers. The pressure from Mebane is especially intense. Reference to Map 2 shows how the OAWS and Mebane systems cross in some areas and run parallel to each other along the same street in others. Both systems now have surplus water, serve adjacent growth areas and are looking to expand. The question is where are the areas of possible expansion. Having a map view of the network and all properties with attribute data gives the user a powerful tool for querying the data and finding properties that may be subdivided and developed in the future. Simply having the network and parcels shapefiles in a map document already goes a long way to fulfilling this objective. Recently a group of residents from the Mill Creek Road area of northern Orange County approached OAWS to ascertain the possibility of expanding the system to that area, which at present has only wells and notoriously poor quality water. OAWS has agreed in principal to assist the Mill Creek residents if they can find funding for the project. The citizens group is presently trying to interest other residents of the area in pursuing this possibility. They have proposed to send out a questionnaire to ascertain the level of interest.

The third objective of the project therefore focuses on building a tool within the GIS database that can be used to compile and geographically view the results of the surveys.

Data and Sources

To construct a prototype GIS database for a water system there are three data requirements:

1. A file of the system's delivery lines and neighboring systems’ lines;

2.  The customer database, with names, mailing addresses, service addresses and account numbers;

3.  A file of land parcel data in area surrounding the system lines.

These three data sets are all available for the OAWS system and are described in the following sections:

1. System Delivery Lines:

OAWS has little map documentation of it delivery network. An old CAD map was assembled some years ago by the system's engineering consultants (Map 3), but it only shows the relationship of the lines to roads. The map is at a small scale and difficult to interpret at some points; it was scanned, georeferenced using ArcGIS and digitized to create a shapefile of the system delivery lines. The map was then overlain on a shapefile of land parcels purchased from the Orange County Land Department. The OAWS CAD-produced map was not cartographically pristine and lines tended to be somewhat irregular in relationship to roads. In many cases it was difficult to interpret which side of a street the line was on. To avoid mistakes and maintain consistency we relocated all system lines down the center of roads and streets along which they fell. At some later time these lines should be correctly located. No attributes are attached to the networks segments. This is an additional task for the future.

The lines for the Mebane system were not obtained so easily. For security reasons the city will nor release maps or files of its system. We did manage late in the project to obtain a large-scale, page-sized map of the system (Map 4). The map is extremely difficult to read because of scale and conflicting colors. We scanned the map, georeferenced it and produced a shapefile of the Mebane system.

2. Land Parcel Data

Excellent land ownership data is available from the Orange County Land Records Department, which produces and sells a polygon shapefile of land parcels in the county with all relevant ownership and tax attribute data. In addition a similar file is available through Alamance County at no cost. The Alamance County file does not contain tax data, but can be linked to this information through a common land parcel ID number. The Orange and Alamance shapefiles (names of files) form the basis for the database’s property ownership grid (Map 5).

3. OAWS Customer Database

OAWS personnel provided a copy of their customer database, which is maintained in a commercial invoicing and accounting package. The data was downloaded in ASCII format and loaded into Microsoft Excel and Access for processing. The data consists of a customer account number, customer name, mailing address, city and zip code and a service address.

Data Problems

We had originally thought that we would be able to link the OAWS customer database to the land parcel data by either matching services addresses in the OAWS with property addresses in the Orange and Alamance County parcel files. Because of non-standardization in the two files this proved impossible. Of 3600 addresses (includes active and inactive customers) we were able to match 434 addresses in this manner in the two counties. As a second approach we tried geocoding the service addresses from the OAWS data and overlaying the resulting point shapefile over the land parcels. Theoretically the matched address points should overlay the parcels with the same address. We used Tele Atlas, an online commercial geocoding service to accomplish the address matching. Of 3600 addresses submitted, Tele Atlas made Type 1 (‘exact’ matches) matches for 1588 addresses. Upon overlaying these on the parcel file we ascertained that very few matches fall within the correct parcels, though the points fall along the correct streets.

When we initially purchased the parcel shapefiles from Orange and Alamance counties we noticed that property addresses were not standardized. For one parcel the address might be ‘Highway 70 W,’ whereas for an adjacent property the address might be ‘Hwy 70 West.’ The same degree of non-standardization applied to addresses of service locations in the OAWS customer database. Matching customers to parcels by database query functionality is therefore impossible. Later we discovered that Orange County has is building a standardized street address file for its parcel database. We obtained the incomplete, preliminary version of this file and joined it to the Orange County parcel data. Alamance County does not yet have a standardized address file.

In order to accomplish the second objective of the project we decided to build a tool to assist users to make the critical join between customers and their properties.

Tool to Link Customers to Properties

The basic design for this tool is a form that opens in the GIS map project (the system database) and allows a user to identify properties along the system delivery network and see information about these properties from the parcels database. The user can then visually match an address in the customer database to a standardized service address in the parcels file. Since this match cannot be accomplished by database functionality, it must by its very nature be done manually and visually. A user must make the judgment that a customer service address matches an address in the parcels file. When a match is made, the user types an OAWS customer account number into the form. This links the customer database with the parcel file. After linkages are made, the parcels with matches (i.e., OAWS account numbers) can be converted to a shapefile and displayed. The process of interactively matching continues until all customer properties have been identified and linked. As new customers are added or removed the shapefile can be queried to remove or flag the altered records. Since the customer property shapefile contains the OAWS account number, the system could be linked to OAWS’s existing accounting system to display account and invoice information through the GIS database.

There may be customers in the OAWS system that cannot be matched with their properties, especially in Alamance County where there is no address standardization. These customers will require additional research to match, perhaps by interviewing the customer over the phone to ascertain the exact property location or by a field visit.

When all customers have been matched OAWS will have a valuable data reference that will show its network and properties served and will reveal any properties not yet tied to the system. Any property owner within the network who is not tied into the system is a potential customer. The management of OAWS will also have a valuable reference to view all properties outside of the delivery network to ascertain if their locations are candidates for future expansion.

Tool for Assisting in the Assessment of Strategic Expansion

As mentioned previously, a group of concerned residents of the Mill Creek Road area in northwest Orange County has approached OAWS about the possibility of extending it delivery lines to that area. The location of Mill Creek Road and environs is shown in Map 6. All homes and businesses in this rapidly growing part of Orange County have wells and septic tanks. The well water is of poor quality on many properties.