GEOGRAPHICAL SPREADSHEET TOOL FOR

SUBAREA TRAFFIC FORECASTING

USER MANUAL

Documentation of geographical spreadsheet tool

for subarea traffic forecasting

December 2006

Sponsored by Iowa Highway Research Board

This project was financed in part through funds provided

by Iowa Highway Research Board RFP IHRB-04-08.

This manual was developed by the

Center for Transportation Research and Education

IowaStateUniversity

IowaStateUniversityResearchPark

2711 South Loop Drive, Suite 4700

Ames, IA50010-8664

Telephone: 515-294-8103

Fax: 515-294-0467

Contents

1 Overview

2 Program Objectives

3 How to Use this Manual

4 Getting Started

4.1 Setting Up the Directory Structure

4.2Data Preparation and Installing the Software

5 Using the Spreadsheet Tool

5.1 Trip Generation

5.1.1 Using the spreadsheet tool to record trip volumes

5.1.2 Trip generation from land use master plan

5.2 Distribution to Driveways

5.3 Trip Assignment on Street Network

5.3.1 Setting Initial Turning Percents

5.3.3 Initial Trip Assignment

5.4 Refining Trip Assignment

5.4.1 View and Refine Flow Pattern

5.4.2 Multiple paths for trip assignment

5.4.3 Select Turn Analysis

5.5 Viewing Turning Volumes

5.6 Interfacing with Synchro

5.6.1 Build Synchro Network

5.6.2 Export Synchro Volume Table

5.7 Growth Scenario Management

5.7.1 Save Existing Traffic Counts

5.7.2 Translate Existing Counts from Synchro

5.7.3 Growth Scenario Management

Appendix A GIS Data Processing for Subarea Traffic Forecasting

Tutorials

Start the Spreadsheet Tool

Input/Edit your Trip Generations

Edit Driveway Distribution

Set Initial Intersection Turning Percents

Set Initial Driveway Turning Percents

View Flow Pattern

Refine Flow Pattern

Select Turn Analysis

Manage Growth Scenario

1 Overview

This document presents a user’s guide for the Geographical Spreadsheet Toolfor Subarea Traffic Forecasting program. The program was designed to be useful and user-friendly. To assist you, the user, in becoming familiar and comfortable with the spreadsheet tool program, we suggest you read through this manual and keep it handy during use of the program.

2 Program Objectives

The objective of the spreadsheet tool is to assist local planners and engineers in forecasting traffic volumes and testinghow road facilities will handle traffic variationunder different land development and growth scenarios.The determination of future traffic demands based upon land uses (retail, industrial, residential, etc.) is an effort to match civil road design needs to developer driven estimates of actual conditions.The tooldevelops traffic information on a peak hour basis as opposed to the average daily traffic used in most travel demand planning applications. Peak hour analysis helps evaluate traffic operations at intersections, the source of most urban congestion, and allows scenario planning for both short and long-term operational analysis.

The spreadsheet tool provides an interactive and intuitive platform for performingtraffic planning studies. From a methodology perspective, the tool incorporates methods used in the typical traffic impact study, such as estimating trip generation from ITE land uses, considering internal capture rates and pass-by percents, using actual driveways and detailed street network, and assigning traffic depending on intersection turning percents. In addition, it includestechniquesfrom travel demand modeling, such as automatic building of skim tree (shortest paths), default traffic assignment along shortest paths, multiple path assignment, and select turning movement analysis. Moreover, the new tool can automatically build Synchro network and export assigned turning volumes to a Universal Traffic Data Format (UTDF) volume file, which is readable to Synchro. In this way, the tool supports intersection level of service analysis in an easy to use manner.

3 How to Use this Manual

As you work through the spreadsheet tool, a sequence of events must be carried out in order for the process to be successful. In a flowchart below shows the required sequence for conducting an analysis. Each process in the flowchart has a section in this manual associated with it. After identifying a process on the flowchart, you can find the corresponding section in this manual for an in-depth discussion on each segment of the interface program.

Instead of isolated descriptions of menu items and buttons, the manual uses an example to show how these items and buttons work in a complete traffic forecasting analysis. By example, users will learn the software more efficiently.

4 Getting Started

4.1 Setting Up the Directory Structure

The spreadsheet tool requires a directory to store all necessary data files. It is recommended that the folder be on the computer’s hard drive and be named C:\LandUse. Four subfolders should be created under this folder. Their names are: Database, Excel, RawData and Reports.

4.2Data Preparation and Installing the Software

Highway and street networks are basic inputs for the tool. The network should be drawn on a GIS platform such as ArcGIS/ArcView, or drawn in TransCAD. It is important to make sure that lines and points are connected to each other, which means the network is topological. The coordinates of the lines and points are required to build the network in Excel spreadsheet. If you do not have access to ArcGIS or TransCAD, CTRE can build network for you.

