: Data

Description:
The sample script returns the "State Code", "LTPP Section Identifier", "Layer Number", and "Average 28 Day Comp Strength" for each PCC layer that has information in the TST module. The script uses multiple joined tables to assign an age to all the compressive strength tests performed by the program, select only those performed at 28 days, and assign the properties to all appropriate layers. Because this data was only collected for SPS sections, the query uses the project layering information contained in TST_L05B to assign the average properties to all associated layers. The results are sorted by "State Code", "LTPP Section Identifier", and "Layer Number" in ascending order.

Script:
Select LAYER_STRENGTH.STATE_CODE As "State Code", LAYER_STRENGTH.SHRP_ID AS "LTPP Section Identifier", LAYER_STRENGTH.LAYER_NO AS "Layer Number", LAYER_STRENGTH.AVG_28_DAY_COMP_STRENGTH as "Average 28 Day Comp Strength" From (Select Distinct L05B_CODE.STATE_CODE, L05B_CODE.SHRP_ID, L05B_CODE.LAYER_NO, Round(Avg(PC01_AGE.COMP_STRENGTH) Over(Partition By L05B_CODE.STATE_CODE, L05B_CODE.PROJECT_ID, L05B_CODE.PROJECT_LAYER_CODE),0) As AVG_28_DAY_COMP_STRENGTH From (Select TST_PC01.STATE_CODE, TST_PC01.SHRP_ID, TST_PC01.LAYER_NO, TST_PC01.FIELD_SET, TST_PC01.TEST_NO, TST_PC01.LOC_NO, TST_PC01.SAMPLE_NO, TST_PC01.COMP_STRENGTH, TST_PC01.TEST_DATE, TST_FRESH_PCC.DATE_SAMPLED, TST_PC01.TEST_DATE - TST_FRESH_PCC.DATE_SAMPLED as TEST_AGE From TST_PC01 Inner Join TST_FRESH_PCC On TST_PC01.STATE_CODE = TST_FRESH_PCC.STATE_CODE And TST_PC01.SHRP_ID = TST_FRESH_PCC.SHRP_ID And TST_PC01.FIELD_SET = TST_FRESH_PCC.FIELD_SET And TST_PC01.LOC_NO = TST_FRESH_PCC.LOC_NO Where TST_PC01.TEST_DATE - TST_FRESH_PCC.DATE_SAMPLED = 28) PC01_AGE Right Join (Select Distinct TST_L05B.STATE_CODE, Substr(TST_L05B.SHRP_ID, 1,2)||'00' as PROJECT_ID, TST_L05B.PROJECT_LAYER_CODE, TST_L05B.SHRP_ID, TST_L05B.LAYER_NO From TST_L05B Where TST_L05B.PROJECT_LAYER_CODE Is Not Null) L05B_CODE On PC01_AGE.STATE_CODE = L05B_CODE.STATE_CODE And PC01_AGE.SHRP_ID = L05B_CODE.SHRP_ID And PC01_AGE.LAYER_NO = L05B_CODE.LAYER_NO) LAYER_STRENGTH Where LAYER_STRENGTH.AVG_28_DAY_COMP_STRENGTH Is Not Null Order By LAYER_STRENGTH.STATE_CODE, LAYER_STRENGTH.SHRP_ID, LAYER_STRENGTH.LAYER_NO

Description:
This sample script returns the “State Code,” “LTPP Section Identifier,” “Survey Date,” and magnitude of “Alligator Cracking” at each severity level for asphalt concrete manual distress surveys for the state of Georgia (state code 13). The alligator cracking data for a different state can be returned by changing the state code in the “where” expression.

