Speak

LTPP InfoPave SQL

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?

Start Tour
Close
Speak

Select Table

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.

Previous Next
End Tour
Speak

SQL Editor, Refresh and Clear

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.

Previous Next
End Tour
Speak

Query Properties

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.

Previous Next
End Tour
Speak

Select Fields

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.

Previous Next
End Tour
Speak

Preview Data

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

Previous Next
End Tour
Speak

Options and Sample Query

The Options button allows you to Open, Save or Clear a 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.

Previous Next
End Tour
Speak

Querying Multiple Tables

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.

Previous Next
End Tour
Speak

Add to Data Bucket

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.

Previous
End Tour

: Data

SQL Export
  • Help Tour
SQL Export
SQL Export feature is designed for users familiar with Structured Query Language (SQL) programming. The selection of data using SQL scripts is made available through this feature. Users can type SQL scripts directly into the SQL Editor window or build it using Query Builder tools provided in this feature. There are sample SQL codes available, and users can share their SQL codes with others. However, the information regarding table content, field definitions, data types, units, and codes are not provided for users extracting data using SQL Export. To obtain the information on table content, field definitions, data types, units, and codes and the codes themselves, the tables LTPPTD, LTPPDD, CodeTypes, and Codes should be included in the data export. See the Sample Query dialog for examples.

 

 

    SELECT Default
    DISTINCT
    DISTINCTROW
    ALL
    TOP PERCENT
    INTO
    SELECT Default
    DISTINCT
    ALL
    Fetch FIRST
    SELECT Default
    DISTINCT
    ALL
    FIRST
    SKIP
    SELECT Default
    DISTINCT
    ALL
    TOP ( )
    SELECT Default
    DISTINCT
    ALL
    TOP PERCENT WITH TIES
    INTO
    GROUP BY ALL
    SELECT Default
    DISTINCT
    ALL
    UNIQUE
    SELECT Default
    DISTINCT
    ALL
    LIMIT
    OFFSET
    INTO TEMPORARY
    TABLE
    SELECT Default
    DISTINCT
    ALL
    LIMIT
    OFFSET
    SELECT Default
    DISTINCT
    ALL
    SELECT All records
    FIRST
    TOP STARTING AT
    SELECT Default
    DISTINCT
    ALL
    TOP
    SELECT Default
    DISTINCT
    ALL
    TOP PERCENT WITH TIES
    GROUP BY Default
    CUBE
    ROLLUP
    OPTIONS HIGH PRIORITY
    STRAIGHT JOIN
    SQL BIG RESULT
    SQL BUFFER RESULT
    SQL SMALL RESULT
    SQL CALC FOUND ROWS

    Cache Option:
    SQL CACHE
    SQL NO CACHE
    GROUP BY WITH ROLLUP
    LIMIT
    OFFSET
    LOCK FOR UPDATE IN SHARE MODE
    Query Properties
    SQL Editor

    Please enter query name:

     
    Required

    Please enter query name:

     
    Required
     
    Required
     

    Please select query file (*.txt or *.sql):

     
    Required

    Following is the preview of selected data:

     
     
    Query Name Date
     
    Query Name

    Please enter query name to add in data bucket: