This paper describes the challenges faced by the City of Los Angeles Department of Transportation (LADOT) to replace the paper-based process of issuing engineering work order for installing and maintaining traffic control devices with a Web-based GIS application.

In the LADOT legacy database, all streets are assigned a unique five-digit ID, and street blocks for each street are assigned a five-digit ID called "block sequence," starting at the most northerly or easterly end of each street. The location of each traffic control device is associated with a street block and is measured from the block's northerly or easterly intersection in the southerly or westerly direction. To develop a Web-based GIS work order system with ArcIMS, we were faced with the following challenges:

1. Converting the Department's existing mainframe data sets of the traffic control devices to GIS and avoid the expense of re-inventory of the devices
2. Developing a Relational Database Management System (RDBMS) to meet the requirements of the LADOT business processes and store the spatial information for traffic control devices
3. Taking advantage of the ArcIMS in a Web environment for rendering and creating (adding) new features (spatial locations) with acceptable response time

The unique nature of the mainframe data sets provided us with the opportunity to assign a unique 10-digit ID called Segment ID to each street block and incorporate this ID into a new ArcInfo route system. This route system was then used to create spatial references for traffic control devices in the legacy database. The ArcIMS HTML Viewer was selected as the client viewer with ColdFusion pages to process database requests to and from the HTML Viewer and the RDBMS. The paper reports on the structure of the database, methodology and development of the route system, customization of the ArcIMS HTML Viewer, and development of the ColdFusion pages.

LADOT is responsible for the installation and maintenance of traffic signals, signs, parking meters, and other traffic control devices. Engineering work orders document all changes to the existing traffic control devices including new installation or modifications to control devices.

The engineering staff manually prepare work orders including writing in the location and type of the control device and the task required to install or maintain a traffic control device (i.e. signs, pavement marking, etc). After approval of a work order by a senior engineer, the work order is sent to the installation crew for completion of the required installation or maintenance work. A copy of the completed work order is then sent to LADOT’s Systems group for data input to a mainframe system.

The TRECS was designed and implemented in 1970’s. TRECS is an information system that consists of three major subsystems; a sign subsystem that provides information for all signs within the City including the maintenance history; a traffic signal subsystem that is an inventory of all traffic signals; and a roadway characteristics subsystem containing information about the physical characteristics of the City’s streets, such as striping and pavement characteristics. The system architecture was designed at the intersection and street block level to collect and maintain traffic control device data. All streets were assigned a five-digit code and the streets were divided into about 74000 street blocks with an assigned "block sequence" number starting in a southerly or westerly direction. The intersection street names and the intersection longitudes and latitude spatial information and the street block information were added to the TRECS subsystems. Table 1 and Figure 1 show the relationship between the intersection street name and the street block information for a typical network of streets. The locations of the traffic control devices (i.e. signs, etc) were referenced by (feet) distance on a street block from the most northerly or easterly intersection for that street block (see Table 1). The TCD distance information along with other relevant information such as side of the street, direction facing the traffic and LADOT’s traffic control inventory stock number were also included in TRECS. The only spatial information provided for the installed location of the traffic control devices is the longitude and latitude information for the intersections (Figure 1).

In order to convert the traffic control devices datasets to GIS, a Linear Referencing System (LRS) of the Los Angeles street network was developed using the Environmental Systems and Research Institute’s (Esri) ArcInfo Route System. To leverage the TRECS’existing street block-based datasets, a single route was created between every pair of connected intersections. The next challenge was to develop a common field between the TRECS datasets and the new LRS. Combining the TRECS street code numbers and the street block sequence numbers provided the opportunity to assign a 10-digit unique ID, called Segment ID, to every street block in the City. The street block unique ID was then incorporated into the new LRS to establish a common field between the two systems. The traffic control datasets were exported from TRECS to a Relational Database Management System (RDBMS), and were attributed to the new LRS using ArcInfo EVENTSOURCE to generate spatial references for the traffic control devices (Figure 2).

The objectives of the project were as follows:

1. Create a Web-based GIS application to streamline the engineering work order process with minimal change to the business specific processes
2. Developing a Relational Database Management System (RDBMS) to meet the requirements of the LADOT business processes and store the spatial information for traffic control devices
3. Implement a non-software specific GIS solution for storing spatial reference information
4. Implement a web-based interface with user-level security to issue, approve, distribute, install and complete engineering work order

For a non-software specific GIS solution, the database had to be designed to store spatial reference information (SR) of the TCD. Rather than storing the SR for every TCD, the SR information would be stored for actual location of the installed device in a separate table called Spatial Location table. The Spatial Location tables would store the longitude and latitude information about the location of the installed traffic control device. The spatial locations then would be related to the inventory tables of the traffic control devices with the Spatial Location ID. The inventory tables of the point type (i.e. stop sign) and line type (i.e. red curb zone) TCDs were saved in different tables. The inventory tables of the TCDs were kept independent of the work order transaction tables for issuing and completing a work order. A cross-reference table was then created to establish relationship between the TCDs inventory tables and the work order transaction tables with the Inventory ID (Figure 3).

The Esri’s ArcIMS and Allaire’s ColdFusion were selected to design the interface for the GWOTS. The GWOTS interface uses ArcIMS™ as the base GIS technology and Cold Fusion to handle the LADOT’s business process. The Cold Fusion would process database requests to and from the HTML Viewer. The Cold Fusion would also process HTML Viewer’s requests for rendering existing features and storing spatial reference information for the new features. LADOT will then deploy the GWOTS interface as an Intranet application to issue and distribute engineering work orders.

The GWOTS Interface functionality:

• Allow the user to browse, zoom and pan the map.
• Allow the user to use the map to add a new point or line feature.
• Provide user-security level to issue, approve and complete engineering work orders.
• Retrieve street block information for input into an engineering work order based on the user's selection of the desired street block from the map.
• Calculate the longitude and latitude (X, Y coordinates) of the added feature(s) for storing in the RDBMS.
• Populate a departmental engineering work order form with appropriate street block data elements and appropriate data elements for the existing traffic control devices
• Populate a departmental engineering work order form with the measurements (distance from cross street and from end of block street for point events, and "from" and "to" distances from cross street and from end of block street for line events) for the new features.
• Enable use of an "Area of Interest" function to navigate to a portion of city to view work orders, including navigation using street, and street intersection
• Implement predefined scale dependent display for data layers
• Create shapefile of the point or line feature representing the new device/sign/curb zone and add to the traffic control device theme.

For years the Geographic Information Systems Industry has attempted to standardize the GIS format to promote the exchange of data between public and private agencies. This application makes an attempt to store the spatial information in a non-GIS environment to allow easy migration to any GIS software and the future GIS systems. The process also revealed that application developers must not abandon the existing systems without understanding them. The existing system saved LADOT millions of dollars in re-inventory of traffic control devices cost. Utilizing Esri’s products, we are able to create GIS layers of the traffic control devices and develop a web-based interface and enable LADOT Traffic Engineers issue and distribute work orders with minimal data entry and less error.

 ACKNOWLEDGMENTS

Deborah Wittwer and Mony Patel, LADOT

Kevin Gates and Mingi Chu, Esri

 Figure 1

 Figure 2

Migration of Mainframe Data Sets to GIS-

LADOT’s Linear Reference System (LRS) Relationship to Traffic Control Devices Inventory Tables

 LRS Data Elements

 Figure 3

LADOT’s Engineering Work Order System Database Design

LADOT GWOTS- ArcIMS HTML Viewer

  Figure 5

LADOT GWOTS- Creating a New Spatial Location to Install a Traffic Control Device

 Figure 6

LADOT GWOTS- Creating a Work Order for a New Traffic Control Device