Canadian Region adds Spatial Capability to Storm Water Management System

By Rob Gallivan

The Region of Waterloo, Canada, has added geospatial display and analysis functionality to its storm water management system by creating a GIS link to its Hansen Storm database software. This link, built with Intergraph's GeoMedia software, provides a seamless conduit through which spatial data can be shared among multiple local governments.

Adding spatial viewing and analysis capabilities allows the Region of Waterlook to plan for upgrades or repairs to above and below ground infrastructure at the same time, so the street only has to be excavated once.

The GeoMedia-Hansen link component now enables Waterloo personnel to graphically view storm water facilities in a geographic context clearly showing the relationship with other public works infrastructure, such as the transportation network. An additional project includes establishing live, two-way access to the storm water databases maintained by two municipal governments, which will eliminate the redundancy and inefficiency that usually accompany the sharing of data.

In Canada, storm water control infrastructure is managed as part of the transportation system due to the interconnection of their components. Management responsibilities and maintenance funding for highways and roads are divided between regional and municipal government bodies. In the Region of Waterloo, for example, the regional government is responsible for storm water drainage on major arterial roads, while local cities and townships are responsible for all other surface water drainage on adjacent properties and local or residential roads.

With storm water pipes and drainage basins winding their way throughout the transportation corridors and crossing municipal/regional jurisdictions, government transportation offices work together for effective management of the storm water system. This need to use and share data was a driving force behind the Region's decision to purchase the Hansen Storm product and then enhance it with the Intergraph GeoMedia link.

Storm Database

The Region of Waterloo is located 100 km southwest of Toronto, Ontario. It includes the municipalities of Kitchener, Waterloo and Cambridge, as well as several smaller townships. The area's population is 450,000.

In 2000, the Transportation Division purchased an infrastructure management software system from Hansen Information Technologies of Sacramento, CA. In addition to the storm module, the other modules purchased from Hansen are customer service, work orders, infrastructure assets and pavement management. Each of these modules is linked to one another in ways in which feature and attribute data can be drawn from a common database.

Prior to the implementation, the Region of Waterloo already had a spatial database in the form of a MapInfo desktop mapping package. This system contained spatial data relating to the transportation network, parcel boundaries, government buildings, hydrologies and street addressing. At the time, there was no storm water system information in a digital format, prompting the division to initiate a mapping project.

Division personnel equipped with handheld computers, GPS and digital road maps went into the field to record the precise locations of these features and collect related attributes. Information gaps from the field data collection were reviewed back in the office and compared against any available as-built drawings to help close the gaps in the storm network. This data was later uploaded into a Microsoft Access database.

The Transportation Division focused its mapping efforts initially on the rural major arterials. The cities of Waterloo and Kitchener had previously mapped storm water infrastructure inside their city boundaries. Kitchener maintains its storm data in an ESRI database, and the City of Waterloo stores its storm water information in an AutoCAD system. A third city in the region, Cambridge, currently has no digital map database of its storm system, but the Transportation Division is working with them to devise plans for development of a GIS and a storm water database.

Spatial Component

Adding spatial viewing and analysis capabilities to its management system was part of the Transportation Division's plans from the start. A major goal of the automation project was to enable division personnel to overlay feature layers onscreen representing multiple components of the transportation infrastructure – roads, storm water, sidewalks, water mains and street lighting.

Division personnel equipped with handheld computers, GPS and digital road maps went into the field to record the precise locations and attributes of storm water system features.

This would allow them to analyze the network as a whole to better plan for capital improvement projects. As is true for most transportation departments preparing to dig up the street, the Region of Waterloo wants to know what other upgrades or repairs can be made at the same time to the above and below ground infrastructure so the street only has to be excavated once.

To make this viewing of multiple data sets a reality, the Transportation Division knew that besides adding geospatial analysis capabilities to its system, it would also have to find an efficient means of transferring data between Hansen and its desktop mapping package, as well as the GIS and CAD systems operated by the two cities.

Division personnel began comparing notes with other government offices in Ontario and learned that several used Intergraph's GeoMedia package for GIS and other spatial applications. At the time, there was no out-of-the-box link between Hansen and GeoMedia, but Intergraph offered to create a custom solution by using a series of ActiveX Dynamic Link Libraries and GeoMedia custom scripts, both written in Visual Basic. This GeoMedia/Hansen link is now offered as an optional product by Intergraph.

The Intergraph product proved to be ideal for the division's needs thanks to the open architecture of the software. GeoMedia was designed as a universal server of spatial data, capable of accessing and importing raster and vector files from multiple databases in their native formats and converting them on the fly for geospatial analysis and viewing. This capability eliminated any need for the Region of Waterloo to undergo repeated conversion of data to make files compatible for sharing.

With the purchase of GeoMedia for development of the bi-directional Hansen link, the Transportation Division realized this product could also serve as its primary GIS. The decision was made to phase out the desktop mapping package then in use and build on the new GIS platform. Once the link was developed, the first major function was to transfer and convert spatial data from MapInfo into the Oracle spatial database running under the new GIS.

This one-time conversion of spatial data carried out as an automated process in GeoMedia. Even with the desktop mapping package out of the picture, this left the Transportation Division with five active databases:

• The Hansen database holding relational data relating to most features in the transportation network with the exception of the storm water system;

• The Oracle database with the spatial data relating to the transportation information;

• The Access database containing storm water features, attributes and locations collected in the field;

• Kitchener's GIS database of storm water and other features; and

• Waterloo's CADD database of storm water and other features.

Linking Databases

Dealing with too many databases may seem ill conceived, but in reality it is the most efficient and cost-effective means of maintaining the data. The GeoMedia link between each database and Hansen allows individual government offices to maintain the data relating to its jurisdiction while keeping that data in its native database. There is no need for one government agency to standardize on another's GIS for the sake of compatibility. And no one office has to foot the bill for maintaining everyone's data.

The Region of Waterloo is working toward establishing an intranet connection with the cities of Waterloo and Kitchener. Division staff will most likely use the intranet to access data files directly from the cities' databases in response to queries from GeoMedia. This retrieval of data will occur seamlessly so that the user will not realize another database has been accessed.

In a typical application, an individual in the Transportation Division will sit down at a desktop computer running GeoMedia/Hansen and perform a standard relational database query or spatial query – specifying the size or age of storm water pipes in a certain area.

Using the linear referencing system of the GeoMedia add-on called GeoMedia Transportation Manager, staff is capable of viewing and querying datasets that show the geographic proximity between infrastructure elements and query attributes relating to them in the graphical environment of the GIS. Again, the GIS will retrieve data from any or all of the available databases to complete the spatial query.

In the example of a capital improvement project, the Transportation Division expects the ability to visualize relationships between storm water pipes and road surfaces will significantly enhance the regional government's efforts to repair and maintain numerous pieces of the transportation infrastructure, above and below ground, in a minimum number of projects. This approach will increase efficiency of regional work crews, reduce disruption to citizens and provide significant cost savings to the Region and the public.

Building on the GIS

Once the link between all of the city databases is completed, the Region of Waterloo plans to leverage its new GIS capabilities more fully. Because GeoMedia automatically georeferences data layers, the Transportation Division wants to equip its field crews with mobile GIS on laptop/handheld computers. These layered digital maps will help crews visualize underground infrastructure and locate buried pipes and valves more quickly.

The division is also examining Intergraph's GeoMedia WebMap product, which could be used to extend GIS data and functionality to other personnel within both the regional and municipal governments via the intranet or even to the public via the Internet. Internal discussions are underway to develop a series of maps for online public access to educate citizens on construction, road closures and detours planned in their neighborhoods.