Script:
Select MON_DIS_AC_REV.STATE_CODE As "STATE CODE", MON_DIS_AC_REV.SHRP_ID As "LTPP SECTION IDENTIFIER", MON_DIS_AC_REV.SURVEY_DATE As "SURVEY DATE", MON_DIS_AC_REV.GATOR_CRACK_A_L As "LOW SEVERITY ALLIGATOR CRACKIN", MON_DIS_AC_REV.GATOR_CRACK_A_M As "MEDIUM SEVERITY ALLIGATOR CRAC", MON_DIS_AC_REV.GATOR_CRACK_A_H As "HIGH SEVERITY ALLIGATOR CRACKI" From MON_DIS_AC_REV Where MON_DIS_AC_REV.STATE_CODE = 13 Order By "LTPP SECTION IDENTIFIER", "SURVEY DATE"

Description:
This sample script returns the "State Code", "LTPP Section Identifier", "Survey Date", and "Construction Number”, “Point Location”, “Dipstick Reading 1” through “-10”, and total “Elevation Difference” for a given selected section. The transverse profile information in MON_T_PROF_PROFILE is normalized, meaning that it only represents the deviation from a straight line connecting the lane edges. This script uses the information in MON_T_PROF_CROSS to un-normalize the values, and bring them back close to what the original dipstick readings would have been – which represent the actual transverse profile. The values are not exact because there is not enough precision in MON_T_PROF_PROFILE to truly re-create the readings, but they are within a millimeter, which is close enough to provide a meaningful representation of the transverse profile. The results are sorted by "Point Location” and “Survey Date" in ascending order.

Script:
Select MON_T_PROF_PROFILE.STATE_CODE As "State Code", MON_T_PROF_PROFILE.SHRP_ID As "LTPP Section Identifier", MON_T_PROF_PROFILE.SURVEY_DATE As "Survey Date", MON_T_PROF_PROFILE.CONSTRUCTION_NO As "Construction Number", MON_T_PROF_PROFILE.POINT_LOC As "Point Location", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X2+MON_T_PROF_PROFILE.Y2,1) As "Dipstick Reading 1", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X3+MON_T_PROF_PROFILE.Y3,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X2+MON_T_PROF_PROFILE.Y2,1) As "Dipstick Reading 2", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X4+MON_T_PROF_PROFILE.Y4,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X3+MON_T_PROF_PROFILE.Y3,1) As "Dipstick Reading 3", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X5+MON_T_PROF_PROFILE.Y5,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X4+MON_T_PROF_PROFILE.Y4,1) As "Dipstick Reading 4", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X6+MON_T_PROF_PROFILE.Y6,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X5+MON_T_PROF_PROFILE.Y5,1) As "Dipstick Reading 5", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X7+MON_T_PROF_PROFILE.Y7,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X6+MON_T_PROF_PROFILE.Y6,1) As "Dipstick Reading 6", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X8+MON_T_PROF_PROFILE.Y8,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X7+MON_T_PROF_PROFILE.Y7,1) As "Dipstick Reading 7", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X9+MON_T_PROF_PROFILE.Y9,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X8+MON_T_PROF_PROFILE.Y8,1) As "Dipstick Reading 8", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X10+MON_T_PROF_PROFILE.Y10,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X9+MON_T_PROF_PROFILE.Y9,1) As "Dipstick Reading 9", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X11+MON_T_PROF_PROFILE.Y11,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X10+MON_T_PROF_PROFILE.Y10,1) As "Dipstick Reading 10", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X12+MON_T_PROF_PROFILE.Y12,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X11+MON_T_PROF_PROFILE.Y11,1) As "Dipstick Reading 11", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X13+MON_T_PROF_PROFILE.Y13,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X12+MON_T_PROF_PROFILE.Y12,1) As "Dipstick Reading 12", Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X14+MON_T_PROF_PROFILE.Y14,1)-Round(MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF/MON_T_PROF_CROSS_SLOPE.T_PROF_WIDTH*MON_T_PROF_PROFILE.X13+MON_T_PROF_PROFILE.Y13,1) As "Dipstick Reading 13", MON_T_PROF_CROSS_SLOPE.ELEVATION_DIFF As "Elevation Difference" From MON_T_PROF_PROFILE Inner Join MON_T_PROF_CROSS_SLOPE On MON_T_PROF_PROFILE.STATE_CODE = MON_T_PROF_CROSS_SLOPE.STATE_CODE And MON_T_PROF_PROFILE.SHRP_ID =MON_T_PROF_CROSS_SLOPE.SHRP_ID And MON_T_PROF_PROFILE.SURVEY_DATE = MON_T_PROF_CROSS_SLOPE.SURVEY_DATE And MON_T_PROF_PROFILE.POINT_LOC = MON_T_PROF_CROSS_SLOPE.POINT_LOC Where MON_T_PROF_PROFILE.STATE_CODE = 18 And MON_T_PROF_PROFILE.SHRP_ID = '1028' Order By MON_T_PROF_PROFILE.POINT_LOC, MON_T_PROF_PROFILE.SURVEY_DATE

