Framework for a Pavement-Maintenance Database System (2016)

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.

107 To demonstrate the use of PMDb, sample data and analysis scenarios were generated to illustrate several possible maintenance applications for asphalt and concrete pavements. Sample data were generated in part from the existing data provided by state agencies as well as from a random data generator (Figure 45). All data and illustrative examples presented in this report are not accurate representations of existing state agency data. The data have been altered and supplemented to help demonstrate the use of PMDb and should in no way be construed as attempting to provide a conclusion regarding pavement-maintenance activity or effectiveness. Login to the Pavement-Maintenance Database Step 1. Open PMDb and click on the login button in the upper right hand corner. A p p e n d i x e Loading Sample Data into the Pavement-Maintenance Database

108 Framework for a pavement-Maintenance database System Step 2. Login to PMDb with administrator credentials. • Username: admin • Password: password Create Route Inventory To load data into PMDb, an inventory of routes needs to be created to which inventory, maintenance, and performance data can be attributed. PMDb was designed to ingest the NHPN dataset and use the dataset as a base inventory although it does allow individual route additions. To add the sample data provided in Table E-1, perform the following steps: Step 1. Click on “route inventory” to view the current inventory.

Loading Sample data into the pavement-Maintenance database 109 Step 2. From the route inventory page, click on “add a route” to add a new route to the inventory. Step 3. Enter the route information. The route state represents the two-letter abbreviation of the state the route is located within, the route name is the formal name of the route, the rural code is the classification of the route, and the functional class represents one of the federal functional clas- sifications for the route. For the minimum ARM and the maximum ARM, enter the accumulated route mile boundary conditions of the route which represents the minimum and the maximum value against which events can be attributed for this section. For routes that traverse multiple codes, you may break them up into multiple segments or simply select the most appropriate or representative section. Click on “save” when complete. Use the information in Table E-1 to create the route inventory. State Route Name Rural Code Functional Class Minimum ARM Maximum ARM MI I-75 Urban Urban Interstate 0 28.29 MI I-94 Urban Urban Interstate 0 23.43 MI I-96 Urban Urban Interstate 0 17.52 MI M-20 Rural Rural Principal Arterial 0 26.371 MI M-25 Rural Rural Principal Arterial 0 82.4 MI M-28 Rural Rural Principal Arterial 0 26.74 MI M-37 Rural Rural Principal Arterial 0 20.642 MI M-43 Rural Rural Principal Arterial 0 18.68 MI M-46 Rural Rural Principal Arterial 0 88.37 MI M-50 Rural Rural Principal Arterial 0 19.22 MI M-55 Rural Rural Principal Arterial 0 25.151 MI M-57 Rural Rural Principal Arterial 0 22.98 MI M-66 Rural Rural Principal Arterial 0 20.38 MI US-10 Urban Urban Freeway or Expressway 0 22.36 MI US-12 Urban Urban Freeway or Expressway 0 21.93 MI US-131 Urban Urban Freeway or Expressway 0 17 MI US-2 Urban Urban Freeway or Expressway 0 26.91 MI US-23 Urban Urban Freeway or Expressway 0 26.71 MI US-31 Urban Urban Freeway or Expressway 0 20.96 MI US-41 Urban Urban Freeway or Expressway 0 76.26 Table E-1. Sample inventory data.

110 Framework for a pavement-Maintenance database System 76.26 Step 4. Regenerate system records. Click on “regenerate systems records” to process the new route into the system records segments. Once the new route has been regenerated, a confirmation screen will appear with a message indicating that the route was successfully populated into the system data segments.

Loading Sample data into the pavement-Maintenance database 111 Step 5. Enter complete inventory of routes. To allow a portion of the sample data to be processed, enter data for the routes as seen in Table E-1 by repeating Steps 1 to 4. Click on “regenerate system records” after each entry to ensure that the route is segmented appropriately in the system record database. Upload Data Files to the Pavement-Maintenance Database Sample Data Folder and Files To illustrate how PMDb performs, several sample data sources were generated. The sample data folder, included in the CD (CRP-CD-179, Volume 2) contains sample data files, as shown in Table E-2. The following steps should be repeated for each sample data file. Sample Data Upload Procedure Step 1. Select “upload a file” from the primary navigation menu. Sample Data File Description MDOT CPM Treatments 1992 to 1994.tab This sample data file contains a listing of various maintenance and preservation treatments by treatment type, length, and cost. MDOT Sample Data.tab This sample data file contains details on project type, surface type, and other details specific to each maintenance project performed. MDOT-Year1.tab MDOT-Year2.tab MDOT-Year3.tab These data files contain sample performance data, limited to a condition index value. Each file represents a subsequent year of performance data capture after the performance treatment. MDOT Performance.tab Table E-2. Sample data file.

