Welcome to LTPP InfoPave SQL Export, a feature for users familiar with the structure of the LTPP database (SDR) to export data in lieu of using Data Selection and Download or Table Export.

With just a few clicks through this tour, you’ll be ready to make custom selections of data as it is stored in the LTPP database. Ready?

The left hand side of the screen contains a list of LTPP database table names. To rapidly find a specific table use the filter at the top of the list to enter all or part of a table name. The desired table can be selected from the results by double-clicking on the table name or drag table and drop on the large window.

The field list for the table with data types and descriptive names will appear in the large window.

The resulting query to select all elements of the table will appear in the SQL Editor box.

In this block you can directly type regular SQL syntax. If you only wanted SPS-1 experiment sections you might edit the initial statement with the condition – “where SHRP_ID like ‘01%’

As soon as you start type, a screen message will appear indicating that it needs to be updated. Click on Refresh button after you finish your edits.

Starting a query from scratch can be done by clearing the SQL Editor. Clear will erase the SQL Editor and remove the selected table.

Query Properties parses a query you have written and permits you to write a query without worrying about syntax or spelling as you type. Editing a query in Query Properties automatically refreshes the text in SQL Editor.

You can select fields for output, perform mathematical operations using the pick list in Aggregate, assign a new field name as the Alias, sort the results by type and order, group data or assign criteria. The alias may auto-populate for some fields.

Date criteria should be entered as ‘DD-Mon-YYYY’.

If the criteria is an “Or” condition, check the placement of the parentheses carefully to make sure you agree with the default logic.

In the main window, the table listing allows both using a different table name and selecting only the fields desired in the export. There is no identification of the key fields.

Selecting desired fields is done by checking the boxes next to those field names. Checking the asterisk selects all field names. The same actions can occur by means of a drop down list that shows when clicking on the table name above the field list. The list also includes the option to delete the table from the query.

A different table name can be assigned with the setting icon in the upper right corner.

This option allows you to review the results of your query. In the case of queries that return a very large amount of data, an error message may appear indicating that the selection is too large to preview

The Options menus allow users to save or open a query on their computer or clear the existing query. It is also possible to import or export a query from your computer.

Not all data in LTPP applies to a single section or over the entire life of the pavement. To find examples of selecting data for specific locations or periods or linking data used for multiple sections to the appropriate sections, see Sample Query. All data in Data Selection and Download and Table Export already has all the linkages.

The SQL Export tool permits both joins and unions of tables. The details of joins require knowledge of the structure of the LTPP database and its key fields. This information is central to successfully associating data collected once with all the sections it applies to. This type of relationship exists in the Materials Testing, Climate and Traffic tables.

Similarly use of unions to create tables of SPS and GPS data where shared data exists requires knowledge of between table relationships. Examples are available in Sample Query.

After finalizing the query you can add it to your Data Bucket by selecting ‘Add to Data Bucket’ button.

On the Data Bucket page, you can submit the data for extraction by selecting the “Submit for Data Extraction”. At this point you may select file format, units and whether or not to include codes. Submitting the data for extraction runs the query to down load the data. You will receive an e-mail confirming your submission and then another e-mail when your data is available for download.