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 Administration (ADM) module contains the master test section control table, tables that describe the structure and content of the database, and general comments tables. Unlike tables in the other modules, the first three letters of the table name do not identify tables in the ADM module. Tables in this module are EXPERIMENT_SECTION (the master control table for test sections), LTPPDD (the data dictionary that describes each field in each table), CODES (describes codes used in the database), COMMENTS_GENERAL (a general comments table), and REGIONS (contains a mapping of States and Provinces to LTPP operations administrative designations).
This can be considered the master table for the entire database . All test sections must have entries in this table. This table contains information on test section experiment assignments, monitoring assignments, dates when a test section changed experiments, dates when maintenance or rehabilitation treatments were applied, types of treatments applied, and monitoring status.
This table has three key fields: STATE_CODE, SHRP_ID, and CONSTRUCTION_NO. STATE_CODE and SHRP_ID uniquely identify a test section or SPS project.
STATE_CODE is a two-digit code used to identify the State or Province where a test section is located. This code is defined in the STATE_PROVINCE code type in the CODES table. These codes are, in part, based on the Federal Information Processing Standards (FIPS) codes and include codes for agencies not participating in the LTPP program.
SHRP_ID is a four-character identifier for the test section. For GPS test sections, the number has no significance other than being unique when combined with the STATE_CODE. For SPS sections, the second character represents the experiment number; the third and fourth characters identify the sections at the project; and the first character is typically "0" for the first such project constructed in a given State or Province, "A" for the second such project, and so on. SPS SHRP_ID numbers ending in "00" are a project-level identifier and do not represent actual test sections. However, when an SPS section changes experiment type because of a rehabilitation event, it will often be transferred into a GPS rehabilitation experiment, although the SHRP_ID will stay the same. The EXPERIMENT_NO field (described below) should always be used to determine the actual experiment classification.
CONSTRUCTION_NO identifies changes in the pavement structure caused by rehabilitation treatments or application of maintenance treatments. When a section first enters the LTPP program, it is assigned a CONSTRUCTION_NO of 1. CONSTRUCTION_NO is incremented by 1 for each subsequent maintenance or rehabilitation event regardless of its impact on the pavement structure. For example, crack sealing causes a new construction event to be generated, even though it does not cause a significant change in the experiment assignment or pavement structure.
CN_ASSIGN_DATE identifies the date that the CONSTRUCTION_NO became active. For a CONSTRUCTION_NO of 1, this is the date that the section entered the LTPP program, otherwise it is the date of the maintenance or rehabilitation activity that triggered the change in CONSTRUCTION_NO.
CN_CHANGE_REASON describes the maintenance or rehabilitation activity that triggered the change in CONSTRUCTION_NO. This is a code of the type MAINT_WORK.
RECORD_STATUS is described in the Quality Control section of this document. The RECORD_STATUS field in EXPERIMENT_SECTION reflects the availability and completeness of critical data relating to that section throughout the database.
GPS_SPS is a code to indicate whether a section is classified as a GPS or SPS experiment for that CONSTRUCTION_NO.
EXPERIMENT_NO is a code indicating to which GPS or SPS experiment the pavement section is assigned. This two-character code consists of a number followed by an optional suffix letter. The suffix is used for some experiments to indicate a subcategory of test sections. EXPERIMENT_NO is a code of the type EXPERIMENT.
STATUS is a code indicating the current monitoring status of a section. A null value indicates that the test section has been approved and has an active monitoring status. A value of "O" indicates that the test section has been placed "out of study" and no future monitoring measurements will be made. This field is set to O when a test section goes out of study. At that time, the STATUS field in all records in EXPERIMENT_SECTION with a matching STATE_CODE and SHRP_ID is set to O.
ASSIGN_DATE is the date when a test section is assigned to the LTPP experiment. The experiment designation for a test section is the combination of EXPERIMENT_NO and GPS_SPS fields in the record. When a section is first accepted into the LTPP program, ASSIGN_DATE is the acceptance date. ASSIGN_DATE must precede any LTPP monitoring measurements taken on the test section for the associated experiment. When a test section changes experiments because of rehabilitation, ASSIGN_DATE is the construction start date and should equal the CN_ASSIGN_DATE (i.e., ASSIGN_DATE(CN+1) =CN_ASSIGN_DATE(CN+1), if EXPERIMENT_NO(CN) ≠ EXPERIMENT_NO(CN+1), where CN is the CONSTRUCTION_NO).
DEASSIGN_DATE is the date when a test section changed to another experiment or was placed in the out-of-study status in the LTPP program (STATUS = O). This field should be null until a rehabilitation construction event occurs that causes a change in EXPERIMENT_NO or the test section goes out of test. When a test section changes experiments because of rehabilitation, the DEASSIGN_DATE for the previous CONSTRUCTION_NO (CN) should equal the CN_ASSIGN_DATE for the next CN (i.e., DEASSIGN_DATE(CN) = CN_ASSIGN_DATE(CN+1), if EXPERIMENT_NO(CN) ≠ EXPERIMENT_NO(CN+1)). If a maintenance-related construction event occurs that does not result in an experiment change, the DEASSIGN_DATE for the previous CN should equal the DEASSIGN_DATE for the next CN (i.e., DEASSIGN_DATE(CN) = DEASSIGN_DATE(CN+1) (even if NULL), if EXPERIMENT_NO(CN) = EXPERIMENT_NO(CN+1)).
SEAS_ID is an agency-specific SMP identification code indicating that SMP measurements were made for the corresponding construction number. SEAS_ID is set to A for the first SMP site installed in a State, B for the second site, and so on. This field is only populated for construction numbers in which SMP data have been collected. When a construction event occurs on an SMP test section that results in termination of its participation in the SMP, or if SMP monitoring is terminated prior to occurrence of a new construction event, the SEAS_ID is set to null in the EXPERIMENT_SECTION record corresponding to the new CN for which no SMP data are available.
SUPPLEMENTAL identifies supplemental test sections. A value of "S" identifies a supplemental test section.
EXP_SECT_RS is a QC summary field like RECORD_STATUS, except that it reflects the quality and completeness of data in the EXPERIMENT_SECTION table only, rather than the entire database.
BASIC_INFO_RS is a QC summary field that reflects the quality and availability of basic inventory information for the section.
PAV_STRUCT_RS is a QC summary field that reflects the quality and availability of pavement structure information in the TST_L05B table.
TRAFFIC_RS is a QC summary field that reflects the quality and availability of traffic data for the section.
The LTPPDD table is the online data dictionary for the LTPP pavement performance database. It defines each field in each table in the database. Critical fields include FIELDNAME, TABLENAME, and DESCRIPTION. This table is a vital reference when searching for appropriate fields for the extraction of data for research purposes.
FIELDNAME is the name of the specific field that is defined by the LTPPDD entry.
TABLENAME is the name of the table in which the field denoted by FIELDNAME resides. Table names generally begin with a three-letter indicator of the data module. For instance, the SMP_FROST_PENETRATION table is part of the SMP module.
DESCRIPTION is a short description of the field. For instance, the NORM_RESISTIVITY field has this entry under DESCRIPTION: "Normalized resistivity-It is the electrical resistivity of the soil at the measurement depth, relative to the extreme values at that depth."
DATA_TYPE specifies the Oracle format data type of the specified field. These fields are typically a VARCHAR (variable-length character field), DATE, or NUMBER (numeric field).
DATASHEET specifies the source of the data stored within the specified field. Typically, this is a paper datasheet number; however, it may be a filename or individual's name. Entries in this field may not be current or complete.
ITEM is the item number of the form denoted within the DATASHEET field. This is the origin of the data that reside within the specified field. Entries in this field may not be current or complete.
LEGALTXT is a description of the codes that are valid for this field. It can be linked to the CODETYPE field in the CODES table.
QA_MINIMUM indicates whether the level-C checks are applicable to this field. An "X" in the QA_MINIMUM field indicates that data must be present in the specified field for it to pass the level-C checks. Entries in this field may not be current or complete.
QA_RANGE indicates the acceptable range of values for this field for the data to pass the level-D range checks. Entries in this field may not be current or complete.
SHEETDATE is the latest revision date for the DATASHEET from which this field was extracted. Entries in this field may not be current or complete.
Many of the elements in the database use a code value to represent alternate standard entries in a field. The CODES table contains a description of the codes used in the LTPP database. To decipher a code value, the LEGALTXT field in the LTPPDD table can be used to link to the CODE_TYPE field in the CODES table.
CODETYPE is the code type as listed in the LEGALTXT field in the LTPPDD table.
CODE is the code value. Although most codes are numeric, some are alphanumeric; therefore, this field is coded as alphanumeric, which creates an apparent illogical sequence when the field is sorted in ascending or descending order.
DETAIL is the description of the code.
ADDL_CODE provides a second reference field for codes that require a combination of two codes to form a unique reference. The only two CODETYPES that use this field are COUNTY, in which ADDL_CODE corresponds to the STATE_PROVINCE code of the State or Province in which the county is located, and EXPERIMENT, in which the ADDL_CODE is "G" for GPS experiments and "S" for SPS experiments.
The CODETYPES table is directly related to the CODES table. Its primary purpose is to provide a general description of the CODETYPE fields contained in the CODES table. Direct links between LTPPDD and CODES, as provided by the Table Navigator software, make the information in this table redundant. This table may be removed from the database in the future.
The COMMENTS_GENERAL table contains general comments related to test section anomalies, general status, and other details that are not reflected in other data tables. Comments are entered in this table at the discretion of the LTPP regional data collection contractors.
The REGIONS table is perhaps the simplest table in the database. It consists of two fields-STATE_CODE and REGION_CODE. This table allows a user to sort State and Provincial agencies by the LTPP administrative region. In the past, this table had not been distributed since its use is primarily for internal LTPP operations.