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.

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.

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.

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.

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.

Updated: 11/22/2013