Houston Metropolitan Transit Authority (METRO) Project:

Creation of a Fixed Route Scheduling Database by the Precision Merging of Schedule, Bus Stop and Base Map Data Sets to Support Scheduling Operations

The project objective was to successfully merge varied databases to support the Computerized Telephone Information System (CTIS) and other METRO Enterprise GIS applications. The design and development of a Microsoft^® Visual Basic^® program to convert schedule data from a commercially available software package into Oracle^® will be explained. Special focus will be provided on the integration of schedule, bus stop and street base map databases that are maintained by individual METRO departments and other governmental entities. Discussion will include the resulting procedures, route model, turn table modifications and AML^� tools developed to create routes and sequence bus stops and turn points.

Introduction

The Customer Information Center at METRO employs 20 agents during peak times and receives an average of 4,100 fixed route service inquiries every weekday. The majority of these inquiries involve trip planning. Currently, the agents generate trip itineraries by using key maps to locate the origin and destination of the trip and to identify the routes serving these points. The scheduled trip timetables are then used to identify the trip times and transfer points.

To better respond to public requests for bus route and scheduling information, METRO decided to implement a Computerized Telephone Information System (CTIS). The core of the CTIS is an itinerary planning software system with a UNIX^® server and a client-based graphical user interface. CTIS supports customer service personnel queries, an Interactive Voice Response (IVR) system for direct telephone access by the public and Web page access via an Internet browser.

Passenger Routing and Information System (PARIS^®) software, by Megadyne Information Systems, was selected by METRO to best fit the existing software and database systems. A goal of the project was to develop procedures and programs for merging and integrating data from the existing databases and make recommendations for altering the databases to better meet the system needs. The existing databases consisted of a commercial scheduling database (Trapeze^® FX) by Trapeze, a bus stop inventory maintained in ArcView^� and a street centerline database from the Houston-Galveston Area Council � Geographic Data-sharing Committee consisting of public and private representatives.

ArcInfo^� and ArcView were the geographic information systems (GIS) software of choice for storing and maintaining the geographic data. Tools were developed in Arc Macro Language (AML), Avenue^� and Microsoft Visual Basic programming languages. The result was the creation of a Microsoft Access database, which produced ASCII files that were loaded into the PARIS development database using the SQL*Loader^® utility for Oracle. The development database was loaded and quality checked and then moved to the production database for service change implementation. Service changes take place three times a year at METRO, and the resulting schedule and route changes must be captured for the CTIS.

Project Resources

Idea Integration^SM provided the principle resources for the database development project. The team included a project manager, a GIS analyst/programmer (dedicated to programming about one-fourth to one-half of the time), two to five GIS analysts who built and sequenced routes, and a technical writer responsible for documenting the process from start to finish. METRO�s route system includes 134 bus routes with over 760 patterns. System design and programming along with building and sequencing the initial routes was a four-month process requiring close collaboration with a variety of METRO departments. Implementing a service change took approximately four to five weeks with a staff of three to five personnel.

Information Required by PARIS

Fourteen data tables are required to populate the PARIS database. Route building and sequence processing result in the creation of the four tables listed in Table 1. The Base Map Processing section further describes the general methods used for building routes and sequencing routes to produce these tables.

Table 1. Tables Created by Building and Sequencing the Routes in ArcInfo

Processing the METRO Trapeze FX reports creates the remaining tables, listed in Table 2. The Trapeze Raw Report Files and Processing section explains how these ten tables were created for PARIS.

Table 2. Tables Created from Processing Raw Trapeze Reports

The 14 tables were temporarily stored and processed in Microsoft Access. Processing in Microsoft Access was limited to queries for performing quality checks, joining tables, formatting fields, updating values, altering values for modified service (i.e., day after Thanksgiving) and dropping routes for employee shuttles.

Additional Access tables (see Table 3) contain essential information that is not provided by the Trapeze reports. The CTIS/PARIS database manager or the Scheduling Department maintains these tables manually.

Table 3. Tables Created and Maintained in Microsoft Access

Trapeze Raw Report Files and Processing

The Trapeze FX scheduling software is a microcomputer-based, multi-user scheduling, run cutting and routing system. It helps the user define routes, generate timetables, assign vehicles, create driver runs and determine off-days assignments. The Trapeze FX version licensed by METRO is a DOS based system and represents the older generation of Trapeze products. It does not have direct access to the database via Open Database Connection (ODBC). Hence, the required schedule data generated from Trapeze FX was in ASCII report format and not in data table format. The reports were converted into relational database format from their original formats, which included variable-length records, header with columns and rows and fixed-record-length formats.