The highway/street network created in the spreadsheet has many interactive components, such as cells for turning movements, driveways, intersections and traffic zones. They provide excellent communication between users and computer. The most unique feature of the new toolis the use of a spreadsheet as an interactive graphic platform to perform traffic analysis.

Street network created in spreadsheet

The file network1.xls located in the Excel subfolder contains street network and all functional macros. It runs trip generation, driveway distribution,trip assignment, interfacing with Synchro, and planning scenario management.

[Try it Yourself]

Start the Spreadsheet Tool

  1. Download the compressed file LandUse.zip from the CTRE website.
  2. Right click on this file and choose “Extract All”, install all files under C:\Land Use. Subfolders will be automatically created under the folder.
  3. Go to the Excel subfolder; double click network1.xls to open the file.
  4. Use Alt+F8 and Menu_Create to build the main menu bar for the spreadsheet tool. The menu bar can be removed later by using Alt+F8 and Menu_Delete.

5 Using the Spreadsheet Tool

5.1 Trip Generation

Trip generation is the first step inperformingsubarea traffic forecasting. The spreadsheet tool presents two methods to generate zonal trip file. The first one provides active cells adjacent to zone names to record exiting and entering trips estimated by users. The second method calculates zonal trips from land use master plan.

5.1.1 Using the spreadsheet tool to record trip volumes

If you prefer to calculate the trips by using your own method, then you need tolet the spreadsheet tool know what trip volumes are for each zone.

[Try it Yourself]

Input/Edit Trip Generations

  1. Choose Network-Create Trip Datafile to create a datafile for recording zonal trips. At this time, the trip datafile contains only zone names.(Before doing this, youcan rename Block_Trips.txt under the RawData subfolder to allow the creation of your own trip file.)
  2. ClickEdit Zone on the menu bar to openthe “Zone Editor” toolbar. (Before the first clicking, use Alt+F8 and Build_EditBlock_Bar to create the “Zone Editor” toolbar. The toolbar can be removed later by using Alt+F8 and Remove_EditBlockBar.)
  1. Change the spreadsheet name to either AM_Total or PM_Total depending on which peak hour you are working at. (default will be PM_Total in the sample file)
  2. Click on the Edit Zonal Trips button to activate two cells adjacent to zone names.
  3. The entering trip volumes should be put in the cell left to zone name. The exiting trip volumes are input in the cell right to zone names. Both cells are in blue color.
  4. Click on the Save Zonal Trips button to save the trip volumes. (this also resets the spreadsheet)

Record zonal trips estimated by users

5.1.2Trip generation from land use master plan

In addition to the trip entry function aforementioned, the spreadsheet tool is capable of processing land use table to obtain zonal trips. Community development departments in most cities prepare land use plans whichcan be detailed, even down to the sub-parcel level.The sub-parcel refers to the construction site plans within a land use parcel. The following figure shows an example of land use table that containsdetailed information.

Land use table used for trip generation

Three fieldsin the land use table above, the GeoparcelNumber, the ITE Code and the Alternate Measure, are required to generate zonal trips.

The next step is to determinethe numbers of parcels in each traffic zone.The user should obtain the spatial relation between one traffic zone and multiple land use parcels. To do this, users shouldfirst identify a centroid for eachparcel, and spatially join the traffic zones to these parcels.In this way,a spatial relation file canbe created which can be used in the calculation of zonal trips. This may be done manually, but is facilitated with a GIS program.

Build spatial relation between traffic zone and land use parcels

In conventional traffic impact analysis, ITE trip generation rates are commonly used to calculate entering and exiting trip volumes for different types of land uses during peak hours.The spreadsheet tool also uses several ITE rates from 7th edition to calculatetrips.

The following figure shows anITE lookup table used by the tool. This table can be found in the RawData subfolder. If the imbedded lookup table does not include a required land use code, users can directly add a new one to the last record of the table. On the right side of the table, usersmay set up pass-by reduction factors for each type of land uses.

ITE lookup table with pass-by reduction factors

When the land use table, the zone-parcel relation file and the lookup table are ready,users just need to click one button to get zonal tripvolumes forAM exiting, AM entering, PM exiting and PM entering.

Calculate zonal trips from land use table

5.2 Distribution to Driveways

This step determines the percent of zonal trips distributed to its driveways. The spreadsheet tool supports up to 30 drivewaysfor each traffic zone. Not all the driveways must be assigned with a portion of trips, andusers can select thedriveways to be used.Each zone has a default distribution percent to its driveways. To make changes, see the following instructions.

[Try it Yourself]

Edit Driveway Distribution

  1. Click Edit Zoneon the menu bar to open the “Zone Editor” toolbar.
  2. Change the spreadsheet name to either AM_Total or PM_Total depending on which peak hour you are working at.
  3. Select a zone on the spreadsheet.
  4. Click on the Edit Driveway Distributionbutton to activate the cells for editing.
  5. Make changes tothe values in the purple cells. The sum of the percents should equal 100(%). Otherwise, an error message will be shown on the spreadsheet.
  6. Click on the Save Driveway Distributionbutton on the toolbar. This action erases red arrows, clears purple cells, and saves the new percent values into a data file.

