Integrating GIS and Oracle for traffic analysis

Integrating GIS and Oracle for traffic analysis

Laura Haley
GIS Coordinator, City of Bloomington, Engineering Department
PO Box 100, Bloomington, IN 47402 USA

Abstract
The City of Bloomington initiated the development of an AM/FM/GIS system in 1989. A completed base map of land based features and water, wastewater, and storm water systems has been in place since early 1994. The City’s initial focus was on maintaining its base map, expanding map features related to planning, public works, and utilities, and developing end-user applications for basic viewing, outputting, and querying map information. The City views GIS as part of a larger Information Management System and is now working towards integrating GIS with other data systems to serve end-user needs across departmental boundaries. With this goal in mind, the City is developing applications to integrate Oracle Relational Database Software data tables with its GenaMap GIS Software. One of the first applications projects involved creating a traffic analysis application for traffic engineers and transportation planners. Data tables were designed for traffic accident report, traffic count, and thoroughfare data and linked to the GIS road centerline network. Staff also entered intersection condition diagrams into the GIS. End-users can display, manipulate, and output graphic and ancillary information through a custom Graphical User Interface (GUI) created with GenaMap’s GENIUS II GUI builder.

Introduction
The City of Bloomington is located 50 miles south of Indianapolis in Monroe County, Indiana. A GIS system developed from a joint effort between the City and its Water and Wastewater Utility. From the beginning, the project’s leaders desired a multi-participant initiative. The City invited representatives from county offices and Indiana University’s Bloomington campus to join in discussions for a comprehensive solution. A data conversion project based on aerial photos and paper source maps produced over 60 layers relating to land based features (roads, buildings, topography, hydrology), planning jurisdiction, and water, wastewater, and storm water systems. Currently there are over 100 GIS layers. GIS data resides on a central server and is distributed over a wide area network to over 50 users in 16 departments. Most users assess GIS through a custom GIS Graphical User Interface (GUI).

Evolution of GIS and Information Integration
The City of Bloomington has made a strong commitment to GIS technology. Considerable time and effort was spent in defining goals, evaluating various products, and quality assurance. Figure 1 outlines the progression of the Bloomington GIS project. There are now four full time staff positions located in the Engineering Departments of the City and City Utilities that are dedicated to GIS. In addition, 2 half-time interns and other technical staff provide assistance in maintaining GIS data. A primary focus of the GIS staff since implementation has been maintaining the quality of the base map. Attribute tables concerning water, sanitary sewer, storm sewer, and road network features were designed and created as part of the conversion process. GIS staff developed maintenance routines to update base map vector and ancillary data. The Utilities and Public Works departments played major roles in developing the GIS project and many of the initial applications focused on assets managed by these departments.

Needs Assessment [1989]
Project Specifications & Vendor Search [1990-1991]
Project Organization, Data Structure, & Staff Training [1992]
Data Conversion & Implementation [1992-1993]
Maintenance & Expansion of Map Layers [1994-Present]
Developing Utilities, Public Works, and Planning Applications [1994-Present]
Extending End-User Accessibility [1995-Present]
Needs Assessment Revision [1996]
Data Development & Integration [1996-Present]
Figure 1: Development of the Bloomington GIS Project

The Bloomington GIS recently entered a new phase in its development. As the GIS system evolved and became assessable to more users, there has been a corresponding increase in demand for GIS based applications. Many of these applications require developing ancillary data in conjunction with new or existing vector data. Like many other organizations, Bloomington is looking to information technology for improvements in service delivery without increases in staff. More users want to efficiently share all types of information between departments and with other public entities as well as improve productivity within their own department. Whenever possible the City wished to use or include GIS as a common tool for accessing information having a geographic component.

Database Considerations
Bloomington’s GIS software, GenaMap from Genasys II, Inc. (Ft. Collins, CO) contains an internal system for assigning attributes to map features. Single primary attributes -- feature tags -- or multiple attribute databases utilizing the tag as the primary key may be the hooked to a map. The multiple attribute table’s flat row column structure presents some limitations in functionality and maintenance efficiency. As GIS established itself in the City’s day to day work flow, requirements grew for maintaining and accessing large amounts of ancillary data that can be linked to GIS features. More recent versions of GenaMap include a functional group of commands that connect map features with more powerful external relational database management systems (RDBMS). These RDBMS commands allow users to select map features based on data fields, label or report on features with data attributes, query a feature for a list of data attributes, transfer feature information to a RDBMS, and execute SQL commands from GenaMap.

A RDBMS was part of the City’s plan for upgrading existing data management systems and modernizing record keeping. The City wished to move towards an organization wide approach to managing information. Integrating a powerful GM and RDBMS system was a critical step towards the goal of making information assessable. The City made the same type of strong commitment to data management in respect to development tools, specialized staff and training 211.when it purchased Oracle in 1994. As with the GIS system, Oracle applications are distributed over a network environment to multiple end-users. All databases with a GIS component will be developed using Oracle.

Traffic analysis Applications

Development Issues
Bloomington has a rapidly expanding urban area. Several factors make it an attractive place to live and work. Bloomington is in close proximity to a major metropolitan area and several national and regional recreational sites. It is home to a major university with an enrollment of 30,000+. The University helps support an active cultural community and provides a strong economic presence. The Urbanized Area population increased 13.2% from 1980 to 1990 and population projections for the next 10 years indicate more of the same. (See Figure 2) Growth has stressed the area’s transportation system and made planning issues more complex.

Year: 1990 1995 2000 2005
Population: 80,332 85,994 91,782 96,271
Figure 2: Population Projections for the Bloomington Urbanized Area
(Source: City of Bloomington Planning Department 1996 Bloomington Area Transportation Study)

The City of Bloomington Plan Commission is the contracting authority and the City Planning Department is the designated staff agency of the Metropolitan Planning Organization (MPO) for the Bloomington Metropolitan Planning Area. The Bloomington MPO consists of representatives from local, regional, and state governmental organizations with interests in transportation issues. MPOS are responsible for implementing the six management systems -- pavement, bridge, safety, congestion, public transportation, intermodal transportation, and traffic monitoring -- required under the Intermodal Surface Transportation Efficiency Act of 1991 (ISTEA). A huge amount of information must be collected, stored, and retrieved in order to fulfill ISTEA’s requirements for these management systems. The MPO’S organizational structure necessitates continuous coordination of efforts and information across several entities. Bloomington saw many benefits to using GIS to fulfill some of these transportation data needs:

The Federal Highway Administration presents GIS technology as integral part of ISTEA management systems.
The City GIS already had a road network with attribute data including data from the Indiana Department of Transportation (INDOT).
Other map layers representing road edge of pavement, rail roads, bridges wherein place.
ISTEA indicates that the MPO can be a funding source for GIS.
GIS was a proven method for managing and displaying information associated with the City’s utility networks.

The Bloomington MPO incorporated several GIS functions into its FY 1995 Overall Work Program (OWP). The City’s GIS system would be involved in coordination and implementation 212.of the Roadway Pavement, Highway Safety, Traffic Congestion, and Public Transportation Facilities and Equipment management systems. The Planning Department uses third-party agreements for some elements of its work program due to its limited staff. The City Engineering Department was the responsible party for several elements requiring intensive data collection. A GIS Implementation and Coordination sub-element called for digitizing or -- otherwise incorporating into map layers -- information related to US Census data, Traffic Analyze Zones, Thoroughfare Plan, transit routes, traffic counts, pavement management data, traffic signals, and traffic accidents. Figure 3 contains a summary of end products and management efforts relating to GIS or geographically reference data for the 1996 OWP. All responsibility for GIS sub-elements fell to Engineering’s GIS Staff.

Traffic count map at end of each counting cycle

Quarterly traffic counts
Condition diagrams for critical intersections
Database of accident reports by location, including an ongoing maintenance program
Annual accident report for the Urbanized Area
Data inventories of RR crossing and turning movement counts
Digitized layers for transit routes and stops
Coordination of intersection condition diagrams, RR crossing inventory, turning movement counts, intersection capacity analysis and accident analysis information for the Traffic Congestion Management Plan
Implementation and coordination of a Public Transportation Facilities and Equipment Management System
Figure 3: Summary of FY 1996 Overall Work Program Geographic Data End Products and GIS Coordination Elements

Data Desire Process
City Engineering, Engineering GIS, and Planning established a working group to define and coordinate data efforts for FY 1996. Plans focused on information pertaining to traffic accidents, traffic counts, and intersection capacity analysis. The group looked for existing data sources whenever possible and would rely on field collection when necessary. Previous work included incorporating Census blocks and tracks, Traffic Analysis Zones, and the current Thoroughfare Plan into GIS. Map layers and ancillary data for these components would be available through the City’s GIS GUI. GIS staff would work on moving data to Oracle at a later time. The GIS staff was responsible for working with the City’s Information Services Department to develop Oracle tables and forms for managing the data. A goal was to provide the ability to view and manipulate traffic data through a GIS interface or through an Oracle PC form application. There were multiple benefits to using Oracle for the MPO’s traffic data. Because the City was already developing Oracle applications in house, a consistent interface could be provided for users to add, update, query and produce reports on information. Likewise, this uniformity could be extended to those accessing information through a GIS GUI. Users could find and sort records based on any available field or combination of fields. It is possible to move between related data forms when additional information is needed even if this information was developed for a different application. Oracle decreases the need to collect and maintain redundant information. Master tables containing address and road network information from GIS already existed in Oracle. The City developed standards concerning this information to increase data integrity. Oracle traffic tables validate street names based on the master road database and when possible, list-of-value pick lists were used to force consistency. As an end result, search and report capabilities are greatly improved.

All traffic and transportation data would be gee-referenced to the GIS road centerline (RCL) network and in some cases, other map layers created to display specific traffic features. The Oracle traffic databases contain fields for street segment (RCL line feature tag) or intersections (RCL point feature tag) to tie it to a road map feature. Using the current RCL network had preference over creating additional map layers for maintenance reasons. Including the RCL identifier allowed the possibility to connect to other road related Oracle information concerning a specific street or intersection.

Traffic Counts
The Engineering Department had an established traffic count program using HI-STAR, a DOS based software from Nu-Metrics. HI-STAR analyzes and stores volume and speed count data collected by electronic traffic counters. Data was not easily accessible and the system was susceptible to data loss because it was locally installed on a PC in City Engineering. The Oracle traffic count table uses RCL feature tags to link count records to the master road database and the GIS road network. An additional GIS layer was not needed. The Planning Department identified additional volume count locations to supplement the regional counts required by the State. Fields describing count type and source were added to further characterize counts. A larger variety of data such as manual turning movement counts and counts collected by other organizations can now be tracked using the same system.

Accident Locations
City Engineering obtains copies of traffic accident reports taken by the Bloomington Police Department. The Police enter only a small percentage of information available in written accident reports into their computer record keeping system. Transportation planners and traffic engineers wished to also track information regarding characteristics of vehicle use, driver actions, roadway, weather, and traffic control devises. Data was imported into Oracle from the Police records system. Some clean up was needed because of inconsistencies in way that the Police records staff enter street information. GIS RCL feature tags link accident records to the road network. Point features with unique tags where created in an accident map layer and the tag was added to the database. These would be visual markers identifying street segments, intersections, or parking areas with accident records. The feature’s symbology changes in size and color as more accidents occur at the location.

Condition Diamams of Simalized Intersections
Condition Diagrams identify geometric and other physical characteristics of critical intersections including road and lane widths, pavement markings, traffic and pedestrian controls, and physical obstructions. Engineering began with the 63 signalized intersections maintained by the City. Engineering interns were hired to collect information and produce scaled drawings. A GenaMap script was written to create field map sheets of the intersections with existing GIS road edge of pavement, driveways and storm water features. This data was to be verified and updates drawn along with 16 other types of features. Engineering drawings from a 1984 INDOT project that included detail sheets for intersection improvements, signal installations and pavement markings where found for about half of the intersections. For these intersections only corrections and missing elements needed to be identified on field drawings. Features from drawings were digitized into GIS for the final product. Any feature with field collected tabular data was uniquely tagged. Connection to the road network and master database will be established through the RCL point feature tag.

GUI
The City uses Genasys’s GUI builder Genius H (Genasys Interactive Users System) to provide end users GenaMap functionality and custom applications in an easy to use menu driven interface. Genius II is OSF/Motif compliant. Developers can build in command options controlling such things as display characteristics, scaling, file management, and topology that are transparent to the user. The need for end-users to understand the software is reduced and there is consistency between users. A common GUI referred to as the City Genius has been developed for use by all departments to view, query, analyze, and output map information. The City Genius is comprised of a shell, menu bar with pull down buttons, graphics window, and status bar. The push button menus access a variety of dialog and text widgets. Traffic menu buttons were added to this interface for wide spread accessibility. Basic GUI functions for traffic features included, displaying features, locating features based on data record characteristics, querying feature for data contents, and including features in laser print maps.

Specific Genius GUI’s were also built for adding and editing traffic accident, count, and intersection information. GUI resources are easily manipulated because Genius II utilizes ASCII and C file structures for interface definition and storage. A library of reusable and editable widgets can be built for commonly used tasks. As a result, a special Genius can maintain common functionality and appearance to the main City Genius. Developers may concentrate on widgets and scripts for performing special tasks. Traffic and Intersection Genius GUIS were created for digitizing condition diagrams and updating Oracle records for accidents and traffic counts. Most of this work would be done by interns or others with various GenaMap expertise. By using a GUI, file management controls, symbology and tagging sequences could all be pre-defined.

Procedures for creating and editing information that would consist of multiple steps at the command line could be built into a single routine. The GUI increases quality by simplifying training and reducing the places errors could occur.

Oracle Forms
Oracle information can be viewed and manipulated in the GIS GUIS through a series of pop up forms. A small link table with fields referring to gee-spatial identifiers and table names manages the interaction between the GUI and Oracle. An Oracle form runtime command imbedded in the calling script allows the user to log into Oracle and pops up the needed form. GIS feature information -- the tag in most cases -- can be found through inputting identifying data through the keyboard or with a mouse click on the map feature. The tag is set as a variable and passed to the link table along with the traffic table name through GenaMap dbexecute update statement. The form is programmed to search the desired Oracle table for a records containing the features tag and those records are made available to the form. The same process is used to create new records in Oracle. The calling script sets and/or retrieves the necessary feature tags with the mouse. Other GIS information such as the street name from the master road Oracle table is also obtained through query or dbquery commands. This data is passed to the specific Oracle table as variables with dbexecute insert into table statement. The Oracle link table is then used to obtain the new record in the pop up form. The user can update additional data through the form and save the record.

Future Plans
Using GIS and Oracle to manage and display data has provided a homogeneous environment for multiple applications from which to expand. These technologies give information managers and developers the ability to put powerful tools in the hands of those most needing to use the information. Future plans call for developing GUI routines that expand user output ability including output based on ad-hoc geographic selections. GIS staff will also be working to integrate the Pavement Management System being furnished by a consultant. The FY 1997 OWP added an additional element for circulation and parking plans for the downtown area. The City hopes to update and expand on a 1994 GIS downtown parking inventory with information resulting from this study.

APPENDIX 1: Software& Hardware Specifications

The City of Bloomington utilizes a suite of UNIX based GIS products from Genasys II, Inc. including GenaMap version 6.2, Application Developers Toolkit (ADT), and GenaCell. Software is loaded locally on 11 Hewlett Packard 700 Series workstations which serve network UNIX X-terminals and PC users running XVision emulation software from VisionWare Limited. GIS data and GUI resources reside on a single Hewlett Packard 700 series workstation and is distributed over a wide area fiber optic network to 4 buildings.

The City’s RDBMS software is Oracle Corporation’s 0racle7 version 7.2 for UNIX. The database engine resides on a Hewlett Packard 9000/800 series server. Front end applications and data forms are developed using Oracle’s Developer2000 tools including Forms 4.5 and Oracle Reports. Data resides on a Hewlett Packard 700 series workstation and is distributed across the same fiber optic network as GIS. Oracle forms and SQL*PIUS can be accessed directly in UNIX. Oracle applications are available over the network to PCs or to UNIX boxes running NTrigue emulation software from Insignia Solutions.