The ASCII reports required for the project were the All Nodes, Patterns, Trips and Schedule reports. Samples of these reports are provided in the appendix. When creating the reports from Trapeze, specific columns (fields) had to be included in the ASCII reports.

The All Nodes Report is composed of fixed records and includes all the time points for the METRO network. Time points represent selected bus stops along the route. The All Nodes Report has the time point or node names that are in the short abbreviated (four-character-wide field named ABBR) and an intermediate length (two, four-character-wide fields named NAME4A and NAME4B) format. The time point names serve as primary keys in a cross-reference table that is used to join the Patterns Report and Trips Reports to the Schedule Report.

The Patterns Report is in header with columns and rows format and includes all the various patterns or paths that the buses travel for a particular route and time points. The Patterns Report includes pattern number, time point, destination sign codes and direction (inbound/outbound) fields. The data beneath each time point abbreviation consists of 1s or 0s. The 1 indicates the node is used in the pattern and the 0 indicates the node is not used in the pattern.

The Trips Report is in header with column and rows format and contains the pattern number, time points, trip times, direction, service type (week day, Saturday or Sunday service) and block number (the bus used for that part of the route).

The Schedule Report is in variable-record-length format and contains the pattern, time points, service, direction and trip time.

TrapezeParse Application

A Microsoft Visual Basic application was developed to convert the raw Trapeze ASCII reports to data tables. TrapezeParse subdivides the processing of the Trapeze FX reports into pre-processing and post-processing. Figure 1 shows the TrapezeParse Application Interface. The challenge of pre-processing lies within the ability to tokenize the parts of the various reports. VideoSoft^® AWK ActiveX^® control was used to tokenize reports that were in header with column and rows format into components that were then read by row and column. The pre-processing directly converts the Schedule, Trips and Patterns reports into separate tables that are stored in a Microsoft Access database. Post-processing uses the Allnodes file that was directly converted into a dBase IV table, and joins the Schedule, Trip and Pattern tables together into a Microsoft Access table called tblDone. Post-processing also creates values for the fields that are used in the PARIS engine. The tblDone table includes every occurrence of a time point/node being used by any pattern of a bus route for the entire METRO system. A series of Microsoft Access queries were developed to filter the tblDone table to create 10 of the 14 tables required by PARIS.

Route modeling differences between PARIS and Trapeze FX required additional selection and update queries to prepare the data for PARIS. One case of a modeling difference was found for circular routes. METRO�s circular routes are designated with different route numbers for each direction, clockwise and counterclockwise. In the Trapeze FX modeling process, METRO�s circular routes (26/27 and 426/427) are designated with one route number for both the clockwise and counterclockwise direction. The designation numbers for these two routes are edited manually on the public time tables and other publications. Update queries were created to select the clockwise or outbound patterns and assign the correct route number.

As described in the following sections, every time a METRO service change occurs, the Trapeze Reports must be processed twice. The first processing is used only as source data to generate a pick list for the ArcInfo Sequencing Tool. The second processing is for the finalized tables for PARIS loading.

Figure 1. TrapezeParse Application Interface

Preliminary Processing

The first time the raw Trapeze FX reports are processed to create the tblDone is to produce a preliminary version of the pattern_dtl2SEQ table that is needed for sequencing. An ASCII file is created from a Microsoft Access query against the tblDone table to produce the pattern_dtl2SEQ table, which is exported to a dBase IV format. The pattern_dtl2SEQ table is then converted to an INFO table called pattern.dat. A frequency is run on the pattern.dat file to create the pick lists from which users select the patterns for sequencing.

Knowing when to obtain and process the updated Trapeze reports for producing the pattern_dtl2SEQ table required close coordination with the Scheduling Department. The new Trapeze reports for the service change had to be moderately stable before they were processed. Routes that were subject to changes by the Scheduling Department were reported to the CTIS/PARIS database manager so they could be created and sequenced last.

Final Processing

The second time that the Trapeze reports are processed by the TrapezeParse Application is to produce the tables required for PARIS loading.

HGAC-GDC Street Base Map

