User Manual for the Relational Multimodal
Contents
1. 0 6297 P3 USER MANUAL FOR THE RELATIONAL MULTIMODAL FREIGHT DATABASE Migdalia Carrion Claire Guzman Dan Seedah Jolanda Prozzi C Michael Walton February 2012 TABLE CONTENTS liue Ies NR m 1 Selection of Variables and Public Databases cei esee tortus panca 2 Usine the 6 LIST OF FIGURES Figure 1 Require Microsoft Net Framework 4 0 Client Profile sse 6 Figure 2 Installation Message dk aa e tee aS cse 7 Figure 3 Multimodal Freight Database Link and 7 POU Ae Start ORO I ERR 8 Fig re 5 Databa s Scree NR 9 Figure 6 Final Report Excerpt oa decies eM AR UNE URSI IN RERO dfe PUE E 10 Figure 7 Example of Sorted Data de io 11 Figure 5 Example ol Filtered Data uui oo p EUH Ene lo 12 Figure 9 Exported Excel 5 da gu tudin aru a au MR 12 Figure 10 Creating the Word 13 Figure 11 Exported Word Table 13 Figure 12 Summary Report for Large Databases 222 012221 14 LIST OF TABLES Table 1 Weight Value and Number of Lo2. each will have its own tab in the 10 final report For example by selecting the BCD tab in Figure 6 the program will automatically show the data corresponding to the Border Crossing Data To sort the data according to a specific characteristic click the column header corresponding to the desired characteristic For example in Figure 7 the Border Crossing Data is sorted alphabetically by Port Name MFD Que Rests Output Wadon T _ Q NOTES 1 Click on the Header Text to sort a column 2 Click on the Filter Button to filter a column 3 Get database background Close clicking on the Info Button 4 Export data into Excel Word or CSV formats and 5 n represents null value ACR BCD CFS FFV MAR Number of Records 33 Empty Rail Containers 1 075 962 Empty Truck Containers 3 342 604 Loaded Rail Contain Loaded Truck Containers 5 911 533 Number Of Trains 21 925 Number Of Trucks 9 309 303 Port State Y Port Name 7 77 Commodity Y 77 Number of Trucks Loaded Truck Containers Empty Truck Containers Numb Texas Brownsville 2009 n Truck Rail 189 588 108 782 81 085 Texas Brownsville 2010 Truck Rail 207 408 123 423 84 543 Texas Brownsville 2008 n Truck Rail 222316 126 559 95 528 Texas Del Rio 2009 n Truck Rail 49 500 33 725 10 147 Texas Del Rio 2010 n Truck Rail 55 852 38 998 10 537 Texas Del Rio 2008 Truck Rail 57 182 40 344 11 624 Texas Eagle Pass 2010
3. 77 Mode Y Number of Trucks Loaded Truck Containers Empty Truck Containers Number of Trains Texas Brownsville 2008 Truck Rail 222 316 126 559 95 528 875 Texas Del Rio 2008 Truck Rail 57 182 40 344 11624 0 Texas Eagle Pass 2008 n Truck Rail 101 991 57 913 40 485 1 654 Texas El Paso 2008 CnJ Truck Rail 758 856 384 586 367 988 2473 Texas Fabens 2008 Truck Rail 0 0 0 0 Texas Hidalgo 2008 Truck Rail 476 000 310 513 166 501 0 Laredo 2008 Truck Rail 1 555 197 988 853 566 561 3 921 Texas Presidio 2008 n Truck Rail 6 197 2 692 2 869 0 Texas Progreso 2008 Truck Rail 44 440 25 394 19 050 0 Texas Rio Grande City 2008 n Truck Rail 30 461 25 293 5 169 0 2008 Truck Rail 7 573 3 485 4 062 0 Texas Brownsville 2009 Truck Rail 189 588 108 782 81 085 484 Texas Del Rio 2009 Truck Rail 49 500 33 725 10 147 0 Texas Eagle Pass 2009 Truck Rail 83 254 49 684 33 897 1704 Texas El Paso 2009 n Truck Rail 644 272 336119 303 777 1 502 Texas Fabens 2009 Truck Rail 0 0 0 0 Texas Hidalgo 2009 Truck Rail 419 426 284 608 136 038 0 Texas Laredo 2009 CnJ Truck Rail 1 382319 924 941 457 514 2716 Dencidin anna in x Treck Dail 70an 2720 AOA a Figure 6 Final Report Excerpt The upper tabs provide the navigation tools to see the query results by data source If multiple databases are selected on the Database screen
4. of the public databases consulted including the methodology used to obtain the data limitations assumptions and contact information for obtaining the database Tables 1 to 4 illustrate which public databases captured the relevant freight data that were included in the Multimodal Freight Database Table 1 illustrates which public databases captured data on weight value and number of loads Table 1 Weight Value and Number of Loads Data Source Weight Number of Loads TBR X Exports ACR X WAY X X Table 2 illustrates which public databases captured origin and destination data and the geographic unit used in capturing the information Table 2 Origin and Destination Information Origin Destination Data Source Country State Province NTAR Imports Exports BEA Table 3 illustrates the modal information captured in each of the public databases Table 3 Mode of Transportation Data Air Mail Multi Piggy Pipeline Rail Truck Unknown Vessel Water Source modal back Table 4 illustrates which public databases captured commodity information the classification system used Table 4 Commodity and Classification System Used Data Source Commodity Classification Industry Classification SCTG STCC NAICS 5 X X FAF X USA X X WBN X WAY X TBR X Categories are slightly diffe
5. 0 365 059 Texas 2009 n Truck Rail 44 117 303 777 28 236 336 119 New Santa Truck Rail 0 18 750 0 38 763 Mexico Teresa New MR Truck Rail 0 12 165 0 32 788 d Mexico Teresa Truck Rail 0 8 662 0 31 593 Mexico Teresa E Texas Fabens 2010 Truck Rail 0 0 0 0 Teras Fahens 2007 n Y Tmek Rail 0 0 0 0 y Words 155 English U S HEE 150 2 U 5 J Figure 11 Exported Word Table 13 When the user clicks on the hyperlink a full report of the selected row is displayed For example by clicking 77 records found in the summary report shown in Figure 12 the user can view all the records for freight transported from Alabama to Arkansas E UT p NOTES 1 Click on the Header Text to sort a column 2 Click on the Filter Button to filter a column 3 Get database background Close clicking on the Info Button 4 Export data into Excel Word or CSV formats and 5 represents null value FAF Summary Records Shown 5 190 Total Number of Records 53 086 Sum of Value 56 120 386 887 600 Sum of Weight 21 851 479 679 100 Kilopounds Origin Country 7 Origin State Y Destination Country Destination State 77 Year 77 Value Weight 100 Kilopounds View Complete Records USA Alaska USA Alaska 2007 9 226
6. 248 500 00 51 139 377 21 records found USA Alaska USA Hawaii 2007 64 517 000 00 282 059 3 records found USA Alabama USA Arkansas 2007 422 106 700 00 826 660 USA Alabama USA California 2007 1 308 561 400 00 461 879 13 records found USA Alabama USA Florida 2007 1 557 130 600 00 1 990 484 15 records found USA Alabama USA Maryland 2007 5414 859 200 00 333 734 13 records found USA Alabama USA North Carolina 2007 1 187 135 400 00 1 898 846 17 records found USA Alabama USA New York 2007 5469 127 100 00 225 757 12 records found USA Alabama USA Pennsylvania 2007 424 873 900 00 421 953 16 records found USA Alabama USA South Carolina 2007 1 388 695 700 00 3 390 186 13 records found USA Arkansas USA Colorado 2007 5148 570 000 00 420 178 13 records found USA Arkansas USA New Jersey 2007 5118 805 500 00 162 652 10 records found USA Arkansas USA New Mexico 2007 35 341 500 00 30 650 11 records found USA Arkansas USA New York 2007 188 982 400 00 80 649 10 records found USA Arkansas USA Texas 2007 2 462 451 200 00 9 666 005 20 records found USA Arkansas USA Virginia 2007 5314 974 300 00 151 863 9 records found ica p uc dicam T Figure 12 Summary Report for Large Databases To begin a new query return to the Database Screen see Figure 5 and select the new database s and filtering options of interest Each new query will open in a separate window 14 For Further Questions and Comments please contact the research team at C
7. ads esee 3 Table 2 Origin and Destination Information eese 4 Table 3 Mode f TEnsporbiblolla caasa hoe a em abes ed edu ares e aU 4 Table 4 Commodity and Classification System Used sees 3 INTRODUCTION This document was developed as part of TxDOT project 0 6297 entitled Freight Planning Factors Impacting Texas Commodity Flows The project focused understanding the critical factors that influence freight planning states are required in by the Intermodal Surface Transportation Efficiency Act ISTEA of 199 and by the subsequent Transportation Equity Act for the 21 century TEA 21 to conduct statewide freight transportation planning However understanding how freight impacts the transportation system of a state and conducting statewide freight planning requires robust data The research team found that most states that are conducting statewide freight modeling planning seem to have relied on the commercial TRANSEARCH database partly because this is currently the only database that captures most of the variables needed for freight modeling Prozzi Mani and Harrison 2006 Although the TRANSEARCH database has most of the necessary variables concerns have been expressed because there is not a clear understanding of how the data is gathered and the methodology used for compiling the database It is also true that this database can be costly makin
8. ase screen see Figure 5 Multimodal Freight Database po eas tone _ Using the software Database Variables Exit Help Database Limitations 1 Select from Available Databases 2 Specify Filtering Options by Origin Destination Port of Entry Departure if available Year Commodity and Mode NOTE Option n signifies a null value For some databases this needs to be selected Origin Destination Port of Entry Departure Year Commodity Mode E Annual Coal Report ACR _ Border Crossing Data BCD Select Origin Select Destination Carload Waybill Public Use File WAY Commodity Flow Survey CFS Freight Analysis Framework FAF Country Continent 0 0 Select All Clear All Country Continent 0 0 Select All Clear All Fruits and Vegetables FFV Maritime Administration Data MAR National Transportation Statistics NTS TransBorder Data TBR USA Trade Online USA E Waterborne Commerce Statistics WBN Select All Search Search pu DS DS D 9 3 Run Query aM D Figure 5 Database Screen The screen shows all the databases that freight data can be extracted from The user can select multiple databases by clicking the checkbox next to the database name Click Select All to select all the databases Click Clear All to erase all selections C
9. crosoft Net Framework 4 0 Client Profile Microsoft Net Framework 4 0 Client Profile can be installed from the Multimodal Freight Database CD or downloaded from the Microsoft website Multimodal Freight Database Installation 1 Double click Setup exe on the Multimodal Freight Database CD 2 The following message box will appear see Figure 2 3 Click Next and follow the instructions to install Multimodal Freight Database Multimodal Freight Database 3 0 Welcome to the installation of Multimodal Freight Database version 3 0 Please close all active programs to avoid any conflicts and press Next to begin the setup procedure or Cancel to exit this wizard 2012 Copyright Center for Transportation Research www utexas edu research ctr Figure 2 Installation Message Box Step 2 Start Screen To begin using the software 1 Goto Start gt Programs gt gt Multimodal Freight Database 2 Click on Multimodal Freight Database see Figure 3 3 The Multimodal Freight Database Start screen will appear see Figure 4 Multimodal Freight Database Figure 3 Multimodal Freight Database Link and Icon Multimodal Freight Database Using the software 7 Database Variables Exit Help Database Limitations MULTIMODAL FREIGHT DATABASE Figure 4 Start Screen Step 3 Selecting Public Databases After clicking Start the user will see the Datab
10. der Bluetooth Exchange Folder 9 9 2011 2 45 AM File folder Ji CoffeeCup Software 10 6 2011 12 03 AM File folder b Music Data Analysis 2 8 2012 9 59 PM File folder gt Pictures Ji dicom 0 1 1 tar 12 22 2011 1 36 AM File folder gt 8 Videos b Integration Services Script Component 2 8 2012 9 38 PM File folder 472 Libraries 2 B Documents m File name Save as type 2003 Word Document doc Hide Folders Figure 10 Creating the Word File Once the file has been created open it and the file should contain a Word table similar to Figure 11 was COUR M NECS IIR m Home Insert Pagelayout References Mailings Review NitroPro7 Design layout m Times 12 A 4 imc HE c SEE LT AaBCdl AaBbCc AaBbCel A 5 Paste us 0 TNormal TNoSpaci Headingi Heading 2 Title Subtitle Subtle Em Clipboard Font Paragraph n Styles a Editing 8 PortState PortName Year Commodity Mode EmptyRailContainers EmptyTruckContainers LoadedRailContainers LoadedTruckC Texas ElPaso 2007 n Truck Rail 89 760 356 863 89 316 402 456 New Santa 9010 n Truck Rail 0 26 835 0 49 384 Mexico Teresa Texas ElPaso 2008 n Truck Rail 84 050 367 988 76 745 384 586 Texas 2010 n Truck Rail 57 448 324 246 32 36
11. enter for Transportation Research The University of Texas at Austin 1616 Guadalupe Street Suite 4 202 Austin Texas 78701 Phone 512 232 3100 Websites http www utexas edu research ctr or http www texasurbanfreight com Email MFDGtexasurbanfreight com 15
12. g it difficult for some planning agencies and policy makers to purchase it Consequently as part of TxDOT project 0 629 the CTR research team developed a Relational Multimodal Freight Database that can capture relevant publicly available freight variables required for updating TxDOT freight models and studies From the Multimodal Freight Database TxDOT is able to identify any missing data that the public databases do not capture The missing data can be obtained through purchasing a commercial database or by collecting the relevant data This manual provides step by step guidance on how to use the TxDOT Relational Multimodal Freight Database Software Freight Database as well as information on how the database was developed and the public databases from which the data was extracted SELECTION OF VARIABLES AND PUBLIC DATABASES The variables that were included in the Multimodal Freight Database were selected in consultation with prospective TxDOT users of the database Specifically the research team met with the SAM Project Director and transportation planners from TxDOT s multimodal office These meetings provided the potential users of the database with the opportunity to provide invaluable input in terms of their data needs the database structure and the software platform to be used In terms of the latter Microsoft Access 2007 was selected to be the software platform Freight Variables In consultation with TxDOT it was decided t
13. lick Help to access the Quick User Guide explore the Database Features and obtain detailed information on each of the public databases consulted including the methodology used to obtain the data limitations assumptions and contact information for obtaining the database Step 4 Filtering Options After selecting the databases of interest specify the filtering options by Origin Destination Port of Entry Departure Year Commodity and Mode by clicking the tabs and checking the appropriate boxes After the appropriate filtering options have been selected click Run Query Step 5 Preparing the Report Upon completing Step 4 a final report containing the freight data will be compiled see Figure 6 for an example of a final report The final report presents the specified freight data by data source MED Query Results Output Window Et a em Q NOTES 1 Click on the Header Text to sort a column 2 Click on the Filter Button to filter a column 3 Get database background Close clicking on the Info Button 4 Export data into Excel Word or CSV formats and 5 n represents null value ACR BCD CFS Number of Records 33 Empty Rail Containers 1 075 962 Empty Truck Containers 3 342 604 Loaded Rail Containers 792 140 Loaded Truck Containers 5 911 533 Number Of Trains 21 925 Number Of Trucks 9 309 303 PortState 77 Port Y Year Y Commodity
14. n Truck Rail 95 028 60 429 35 976 Texas Eagle Pass 2009 Truck Rail 83 254 49 684 33 897 Texas Eagle Pass 2008 n Truck Rail 101 991 57 913 40 485 Texas Paso 2009 Truck Rail 644 272 336 119 303 777 Texas El Paso 2008 n Truck Rail 758 856 384 586 367 988 Texas El Paso 2010 Truck Rail 710 363 365 059 324 246 Texas Fabens 2008 n Truck Rail 0 0 0 Texas Fabens 2010 Truck Rail 0 0 0 Texas Fabens 2009 n Truck Rail 0 0 0 Texas Hidalgo 2010 Truck Rail 459 331 324 350 135 348 Texas Hidalgo 2009 n Truck Rail 419 426 284 608 136 038 Texas Hidalgo 2008 Truck Rail 476 000 310 513 166 501 Tavar larada 2010 fn i 1 697 1177 560 7 Figure 7 Example of Sorted Data To filter the data click on the Filter icon see Figure 8 11 B m Border Crossing Data Number of Records 1 549 Empty Rail Containers 11 236 449 Empty Truck Containers 38 535 277 Loaded Rail Containers 21 202 429 Loaded Truck Containers 106 863 213 Number Of Trains 626 823 Number Of Trucks 164 732 198 Port State Y Port Name 77 Year V Commodity 77 Mode 77 Empty Rail Containers Empty Truck Containers Load Rail Containers Loaded Rail Container Alaska Alcan Truck Rail 0 0 0 e Alaska Dalton Cache seed Alene oe Truck Rai 0 0 0 3 Alaska Skagway 71995 Truck Rail 0 0 0 Arizona Douglas 1996 Truck Rail 0 0 0 A
15. o include the following variables in the Multimodal Freight Database e Year Modes of Transportation Air Mail Multimodal Piggyback Pipeline Rail Truck Rail Truck Unknown Vessel Water e Origin o ByState o ByCountry e Destination o ByState o By Country e Port o Port Location by State e Type of Movement o Export o Import e Number of Loads o Carloads o Container e Value U S e Weight 1 000 Ibs e Commodity Type o STCC Standard Transportation Commodity Code at 2 Digit Level Freight Public Databases The research team identified and reviewed 25 publicly available databases from which freight data could be extracted at no cost After evaluating these public databases it was found that 11 of these databases contained all or some of the variable information of interest to TxDOT at the appropriate level of disaggregation Freight data included in the Multimodal Freight Database were thus ultimately extracted from the following databases Carload Waybill Public Use File WAY Commodity Flow Survey CFS Freight Analysis Framework FAF Transborder Surface Freight Data TBR Annual Coal Report ACR Border Crossing Data BCD Fresh Fruit and Vegetables Shipments by Commodities States and Months FFV Maritime Administration Data MAR National Transportation Statistics NTS USA Trade Data USA Waterborne Commerce Statistics WBN Appendix A provides detailed information on each
16. odity PortState PortName EmptyRailContainers EmptyTruckContainers LoadedRailContainers LoadedTruckContainers NumOfTrain NumOfTruck 2007 Truck Rail New Mexico Santa Teresa 8 662 0 31 593 0 40 267 2007 Truck Rail n Texas El Paso 356 863 89 316 402 456 2 691 782 936 2007 Truck Rail n Texas Fabens 0 0 0 2008 Truck Rail New Mexico Santa Teresa 12 165 32 788 45 856 2008 Truck Rail n Texas El Paso 367 988 384 586 758 856 2008 Truck Rail Texas Fabens 0 0 0 2009 Truck Rail n New Mexico Santa Teresa 18 750 38 763 57 410 2009 Truck Rail n Texas El Paso 303 777 336 119 644 272 2009 Truck Rail n Texas Fabens 0 0 0 2010 Truck Rail n New Mexico Santa Teresa 26 835 49 384 78 879 2010 Truck Rail n Texas El Paso 324 246 365 059 710 363 2010 Truck Rail n Texas Fabens 0 0 0 UU BON M 1 Sheet Sheet3 fj Figure 9 Exported Excel Spreadsheet 12 Similarly a Word file be created by clicking the corresponding icon After clicking window will ask you to name the file and select a folder to be created see Figure 10 RMFD Query Results Output Window Organize v New folder 42 Favorites r3 Documents library BE Desktop Includes 2 locations Downloads Name S Date modified Type J Dropbox 35 Recent Places Arrange Folder v Ji Autodesk 10 13 2011 9 20 PM_ File folder BIMSERVER 10 7 2011 4 32 File fol
17. rent from the standard classification used by the Harmonized System From Tables 2 and 4 it is evident that different public databases used different codes and geographic units when capturing freight data Development of the Multimodal Freight Database thus required the development of a standardized list of codes for each of the freight variables included in the database the public data extracted was thus coded to a uniform set of codes that are included in Appendix B Appendix B also contains the mapping of the commodity codes developed and how they relate to the HS NAICS SCTG and STCC classification systems Finally the use of standardized codes also simplified the coding of the user friendly interfaces that were developed for the Multimodal Freight Database USING THE SOFTWARE Step 1 Installing the software Insert the Multimodal Freight Database CD in the CD ROM drive of the computer System Requirements Microsoft Net Framework 4 0 Client Profile is required to run the Multimodal Freight Database The Framework is packaged with Windows Vista and Windows 7 If the computer does not have Microsoft Net Framework 4 0 Client Profile installed the user will need to first install the Framework see Figure 1 Requirements not met We sorry this program requires the NET Framework version 4 0 Client Profile in order run on your computer Please install it and retry the installation Am Figure 1 Require Mi
18. rizona Lukeville 4 Truck Rail 0 0 0 Arizona Naco 7 1999 Truck Rail 0 0 0 Arizona Nogales 72000 Truck Rail 0 0 0 Arizona San Luis 2001 Truck Rail 0 0 0 California Calexico East 12002 Truck Rail 0 0 0 California Otay Mesa Station 712003 Truck Rail 0 0 0 California San Ysidro Boos Truck Rail 0 0 0 California Tecate 42006 1 Truck Rail 0 0 0 Idaho Eastport 712007 Truck Rail 0 0 0 Idaho Porthill 72008 gj Truck Rail 0 0 0 Maine Bridgewater Truck Rail 0 0 0 Maine Calais Truck Rail 0 0 0 Maine Fort Fairfield I985 GLL J Truck Rail 0 0 0 Maine Fort Kent 1995 n Truck Rail 0 0 0 Figure 8 Example of Filtered Data Additionally the Relational Multimodal Freight Database provides the option to export the query results to several programs By clicking the Excel icon in the upper left part of the window an Excel spreadsheet will automatically be created see Figure 9 ma cic NER T oft Ec T ME S Home Insert Page Layout Formulas Data Review View Nitro Pro 7 a gH 24 zr ED Un th Calibri yr E Wrap Text General M ES EU gi er A d a g Z Paste T 77 8 EE Merge amp Center 9 509 Conditional Format Cell Insert Delete Format Sort amp Find amp I gt ved E p E Formatting as Table Styles gt z X 2 Filter Select Clipboard Font Alignment Number fa Styles Cells Editing F G H 1 K Comm
Download Pdf Manuals
Related Search