Growing a GIS Garden

Anyone with experience will tell you, gardens do not grow - no matter how talented the landscaper - without an effective growth strategy and a rich soil bed. The same can be said for planning, building and maintaining corporate data repositories - isolated databases don’t typically provide data-rich business intelligence. It’s an analogy that utility company San Antonio Water System (SAWS) understands well.

SAWS crews are responsible for maintaining and upgrading more than 10,000 miles of water and sewer mains.

As a provider to the seventh largest city in the U.S., SAWS had been very adept at cultivating and maintaining a significant network infrastructure to ensure clean water and efficient sewer services for its more than one million customers. It had also accumulated layers and layers of data specific to SAWS network assets and service locations to cultivate an internal data pipeline of business intelligence.

However, SAWS information landscape had not been developed with harmonious, integrated information systems, and more importantly, it had not been seeded with the root of all data assets: geography - the often overlooked spatial element that enables personnel to view business assets on a map. With no seamless connection among mission-critical data plots and no unified spatial asset references, SAWS was nurturing a subpar bed of overgrown weeds, making it difficult to maintain its high level of service and plan for business growth, says Cindy Tuttle, SAWS geographic information systems (GIS) manager.

“Our information management systems involved a real patchwork of paper maps, electronic tables, CAD files, mainframe tables and various other disparate databases,” says Tuttle. “All of those isolated pockets of information created a perfect breeding ground for data duplication and inaccuracies and made sharing geographic data and updating really difficult. Moreover, without an accurate spatial view of our assets and customers, it made it hard to analyze our business, adequately respond to clients and proactively plan for business development.”

SAWS crews continually train on equipment such as these backhoes to maintain their significant infrastructure and facilities. Here a team is training on the grounds of the Dos Rios Water Recycling Center, a unique facility that reuses biosolids and converts methane biogas into natural gas to sell on the open market.

Recognizing the need for a better solution to manage and share important business data, SAWS launched an initiative to create an enterprise-wide GIS and an asset management system. Although the combination would provide an integrated and automated environment to easily update and to maintain accurate map, network, operations and account data across the entire organization, Tuttle realized that to succeed, the ambitious project would need to begin by cleaning SAWS data soil bed.

So, SAWS’ GIS professionals began tending to the company’s spatial data stores with the meticulousness of master gardeners - digging through disparate databases, weeding out data inaccuracies and duplications, seeding data gaps, pruning existing data layers and integrating and harmonizing data flows - all in an effort to clean, purge and ultimately integrate critical business data.

Two years later, with the help of a robust “garden” software tool, Tuttle and her small staff have triumphed over significant data conversion challenges, enabling them to deliver on the vision of producing a rich soil bed of business information that is accessible through one seamless path. Indeed, a Web-enabled GIS not only provides a seamless view of SAWS service area - its networks, assets, service points, taps  - it also provides the core datasets for the asset management system, allowing personnel to better assemble and schedule work assignments, monitor the pipeline network, perform quality as-built inspections, correct discrepancies in the customer billing system and analyze its customer base to identify new business opportunities. And business is blooming because of it.

Sowing the GIS seed

Though the history of providing water and wastewater services in the San Antonio, Tex. region dates back to the late 1800s, SAWS birth as a single water utility began in 1992. A consolidation of three previous separate utility providers, SAWS today provides water and wastewater services to more than one million customers and maintains more than 10,000 miles of water and sewer mains buried below SAWS’ 560-square-mile service area – a network long enough to stretch from South Texas to Australia.

True to its conservation mantra, SAWS built and manages the nation’s largest recycled water delivery system, distributing about 115 million gallons of recycled water per day through more than 110 miles of pipeline, benefitting golf courses, parks, commercial and industrial customers throughout the city and surrounding areas. Most recently, in fall 2010, SAWS reached the biowaste-recycling trifecta by completing an innovative biogas facility at its Dos Rios Water Recycling Center. Dos Rios now recycles wastewater, reuses biosolids and now converts methane biogas into natural gas to sell on the open market.