The street base map is derived from raw TIGER data and adjusted to local electrical utility parcel map, digital orthophotography and satellite imagery by the Houston-Galveston Area Council � Geographic Data-sharing Committee (HGAC-GDC) members. Street names and ranges were modified by the HGAC to match local utility parcel map ranges. The street base map continually is being upgraded by the GDC, and each arc segment has been assigned a unique identification number, called HGAC_recnm. METRO has used the July 1999 version of the GDC base map for the first three METRO service changes and has renamed it the CTIS base map. All geographic and attribute modifications to the CTIS base map by METRO have been flagged and provided to the GDC using the HGAC_recnm starting at 500,000 as a key. All new geographic changes are assigned the next sequential value by METRO. Since modifications to the July 1999 version of the GDC base map by other GDC members have not been incorporated into the CTIS, strict HGAC_recnm values are maintained by the GDC. In the future the GDC and CTIS base maps will be balanced and METRO routes repaired.

METRO Bus Stop Inventory Database

Last year METRO successfully completed a comprehensive bus stop inventory project. The addresses (latitude/longitude) of more than 10,500 bus stops were collected using GPS technology. The bus stop inventory database was developed to maintain the bus stop location, routes servicing the bus stop and the amenities at the bus stop such as shelter, bench and trash receptacle information. The bus stop address data is also vital for other forthcoming ITS projects such as the automatic vehicle location (AVL) system. The Scheduling Department maintains the bus stop inventory using work orders that are provided by the Maintenance Department. The bus stop inventory is maintained in the ArcView point shape file format and is converted into the ArcInfo coverage format. The resulting point coverage is used in the route building and sequencing processes.

Base Map Processing

CTIS base map processing is divided into four steps:

1. Coverage version processing
   
2. Route building processing
   
3. Sequencing processing
   
4. Sequencing tables and route loading processing
   

Step 1: Coverage Version Processing

If a new version of the TIMEPT (time point), BUSSTOP (bus stop) or CTIS (base map) coverage is released, the updated coverage must be processed before the tables can be prepared for PARIS loading.

Processing a new version of TIMEPT � If Planning and Development modifies the TIMEPT coverage attributes, it is necessary to update the STRT (street) field in TPLOC.DAT for all the time points. The new TPLOC.DAT is then ready for transfer to the PARIS server.

If a time point is added to an existing bus route and the path traveled by the bus is altered for any pattern, each affected pattern must be rebuilt. In addition, all patterns affected by the new time point must be resequenced.

Processing a new version of BUSSTOP � When a bus stop is added, deleted or replaced in a route that was sequenced already, the sequencing tables must be corrected. If a new bus stop is added after an affected route was sequenced, the bus stop must be added to the BUS.DAT and SOP.DAT files manually. These changes are made by assigning the appropriate sequence number to the bus stop based on the location of the stop in relation to the nearest intersection.

Processing a new version of CTIS Base Map � When the Cartography Department releases a new version of the base map, the coverage (CTIS) has to be processed. CTIS processing is divided into four tasks:

1. Update to the new version of CTIS. This involves making sure the HGAC_RECNM field in the CTIS is unique if a graphical change was made to the base map. If an attribute change was made to the base map, the existing version of CTIS needs to be updated with the appropriate values from the new CTIS. Converting the nodes in the CTIS into a point coverage creates a TURN point coverage. The BUSSTOP point must be appended into the TURN point.

2. Update the Turntable. Every time there is a new version of CTIS, a new Turntable must be created and the attributes of the table must be updated. Every turn ID (TRID) in the Turntable must be unique. New values must be assigned to TRIDs equal to 0.

3. Update existing routes to the new version of CTIS. All the bus routes must be updated to correct any errors that the new map causes. The errors result from the editing of the base map graphically�arcs that are added or split can cause route errors. This task is required only if a graphical change was made to the base map.

4. Correct sequencing of updated routes. If any turns were affected by the new version of CTIS, the sequencing must be corrected.

Step 2: Route Building Processing

When building an ArcInfo route system of the bus routes, the team obtained printed copies of the latest map and driving instructions for each route, as well as the Patterns Reports and the Trips Reports. The reports were used to determine which patterns were still valid and needed to be built. A Route Editing Tool (see Figure 2) was developed to help users create the routes.

Building a route was a multi-step process that required completion of the following tasks:

1. Create an ArcInfo workspace to contain the bus route to be built.

2. Using the Route Editing Tool, build the parent route. The parent route provides a background coverage that serves as a guide for creating the individual patterns that compose the bus route. The bus stop and time point coverages also served as a general guide to help determine which arc segments to select for building the route.

3. Build the patterns for the bus route. Since the parent route has been built in the previous step, building the individual pattern route is a quick and easy step to perform. Basically, the user selects the parent route and unselects paths that do not go through the pattern. The user repeats this step to create an ArcInfo route for every pattern in the bus route.

