Skip to contentU.S. Department of Transportation/Federal Highway Administration
Asset Management | Bridge Technology | Operations | Pavement
FHWA > Asset Management > Pavement Health Track > PHT Final Report > State PMS Data Reader and Converter

Enhancement of the Pavement Health Track (PHT) Analysis Tool Final Report

State PMS Data Reader and Converter

Introduction

Many states maintain their own pavement condition data in local pavement management systems (PMS) that are customized to each state's needs and priorities. The objective of this task was to provide an enhanced mechanism to read pavement condition information required for the PHT analysis directly from existing State pavement management systems when the data is not available in the standard HPMS2010 format.

Graphical User Interface

The State PMS Data Reader and Converter is implemented into the PHT graphical user interface in the form of an import wizard dialog window. The wizard proceeds through three logical steps in select and external data source, map and convert existing State PMS data to the PHT database fields, and optionally filter the source data to select a subset of records for analysis.

This first tab allows you to select the data source to read the highway data as shown in Figure 71.

A screen shot shows the window associated with the Datasource tab of the Import from External Datasource window. The user is presented with file type options, each of which opens up a list for browsing and selecting the desired file.
Figure 71. State PMS Reader/Converter - Data Sources

There are four options to select from when importing a State PMS data source:

  • The first option is to import data stored in a Microsoft® Access database. Using this option will also require you to select a source table within the Access database. Data cannot be read from multiple tables; therefore if the data reside in multiple tables, it will be necessary to design a query to combine all the data into a single table prior to importing it into the PHT analysis tool.
  • The second option is to import data from a dBase file.
  • The third option is to import data from a comma-delimited text file. When using this option, you will need to indicate if the first line of the source file contains field names. It is easier to create the field map if descriptive field names are provided.
  • The fourth option is to import data from any defined ODBC data source such as Oracle, FoxPro, Paradox, or even spreadsheets such as Excel.

The next tab, shown in Figure 72, allows the user to select the source table (if applicable) and define a field map between the data fields in the source data and those of the PHT data table. For each PHT field, a matching field that provides the data must be selected. Hard-coded value can be directly entered also or a field can be left blank if the source table has no matching item. The mapped field must have a compatible data type with the PHT field.

A scree shot shows the window associated with the Field Mapping tab in the Import from External Datasource window. The Field Map pane of the window provides a source table with entries for Destination Field, Type, and External Field. If applicable, a drop down menu appears for selecting the desired data field in the external field file.
Figure 72. State PMS Reader/Converter - Field Map

In addition to one-to-one field mapping, the import wizard also provides formulas to calculate a required value when it is not directly available in the source data. The formula builder provides a list of all of the data fields in the source data. The expression can use simple math, functions, and the values of the other fields in the record to calculate the new value as shown in Figure 73. The supported math and string functions are described in Table 12.

A screen shot shows the window associated with the Formula tool that allows the user to generate a formula using buttons for operators and selecting functions from a pane in the window.
Figure 73. State PMS Reader/Converter - Formula Builder

Table 12. Supported Math and String Functions
Function Prototype Description
SIN SIN(<cell>) Returns the sine of the specified angle
COS COS(<cell>) Returns the cosine of the specified angle
TAN TAN(<cell>) Returns the tangent of the specified angle
ASIN ASIN(<cell>) Returns the angle whose sine is the specified number
ACOS ACOS(<cell>) Returns the angle whose cosine is the specified number
ATAN ATAN(<cell>) Returns the angle whose tangent is the specified number
SINH SINH(<cell>) Returns the hyperbolic sine of the specified angle
COSH COSH(<cell>) Returns the hyperbolic cosine of the specified angle
TANH TANH(<cell>) Returns the hyperbolic tangent of the specified angle
ABS ABS(<cell>) Returns the absolute value of a specified number
EXP EXP(<cell>) Returns e raised to the specified power
LOG LOG(<cell>) Returns the logarithm of a specified number
LOG10 LOG10(<cell>) Returns the base 10 logarithm of a specified number
CEILING CEILING(<cell>) Returns the smallest integer greater than or equal to the specified number
RAND RAND(<cell>) Returns a random number
ROUND ROUND(<cell>) Rounds a value to the nearest integer
SIGN SIGN(<cell>) Returns a value indicating the sign of a number
SQRT SQRT(<cell>) Returns the square root of a specified number
LEFT LEFT(<cell>, <length>) Returns a substring from a string starting from the left-most character
RIGHT RIGHT(<cell>, <length>) Returns a substring from a string starting from the right-most character
LEN LEN(<cell>) Returns the length of the string
SUBSTRING SUBSTRING(<cell>, <index>, <length>) Returns a substring from a string, starting at any position using a 1-based index
CHARINDEX CHARINDEX(<string>, <cell>) Returns the index of the first occurrence of the specified case-sensitive character string
CASE CASE <cell> WHEN <cell> THEN <cell> ELSE <cell> END Compares an expression to a set of simple expressions to determine the result

The final tab, shown in Figure 74, provides an advanced option to filter the records in the source table prior to importing the data to the PHT table. This is useful to read a sub-set of the records that are in the source table. The filter wizard used to create the SQL clause to filter the data, or use a SQL Text window is available to enter the filter clause directly.

Two screen shots show windows associated with the Advanced tab of the Import from External Databases window. One shows the Filter Wizard tool, which allows the user to select criteria for a field. The other shows the SQL Text tool, which allows the user to build a filter using selection from vertical panes under Fields, Operators, Functions, and Values.
Figure 74. State PMS Reader/Converter - Advanced Filtering
Updated: 11/22/2013