SAWS expansion and diversification has seen a continual accumulation and progression of corporate information to record and monitor the daily operations of the company. However, the methods to create, maintain and connect that data has not followed the same advanced and aggressive path as SAWS service expansions. Without a direct and stable hub to internally connect the company’s external service points, SAWS was inadvertently washing business opportunities down the drain.

Data management tools of the past: here water and wastewater plans lie archived in rolls along a wall.

“Carrying out routine, simple business tasks would often send staff on laborious, long hunts - either physically or electronically - to find all the information they needed,” says Tuttle. “Then when they did find data, it often wasn’t complete or accurate, particularly asset attributes and location data such as addresses, which are critical to our service. Compounding the challenge was that core datasets such as assets and customer accounts were held in different databases with no geographical representation. That was impacting our ability to comprehensively analyze our networks, service connections and customers to identify savings or revenue opportunities.”

In 2005, SAWS launched an initiative to displace their data disconnects with a centralized Hansen Asset Management system and a Esri enterprise-wide ArcGIS that would both provide personnel with a unified, detailed and spatial view of the organization’s most critical business information and automate a number of routine tasks such as generating work orders. Though the move would provide the technological environment and data intelligence to enhance SAWS’ business operations and services, building that system would only be possible by resolving significant data conversion challenges.

One of three customized quality assurance/quality control workflows to routinely analyze SAWS’s sewer, water and address/service point data layers. Set to run weekly, the Data Interoperability tool checks for any data discrepancies and automatically alerts the GIS team to any problem areas.

Preparing the garden

It didn’t take long for the small GIS team to uproot notable data interoperability challenges. Of particular concern were how best to validate, harmonize and geographically visualize the account data held in mainframe tables, billing data maintained in separate mainframe tables, operations tabular data in another database and hundreds of Microstation-based water/sewer map layers stored in another database.

“All of our datasets had been created and maintained in isolation, with a single-minded purpose for each department,” says Tuttle. “So significant challenges arose when we tried to integrate data from different formats, schemas and databases to serve a unified information management system that has its own formatting and data requirements. These general incompatibility issues then became magnified when we needed to map all that data to bring a precise visual element to every tabular list and numerical value.”           

However, even more problematic than rectifying missing attributes, inconsistent schemas or incomplete datasets, was the inaccuracy, and often complete absence, of precise spatial representations of customers’ addresses.

“Addresses have been recorded and stored in myriad ways throughout SAWS, the majority of which have been in tabular form with no tied geography,” says Larry Phillips, a planner with SAWS. “The problem with that is accurate location data is fundamental to GIS - it is the basis for which all other elements are referenced. So it was clear that the data conversion process had to start at the root of all other data layers and functionality - the address.”

The GIS team had to convert hundreds of Microstation-based water/sewer map layers such as this one into GIS-based, spatially accurate map files to support the asset management system.

An intense, daily effort then began to untangle and weed through hundreds of Microstation map and attribute layers and account tables to tie asset locations, asset attributions and customer service points to their correct addresses - all 500,000 of them, one point at a time.

“Every tabular address field had to be converted into a geographical location on a map,” says Phillips. “Address points had to be on the correct parcel, and service points had to match the actual service location of their meter or sewer connection. We had to validate and correct every one of those points, and we were doing this manually, one by one, for half a million points.”

After two years of this laborious process, it became clear that the GIS department needed to find a more expeditious, but not overly costly, way to resolve such data interoperability issues to successfully sprout SAWS integrated information system. Trolling the Web, Phillips discovered Esri’s ArcGIS Data Interoperability (DI) extension, a tool that uses Safe Software’s FME spatial ETL (extract, transform, load) technology to enable users to convert, integrate, transform and distribute data in more than 100 geospatial formats. In addition, it provides more than 200 transformers to help automate myriad functions and perform quality-control checks across different data sources. In short, it seemed to be the “do more, with less” tool they needed.

Sprouting a-ha moments

