U.S. Department of Transportation
Federal Highway Administration
1200 New Jersey Avenue, SE
Washington, DC 20590
Federal Highway Administration Research and Technology
Coordinating, Developing, and Delivering Highway Transportation Innovations
|This report is an archived publication and may contain dated technical, contact, and link information|
Publication Number: FHWA-RD-03-088
Date: November 2003
The pavement performance database was designed to store the majority of the data collected by the LTPP program for easy and convenient dissemination and use. The pavement performance database is a relational database originally implemented in Oracle® 5 format. As of this writing, the production database is implemented in Oracle 9i. To harness the power of relational databases to manipulate large amounts of data at a reasonable cost , most users prefer to obtain data from the production database in an alternate database format. (See chapter 14 for data request procedures.) Currently, the LTPP program is using Microsoft® Access® 2000 as a standard format for data releases. This may change in the future. International data users, who do not have access to the English-language version of Microsoft Access 2000, may wish to request customized extractions in other formats.
The overall structure of the database is based on the LTPP data collection and processing flow. LTPP data are collected and processed by four regional contractors. Each of these contractors is responsible for loading and processing the data for test sections in their region into regional databases. Each regional database contains data for the test sections in that region (the breakdown of States and Provinces by region is located in the REGIONS table of the database). Data from the regional databases are uploaded to the national database for consolidation and release to the public on a 6-month cycle.
The LTPP pavement performance database is a relational database, meaning that it is composed of separate, but related, tables of data. The importance of a relational database from a user's viewpoint is that all data are stored in a simple row/column format in tables (rows are sometimes referred to as records and columns are sometimes referred to as fields). Each row of data is uniquely identified by the values in a primary key column or a combination of columns (most of the tables in the LTPP database use multicolumn keys). In addition, relationships exist among the tables of the database that are represented by common data values stored in more than one table. For example, many data tables contain STATE_CODE and SHRP_ID columns, which are how test sections or projects are uniquely identified. These fields can be used to located data for a specific test section in many tables.
One critical characteristic of relational databases is that they are self-describing. This means that information about the structure of the database is represented in the same row and column format as the data itself. The data dictionary, stored in the LTPPDD table, includes much of this information. Users unfamiliar with the database should examine LTPPDD and learn how to use it. Alternatively, the LTPP program developed the Table Navigator software that allows a user to browse the database structure as a three-tiered representation consisting of tables, fields, and codes. Currently, Table Navigator can be obtained as a program running on Microsoft Windows® platforms. It is planned to migrate this software to an Internet Web platform.
Structured Query Language (SQL) is the standard language for controlling and interacting with relational databases. It is supported by modern relational database management systems (RDBMS's). For data users, one of the most important features of SQL is its ability to retrieve and combine data elements stored in multiple tables based on conditions set by the user. SQL can be used to create new tables, called queries, which contain data elements of interest in a specific analytical objective. To harness the full power and convenience of the LTPP database, users should become familiar with SQL. Some example data extractions using some fundamental SQL commands are provided in appendix C of this document. The data extraction examples in appendix C require a basic knowledge of SQL.
The QC checks applied to LTPP data are limited. It is not possible to inspect all of the data for all types of potential anomalies. As the program evolves and improvements are made to the data QC checks, level-E data included in previous releases may be reclassified.
Records with a status of less than E can be interpreted as:
Note: As of this writing, QC checks are not performed on supplemental sections. Supplemental sections are sections on an SPS project that are not part of the national experiment, but have been designed by the owning agency for their own purposes. The LTPP program is currently in the process of developing checks on these very important data sets.
LTPP data users assume the responsibility for conclusions based on interpretation of data collected by the LTPP program. Level-E data should not be considered as more reliable than non-level-E data. Likewise, non-level-E data should not be considered less reliable than level-E data. The record status for non-level-E data can be used as a relative indicator of potential issues that might exist for these data. As the LTPP program continues to evolve, users can expect changes to be made to LTPP data to improve their use in analyses.
LTPP test sections fall into one of two categories: General Pavement Studies (GPS) or Specific Pavement Studies (SPS). From the database viewpoint, the critical difference between GPS and SPS sections stems from the fact that multiple SPS sections are co-located on a single project. This co-location allows these sections to share climatic, traffic, and some materials data. Sections co-located on an SPS project are identified as sharing a STATE_CODE and PROJECT_ID in the SPS_PROJECT_STATIONS table. The TEST_SECTION field in this table contains the actual SHRP_ID of the test section. The SPS_PROJECT_STATIONS field also includes information about the location of these test sections relative to each other.
From a data user's viewpoint, another important difference between GPS and SPS test sections is that similar information is stored in different tables. Construction and general information for GPS test sections are stored in the INV tables. For example, the road designation and test section location coordinates for a GPS section are stored in the INV_ID table. For SPS sections classified in maintenance or rehabilitation experiments, location information is stored in the INV_ID tables using a project-level ID in the SHRP_ID field that applies to all test sections located on the project. Additionally, data about the pavement structure prior to treatment are stored in the INV module. Construction information for SPS test sections is stored in the SPS tables. Other general information for the new construction SPS experiments is also stored in the SPS tables. Location information for these experiments is in the SPS_ID table.
LTPP Database Tip!
The GPS_SPS field in the EXPERIMENT_SECTION table identifies whether a section is a GPS or SPS section. The SHRP_ID field for SPS sections is "smart". The first character in SHRP_ID for SPS sections is always a 0 or a letter. The second character in SHRP_ID for SPS sections identifies the experiment number. Over time, some SPS test sections are reassigned to GPS because of a rehabilitation activity; however, they retain the original SHRP_ID. However, all sections with a SHRP_ID beginning with a 0 are not SPS. A GPS test section in Texas has a SHRP_ID of 0001. Always check the GPS_SPS field in EXPERIMENT_SECTION before assuming that a section is an SPS section because of its SHRP_ID.
The database is divided into modules containing similar sets of tables. With the exception of the tables in the Administration module, the first three letters of the table name identify the module to which a particular table belongs. The modules are as follows: