Integrating GIS with utility Information management Systems

Integrating GIS with utility Information management Systems

Steven W. Uhrick
P.E., Chief, Technical Services Section, Environmental Engineering Division
Broward County Office of Environmental Services, 2555 West Copans Road
Pompano Beach, Florida, 33069, U.S.A.
E-mail: Suhrick@co.broward. fl.us

Daniel H. Feinberg
GIS Specialist, Montgomery Watson Americas, Inc., 560 Herndon Parkway, Suite 300
Herndon, Virginia, 22070, U.S.A.
E-mail Dan.Feinberg@us.mw.com

Abstract
The Broward County, Florida, Office of Environmental Services (OES) is developing a water and wastewater master plan. As part of the master planning effort, OES is implementing a geographic information system to automate mapping and data management operations and to develop automated links between the GIS, an existing infrastructure database, and hydraulic models. The GIS implementation and system integration required a carefully planned database design, custom application development, and significant organizational changes. A pilot implementation was performed to test the results, and the GIS design, applications, and procedures are being updated accordingly. This paper discusses the challenges of linking independent information systems with the newly-formed GIS.

Introduction
In the past few years, advances in infrastructure management technology have been occurring at an accelerated pace. The development of database management systems (DBMS) and geographic information systems (GIS) are greatly expanding the potential to integrate independent utility operations, computer information systems, and applications. This paper describes the integration of an existing infrastructure inventory system database and hydraulic modeling applications with a newly developed GIS for the Broward County, Florida, Office of Environmental Services (OES) water and wastewater utility operations.

Primary objectives of the integration effort include utilization of the OES’ existing infrastructure management system database as a centralized database, development of a GIS to query and display infrastructure data and to generate map products, and development of GIS-based applications to automatically prepare water and wastewater hydraulic models. This paper provides background for the integrated information system and a brief description of the system organization, and describes the methodology applied to integrate the infrastructure management system, GIS, and hydraulic models. The resolution of conflicting representations of water and sewer network facilities and the development of automated routines to transfer information between several end-user software packages are described. Also discussed are organizational and operational changes made to support the integrated system. Broward County is located on the southeast coast of Florida between Dade and Palm Beach counties. The OES, one of the five oflices of the Public Works Department, is responsible for both retail and regional water and wastewater services. OES provides regional wastewater service from the 80 m.g.d. North Regional Wastewater Treatment Plant and provides raw water supply to several communities via the County’s Regional Raw Water Supply System.

OES is responsible for planning, construction, operations, maintenance, customer service, and financial management for its retail customers, as well as providing water for resale to Coconut Creek, a City of 33,000 residents. Retail service is provided to three non-contiguous districts ranging from the northern to the southern borders of Broward County totaling in size to 44 square miles. OES retail operations serve 50,000 water customers from water plants with a total capacity of 52.7 m.g.d. through 600 miles of distribution mains. 33,000 wastewater customers are served by 340 miles of gravity sewer, 190 lift stations, and 70 miles of force main sewer. The number of new customers increases by 1 percent per year. OES currently has 350 employees and has a five-year Capital Improvement Program budget of $240 million.

In 1994 OES made the decision to replace its 1988 Master Plan. The new Master Plan effort has the traditional master plan goals and some additional goals including the following:

Set performance goals for the retail water and wastewater infrastructure and then compare existing facilities to those goals to determine where improvements are needed;
Set performance goals for operational practices and then compare existing practices to those goals to determine where improvements are needed;
Forecast new infrastructure needed to serve future customers;
Construct new hydraulic models of the water distribution system and model the wastewater collection system for the first time;
Integrate several independent information systems in use within OES; and
Convert the OES’ 350 water and wastewater system maps from paper to GIS format.

The Master Plan is being prepared by Montgomery Watson, Inc. with heavy involvement from OES staff. All final deliverables will be in electronic format (GIS, database, CADD, word processor for the report document) so OES staff can maintain the Master Plan into the future.

Geographic Information System Implementation