112 Framework for a pavement-Maintenance database System Step 2. Select a data source and select a file that needs to be uploaded. Using the drop-down menu, select the name of the data source, according to Table E-3. The uploaded file will be attributed to this data source. If the data source name is not listed in the drop-down menu it needs to be created. For the sample data, select “add a data source” to add a new data source to the database. Name Hash User Description Corresponding SampleData File(s) Treatments Treatments Data source providing treatment type, length, and cost. MDOT CPM Treatments 1992 to 1994.tab Treatment Details Treatment- Details Data source providing project type, surface type, and other details specific to each maintenance project performed. MDOT Sam MDOT Performance.tab ple Data.tab Performance Performance Data source providing performance data on an annual basis for pavement sections. MDOT-Year1.tab MDOT-Year2.tab MDOT-Year3.tab Table E-3. Sample data sources and files.

Loading Sample data into the pavement-Maintenance database 113 From the data source input screen enter in the name, hash, and user description for a data source as seen in Table E-3. Press “save” when complete to store the new data source and then select it from the drop-down menu. Next, select the data file to be uploaded by clicking on the “browse” button.

114 Framework for a pavement-Maintenance database System To select the file associated with the data source described in Table E-3, insert the CD into the drive on your computer. To access the CD-ROM drive from the virtual terminal it must be connected to the virtual machine. To verify and enable the CD-ROM drive, click on the “player” button on the upper left hand corner of the VMware Player and ensure that “CD/DVD” is checked under “removable devices.” Now click on browse from the PMDb application and a file dialog box will appear. From the dialog box, select the “sample data” CD/DVD as seen below and navigate to the “sample data” folder and click open.

Loading Sample data into the pavement-Maintenance database 115 From the sample data folder, select the appropriate file that aligns with the data source selected, as seen in Table E-3. Once selected, click on “open” and the file name will be placed into the application interface for upload. Once the file path has been set, click on “upload” to process the file into PMDb. This will automatically lead to the next step.

116 Framework for a pavement-Maintenance database System Step 3. Click on “build archive” in the workflow and then click on “process file” to import the uploaded data file into the source record archive data table. When this step is complete you will be taken to the “translate index” step. The uploaded file format must adhere to the format defined within the report in order to successfully be processed. Step 4. Translate the source index files. This step can be used to clean and transform source data so that it aligns with routes defined within the inventory. For the sample data provided, no translations are required so the table listing will be blank.

Loading Sample data into the pavement-Maintenance database 117 Step 5. Preview the matches found in the source data. During this step the transformations are applied to the source data and compared against the original data. In the example below, note how the route index value has been modified. This will only occur if an index translation has been made. Click on “map fields” in the workflow at top to proceed to the next step. Step 6. Create the field mappings. This step is where the source field names are mapped to the PMDb data fields. One can add transformations to the source data as well during this step to allow for data to be cleansed or validated. To add a field mapping click on the “add a data field mapping” in the upper right as seen below.

118 Framework for a pavement-Maintenance database System Complete the entry fields for the field mappings for the data source you are working on as seen in Table E-4. For all non-specified inputs, use the default value. Once complete with each entry, click “save” and you will be returned to the field mapping listing page. Data Source Name Hash Archetype Source Field Destination Field Operation Treatments Treatment Type Gen-TrType Maintenance -General WORK TYPE Maintenance > General > Treatment Type Copy Treatments Total Cost Gen- TotalCost Maintenance -Cost COST Maintenance > Cost > Total Cost Copy Performance PCI Cond-PCI Condition- Overall Condition Index PCI Condition > Overall Condition Index > PCI Copy Treatment Details Surface Type Inv-Surface Pavement Surface- Surface Material SURFACE TYPE Inventory > Pavement Surface > Surface Material Copy Treatment Details Treatment Reason Inv- TrtReason Maintenance -General TREATMENT REASON Maintenance > General > Treatment Type Copy Treatment Details Treatment Type Inventory- ContractType Maintenance -General WORK TYPE Maintenance > General > Treatment Type Copy Table E-4. Sample data mappings.

