Automating Road System Maps
Continued...

Translating Road Sequences

This required translating approximately 21,000 road sequence records that were stored in the DB2 database to geometries in the ArcSDE Oracle database maintained by ISD. The original road segment information is stored as sequence records that contain a sequence limit description for each road segment. The description is a freely formatted text description of a section of road that logically defines the start and end points. For example, the sequence limit description "0007M W,JACKSON E/0003M W,RANCHO AV" means the sequence starts 7/100 of a mile west of Jackson and goes east until it reaches 3/100 of a mile west of Rancho Avenue.

To complicate matters further, not all sequence limit descriptions follow a standard format. For example, the sequence limit description "SH62 N.04M" means that the sequence starts at State Highway 62 and continues north for 4/100 of a mile. The description "END N/ARBON LN" means the sequence goes from the end of the sequence to Arbon Lane.

Rick Pourroy, the ISD senior GIS programmer analyst, wrote ArcObjects programs to directly read the DB2 sequence limit descriptions and translate them into the geometry required to create a new ArcSDE Oracle Traffic Maintained Road feature class using the county street centerline feature class as a base. Pourroy architected a database structure, created programs, and defined workflow to provide for the integration of this new feature class into a system that allows mapping, continued maintenance, and synchronization from the DB2 data. This conversion process is called the Limit Description Conversion Workflow. Once the limit sequence records were parsed and geocoded, each feature in the Traffic Maintained feature class was inspected for quality.

The Traffic Road Book Accepted Data Reporting Web page is refreshed each time the page is opened. Each record is a description of the road sequence. The hyperlink at the end initiates an ArcGIS Server application that allows the user to inspect the road sequence for quality control.

Andr� Larabie, ISD programmer analyst, created the online Traffic Road Book Accepted Data Reporting tool, which references a SQL table created during the translation process. The report lists all the road feature records in the Traffic Maintained Road feature class, where road features and the geocoded limit descriptions are matched. The report is re-created each time the Web page is opened. "Our intention is to provide the user with a complete online review process," said Larabie. "They can review the whole conversion process without having to refer to any paper documents."

Each record in the report includes a hyperlink that launches an ArcGIS Server Web mapping application that allows the user to inspect the road feature. "We felt that the reports would be much more complete by having a GIS component that would display a map of the area being investigated," said Cohen. "Behind the scenes, a very complex data conversion process is taking place, which includes integration of a custom ArcMap application with the Microsoft SQL Reporting Server to help produce the report. However, all of this is hidden from the user, who simply clicks a link on the Web page report and is provided with an online map of the area of interest."

Adding a GIS element to the quality control portion of the workflow eliminated the need to manually research the county road segment information contained in the conversion reports. The hyperlink is embedded with special keys that identify specific road segments. Clicking on the link invokes an online map of the selected road feature without the user having to load or run special software. Anyone in the organization can view the maps using a Web browser without having to know anything about GIS. The map includes noncounty roads and other thematic features such as jurisdictional boundaries and imagery.

The ArcGIS Server Web application is used for quality control and allows the user to view the road sequence in question in context with other layers including a high-resolution image.

The Web application that contains the online map is a slightly modified version of the Search Template included with ArcGIS Server. Users can enter new search parameters, such as the road number, to generate a new map. They can also navigate the map by zooming in or out or panning.

If a mistake is found during a quality control check, a user can access a custom editing environment, also developed by Pourroy, for editing of the Traffic Maintained Road feature class. "We've created an interactive maintenance shell running out of ArcMap to handle the workflow and process sequences that we could not match in bulk," said Pourroy. "This maintenance shell also illustrates how the textual sequence description is broken up into the components that we can programmatically apply to the street network feature class and match."

Building an Integrated System

Babico recognizes that creating the Traffic Maintained Road feature class is a huge step in the right direction. "Of course, the Road Book data conversion and quality control effort is only Phase One of the project," said Babico. "It was absolutely necessary that we established a foundation upon which to build the integrated system we've envisioned. Next, we'll link the Road Book data with the Traffic Collision database, which will significantly decrease the time it takes to do an accident investigation and complete the TEI reports."

The maintenance shell enhances both the data kept in the IBM DB2 database and the street network because it sends any changes reported back to the database.

Cohen sees the Traffic Road Book modernization project as an opportunity to put ArcGIS Server to work and take the first steps toward implementing similar applications in the future. "With this project, we are trying to improve our ability to provide a rapid application development methodology to more efficiently create GIS applications and leverage the extensive GIS object model that comes with ArcGIS Server," said Cohen. "ArcGIS Server has a learning curve associated with it, and our staff has taken some training and plans to acquire additional training to help us fully utilize the ArcGIS Server capabilities. This important mapping functionality was added in about six hours and fully supports the reporting tool. We estimate that it would have taken three or four days to develop an application on our own that provided the same functionality as the ArcGIS Server template."

The applications created by Cohen's team also helped to automate the TSMS by making it easier to update the Traffic Road Book and publish new, full-color versions of the map pages. County staff can access the Traffic Maintained Road feature class via Web applications or ArcGIS software.

Acknowledgments

This article was made possible by contributions from Mike Cohen, Rick Pourroy, and Andr� Larabie from the San Bernardino County Application Development Division GIS Team and Jacob Babico, Mary Anne Causey, and Trieu Tran from the San Bernardino County Department of Public Works.