Semisequicentennial Transportation Conference Proceedings
May 1996, Iowa State University, Ames, Iowa
Database and Graphical Techniques for Managing and Displaying Travel Forecasting Information
Associates Transportation Consulting
389 Alcatraz Avenue #12
Oakland, California 94618-1362
Managing and interpreting information input to and output by travel forecasting processes are often hindered by the weak data management and graphical interfaces of transportation planning software. While some software vendors have improved these interfaces, many organizations do not yet have access to the new capabilities, for a variety of reasons. These organizations can take advantage of widely available database programs and inexpensive graphics equipment to create better data management and interface tools. Two experiments to design database and graphical tools intended to facilitate use of forecasting data were conducted, one to create displays of changes in highway facilities between networks, the other to produce summaries of changes in operating conditions. Both tools allow information from two networks to be displayed on a single graphic, on-screen or plotted. They create products previously unavailable or prohibitively time-consuming to produce, and are suitable for adaptation by organizations with limited resources. They require database software, basic familiarity with database applications and rudimentary programming skills, and an inexpensive plotter. The tool for displaying differences in network coding has been found useful for checking and cataloging networks, in addition to fulfilling its intended use, to aid local jurisdictions in verifying that regional networks correctly reflect programmed projects. The second tool was found to be effective for creating displays comparing loaded networks, but the project concluded that further work is needed to understand how better to display and interpret modeling information for use by agency decision makers and the public. Key words: travel forecasting, database, graphical, transportation planning, MINUTP.
The regional travel demand modeling process collects and generates a wealth of information about transportation facilities, planned changes to facilities, existing travel, and forecast travel. As many researchers have pointed out, much of this information is unavailable from other sources and is potentially useful for air quality and land use modeling and planning, as well as for transportation planning (1). However, access to travel demand modeling information can be hindered, in part because modeling software stores data in cumbersome ways, and because graphical display capabilities are limited and expensive. Having been developed directly from mainframe UTPS for first generation personal computers, many packages still emphasize computational aspects over user interface and data management.
While some newer, and more expensive, modeling packages have vastly improved data management and interface capabilities, many organizations still use older or inexpensive software with limited capabilities, for a variety of reasons. Some lack the resourcestime, money, and expertiseto upgrade, others only occasionally work with modeling data and do not need sophisticated tools, and others have developed model sets and procedures around older software and wish to continue established practices. These organizations can take advantage of widely available database programs and inexpensive graphics equipment to create better data management and interface tools, designed specifically to suit their needs.
This paper describes efforts to develop tools for creating graphics and printed output from regional transportation modeling data using custom database applications. Two projects are described, one to create a way of verifying with local jurisdictions that network coding corresponded to programmed projects, the other to create graphics for a congestion management agency board summarizing changes in operating conditions from year to year. Both tools allow information from two networks to be displayed on a single graphic, on-screen or plotted, previously impossible or prohibitively time-consuming to produce.
The tools are designed to be adapted by any organization that uses travel forecasting data; they require database software, basic knowledge of the software, rudimentary programming skills, and an inexpensive plotter. They are especially suitable for organizations with very limited budgets. The applications were designed to manage data produced by MINUTP, but should be adaptable to other popular planning packages.
A TOOL FOR COMPARING NETWORK CODING
The first tool was created for the Metropolitan Planning Organization (MPO) of a medium sized region, whose regional modeling networks include four counties and several dozen cities.
The MPO sought to improve the process of creating networks that correctly reflect highway and transit improvement projects programmed for each county and city within its jurisdiction. The agency had experienced a few instances of local jurisdiction staff and decision makers finding discrepancies between network coding and programmed projects. Some of these were unavoidable, the result of late changes in the projects themselves, but some were clearly the result of miscommunication.
The MPO was in the process of creating a more formal communication process, which would add a network checking routine to its established practice of collecting an inventory of projects from local agencies and coding networks from the information provided. For the network checking routine, the MPO proposed to create a report for each member jurisdiction, displaying changes in each future year network from a base network, and to ask each participant to double check the report against its most recent project descriptions. Staff wanted a tool that could be used to create these reports quickly, with minimal use of staff time to manually extract data or to write or modify custom programming. It also wanted the tool to be able to create graphical displays that summarized the changes relevant to each jurisdiction in a concise and easily decipherable form.
The MPOs modeling staff are knowledgeable and experienced, but operate under time and budget constraints. Its well maintained networks included fields coding political jurisdiction for each zone, it had written and tested a variety of colorsets and plotting routines for graphical display, and had excellent plotting equipment and expertise. Software was an up-to-date version of MINUTP.
Conceptual Design of the Database Application
It was clear from the outset that the tool should be created using database software, for the following reasons.
- Database software is designed to facilitate the creation and manipulation of multiple tables, each of which serves a specific purpose. The tables, once indexed and given common keys, can be linked, compared and combined. This property was ideal for the toola table could be created to store each network and child tables could copy and manipulate selected fields of each.
- Databases are designed to store and operate on large files quickly. Some spreadsheet applications tested could not hold the nearly 30,000 records in each network.
- Once converted to table form, data are highly accessible. The user can sort data on any field or fields very quickly, scroll through the data, use sophisticated search and replace functions, grab fields with the mouse and drag them side-by-side, and create new fields as functions of the old ones. Original data can be locked, preventing alteration.
- The database tables can store more fields than MINUTP allows, and easily export different sets of fields back to MINUTP.
- The database provides the capability to easily create and modify report skeletons that can be used to automatically produce the same report from any network data table.
The conceptual design for the process called for combining MINUTP NETVUE, NETPLT, and the custom database programming: NETVUE would be used to create ASCII network files, the database application would store network databases and create comparison databases then format them for export back to NETVUE. NETVUE could then be used, with appropriate colorsets, to display comparison networks. Comparison networks could then be plotted using NETPLT, using the geographical designations coded by the MPO to display only the sub-area relevant to each jurisdiction. This design took advantage of the capabilities and minimized the writing of new code.
Applying the Tool
The conceptual design was successfully implemented using FoxPro2 for the database functions, and MINUTP NETVUE, NETBLD, and NETPLT. Applying the tool to a base network and one or more alternatives involves executing four modules, described briefly below. More detailed descriptions can be found in the Users Guide created for the agency (2).
1) NETVUE: An ASCII file is created using a NETVUE macro, the file formatted to be compatible with a database structure created in FoxPro. Links are written as one way, and network identifier is added to each link. Step 1 is repeated for the base network and each alternative.
i) The ASCII file for the base network is appended to an empty database structure, which matches the network structure and is copied from a read only version on disk. The resulting table is then processed through a short series of programs designed to create a unique identifier for each link based on a concatenation of its two node numbers, and the table is indexed and sorted on this key. The table can also be merged with a separate database containing street names for some or all links, if such a table has been created. Step 2i is repeated for each alternative network.
ii) Selected pairs of network tables created in Part 1 are run through a second processor. The processor creates another table containing attributes selected from both networks and new fields to hold comparisons on each attribute. The attributes the MPO chose to compare were direction, distance, speed, and number of lanes. An additional field stores a code identifying each link in the alternative network as added, deleted, changed from, or identical to the link in base network. The last function of the processor is to copy and reformat the comparison fields for export back to NETVUE. A pre-formatted setup for creating reports listing all changes in links, by jurisdiction, can be run before exiting the database program.
3) MINUTP NETBLD builds a comparison network from the ASCII file output in the previous step. This network is displayed on-screen in NETVUE using a colorset showing added links and deleted links in different colors, and identical links in gray to fade into the background. Links whose attributes have changed use another color, and the changes can be posted.
4) NETPLT is used to produce a color plot identical to the screen display, with links relevant to each jurisdiction selected in the NETPLT setup using the networks geographical codes. Alternatively, a shareware screen dump utility can grab and print sections of the screen directly from NETVUE. This process produces one graphic comparing each pair of networks, and one report of link changes corresponding to each graphic. At present, each module must be executed manually. An experienced user can convert and display comparison networks for a base network and a series of alternatives in a few hours, once the process is set up. The user need not be familiar with the database program, or with NETVUE to create comparison networks, as long as the creator o
A TOOL FOR COMPARING ATTRIBUTES OF LOADED NETWORKS
A second database application for displaying travel demand modeling data was created for a local transportation authority in the same region. The agency had begun a comprehensive effort to provide its board with guidelines for apportioning limited funds among numerous projects. Part of this effort was to present to the board information about the predicted operating conditions of the regions highway system in two future years. The agency, having no travel forecasting capability of its own, had obtained loaded networks from the MPO. The purpose of the project was to experiment with ways to display information from the networks that could be easily interpreted by the board. Again, the products were to be graphics comparing, on one plot, attributes of pairs of networks, and printed reports presenting the numbers behind the graphics.
The project involved modifying the Network Comparison tools described above to compare, from loaded networks, the volume-to-capacity ratio for each link from base to year 2000, and from base to year 2015. Database tables were created from each loaded network, containing assigned volumes and the fields necessary calculate capacity, and basic link descriptors (lane miles, link miles, by functional class). Each pair of tables was then run through the modified link comparison program to calculate and record the change in v/c ratio. Links whose physical descriptions had changed were flagged, also. Changes in v/c ratios were recoded into a few levels for translation into color for on-screen display and plotting. Comparison networks were then exported, built in NETBLD, displayed in NETVUE, and plotted. As in the previous case, the databases tables could be created and compared quickly and easily once the formats had been created and programming modified.
The most time-consuming parts of the project were experimenting with plot setups and printed report formats, seeking ways to display two sets of information in the way staff and the consultant thought would be most easily understood by the overburdened board. The plot setup finally chosen produces a graphic showing Future Year LOS differentiated by bandwidth and Change in LOS from Present to Future year differentiated by color. Four bandwidths categories are plotted using lines of distinctly increasing thickness: LOS A through C, LOS D and E, LOS F1, LOS F2 (LOS F2 corresponds to assignment results producing a v/c > 1.5. The practical nonexistence of v/c ratios greater than one was explained, and an interpretation of why the forecasting process produces a nonexistent number provided.) Four categories of change in LOS are plotted: Future Year LOS Better than Base Year in Green, Future Year About the Same in Yellow, Future Year Worse in Red, and Future Year Much Worse in Black. The traffic signal color scheme proved easiest to interpret, and had the added benefit that color saturation was highest the more the change in LOS. The displays are more easily interpreted by remembering that the most improvement in operating conditions is represented by a thick green link, and the most deterioration by a thin black line. With practice, staff judged the plots useful devices. Color versions of the plots, which do not reproduce well in black-and-white, can be found in the Sacramento County Transportation Plan (3).
Outcome: Did the Graphics and Tables Convey to the Board the Desired Information?
This project was judged partially successful. The graphics and tables envisioned were successfully created, with limited resources, and the database tools proved useful. However, most of the agencys board members did not find the products directly useful. Board members who were able to take the time to study the graphics did find that they could get a general sense of how well the modeling process says the highway network is working and how it is predicted to change, but most found the displays confusing. One board member, who has training in science, could interpret the displays and found them interesting as background, if not useful to his immediate task of prioritizing highway projects.
Two experiments with inexpensive database methods for managing and displaying travel forecasting data were undertaken. Methods of quickly comparing attributes of pairs of networks were developed, along with methods of graphically displaying the network comparisons. The products of these methods were found especially useful by staff involved in modeling and as tools for verifying and documenting networks. As tools to convey information from travel forecasting efforts to public agency board members the products proved somewhat less successful. The database methods themselves were useful in quickly and easily producing reports and graphics. More effort should be spent to learn how to use these tools to convey and interpret modeling information in a manner that can inform board-level and public decision processes, and indeed to learn whether this information can be useful to these debates at these levels.
Gordon Garry, Joe Concannon, and Bruce Griesenbeck of the Sacramento Area Council of Governments, and Brian Williams of the Sacramento Transportation Authority were instrumental in the creation and testing of the methods described in this paper.
- G. Harvey, E. Deakin, et al. A Manual of Regional Transportation Modeling Practice for Air Quality Analysis. Version 1.0. National Association of Regional Councils, July 1993.
- A.D. Stevens. SACOG Network Comparison Users Guide, Version 2. Sacramento Council of Governments, 1995.
- Sacramento Transportation Authority. Sacramento County Transportation Plan and Congestion Management Program (Draft). October 1995.