4. Check the route for errors and correct them. One type of error is a gap within a route, represented by three or more triangles for each route segment. Each pattern segment should have only two triangles�one at the beginning and one at the end. If a segment has more than two triangles, this indicates the segment is composed of more than one route. A gap occurs if there is a new arc added within a route. Another type of error is an overlap, represented by a rectangle with lines extended from the corners. An overlap occurs if the user appends an arc on top of an existing arc.

Figure 2. Route Editing Tool

The Arcs section (toward the bottom of the tool) allows the user to select arcs or unselect arcs. The AE Identify button allows the user to identify the street name associated with an arc before adding it to the route path. Selecting a route path before making a route is a function built into the Route section of the tool. Other buttons allow the user to unselect the path or add a new route path to the selected set of the route path. After selecting a route path, the user is ready to make the route by clicking the Make Route button. Before creating a route for each pattern, the team created a parent route containing route paths for all possible patterns within the bus route. The user selected the arcs in the path and clicked the Make PARENT route button in the Parent route section. To edit an existing parent route, the Calc Old PARENT route button is used to select arcs along the parent route so they can be added to or removed. The Select PARENT arc button provides a shortcut for building the individual patterns. Clicking the button selects all the paths within the parent route. When building the individual patterns of the bus route, the user simply unselects the paths that do not go through that pattern. To temporarily store the current selection set of the path, the Calc Pattern Arc button is used. And to retrieve the path that was previously stored, the user clicks the Sel current Pattern Arcs button. The bottom section allows users to undo mistakes, save their work, access the ArcInfo command line and exit ArcEdit.

Step 3: Sequencing Processing

The time points for each route were pre-assigned sequence numbers in both directions. The first time point for a route is always numbered 200, and subsequent time points are incremented by 100. Sequence numbers must be assigned manually to every bus stop and vehicle turn point between each time point. Typically, bus stop and turn points are sequenced in increments of 5 unless there are more than 19 bus stops and turns before the next time point. This strategy allows room for adding additional stops or turns if needed later or a mistake is made. Figure 3 illustrates how a pattern is sequenced.

Before sequencing a route, the team obtained the latest map and driving instructions for the route and printed out the current Patterns Report and Trips Report. The reports were used to determine which patterns were still valid and needed to be sequenced. Also, the team used the Patterns Report to establish which time points were used in each pattern. A 1 in a time point column indicates that the time point applies to the particular pattern. A 0 indicates that the time point is not used for the pattern.

The sequencing process requires the following coverages: pattern route, bus stop, time point and turn point. The result of the sequencing process is a series of newly created tables (i.e., BUS.DAT, TURN.DAT, SOP.DAT and TPLOC.DAT). A Sequencing Tool (see Figure 4) was developed to make the task of sequencing easier.

In general, the procedure to sequence a route involves these steps:

1. Select the pattern from a list.

2. Using the Sequencing Tool, register the first time point.

3. Sequence the bus stops and turns until the next time point is reached.

4. Register the next time point. This process of registering the time point and sequencing the bus stops and turns continues until the final time point of the pattern is reached.

5. Perform a quality check on the sequencing tables and correct any errors. Two of the most common errors that occur are:

   • Sequencing a point (bus stop or turn) twice. If this happens, a duplicate record in the sequencing tables is created.

   • Applying the same sequence number to a bus stop and a nearby turn. This error creates a duplicate bus stop ID (STID) and turn ID (TRID) record.

If any of these errors occurs, the tables do not successfully load into PARIS. AML programs were created to detect errors in the sequencing tables and were run once the sequencing process was complete.

Figure 3. Sequencing Example

Figure 4. Sequencing Tool

The top part of the tool identifies the route pattern currently being sequenced. It also allows the user to choose another pattern to sequence when finished with the current pattern. The TIMEPT information section allows the user to select a time point and display its sequence number. The CTIS data section allows the user to identify the selected street name. A scroll bar in the POINT data info and SEQ section allows the user to choose a sequence increment between 1 and 5 (the default is 5). When the user selects a point, the tool increments the last sequence number with the number chosen. If the point selected is a bus stop, the user simply clicks the Apply SEQ button to add the data into the sequencing tables. However, if the selected point is a turn, a popup list of turns available at the point appears. The user selects the appropriate turn from the list and then clicks the Apply SEQ button to populate the table. If a point is selected that has already been sequenced, the tool indicates that the entry exists, lists the sequence number and asks whether to delete the existing entry. If the user chooses to delete the sequence number, the tool deletes the record in the appropriate sequencing tables. The rest of the tool regulates the ArcEdit display, allows users to save their work and provides access to the command line.