Integrating GIS and Oracle for traffic analysis
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.