GIS Project Goals
During the Master Plan scope preparation, OES staff realized that important data, a pipe’s diameter for instance, was being stored and maintained in three separate locations including paper maps, hydraulic models, and the OES infrastructure inventory database, CASS WORKS (RJN Group, Inc., Wheaton, IL). Staff also realized that there was no formal system in place to assure the same value for a data item in each of the three locations. The hydraulic modeling and GIS development portions of the Master Plan project will link two of the three data sources. OES strongly felt that there should be a “master” source of data that “feeds” all OES operations and computer applications. With this in mind, OES set the following goals for its GIS and integrated database system:

Mapping, hydraulic modeling, and the CASS WORKS infrastructure inventory database will be maintained in an integrated manner;
CASS WORKS will be the “master” storage location for all data it is configured to store;
Additional information required to support hydraulic modeling and other applications will be stored in supplemental (to CASS WORKS) database tables in various locations throughout the OES LAN and computer system;
Applications will be developed to automate data exchange between CASS WORKS and the mapping and modeling operations. Maps and hydraulic models will be updatable from the master database with the least amount of manual intervention;
GIS-generated maps will fully replace and automate the OES paper system maps;
GIS technology will allow for easy creation of CADD drawings to support engineering efforts; and
Many OES functions that currently require paper maps will be converted to direct data access using GIS software, therefore the GIS “viewing” software must be easy to use and customize.

GIS implementation will be accomplished using County standard software products including ESRI GM software; Oracle relational database management software and dBASE for database maintenance; Hydroworks for wastewater collection system modeling, and H20NET for water system modeling.

GIS Database Desian
The GIS database design evolved over the course of several months from an initial design as specified in the Master Plan project scope to a more detailed, and revised, final database design. OES and its consultant, Montgomery Watson, conducted numerous meetings and workshops to identifi the best approach to meet the identified GIS goals. Emphasis was placed on the need to cartographically reproduce OES paper maps, store map data in the master CASS WORKS database, and to organize data in such a manner to efficiently prepare hydraulic model input files. One major constraint was that no modification was to be made to the existing CASS WORKS inventory database structure during the GIS database design phase. This decision was made not because of limitations of the infrastructure inventory database and related software application (both of which can be customized), but rather as a preference of OES management.

The result of several months effort, a database dictionary document defining all GIS layers, valid database attribute values and codes, and cartographic standards was developed. This document will guide the GIS conversion and all custom application development efforts. The GIS database dictionary acts as a living document which must be updated as new facilities are represented in or changes made to the current GIS structure.

Each of the three OES retail service districts are stored separately on the GIS file server and include the three major utility layers (potable water, raw water, and the wastewater collection system), each including system facility location, cartographic symbology, and map annotation (text). Map features are not broken at map sheet borders, allowing the “seamless” GIS database to be easily downloaded to the hydraulic models with no artificial model nodes at map sheet boundaries.

As an illustration, the potable water distribution system is represented as follows:

Figure 1. GIS Representation of Broward County OES Potable Water Distribution System.
The map conversion effort, now underway, was designed in such a way to preserve existing data in the CASS WORKS database. GIS features are being linked to existing infrastructure inventory database records when available, with several database fields updated in the process, and new records are being added to the infrastructure inventory database when no record existed at the time of GIS database conversion. Extensive quality control programs were developed by the consultant and OES using Oracle SQL, C++, Arcview Avenue, and Arc/Info AML to ensure that existing information in CASS WORKS is not compromised, and that new information conforms to the rules defined in the GIS database dictionary.

Information Flow
Once in place, the integrated system allows sharing of data between the master infrastructure inventory database, the hydraulic models, and the GIS and mapping environment. OES is developing procedures to control how and when data will be transferred between the information systems. In general, data are moved from the master infrastructure inventory database to the modeling and GIS applications, but not from the GIS and models back to the master infrastructure inventory database. Figure 2 on the next page illustrates the movement of data between the major information systems within OES.