Description:
This sample script returns the “State Code,” “LTPP Section Identifier,” “Visit Date,” “Avg Left Wheel Path IRI,” and “Avg Right Wheel Path IRI” for each visit number. Each visit number typically includes multiple IRI values for both the left and right wheel paths; this script averages all of the IRI values corresponding to a particular visit number and wheel path. The “MON_HSS_PROFILE_SECTION” table (the table that contains the IRI values) is joined to the “MON_HSS_VISIT_NO” table (the table that contains visit number information). The results are sorted by “State Code,” “LTPP Section Identifier,” and “Visit Date” in ascending order.

Script:
Select MON_HSS_VISIT_NO.STATE_CODE As "STATE CODE", MON_HSS_VISIT_NO.SHRP_ID As "LTPP SECTION IDENTIFIER", MON_HSS_VISIT_NO.VISIT_DATE As "VISIT DATE", ROUND( Avg(MON_HSS_PROFILE_SECTION.IRI_LEFT_WHEEL_PATH),2) As "AVG LEFT WHEEL PATH IRI", ROUND(Avg(MON_HSS_PROFILE_SECTION.IRI_RIGHT_WHEEL_PATH),2) As "AVG RIGHT WHEEL PATH IRI" From MON_HSS_PROFILE_SECTION Inner Join MON_HSS_VISIT_NO On MON_HSS_PROFILE_SECTION.VISIT_NO = MON_HSS_VISIT_NO.VISIT_NO Group By MON_HSS_VISIT_NO.STATE_CODE, MON_HSS_VISIT_NO.SHRP_ID, MON_HSS_VISIT_NO.VISIT_DATE, MON_HSS_PROFILE_SECTION.VISIT_NO Order By "STATE CODE", "LTPP SECTION IDENTIFIER", "VISIT DATE"

Description:
This sample script returns the “State Code,” “LTPP Section Identifier,” “Survey Date,” and “Average Wheelpath Fault” for all SPS-2 manual distress surveys. The script uses a joined table to select only SPS-2 projects. The results are sorted by “State Code”, “LTPP Section Identifier”, and “Survey Date” in ascending order.

Script:
Select MON_DIS_JPCC_FAULT_SECT.STATE_CODE As "STATE CODE", MON_DIS_JPCC_FAULT_SECT.SHRP_ID As "LTPP SECTION IDENTIFIER", MON_DIS_JPCC_FAULT_SECT.SURVEY_DATE As "SURVEY DATE", MON_DIS_JPCC_FAULT_SECT.AVG_WHEELPATH_FAULT As "AVERAGE WHEELPATH FAULT" From MON_DIS_JPCC_FAULT_SECT Inner Join EXPERIMENT_SECTION On MON_DIS_JPCC_FAULT_SECT.STATE_CODE = EXPERIMENT_SECTION.STATE_CODE And MON_DIS_JPCC_FAULT_SECT.SHRP_ID = EXPERIMENT_SECTION.SHRP_ID And MON_DIS_JPCC_FAULT_SECT.CONSTRUCTION_NO = EXPERIMENT_SECTION.CONSTRUCTION_NO Where EXPERIMENT_SECTION.GPS_SPS = 'S' And EXPERIMENT_SECTION.EXPERIMENT_NO = '2' Order By "STATE CODE", "LTPP SECTION IDENTIFIER", "SURVEY DATE"