Loading Sample data into the pavement-Maintenance database 119 Step 7. Import and process sample data records. Once you have successfully mapped the fields for the data source, click on “import data” at the top to proceed to the next step. To process the records and complete the import, click on “process system data record mappings” as seen below. Once this process completes, the data will have been imported into system records. At this point you have completed the upload process.

120 Framework for a pavement-Maintenance database System Perform System Maintenance Following completion of uploading a new batch of files, one must perform the system maintenance functions to rebuild the lookup tables and statistic tables that help preview the results for the user. The steps to perform system maintenance are as follows: Step 1. Click on “administration” from the top navigational menu as seen below. Step 2. Click on “rebuild lookup” and when complete click on “rebuild statistics.” Each process can take several minutes to complete based on the size of the database and the speed of the server.

Loading Sample data into the pavement-Maintenance database 121 Search and Download Data Once data has been uploaded and processed into the system, the search function located in the top-most menu enables the user to find sections that satisfy criteria across any data source and that are based on the system record segments that match the given criteria. To apply a filter, select “add a filter” from the active filters menu and select a filter type. A value filter allows you to match a particular value whereas a range filer is available for numeric entries and allows you to specify a range of values. In the example below, all segments of routes within the system that had a surface material of asphalt and PCI between 10 and 50 were found by using the search and filter mechanisms. In this example, the search result indicates that there are 6 miles of matching segments on M-28 and 11 miles of matching segments on US-41 in the sample data that both had an asphalt surface and a PCI between 10 and 50. To perform additional analysis or explore the additional data associated with these segments, download the source records or the system records using the buttons located at the bottom of the screen.

Abbreviations and acronyms used without definitions in TRB publications: A4A Airlines for America AAAE American Association of Airport Executives AASHO American Association of State Highway Officials AASHTO American Association of State Highway and Transportation Officials ACI–NA Airports Council International–North America ACRP Airport Cooperative Research Program ADA Americans with Disabilities Act APTA American Public Transportation Association ASCE American Society of Civil Engineers ASME American Society of Mechanical Engineers ASTM American Society for Testing and Materials ATA American Trucking Associations CTAA Community Transportation Association of America CTBSSP Commercial Truck and Bus Safety Synthesis Program DHS Department of Homeland Security DOE Department of Energy EPA Environmental Protection Agency FAA Federal Aviation Administration FAST Fixing America’s Surface Transportation Act (2015) FHWA Federal Highway Administration FMCSA Federal Motor Carrier Safety Administration FRA Federal Railroad Administration FTA Federal Transit Administration HMCRP Hazardous Materials Cooperative Research Program IEEE Institute of Electrical and Electronics Engineers ISTEA Intermodal Surface Transportation Efficiency Act of 1991 ITE Institute of Transportation Engineers MAP-21 Moving Ahead for Progress in the 21st Century Act (2012) NASA National Aeronautics and Space Administration NASAO National Association of State Aviation Officials NCFRP National Cooperative Freight Research Program NCHRP National Cooperative Highway Research Program NHTSA National Highway Traffic Safety Administration NTSB National Transportation Safety Board PHMSA Pipeline and Hazardous Materials Safety Administration RITA Research and Innovative Technology Administration SAE Society of Automotive Engineers SAFETEA-LU Safe, Accountable, Flexible, Efficient Transportation Equity Act: A Legacy for Users (2005) TCRP Transit Cooperative Research Program TDC Transit Development Corporation TEA-21 Transportation Equity Act for the 21st Century (1998) TRB Transportation Research Board TSA Transportation Security Administration U.S.DOT United States Department of Transportation

TRA N SPO RTATIO N RESEA RCH BO A RD 500 Fifth Street, N W W ashington, D C 20001 A D D RESS SERV ICE REQ U ESTED ISBN 978-0-309-44598-6 9 7 8 0 3 0 9 4 4 5 9 8 6 9 0 0 0 0 N O N -PR O FIT O R G . U .S. PO STA G E PA ID C O LU M B IA , M D PER M IT N O . 88 Fram ew ork for a Pavem ent-M aintenance D atabase System N CH RP Report 820 TRB