After much internal discussion, OES made the decision not to update its master infrastructure database directly from the GIS environment. This is due to an organizational preference rather than software functionality limitations. As new features are added to, or existing features edited in, the GIS maps, changes will be made to the master infrastructure inventory database using the CASS WORKS software interface independently of the changes to the GIS database. Procedures will be implemented to noti@ those staff responsible for maintaining CASS WORKS of any changes. These procedures will need to be rigidly followed so that changes to the infrastructure inventory database and GIS are properly synchronized. These procedures will require close coordination between the operating and engineering divisions as they will have co-responsibility for the maintenance of the master infrastructure database and GIS.

Figure 2. Information Flow between Master Database, GIS, and Hydraulic Models.
To mitigate data access problems on the current OES LAN, a copy of select tables will be downloaded from the master infrastructure inventory Oracle database located on a file server in the OES operations division to the local GIS file server located in the OES engineering division. The copy of the infrastructure database will be stored in an INFO format for rapid data retrieval in Arc/Info and Arcview. Data not accounted for in the master infrastructure inventory database, yet required for mapping and modeling, are stored in “supplemental tables” in an INFO format. These “supplemental tables” reside outside of the realm of CASS WORKS and are updated directly from the GIS. Automated applications using Oracle SQL and Arc/Info AML are being developed to maintain these data and to verify referential integrity between the CASS WORKS tables and the supplemental tables.

When OES engineering division updates its hydraulic models, modeling attributes are downloaded from the CASS WORKS infi-astructure inventory database and the network representations (graphics) are downloaded from the GIS. Applications developed using Arcview/Avenue automate this procedure and ensure proper synchronization between the attributes and the graphics during model data preparation, warning OES staff of any inconsistencies noted during the data preparation process. Both modeling software applications, H20NET for water and Hydroworks for wastewater, contain powerful data display, query, and specialized analysis tools, therefore providing all necessary functionality for review and analysis of modeling results and eliminating the need to upload model result data back to the master infrastructure inventory or GIS databases.

Methodology
Several challenges arise when moving data between an infrastructure inventory database with a prescribed data structure and conceptual representation of water/wastewater system facilities, a GIS environment where the primary goal is cartographic reproduction of existing paper maps, and hydraulic models with their own prescribed data structures and waterlwastewater facility representations.

Linkina GIS to the CASS WORKS Infrastructure Inventory Database Of primary importance was adhering to the goal to store all data in CASS WORKS that the OES CASS WORKS database is configured to store, and to store additional information in supplemental database tables. As a result, some GIS features are linked to records in one CASS WORKS table, other GIS features are linked to records in numerous CASS WORKS tables, and still other GIS features are linked to records in CASS WORKS and in supplemental tables, depending on which tables were configured to store specific information.

For example, an air release valve in the GIS is linked to a record in the CASS WORKS Valve Inventory table only, whereas a flow control valve in the GIS is linked to a record in the CASS WORKS Valve Inventory table and a record in the CASS WORKS Node Inventory table. The CASS WORKS Node Inventory stores information required for hydraulic modeling, and the CASS WORKS Valve Inventory stores information specific to valve facilities required for GIS mapping, yet not required for modeling. Air release valves are not being modeled, whereas flow control valves are being modeled and therefore flow control valves contain a record in the CASS WORKS Node Inventory while air release valves do not. Additional database table combinations exist for each of the approximately 30 water/wastewater facility types represented in the GIS database.

To mitigate the potential loss of productivity as OES staff attempt to navigate a comprehensive and sometimes complicated database structure, OES and Montgomery Watson are developing custom GIS applications to facilitate data retrieval during routine GIS display and query operations. These applications, developed using Arc/Info AML and Arcview Avenue, will facilitate data retrieval so that OES staff can focus on examining data rather than trying tojkzd the requested data, which would otherwise be a lengthy, difficult, and frustrating task. The majority of OES staff will be trained on the use of the GIS software applications rather than on the internal database structures.

Downloading GIS and Infrastructure Invento ry Database Information to Hydraulic Models Another challenge was developing a procedure to translate data from the infrastructure inventory and GIS databases to the hydraulic models. A conflict arises between the GIS and infrastructure inventory databases, which both represent some water/wastewater system facilities in one fashion, and the hydraulic modeling software, which represent and model the same facilities in an entirely different fashion. A flow control valve in the water system, for instance, is represented as a point (single X, Y coordinate with a unique identifier) in the GIS to facilitate cartographic replication of the paper maps, whereas a flow control valve is represented as a linear feature with discrete “upstream” and “downstream” nodes in the H20NET hydraulic model.

Rather than impose the required model representation in the GIS, a semi-automated procedure was developed to transform the point-based representation of the facility in the GIS to the linear representation of the facility in the hydraulic model, as illustrated in Figure 3. To facilitate this transformation, a supplemental database table was developed to store model information for the valve’s “downstream” node (an assumption is made that the GIS point represents the valve’s “upstream” node). A record must be added to this “Downstream Model Node” table when the valve facility is added to the GIS by OES staff. Should the record not be added to the supplemental table by OES staff, an automated model preparation program (developed using Arcview Avenue) warns the user that model inputs cannot be properly generated until the required information is added to the

Figure 3. Conflicting Representations of Water System Features in GIS and Hydraulic Model.
supplemental table. This procedure must be performed for pressure and flow control valves, water pump stations, and sewer lift stations.

A custom application was developed using Arcview Avenue to automatically prepare model inputs directly from the infrastructure inventory tables, supplemental database tables, and from the GIS graphics. Upon initiation of the download process, the OES technician must indicate for which of the three retail service districts the model is being prepared, which utility layer is being modeled (potable water, raw water, or sanitary sewer), for which planning period the model is being prepared for (existing system only, 5-year planning period, 10-year planning period, etc.), and the names and storage locations of the model input files. Information is extracted from pertinent inventory database tables and graphical information including node locations, pipe lengths, and pipe shape (OES requires a curvilinear representation of piping in the modeling software rather than a generalized “node-to-node” representation) are re-formatted as required by the H20NET and Hydroworks modeling software packages. At that point, OES planners receive the data, import the data into the respective modeling sot%varepackages (using standard import fimctionality provided by each), run and evaluate the models.

Organizational Impacts
Implementation of the GIS portion of the Master Plan and the integration between the GIS, infrastructure management database, and hydraulic models has and will continue to have organizational impacts, some of which very likely are not known at this time. OES realizes that sharing data in an efficient, semi-automated, non obtrusive manner makes the entire organization more effective. OES recognizes, however, the need for closer coordination between the operating and engineering divisions as they will have co-responsibility for the maintenance of the master infrastructure database, GIS, and hydraulic models. This need, coupled with the requirement to migrate many people from paper maps to electronic maps and GIS-based applications, may cause problems during the transition phase. A carefully designed GIS database, properly implemented data sharing procedures, and proper documentation and training, should facilitate a smooth transition.

Additional requirements necessary to support the GIS mapping effort include the dedication of approximately 10,000 staff hours of CADD operator time to prepare background base maps and other necessary map products and the need to upgrade computers to accommodate the selected GIS and 185.database viewing software and applications. The OES LAN may need to be upgraded to handle the increased network traffic due to increased data sharing and GIS use by an increasing number of staff. The anticipated proliferation of other GIS applications once GIS capabilities are better understood by more people will cause further necessary changes to daily operating procedures within OES.

Conclusion
Several carefi.dly planned steps must be taken to successfully implement a GIS and to integrate the GIS with other information systems including infrastructure inventory databases and hydraulic models. Through a properly-developed database design, data can be efficiently migrated between independent information systems. Custom application development allows GIS users to access data stored in a comprehensive database structure with a minimal amount of effort. Additional applications are being developed to ensure synchronization between the different databases and engineering/planning applications.

GIS implementation has not yet been completed, but with proper training and documentation, proper understanding of goals by OES staff, and application development, coupled with a carefully designed GIS data structure and carefi.dly implemented procedures, most GIS users will gain the advantage of GIS technology with a minimal amount of change in daily roles and operations. Future papers may provide a retrospective on how the GIS has affected the organization and daily operations, and how GIS use may be expanded for additional applications and use within the Broward County, Florida OES.