Thirty days after implementing DI, Phillips had successfully transformed a two-day data-manipulation task into a five-minute exercise. SAWS road network layers are routinely received  from a third-party source, requiring a  GIS professional to spend two days manipulating and correcting the road features to properly integrate with the GIS. Using Esri’s DI tool, Phillips created an automated workflow that transforms, corrects and integrates each new road file with one mouse click.

“The time savings just for the road tool paid for the first DI license,” says Phillips.

For the GIS team, that automation was not only an “a-ha” moment for the positive impact on merging road layers, they suddenly saw the potential for it to become the garden tool of choice for cleaning up, integrating, manipulating and distributing more than 3 million data features and for supporting a host of automated workflows and business analysis tools.

Using Esri’s DI tool, Phillips created an automated workflow that transforms, corrects and integrates each new road file with one mouse click, saving a GIS professional two days worth of work.

 

Yielding  million-dollar savings

To ensure the central data layers were ready for operational applications, Phillips applied the DI to both validate the initial data clean-up and rectify any discrepancies as well as create the framework to easily update and integrate new customer address accounts and service points. Because the Microstation-based sewer and water features were not directly connected to their attributes, it was difficult to readily identify which sewer lateral line belonged to which sewer connection point or its exact geographic location. With  DI, Phillips designed an automated “point snapper” workflow, triggering the software to automatically snap a sewer service point to its correct lateral line, along with all the attribute information relative to each point and line. In three days, the tool corrected and moved 211,000 sewer lateral points, saving two GIS professionals eight months of work.

Phillips customized a similar process for spatially correcting and linking sewer laterals to manholes - a task that he says would not have even been considered possible with previous software tools.  Previously,  measurement references for distances from laterals to the downstream manhole were annotated on each Microstation drawing, with widely differing numerical formats. With DI, Phillips was able to accurately tie each lateral to its relevant manhole and transform those annotations into precise and consistent spatial references attributed to each lateral/manhole asset. In two days, the tool populated the GIS with nearly 171,000 sewer lateral measurements.

Larry Phillips sits at his desk, viewing one of hundreds of transformed GIS maps of the SAWS network infrastructure. With the help of the DI tool, the GIS team was able to reduce labor efforts from days into hours for a host of routine data tasks.

 

Now that the DI had readied the GIS data garden, the team continually sprouted new ideas for where they could next apply DI. For example, Phillips created three customized quality assurance/quality control workflows to routinely analyze SAWS’s sewer, water and address/service point data layers. Set to run weekly, the tool checks for any data discrepancies and automatically alerts the GIS team to any problem areas. And the quality checks are not confined to only the GIS; the DI tool also analyzes the Hansen data to ensure it syncs with the GIS.

With SAWS once insurmountable data conversion issues solved, the GIS is now ready to support  several Hansen-based operational applications and functions including an automated work-order module that enables personnel to seamlessly integrate service requests and automatically generate corresponding work orders - functionality that allows them to execute 11,000 orders a month. And by the end of 2010, the GIS will also serve the core data for more automated business tasks such as permitting and customer service.

“The DI tool has truly allowed us to be much more productive in much less time with far fewer people, and with much higher quality results,” says Phillips. “We have significantly reduced labor efforts from days into hours for a host of routine data tasks, freeing us to pursue projects we otherwise would not have had the time or resources to do. And apart from being able to develop a consistent, integrated, accurate and dynamic GIS, we now have a seamless environment to view and analyze our business with far better intelligence.”

“Without the cost-effective DI tool, we would have needed to double our team to accomplish what we did with a few people’s effort,” adds Tuttle. In fact, we estimate the money saved in our first year alone is nearly one million dollars.”

With more time to dedicate to bettering the GIS datasets and functionality, it’s clear that SAWS data management garden will continue to grow organically to support increasing demands and changes in business directions. That is a landscape worth nurturing.

 

Toni Jackson, GISP, is a GIS Tech II at San Antonio Water System; e-mail: tjackson at saws.org.