Houston Metropolitan Transit Authority (METRO) Project:
|
Table Name |
Description |
pattern_dtl_dat.txt |
Contains all time point, bus stop and turn identification numbers with an ascending sequence value for each pattern of each route |
trn_location_dat.txt |
Contains the turn identification numbers with a location description |
busstop_dat.txt |
Contains the bus stop identification numbers with a location description |
tpt_location_dat.txt |
Contains the time point identification numbers with a location coordinate value |
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
Table Name |
Description |
schedline_dat.txt |
Contains the direction and number of time points within each pattern of a route |
schedtrip_dat.txt |
Contains the trip number, direction, type of service (week day, Saturday and Sunday) and direction for each pattern of a route |
patncorr_dat.txt |
Contains values for cross-referencing tables |
route_desc_dat.txt |
Contains the route description, service types and wheelchair accessibility |
pattern_hdr_dat.txt |
Contains all pattern numbers and directions for each pattern of a route |
timepoint_dat.txt |
Contains the order and time point identification values for each pattern of a route |
triptimes_dat.txt |
Contains the time point time, order and time for each pattern of a route |
tpt_pattern_dat.txt |
Contains the order of the time points for each pattern of a route |
dir_route_dat.txt |
Contains the pattern direction, unique number and destination sign information for each pattern of a route |
par_route_dat.txt |
Contains the pattern that uses the most time points for each route |
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
Table Name |
Description |
ACCS |
Lists wheelchair accessible routes |
Sign_code |
Contains the messages displayed on vehicle destination signs for each route |
Par_route |
Contains the pattern that uses the most time points for each route |
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.
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
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.
The second time that the Trapeze reports are processed by the TrapezeParse Application is to produce the tables required for PARIS loading.
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.
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.
CTIS base map processing is divided into four steps:
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:
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.
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.
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.
Correct sequencing of updated routes. If any turns were affected by the new version of CTIS, the sequencing must be corrected.
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:
Create an ArcInfo workspace to contain the bus route to be built.
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.
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.
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.
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. |
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:
Select the pattern from a list.
Using the Sequencing Tool, register the first time point.
Sequence the bus stops and turns until the next time point is reached.
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.
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.
Sequence |
Description |
Sequence |
Description |
|
400 |
First time point of pattern |
710 |
Bus stop |
|
405 |
Bus stop at Park & Ride |
715 |
Left turn (West St to Elm St) |
|
410 |
Bus stop |
720 |
Bus stop |
|
415 |
Left turn (Ave A to 2nd St) |
725 |
Bus stop at Transit Center |
|
420 |
Bus stop |
800 |
Time point at Transit Center |
|
425 |
Right turn (2nd St to Ave B) |
805 |
Bus stop |
|
430 |
Bus stop |
810 |
Left turn (Elm St to West St) |
|
435 |
Bus stop |
815 |
Bus Stop |
|
600 |
Time point |
820 |
Continuation turn for name change (West St to East St) |
|
605 |
Bus stop |
825 |
Bus stop |
|
610 |
Left turn (Ave B to West St) |
830 |
Bus stop |
|
700 |
Next time point of pattern |
900 |
Final time point of pattern |
|
705 |
Bus stop |
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. |
The final step for processing the base map involves two tasks:
Unload INFO files into ASCII format. Four tables from the route building and sequencing processes are required for loading into PARIS:
These INFO files must be unloaded into ASCII format tables and named tpt_location.txt, busstop_dat.txt, trn_location.txt and pattern_dtl_dat.txt, respectively. They are then ready for loading into PARIS.
Create shapefiles for PARIS loading. When routes are created or modified after updating to a new version of CTIS, the routes must be converted into shapefiles for loading into PARIS.
The new version of the CTIS base map that is used for the route building and sequencing is converted into the shapefile format and processed by an Avenue script to produce a comma delimited ASCII file. The base map ASCII file is loaded into PARIS using a SQL*Loader script and is built in the PARIS development database. The required PARIS tables from the route building, sequencing and TrapezeParse processing are output to comma delimited ASCII format files and loaded into PARIS using SQL*Loader scripts. A series of PARIS processes are then run on the UNIX server to build the database. Quality checks are made against the development database, errors are corrected and the database is moved into the production database. At this point the database is ready to be used by the PARIS itinerary planning software.
The first three METRO Service Changes used the SQL*Loader method to populate PARIS with comma delimited ASCII format files. The ASCII files were transferred via either FTP or directly from the PC with Windows NT OS to the Solaris OS using a mapped disk drive made available using Samba. The SQL*Loader method was preferred because of the ability to view the results of each table load in a report, which proved invaluable in troubleshooting data errors. It is anticipated that future database loads will be performed using ODBC from Microsoft Access.
METRO’s Scheduling Department is planning to acquire a Windows NT based schedule system. The new generation of scheduling software systems has powerful data reporting and exporting capabilities and can connect with other relational databases through ODBC. A more advanced scheduling system with GIS and relational database capabilities will certainly reduce the time and effort to produce and process the schedule information required by PARIS.
Sample reports in this appendix illustrate the format of reports produced by METRO’s Trapeze FX scheduling software.
Kim Dinh
Senior Consultant
Idea Integration
5 Greenway Plaza, Suite 1700
Houston, Texas 77064
Tele (713) 627-7878
Fax (713) 627-7879
kim.dinh@idea.com
Kimberly Heintschel
Senior Consultant
Idea Integration
5 Greenway Plaza, Suite 1700
Houston, Texas 77064
Tele (713) 627-7878
Fax (713) 627-7879
kimberly.heintschel@idea.com
Jason Soroko, formerly of Idea Integration
Senior Developer/Analyst
Chrysalis-ITS
1688 Woodward Drive
Ottawa, Ontario, Canada
K2C 3R7
Tele (613)-723-5077
Fax (613)-723-5078
jsoroko@chrysalis-its.com
Imad Haj-Ismail
Manager of Scheduling
Planning and Scheduling
Metropolitan Transit Authority
1201 Louisiana
Houston, Texas 77208
Tele (713) 739-3724
Fax (713) 739-4604
Ii01@hou-metro.harris.tx.us