Edit driveway distribution

5.3 Trip Assignment on Street Network

5.3.1 Setting Initial Turning Percents

The spreadsheet tool allows users to set up initial intersection and driveway turning percents, and then automatically assignszonal trips based on these percents and along the shortest paths from each zone to all other zonesand external nodes.

With professional understanding of local traffic characteristics, the usercan setthe turning percents instead of using the default values generated by the computersuch as those 10-80-10 for a 4-approach intersection and 50-50 or 85-15 for a T-intersection.

Set initial turning percents

[Try it Yourself]

SetInitial Intersection TurningPercents

  1. Choose Network–Edit Default Turn Percents for All Intersections to display the default turning percents.
  2. Make changes to these percents using engineering judgment.A zero (or blank) turning percent means that turning movement is prohibited. (see the figure above)
  3. Choose Network-Save Initial Turn Percents for All Intersections to save the percent values you edited.

[Try it Yourself]

SetInitial Driveway TurningPercents

  1. Select a driveway (light blue number) on the spreadsheet.
  2. Choose Network–Edit Default Turn Percents for One Driveway to display the default turning percents.

Set initial turn percents for driveway

  1. Make changes as desired.
  2. Special driveways(like right-in-right-out driveways) can be designated at this step by setting up a special set of turning percents at the driveway. Driveway 1571 in the example has the right-in-right-out layout. The setting of its turning percents are shown on the following figure.

Designate special driveway

  1. Click the Save button on the pop up frame to save the percent values you edited.
5.3.3 Initial Trip Assignment

To carry out aninitial trip assignment, click on the Assignment menu, then Initial Assignment for All Zones. The spreadsheet tool will assign zonal tripsto driveways, and then assign the driveway trips on the shortest paths from each driveway to all other traffic zones and external nodes. At the intersections and driveways on the shortest paths, the tool looks up default turning percents and calculates downstream volumes. All the turning volumes computed during this step are saved into a percent-volume database file.

Initial trip assignment

[Note] To run an assignment for AM exiting, AM entering, PM exiting and PM entering trips,users need only change the spreadsheet name to the corresponding time periods and flow directions. The spreadsheet name can be one of the following(case sensitive), AM_Exit, AM_Enter, PM_Exit, and PM_Enter.

Change spreadsheet name

5.4 Refining Trip Assignment

5.4.1 View and Refine Flow Pattern

After the initial assignment, the flow pattern of a particular zone-driveway pair is available to be displayed by colorcoded lines. This is another feature of the spreadsheet tool. It allows users to examine and rethink their initial trip assignments. Refinement of the initial assignment can improve the quality of the traffic forecast.To view or edit the flow pattern, users can learn from the following example.

[Try it Yourself]

ViewFlow Pattern

  1. Click Edit Turn Percents on the menu bar to open the “Turning Percent Editor” toolbar. (Before the first clicking, use Alt+F8 and Build_EditPcnt_ToolBarto create the “Turning Percent Editor”toolbar. The toolbar can be removed later by using Alt+F8 and Remove_EditPcntBar.)
  1. Select a zone and Ctrl+click one of its driveways, and then click on the Path_Show button on the toolbar. This action shows intersection turning percents and flow patterns for the zone-driveway pairselected.

Flow pattern of a zone-driveway pair

  1. Click on the Show Ints Volume button to display intersection turning volumes.
  2. Click on the Show Drwy Volume button to display driveway turning volumes.

Show driveway volumes

  1. Click the Reset Spreadsheet button to remove red lines and turning volumes (percents).

[Try it Yourself]

RefineFlow Pattern

  1. Click Edit Turn Percents on the menu bar to open the “Turning Percent Editor” toolbar.
  2. Select a zone and Ctrl+click one of its driveways, and then click on the Path_Show button on the toolbar.
  3. Make changes to intersection turning percents using engineering judgment. See the blue circles in the following figure.
  4. Click the Show/Edit Drwy Pcnt button to edit driveway turn percents. See the pop up window for driveway 1016 in the following figure. In addition, say if you want to extend the path beyond the driveway 1016, then its downstream turning percents should also be set up at this step. See the green circle on the following figure.
  5. Click the Path_Update button to save into the percent-volume database the new turning percents andthe new turning volumes calculated during the update process. The flow pattern will be automatically updated.

Edit turning percents to refine flow pattern

Flow pattern after refinement

5.4.2Multiple paths for trip assignment

Since congestion induces travel oncompetitive pathswith similar or better impedance,multiple paths can be selected for trip assignment. For instance, drivers leaving zone 1169 via driveway 1166 may use four paths to get to the street marked by blue circles on the right side of the figure.