This paper describes a step-by-step approach for moving the 1990 Census Transportation Planning Package data from its proprietary extraction format to a commonly used Geographic Information System platform. This paper was prompted by several needs. First, was the fact that the 1990 public release version of the data resides in a proprietary software written to run with Windows98 and NT 4.0 or earlier operating systems. It will not run on the newer versions of Windows 2000 or NT 5.0. Second and more important, were the many requests from the user community for some guidance on a conversion process. By providing the user community with the guidance described in this paper we hope to help extend the utility of the data for many years to come.

This paper is organized into three sections. Section 1 is a general introduction to the topic. Section 2 illustrates the general principles for doing a conversion, while section 3 presents a detailed step-by-step demonstration for the Washington D.C. area.

Disclaimer

This document was prepared to assist those who want to use the 1990 CTPP data in a GIS platform, specifically Arcview. We are aware that this may not be the only way to do the conversion, nor are we endorsing any software by demonstrating our method. This must be treated as a "working paper" developed for the sole purpose of helping the CTPP user community to effectively use the 1990 CTPP. Neither we, nor our employers, specifically the U.S. DOT endorse this as the only method, or endorse any of the software referenced in the paper. While we took care to test the method, we do not own any legal responsibility if this method does not work.

When the 1990 Census Transportation Planning Package (CTPP) data became available, it was packaged in one of two ways. The first releases were distributed to States and MPOs in a format approximating the more traditional census products such as the STF (Standard Tape Files) and PL (Public Law) -94-171 series. These first releases came with no extraction software and required an experienced programmer and sophisticated software (such as SPSS, SAS etc.). While this worked for many of the advanced users, most preferred to wait for a CD-ROM version packaged with its own extraction software.

The CD-ROM version was developed under a Bureau of Transportation Statistics (BTS) contract and featured extraction software called TRANSVU. TRANSVU was developed as proprietary software by Caliper Corporation. This software allowed the users to "custom" view the data, map it and print selected tables. For the first time in the history of the CTPP development, TRANSVU filled the need for general-purpose extraction software. Although the database used TRANSVU for its extraction it was readily exportable to several GIS packages including TRANSCAD, or Maptitude. While this served several users very well, there were others who expressed the desire to move the data from TRANSVU to other GIS platforms and applications. This paper addresses that need.

TRANSVU was developed under a DOS-based operating system for Windows 3.1 or Windows 95. Later modifications were purchased which allowed it to run on Windows98 and Windows NT. However, as operating systems keep evolving the original sponsors of the CTPP data product have not been able to keep pace with continued purchases of new patches. As a result, we have arrived at a point in time when TRANSVU will not run on the latest Windows operating systems (Windows 2000 or NT 5.0).

Since TRANSVU did not allow the data to be easily exported to GIS formats other than TRANSCAD and that operating systems were restricting its use, the need to provide for the on going preservation of the data by the user community became paramount. This paper was written with that spirit in mind.

The remainder of this paper is divided into two parts. The first presents a general overview of how to move the basic data into a GIS format. The second, and more important takes the user though a step-by-step application using the ESRI Corporation product, ArcView. It must be noted that although this paper uses an example for a specific software it in no way should be construed as a product endorsement. This paper was prepared with the sole purpose of providing the CTPP user community with a framework/methodology for preserving the basic CTPP data.

Questions or suggestions on improving this paper are appreciated. Send you comments to Nanda Srinivasan at ns@camsys.com

2.0 General Instructions

2.1 Prepare Your Base GIS Layer from TIGER

The BTS TranStats website has all the geographic files used in tabulating 1990 CTPP for free. More information, please visit: www.transtats.bts.gov

Alternately, you can follow these steps:

Step 1: Download any version of TIGER/Line from 1994-1998 that contains a TAZ layer from the Census Bureau website.

Step 2: Buy any commercial software that can convert TIGER/Line to a GIS Format

TGR2SHP and TGR2MIF are 32 bit Windows applications for converting TIGER 99, 98, 97, 95, 94 and Census 2000 Dress Rehearsal files to ArcView shape files and MapInfo MIF/MID files, respectively. Version 4 of these programs have many features which make converting TIGER files a simple matter of point-and-click.

Another way to create a TAZ layer is by using equivalency files from the CTPP. The 1990 CTPP package also came with a block to TAZ equivalency file. These can be used to establish the geography files for the TAZ layer from blocks in TIGER/Line.

2.2. Obtain TRANSVU and 1990 CTPP CD-ROMs

Step 1: Obtain the 1990 CTPP and software from Bureau of Transportation Statistics for free.

When this URL is accessed, a window pops up saying "You have chosen to download a file from this location. Run from current location or save to a disk." You can either save the file to your hard drive or run the installation from the website. The installation typically takes 20 seconds. We used d:\transvu as the installation folder (You can choose any drive. For example, you may find more space on the c:\ drive).

