Below is the uncorrected machine-read text of this chapter, intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text of each book. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.
125 In most cases, it is possible to store and maintain records using simple spreadsheets or word processing tools. When data becomes more complex and the user desires more func- tionality, a relational database management system becomes very useful. The Reference and Model Database constructed as part of NCFRP Project 25, âFreight Trip Generation and Land Useâ (referred to as Database hereafter in this docu- ment) intends to assemble an online relational database of FG and FTG models (e.g., trip rates, regression models), pub- lications, and case studies related to FTG. The Database is designed so that it does the following: â¢ Be stored and made available on the Internet, which would enable practitioners and researchers to have access to it when needed. â¢ Be integrated with an expert system that, in return to a query about trip rates, would provide the closest match. â¢ Enable practitioners and researchers to add data, after pass- ing a quality assurance protocol. The Database constructed was designed in Microsoft Access and contains three primary tables: Publications, Mod- els, and Case Studies. The relationship of the three tables is shown in Figure 20. The Publications table contains information about existing papers, reports, and/or books that are related to FG/FTG, including typical bibliographic information such as author list, year of publication, journal, title, page num- ber, as well as fields indicating whether a particular refer- ence contains FG/FTG case studies and/or models. It also contains a source number as its unique identifier. If a ref- erence contains specific FG/FTG cases studies or models, such information will be used to construct the case study table and Model table. In other words, one reference can have many case studies and many models. Sometimes one case study may also have many models, as shown in the Figure 20. The Case Study table is constructed by summarizing all the case studies in the references in the Reference table. The table contains fields such as project title, where the case study was reported, table/chapter title, where the case study was mentioned in the particular project report, and the city, state, and country where the case study was conducted. It also con- tains a source number in Reference table indicting the refer- ence where the case study was reported. The Models table is constructed by summarizing the FG/ FTG models reported in the references or case studies. A model could be a trip rate, or other type of FG/FGT model, such as regression model, time series model, or neural-network model, among others. The table contains fields in terms of: â¢ Level of aggregation: aggregated, disaggregated. â¢ Level of geography: zonal/urban, regional, national, cor- ridor, special facilities. â¢ Estimation technique: trip rates, Ordinary Least Square, spatial regression, MCA, trend and time series, IO, neutral network, growth rates, others, and not specified. â¢ Model structure: linear, nonlinear. â¢ Time unit: per day, per week, not specified. â¢ Model type: production model, attraction model, not specified. â¢ Dependent variables: the database includes the variable names as found in the source documents, which resulted in a large number of variables. The user is referred to the supplemental material of this Appendix, available online,11 for a list of available variables. â¢ Independent variables: the database includes the variable names as found in the source documents, which resulted in a large number of variables. To ease the use and iden- tification of these variables, they are further classified in terms of a qualifier (e.g., variable type), and the industry a p p e n d i x G FG/FTG Models Relational Database Manual 11Available at http://transp.rpi.edu/~NCFRP25/Appendix_G_-_Variables.pdf
sector (e.g., SIC code) or land use. (The reader is referred to variables supplemental material of this Appendix, avail- able online, for a complete list of the individual variable classifications.) Taking into consideration the large com- binatorial number of possible variables, an aggregation pro- cedure for this classification was implemented, specifically for the variable type and qualifier. â Variable type aggregations include: employment, area, establishment, household, individuals, travel time, fleet, industry segment, income, land use, parking, traf- fic volumes, sales, cargo, and other. â The qualifiers include: agriculture, forestry and fisher- ies, mineral industries, construction industries; manu- facturing; transportation, communication and utilities; wholesale trade; retail trade; food; finance, insurance, real estate, service industries, public administration; and land use. In addition, each model contains a source number from the Reference table to link it to the reference where the model was proposed. As of the production of this manual, there are 63 ref- erences included in the database (See the supplemental material of this Appendix, available online,12 for a list of refer- ences included.); 23 of these references provide descriptions of case studies. A total of 292 case studies are described. The references and case studies identified provided 1890 distinct FG/FTG models. (Again, for a list of all models with basic information see the supplemental material of this Appendix, available online.13) One advantage of using a relational database to store and describe references, case studies, and models is that the user can query the database tables to extract specific information. For example, a user may be interested in knowing the trip rates for restaurants in the State of New York. In this case, the Database provides specific visual tools that allow the user to input the query and retrieve query results in a user-friendly manner. This document is provided as a reference guide for using the Database. The document briefly explains the basics of opening the Database, how to navigate through the differ- ent sections (e.g., models, references and case studies) and provides a usability walkthrough explaining the following examples: 1. Trip rates and OLS on employment and food. 2. Production model under trip rates and OLS on employment. 3. Search publications on research papers, any independent variable, and employment. Using the NCFRP 25 Reference and Model Database Open the Database: Double click the icon of the Micro- soft Access file containing the Database. If the security warn- ing appears, click the âOptionsâ button (see Figure 21) and enable all content in database. Click the âOKâ button. The switchboard automatically loads upon opening the database. This form allows the user to navigate through the database. The switchboard (see Figure 22) directs the user to choose between model, publications, or case study databases. The user is also able to close the database from here. Each of the three modules of the database includes search, view, edit, and add capabilities described in this manual. These capabilities, as their names suggest, allow the user to find, modify, and add information to the database. Go to Model Database This module of the database allows for the viewing and managing of the models (Figure 23). Depending upon the specific needs of the user, a new source may be entered, mod- els may be sorted using specified criteria, or a general listing of the models in the database may be viewed. View all Models will open the entire list of models available on the database. To edit models, it is necessary to identify the model to be modified, and then make the corrections. To save the modifications made, click on âEdit modelâ button. Adding a new model is possible if an existing publication or case study has been previously added. Therefore, before add- ing a new model, it is necessary to add either a new publica- tion or a case study, as a new model must be added to an existing publication. 126 Case studies FG/FTG Publications Figure 20. Database components. 12Available at http://transp.rpi.edu/~NCFRP25/Appendix_G_-_Publications.pdf 13Available at http://transp.rpi.edu/~NCFRP25/Appendix_G_-_Models.pdf
127 Figure 21. Opening the database. Figure 22. Database switchboard.
128 When Search Models is selected, the user is directed to the form in which one can input the search criteria (see Fig- ure 24). The user may select one option, multiple options, or no options from any of the five fields. To select multiple options, press âCtrlâ key and click on the desired options. It is important to stress that the linking options âandâ and âorâ allow the user to expand or restrict the search, respectively. In order to visualize the number of records of a specific search, the user must click the button âNumber of recordsâ located on the left, bottom corner of the switchboard. This feature allows the user to know in advance whether or not the search will show results. Another important characteristic is that the user can view complete, detailed information about each model from the search, or only a summary list with basic information. When the Add New Model option is selected, the user is directed to the Model form. Figure 25 partially shows the different input options available to the user. It is pos- sible to input several variables to specify the model being entered. Go to the Publication Database This module allows the user to visualize the publications (e.g., books, journal articles, reports) in the database, and with the same options as the model database (Figure 26). View all Publications will show the entire list of publica- tions with detailed information available on the database. Add New Publications allows the user to input the informa- tion for a new reference. The search publication switchboard is shown in Figure 27. It includes three fields that can be chosen to select the type of publications of interest. As with the Search Models mod- ule, it is necessary to click the button âNumber of recordsâ to visualize this information before completing your search. Go to the Case Study Database When the âGo to Case Study Databaseâ option is selected, the switchboard in Figure 28 is loaded. Since the purpose of the Database is to provide a source of FG/FTG models, the Figure 23. Model database switchboard.
129 Figure 24. Options to search models in the database.
130 Figure 25. Add new model form.
131 Figure 26. Publications database switchboard.
132 Figure 27. Search publication switchboard.
133 Figure 28. Case study switchboard.
134 first option available in this module is to view the models for each case study contained in the available publications. It is also possible to add a new case study, though to do so it is necessary to either add it to an existing publication, or first add a new publication and then incorporate the new case study into it. The following sections will provide a set of examples to show possible uses of the database. Examples This chapter includes two examples for searching specific models, and an example for searching publications. In gen- eral terms, the user must select a set of search criteria and then check the ânumber of recordsâ of his/her search. If zero records appear, then the user should change the criteria to make it less restrictive. Finally, the user will have the option of viewing the complete detail or just a summary of the mod- els, case studies, or publications that appear. Example 1: Trip Rates and OLS on Employment and Food This example searches all models included in the database, incorporating employment and food as independent vari- ables in which the estimation technique can be either trips rates or OLS. Figure 29 shows the options from which the user needs to select for this search. Again, it bears repeating that to obtain the number of models contained in this search âNumber of recordsâ must be clicked. Once the respective alternatives are selected, the user has the option of viewing the complete detail, or just a summary of the models. Figure 31 and Figure 30 show each of them respectively. It can be seen that this search will produce 10 records. It is also possible, from the completed search page, to print the summary report. As shown in Figure 29, the conditional âandâ is selected. The purpose is to query for models with the specified charac- teristics: they are either trip rates or OLS regression models and independent variable type is employment, select industry sector is food. Example 2: Production Model Under Trip Rates and OLS on Employment This example is similar to the previous, as the user is searching for models. In this case, the purpose is to obtain trip rates or OLS regression freight/freight trip production models that are dependent on employment. The number of records is larger in this instance than the previous because the user has unselected food. Therefore, the database will show the models for any category of independent variable or SIC. The selection criteria are shown in Figure 32. Summary results are shown in Figure 33, and detailed results are shown in Figure 34. Example 3: Search Publications on Research Papers, Any Independent Variable, and Employment In this example the user can obtain the set of publica- tions including different criteria. Document type and either independent or dependent variables are the alter- natives to be selected. In a similar fashion to models, it is possible to visualize and/or view the detail of the publi- cations. Figure 35 shows these fields in detail, depicting the current search example: all research papers where the inde- pendent variable is employment. The summary of publica- tions and detailed results are shown in Figure 36 and Figure 37, respectively.
135 Figure 29. Search on models example 1.
136 Figure 30. Summary report example 1.
137 Figure 31. Detailed report example 1.
138 Figure 32. Search on example 2.
139 Figure 33. Summary report on example 2.
140 Figure 34. Detailed report on example 2.
141 Figure 35. Search on publications.
142 Figure 36. Summary of publications.
143 Figure 37. Detailed results in publication search.