Description:
Code Definitions in the TST_L05B table

Script:
select * from codetypes ct where exists (select 'x' from ltppdd ld where ld.codetype = ct.codetype and tablename = 'TST_L05B') order by codetype

Description:
Code values in the TST_L05B table

Script:
select * from codes cs where exists (select 'x' from ltppdd ld where ld.codetype = cs.codetype and tablename = 'TST_L05B') order by codetype, code

Description:
Explanation of TST_L05B (LTPPTD)

Script:
Select * from LTPPTD where tablename = 'TST_L05B'

Description:
This sample script returns the "State Code", "LTPP Section Identifier", "Layer Number", and "Approximate Fracture Date" for each fractured PCC layer present in the LTPP program. The script uses multiple tables to combine date information on fractured layers that existed before the LTPP program was initiated (INV_MAJOR_IMP) and layers that were placed as part of the program (RHB_IMP). The information available before the initiation of the program is not as specific as the newer data, and so the year is converted into a date by assuming that the activity occurred mid-year. The results are sorted by "State Code", "LTPP Section Identifier", and "Layer Number" in ascending order.

Script:
Select TST_L05B.STATE_CODE As "State Code", TST_L05B.SHRP_ID As "LTPP Section Identifier", TST_L05B.LAYER_NO As "Layer Number", To_Date('06/15/'||INV_MAJOR_IMP.MAJOR_IMP_YEAR, 'mm/dd/yyyy') As "Approximate Fracture Date" From TST_L05B Inner Join INV_MAJOR_IMP On TST_L05B.STATE_CODE = INV_MAJOR_IMP.STATE_CODE And TST_L05B.SHRP_ID = INV_MAJOR_IMP.SHRP_ID Where TST_L05B.MATL_CODE = 341 And INV_MAJOR_IMP.MAJOR_IMP_TYPE in(46,47) Union Select RHB_IMP.STATE_CODE As "State Code", RHB_IMP.SHRP_ID As "LTPP Section Identifier", FRAC_LAYERS.LAYER_NO As "Layer Number", RHB_IMP.IMP_DATE As "Approximate Fracture Date" From RHB_IMP Inner Join (Select distinct TST_L05B.STATE_CODE, TST_L05B.SHRP_ID, TST_L05B.MATL_CODE, TST_L05B.LAYER_NO From TST_L05B Where TST_L05B.LAYER_TYPE = 'PC' And (TST_L05B.MATL_CODE = 341 Or TST_L05B.COMMENT_NOTE like '%CRACK%' Or TST_L05B.COMMENT_NOTE Like '%RUBB%' Or TST_L05B.COMMENT_NOTE Like '%FRACTURE%')) FRAC_LAYERS On RHB_IMP.STATE_CODE = FRAC_LAYERS.STATE_CODE And RHB_IMP.SHRP_ID = FRAC_LAYERS.SHRP_ID Where RHB_IMP.IMP_TYPE in ('46','47') Order By "State Code", "LTPP Section Identifier", "Layer Number"

Description:
Information on fields included in TST_L05B (LTPPDD)

Script:
Select tablename, fieldname, description, data_type, units, codetype from LTPPDD where tablename = 'TST_L05B'

Description:
This sample script returns the “State Code,” “LTPP Section Identifier,” most recent “Estimation Year,” and most recent “AADT” for all active test sections. The script uses multiple joined tables to return the AADT for the most recent year with available AADT data as well as return information for active sections only. The results are sorted by “State Code” and “LTPP Section Identifier” in ascending order.

Script:
Select TRF_MON_EST_ESAL.STATE_CODE As "State Code", TRF_MON_EST_ESAL.SHRP_ID As "LTPP Section Identifier", TRF_MON_EST_ESAL.YEAR_MON_EST As "Estimation Year", TRF_MON_EST_ESAL.AADT_ALL_VEHIC As "AADT" From TRF_MON_EST_ESAL Inner Join (Select TRF_MON_EST_ESAL.STATE_CODE, TRF_MON_EST_ESAL.SHRP_ID, Max(TRF_MON_EST_ESAL.YEAR_MON_EST) As YEAR_MON_EST From TRF_MON_EST_ESAL Where TRF_MON_EST_ESAL.AADT_ALL_VEHIC Is Not Null Group By TRF_MON_EST_ESAL.STATE_CODE, TRF_MON_EST_ESAL.SHRP_ID) TRF On TRF_MON_EST_ESAL.STATE_CODE = TRF.STATE_CODE And TRF_MON_EST_ESAL.SHRP_ID = TRF.SHRP_ID And TRF_MON_EST_ESAL.YEAR_MON_EST = TRF.YEAR_MON_EST Inner Join EXPERIMENT_SECTION On TRF_MON_EST_ESAL.STATE_CODE = EXPERIMENT_SECTION.STATE_CODE And TRF_MON_EST_ESAL.SHRP_ID = EXPERIMENT_SECTION.SHRP_ID Where EXPERIMENT_SECTION.STATUS Is Null Order By TRF_MON_EST_ESAL.STATE_CODE, TRF_MON_EST_ESAL.SHRP_ID

Description:
This sample script returns the “State Code,” “LTPP Section Identifier,” “Layer Type Code,” and “Representative Thickness” for all active test sections. Each layer in the LTPP PPDB has a corresponding layer type code such as AC (asphalt concrete) and GB (granular base). The script adds the thickness of each layer with the same layer type code for each section. The script uses a joined table to return only data corresponding to the most recent construction number. The results are sorted by “State Code" , “LTPP Section Identifier," and “Layer Type Code” in ascending order.

Script:
Select TST_L05B.STATE_CODE As "STATE CODE", TST_L05B.SHRP_ID As "LTPP SECTION IDENTIFIER", TST_L05B.LAYER_TYPE As "LAYER TYPE CODE", Sum(TST_L05B.REPR_THICKNESS) As "REPRESENTATIVE THICKNESS" From TST_L05B Inner Join (Select EXPERIMENT_SECTION.STATE_CODE As "STATE CODE", EXPERIMENT_SECTION.SHRP_ID As "LTPP SECTION IDENTIFIER", Max(EXPERIMENT_SECTION.CONSTRUCTION_NO) As "CONSTRUCTION NUMBER" From EXPERIMENT_SECTION Group By EXPERIMENT_SECTION.STATE_CODE, EXPERIMENT_SECTION.SHRP_ID) ES On TST_L05B.STATE_CODE = ES."STATE CODE" And TST_L05B.SHRP_ID = ES."LTPP SECTION IDENTIFIER" And TST_L05B.CONSTRUCTION_NO = ES."CONSTRUCTION NUMBER" Where TST_L05B.LAYER_TYPE != 'SS' Group By TST_L05B.STATE_CODE, TST_L05B.SHRP_ID, TST_L05B.LAYER_TYPE Order By "STATE CODE", "LTPP SECTION IDENTIFIER", "LAYER TYPE CODE"

Description:
This sample script returns the “State Code,” “LTPP Section Identifier,” “Experiment Classification Code,” and “Monitoring Status” for Texas (state code 48). The results are sorted in ascending order by “LTPP Section Identifier.” The test sections for a different state can be returned by changing the state code in the “where” expression.

Script:
Select Distinct EXPERIMENT_SECTION.STATE_CODE As "STATE CODE", EXPERIMENT_SECTION.SHRP_ID As "LTPP SECTION IDENTIFIER", EXPERIMENT_SECTION.STATUS As "STATUS CODE", EXPERIMENT_SECTION.GPS_SPS As "EXPERIMENT CLASSIFICATION CODE", EXPERIMENT_SECTION.EXPERIMENT_NO As "EXPERIMENT NUMBER CODE" From EXPERIMENT_SECTION Where EXPERIMENT_SECTION.STATE_CODE = 48 Order By "LTPP SECTION IDENTIFIER"