Step 2: Run TransVu by double clicking on the icon the installation software creates for you. On the screen that appears, click on "Install new dataset" option. A new screen appears as shown below.

Step 3: Insert the CD containing data for your area. We inserted the CD-ROM with the CTPP Urban Element for the District of Columbia, "BTS-05-16" CD-ROM, and changed the path to f:\ drive (the CD-ROM drive on our machine). The following screen now appears.

Step 4: Select all options and click ok.

Step 5: A new screen comes (as shown below). up with options on summary level and CTPP Part. We chose TAZ as summary level, and Place of residence, because we wanted to export place of residence data to the GIS. You can also choose place of work, or journey to work. For journey to work data, you need to consider that the matrices are large, and the flow data cannot be easily displayed graphically.

Step 6: A new screen comes up (as shown below), with a map-screen on one side, and the data screen on the other. Using the map selection tool, we selected all the TAZs we needed the data for. You do not have to do this, in case you want the entire data set to be exported, skip this step.

Step 6: Select the table(s) you want to export.

Using File

Choose Tables, we chose Table U120

This table contains counts of households stratified by household income and auto-ownership at the residential end.

Tip: By double clicking on a column, you can find out exactly what data the column contains.

For example, the column highlighted in the screen shot below contains count of households with income range of $17,500-19,999, and with no vehicles in the household.

Step 7: Export to DBF Files.

We selected all columns and exported them as dbf format. Click on File and then Save As .dbf. We saved this file in d:\1990ctpp folder (the folder where we kept my TIGER/Line TAZ layer).

3.3 Massaging the Data

We chose ArcView as our GIS to import CTPP data. Since we intend to get this table to arcview, we need to create a column in both the CTPP table in dbf and the TIGER layer that will serve as the common field in a join. This field is called the key field in database jargon. The FIPS in TRANSVU actually contains a region code and the TAZ concatenated together. We need to separate the region code from the TAZ code.

Step 1: To do this, we need to create a column for just the TAZ numbers. The CTPP table in dbf contains a regional code (shown above) plus the TAZ number. So, we opened the table dc.dbf in excel and created a new field called key. Next we entered the formula that will take just the right six numbers from the "fips" column and put them in the "key" column.

The following screen shots demonstrate how to create a new field/column in Microsoft Excel, and calculate the right six fields into the "key" field/column.

Calculating key =right(a2,6)

We saved this as a new dbase file dc1.dbf. This is not really necessary, you could have over written the old file.

Step 2: Start a new Arcview session, add a view, and add the shape file for Washington, D.C. from the folder (d:\1990ctpp\).

Step 3: Open the attribute table for this shape file by clicking on "theme" then "table", and do the same massaging as the CTPP Data file. This is because the TAZ field in the attribute tablefor the TIGER Shape contains the "county" code concatenated to the TAZ code.

The screen shots above demonstrate how to view the table for the shape file added in the view.

Step 4: Put the table in "edit" mode by clicking on "table" and "start editing" as shown below.

Step 5: Add a new field/column by clicking on "edit", add then "add field" as shown below"

Step 6: Make this field a "string" (ArcView's name for a character field), and calculate it as the right 6 characters of the County + TAZ field. We called this new field "Keyf."

Demonstration:

Select all records and press the calculate button. To select all records Click on "edit" and then "select all." All the rows now get highlighted in yellow. Now click on the header "keyf" so it gets higlihted in dark grey. Press the "calculate" button as shown in the following screen-shot.

In Arcview the syntax is Keyf =[key].right(6)

This will calculate all the rows in the column (field in ArcView) as the 6 right most characters in the "Key" field (the County +TAZ Field). The heading on top of the column should be keyf. We highlighted that field, and then pressed the calculate button. The following screenshot should appear.

In the box that appears, We typed in [KEY].right{6}

Step 7: We then saved the edits in the table and deselected all the records. The new table looks like this:

3.4: Add the massaged CTPP Table into ArcView and join it with the geography file.

Step 2: Open both the tables by double clicking on them, and arrange the sizes of their windows so that they can be viewed simultaneously. The next screen shot shows how the computer screen should look.

Step 3: Viewing both the tables together, click on the "key" field in dc1.dbf, and the "keyf" field in "Attributes of 11001.shp" (the order is very important).

Step 4: Click on the "join" button to ("finally") attach the datafile to the shapefile. Congratulations. You have now transferred your CTPP Data file into a GIS. You can now either save the shapefile into another new shape file, or just save the Arcview project. The following screen shot suggests how the new "Attributes of TAZ110001,SHP" table should look. You can now go to the "view" and make some colorful plots!