Michael J. Berman, David Kreinheder, Tamara Davis, Stephen Krippner

Integrating GIS and Existing Corporate Data

The King County Department of Transportation Transit Division GIS group has developed several applications with varied functionality using Visual Basic, MapObjects, Access, and Oracle. Each of these uses existing Transit scheduling and facilities data, stored in a corporate Oracle database and shape files stored in a corporate GIS production database. These applications required little or no data development, but simply took advantage of the wealth of existing data compiled within the agency. This permitted the development team to focus on the interface design, functionality, and application portability without requiring additional data maintenance responsibilities of the GIS and Oracle database administrators. MoEmitters is one of these applications and is used to optimize placement of radio frequency vehicle location emitters that track real-time coach movements in one of the largest Transit agencies in the country.

Introduction

King County Metro is a large Transit agency providing public transportation services to the Central Puget Sound Region including Seattle. Since the early 1980's, Metro has come to rely on a variety of critical information systems to optimize scheduling, monitor fleet movements, tabulate ridership, supply trip planning information to customers, and other operations. Among these systems was an in-house developed GIS that was replaced with ArcInfo in 1993, and the introduction of a corporate distributed database for the agency. There is a wealth of transit information stored in these databases which can be integrated with other data given the right tools. As part of a federal grant to provide specialized Geographic Information Systems (GIS) desktop tools to the agency, the GIS group within Transit has developed several GIS tools to access Transit information. These would not have been possible without the tremendous investment to update and maintain corporate data within the agency. This paper describes the organization of information systems that made these tools successful, and highlights one of those tools used by the work group that tracks real-time bus locations.

Distribution Database

Throughout the 1980's, Transit data were shared throughout the agency by implementation of software interfaces between individual systems. System A would request data from System B requiring an interface between the two. If System C required data from System A and B, two new interfaces would be developed. In many cases, it was necessary to maintain multiple diverse interfaces between production systems throughout the agency (Figure 1). Interfaces were complex and system specific, requiring high maintenance. Even minor modifications to a system could effect many data subscribers. For example, in Figure 1, deployment of an upgrade to the GIS system would necessitate, minimally, research into the affects on interfaces that receive GIS information (BusTime, AVL, and APC) and those that send to the GIS (Scheduling). Some or all of these interfaces may have required adjustment. Furthermore, modifications to one interface to accommodate a change in another system might cascade into other unrelated interfaces. It was also nearly impossible to insure that data received from one system would be compatible with data from another as each system had independent update schedules.

Figure 1
Figure 1: Sample interface structure between Automatic Passenger Counter (APC) system,
Automatic Vehicle Location (AVL) system, Transit Scheduling, GIS,
and customer information (BusTime).

In the early 1990's, King County Metro Transit restructured their approach to information systems by implementing a distributed database. The distribution database or DDB is a corporate distributed Oracle database that stores transit scheduling, geographic, and financial information. This database receives data from and feeds data to a variety of Transit systems including customer information systems, transit system planning, and operations (Figure 2). It serves as the focal point for data synchronization, standardization, and distribution. The DDB simplified the exchange of information within the agency and required staff in downstream systems to maintain a single interface to one industry standard database. Users were free to manipulate the structure of data or application software within their own system as long as the interface to the DDB was maintained. Although data were uploaded to the DDB from various work groups, the scheduling system and GIS provided the core information used by most other systems.

Figure 2
Figure 2: Distributed (non-spatial) data environment at King County Metro Transit.

In addition to the implementation of a corporate database, Metro Transit modernized the in-house developed GIS with an ArcInfo, ArcView, and MapObjects driven system (Figure 3). This complemented the DDB with a corporate spatial database. A shapefile library consisting of data sets from numerous themes was put in production and expanded the capabilities of existing customer information, planning, and Transit operations systems. Furthermore, agency staff were provided ArcView to access the GIS library, perform spatial analyses, and make maps for internal planning, community meetings, and local officials.

Figure 3
Figure 3: King County Metro Transit GIS.

Data Tools

With the implementation of the DDB and GIS shape library, users have a wealth of Transit specific and general county data that could be used in operations and planning contexts. Users could gain access to the DDB data directly from tools such as SQL Plus, indirectly through ArcView and ODBC drivers to the Oracle database, or through copies of the data made in their own systems. Most users, however, were unfamiliar with database operations using SQL or ArcView, and resorted to extracting copies of data into their own non-distributed systems with which they were most familiar. This defeats the purpose of a centralized, synchronized corporate database. Copies of data can get out of synchronization with other data, they take up extra space unnecessarily, and they require interfaces for data acquisition that must be maintained. In general, the DDB was not serving as a database to end-users, but as a conduit by which information was transferred into and out of other systems.

It was clear that the agency required access tools to bridge the gap between users and the Transit data warehouses (GIS and DDB). The Transit GIS work group developed several MapObjects applications that used DDB and GIS data. Database linkages and operations were automated in easy to use applications each of which required little or no data development (Figure 4). They were, in fact, made possible by the rich store of transit information in these databases. The display of routes as a transit planner would design them, as a scheduler would schedule them, as a transit rider would interpret them, or as a bus driver was assigned to them was simply a matter of taking route fragments stored in the GIS and piecing them together using tables in the DDB. These could then be associated to trip start and end times, passenger counts, security data, and other information stored in either the GIS or DDB to develop a variety of facilities management, planning, and operations applications. For each application, almost the same database linkages were required to display a wide variety of already available Transit information.

Figure 4
Figure 4: Sample GIS/DDB Linkages.

Examples

MoEmitter is an interactive Visual Basic/MapObjects desktop application for staff working with the Automatic Vehicle Location (AVL) system in Transit (Figure 5). It assists staff in the placement and analysis of vehicle location emitters that track the movement of busses. This application has made the process of placing emitters much more efficient as staff are able to properly identify appropriate locations to cover the most area. It has also significantly reduced staff time to research and identify off-route situations.

Figure 5
Figure 5: MoEmitter screen shot showing "ring" tool for evaluating
radio emitter broadcast signal extents.

The application uses the GIS shape library and scheduling information in the DDB to display routes and transit facilities. Even the AVL emitters themselves are stored as XY coordinates in the Oracle DDB rather than shapes in the GIS. This way, updates to the system are immediately made available to other systems without complex nightly batch processing. The GIS shapes are recreated each night, but users could access the DDB in ArcView to get that day's changes. By accessing the DDB directly, the application allows AVL users to see the latest changes to routes, stops, and other facilities without having to download this information to GIS or their own system. And by using the security features that come with Oracle, update access is easily controlled at the user level as well as allowing multiple users to update simultaneously.

The GIS work group is currently exploring the development of a GIS tool box that would compartmentalize most of the general transit object viewing and analysis capabilities. Specific modules would then be developed targeted at individual work groups throughout Transit and designed to edit specific transit features. In this way, a single change could be made to the generalized tool box to, for example, add new data available in the corporate spatial or Oracle databases. Each module, then, would be recompiled. In addition to simplifying maintenance of a variety of transit GIS based applications, this tool box will also standardize the look and feel of those applications.

Conclusions

First, applications are only as good as the data behind them. In the case of Metro Transit, a great deal of agency information was available in centralized corporate spatial and Oracle databases. Second, a dependable, stable data structure significantly reduced the amount of maintenance required throughout an applications use-life. Staff effort was able to be re-directed toward producing additional tools rather than maintaining existing ones. Finally, application development was not driven by the data, but with a corporate data maintenance and update infrastructure in place, GIS development staff were able to focus attention on interface design, functionality, and portability.

ACKNOWLEDGMENTS

We would like to thank Guido Riley who read earlier drafts of this paper. We would also like to thank the staff of the Transit Division for their helpful suggestions and patience.

Michael J. Berman, GIS Administrator
David Kreinheder, Senior GIS Analyst
Tamara Davis, GIS Database Administrator
Steve Krippner, GIS Analyst

Management Information and Transit Technology
Transit Division
King County Department of Transportation
821 Second Ave.
MS 150
Seattle, WA 98104-1598

Telephone: (206) 689-3732
Fax: (206) 684-2059
E-mail: michael.berman@metrokc.gov