Framework for a Pavement-Maintenance Database System (2016)

Below is the uncorrected machine-read text of this chapter, intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text of each book. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.

60 C H A P T E R 5 PMDb is a web-based database framework that can be run and installed in both a desktop environment to serve an individual user as well as a server environment to serve many individuals. This chapter describes the steps involved in using the browser application and illustrates the use through a series of example applications using sample data. Workflow Description PMDb has four major workflows available, all of which are focused on enabling designated users to optimize their use of the application. • Adding or editing route inventory data. This workflow is required to create the route inven- tory onto which source event data can be attributed. • Adding source data. This workflow enables administrators to upload, translate, and map data into PMDb. • Extracting data from the database system. This workflow enables users to download data or data subsets for analysis, manipulation, and review. • Managing glossary terms. Glossary terms allows PMDb administrators to review, add, and edit terms in the PMDb glossary. These workflows are discussed in greater depth in the following sections. Adding or Editing Route Inventory Data PMDb provides a workflow to create route inventory records within PMDb. The steps include: 1. Add a route, 2. Edit a route, and 3. Regenerate system records. Add a Route The “add a route” function is available from the route inventory interface as seen in Figure 22. When users click on the add a route button, they will be prompted to enter in the state, route name, minimum ARM, and maximum ARM for the route. Additional information on rural cost and functional class may also be entered. Long routes may extend through various rural codes and functional classes. Each unique route segment should be entered discretely into PMDb. Once complete, users need to click save and will be shown the saved record, as seen in Figure 23. Pavement-Maintenance Database Workflow and Applications

Pavement-Maintenance Database Workflow and Applications 61 Figure 22. Screenshot of interface for adding a route. Figure 23. Screenshot of the completed route inventory interface.

62 Framework for a Pavement-Maintenance Database System Edit a Route Similar to adding a route, users can “edit a route” definition by clicking on the pencil icon as seen in Figure 24. The subsequent interface will allow users to edit the route data in the same manner by which they originally added the route data. Regenerate System Records Once route data has been added a pre-processing step to “regenerate system records” must be performed. To perform this step, users need to click on the regenerate system records button as seen in Figure 25 from the route inventory entry screen. This step will pre-populate the system record with the segments required for dynamic population during source record ingestion. Once system records have been generated, a confirmation screen, as shown in Figure 26, will be visible. Adding Source Data The PMDb framework provides a comprehensive process to upload source data. The steps are described further in this section and include: 4. Upload, 5. Build archive, 6. Translate index, 7. Preview matches, 8. Map fields, and 9. Import data. Upload The “upload” a data file function gets users started on the adding source data workflow process. A screenshot is shown in Figure 27. To specify a data source, users with appropriate Figure 24. Screenshot of interface for editing route information.

Pavement-Maintenance Database Workflow and Applications 63 Figure 26. Screenshot of successful system record regeneration following addition of route. Figure 25. Screenshot of interface used to regenerate system records. permissions can either select the data source from the drop-down list or use “add a data source” to include a name and a hash designation for the source data file if it is not listed. If a data source needs to be added, a prompt will appear as seen in Figure 28. Users need to enter the data source name and a hash, which defines the uniqueness of the dataset (the server file- system folder by which the data should be stored for the organization). Once a data source has been created, it will appear in the list of data sources available within PMDb, and can be used to locate and update the data file. When users have successfully uploaded a data file to PMDb, a screen similar to the screen- shot, as shown in Figure 29, will appear. Users need to click on “build archive” to proceed to the next step.

64 Framework for a Pavement-Maintenance Database System Figure 27. Screenshot of the data file upload interface. Figure 28. Screenshot of interface for adding a new data source. Figure 29. Screenshot of an uploaded data file.

Pavement-Maintenance Database Workflow and Applications 65 Build Archive During the “build archive” step, data that was in the original data source is uploaded into PMDb and placed in the source record archive. To execute the process, users will have to click on the process file button, as shown in Figure 30. Imperfect input data will generate an error message. Translate Index “Translate index” is done once per data source. This allows users to create a consistent source for the data and ensures the inclusion of key fields in the data source for it to be uploaded suc- cessfully. This involves adding index translations using the interface seen in Figure 31, whereby users need to select the data source and source index field and add a description of the translation to be performed. Defining the sequence of translations is important if multiple translations are planned. The transformation fields enable the setting of a transformation type and the values to be found and replaced in the transformation match and transformation replace fields. As the name indicates, the default value field allows users to set a default value for the field where no value currently exists. Preview Matches Once users have completed defining or reviewing the source record index translations, they need to click on “preview matches” to dynamically review the results of the translation. This will display a list of the existing data in gray and the post-translated data in black, as seen in Figure 32. Users can compare how the translations ended up and return to translate index if further refinement is needed. This function is especially useful to determine how many source records match an existing route and to identify additional translations that need to be made. Map Fields To map the source data fields to specific data fields within PMDb, users need to click on “map fields.” This step involves identifying the data source, identifying the record name, and recording a record hash—a unique name for the record (Figure 33). The record archetype identifies the grouping/data element for the record. Source and destination fields are needed to identify the original column name and find out where it is to be mapped. Action and ordinal sequence of events are set at their default values of copy and 0. The active field is set at 1. The final action before saving is to set the transformation type. Users will have to choose between the options of none, regular expression, replace, value set, and validation. Figure 30. Interface for building the source record archive from an uploaded data file.

66 Framework for a Pavement-Maintenance Database System Figure 32. Screenshot of source index translation matches during upload workflow. Figure 31. Interface to specify a source index field translation definition.

Pavement-Maintenance Database Workflow and Applications 67 Import Data During “import data,” data and transformations populate the systems record database. This step can take a significant amount of time depending on the amount of data being processed. A screenshot of the step is seen in Figure 34. Extracting Data from the Database System The “export data” function allows administrators to extract and download data from PMDb for review, analysis, manipulation, or other operations. PMDb provides a simplified process to extract both source data and normalized system data records for further analysis. The steps are described further in this section and include: 1. Apply filters, 2. View summarized results, and 3. Download data. Apply Filters The search function located at the top of the screen is driven by the use of data filters as shown in Figure 35. The filter function allows users to find pavement sections within the database whereby certain conditions are met to allow that data to be downloaded for further analysis. Either a value filter or a range filter must be applied to display the desired data subset. A value filter, as seen in Figure 36, allows users to select a specific value for any attribute within PMDb. The result set will return all system records that contain the exact match. Figure 33. Screenshot of field mapping edit interface.

68 Framework for a Pavement-Maintenance Database System Figure 34. Screenshot of the import data step in the upload workflow. Figure 35. Screenshot of the active filter interface in the search function. Figure 36. Value filter interface.

Pavement-Maintenance Database Workflow and Applications 69 A range filter allows users to indicate a range of values in the active filter interface. Only attri- butes that can be queried by a numeric range will be visible in the “select a range filter” box, as seen in Figure 37. View Summarized Results Once a filter has been applied, the summarized results of the filter will be displayed to indi- cate what type of data has been returned. The data summary, as seen in Figure 38, indicates the amount of data that exists and can be returned to users for the selected pavement sections based on the filter criteria. This is helpful if users are looking for specific pavement sections that con- tain inventory, maintenance, and condition data. Download Data To download data for subsequent analysis, users are presented with two options at the bot- tom of the result list. The download source data presents data in its original format as it was uploaded in PMDb (Figure 39) and the download system data provides users with the system record segments generated after the data was mapped and translated. Managing Glossary Terms The PMDb framework has been populated with a large number of data terms and elements as described in this report. These terms are used to define what each attribute or value represents within the database and serve as a guide in identifying what information to collect. Within the PMDb browser application, these terms are also defined in a dynamic glossary which allows users to add new terms and modify existing terms, as required. The steps involved in adding a term and editing a term are described in this section. Adding Glossary Terms Once users have determined that a glossary term has not been defined, the glossary terms link under data management can be used to go to the listing page, which lists all existing terms. To add a new term, click on “add a term” and follow the on-screen prompts to enter a term name, uniform resource identifier (URI), and a one-paragraph definition as seen in Figure 40. The URI is a lowercase version of a single term or a lowercase and hyphenated version of two or more terms without any spaces between the terms (e.g., Analysis Period will be entered as analysis-period). Figure 37. Range filter interface.

70 Framework for a Pavement-Maintenance Database System The definition field is where the user will add the definition source, citations, and links, using the format seen in Figure 41. When users have completed adding the term, they need to click on “save” to return to the glossary term listing page, as shown in Figure 42. Editing Glossary Terms Users can also use the glossary terms function to edit a glossary term by rolling the mouse pointer over the appropriate term and clicking on the pencil icon, as shown in Figure 43. The term will open in edit mode, enabling users to make changes and updates on an interface screen similar to that of adding a glossary term (Figure 44). Applications and Case Studies To demonstrate the use of PMDb, sample data and analysis scenarios were generated to illus- trate several possible maintenance applications for asphalt and concrete pavements. Sample data were generated in part from the existing data provided by state agencies as well as from a random data generator (Figure 45). All data and illustrative examples presented in this report are not accurate representations of existing state agency data. The data have been altered and Figure 38. Browser application interface displaying search results from filter.

Pavement-Maintenance Database Workflow and Applications 71 Figure 39. Example of downloaded source record data file. Figure 40. Interface for editing a glossary term.

72 Framework for a Pavement-Maintenance Database System Figure 42. Screenshot for saving a glossary term. >**Source:** Preservation Approaches for High-Traffic-Volume Roadways (SHRP 2, TRB 2011) >**Reference:**[onlinepubs.trb.org/onlinepubs/shrp2/SHRP2_S2-R26-RR- 1.pdf)](http://onlinepubs.trb.org/onlinepubs/shrp2/SHRP2_S2-R26-RR-1.pdf) Note that if a link to your reference is not available, please use the following format: >**Reference:** Author, Title, Publication, Date, Page Number. > **Category:** [Maintenance](#dynamicglossary/category/maintenance) Figure 41. Syntax for entering a glossary term definition.

Pavement-Maintenance Database Workflow and Applications 73 Figure 44. Interface for editing an existing glossary term. Figure 43. Interface for selecting a glossary term to edit.

74 Framework for a Pavement-Maintenance Database System supplemented to help demonstrate the use of PMDb and should in no way be construed as attempting to provide a conclusion regarding pavement-maintenance activity or effectiveness. The following sections describes several possible applications of PMDb. Examining Chip Seal Effectiveness When Used as a Preventive Measure versus a Reactive Measure An agency may use a chip seal as both a preventive maintenance treatment and as a reactive (stopgap) application. Sample data were generated to illustrate how available treatment and performance data from PMDb can be used such that the expected purpose for using the chip seal could be determined. Some reasons for an agency to perform such an examination include providing improved analysis of preventive maintenance treatment performance by separating out of any analysis the use of the treatment in non-preventive applications and examining the effectiveness of chip seals applied at different times and conditions in the life of a pavement. Data Elements and Attributes To conduct this illustration, a dataset containing the following data elements was generated to describe the maintenance sections: • Pavement section elements (state, route, direction, lane, begin reference point, end reference point, year, month, day, end year, end month, end day), • Inventory elements, Figure 45. Screenshot of sample data in Excel format.

Pavement-Maintenance Database Workflow and Applications 75 • Surface type, • Maintenance elements for chip seal, • Treatment name, • Treatment type, • Treatment reason, • Condition elements at treatment year and after 3 years, and • Overall condition index. Performing the Analysis The steps to perform the analysis include: • Develop a primary filter for all the chip seal treatment sections. • Select and download the data elements listed above. • Export to Excel and illustrate analysis case showing the effectiveness of chip seals applied under different treatment applications (preventive versus reactive) by examining conditions of pavement performance as well as differing pavement life. Examining the Effect of Thin Asphalt Overlays on Existing Asphalt Pavements Thin HMA overlays are commonly used as a maintenance treatment. This case study will examine the effect of thin HMA overlays placed on existing asphalt pavements by illustrating how historical performance data can be used to quantify the change in one or more condition measures as well as the duration of that change. Some reasons for an agency to perform such an examination include determination of the effect of thin HMA overlays on pavement perfor- mance and the impact of traffic on performance of thin HMA overlays. Data Elements and Attributes To conduct this illustration, a dataset containing the following data elements was generated: • Pavement section elements (state, route, direction, lane, begin reference point, end reference point, traffic, functional class, year, month, day, end year, end month, end day) are required for each element group below, • Inventory elements, • Surface type, • Age of surface, • Maintenance elements for thin HMA overlay, • Treatment name, • Treatment type, • Condition elements at treatment year and after 3 years, • IRI, and • Overall condition index. Performing the Analysis The steps to perform the analysis include: • Develop a primary filter for all thin HMA overlay treatment sections. • Identify and refine the filter for segments with the available data elements listed above. • Select and download the data elements listed above. • Export to Excel and illustrate analysis case showing the service life of thin HMA overlays by developing performance trend charts describing pre- and post-treatment performance. • To demonstrate how additional characteristics can be used in the analysis, group data by traf- fic levels to assess the impact of traffic on the performance of thin HMA overlays.

76 Framework for a Pavement-Maintenance Database System Examining the Cost and Performance of a Crack Sealing Program Crack sealing is commonly used as a maintenance treatment. This case study will illustrate how the effectiveness of a crack sealing program can be analyzed by combining the calculation of effectiveness with the associated costs to achieve that effect. A key reason for an agency to per- form such an examination is to determine the cost-effectiveness of their crack sealing program. Data Elements and Attributes To conduct this illustration, a dataset containing the following data elements was generated: • Pavement section elements (state, route, direction, lane, begin reference point, end reference point, traffic, functional class, year, month, day, end year, end month, end day) are required for each element group below, • Inventory elements, • Surface type, • Age of surface, • Maintenance elements for crack sealing, • Treatment name, • Treatment type, • Treatment reason, • Project cost, • Condition elements at treatment year and after 3 years, and • Overall condition index. Performing the Analysis The steps to perform the analysis include: • Identify and refine filter for segments with the available data elements listed above. • Select and download the data elements listed above. • Export to Excel and illustrate analysis case showing the treatment service life by developing performance trend charts describing pre- and post-treatment performance. • To demonstrate how cost characteristics can be used in the analysis, use treatment service life and associated cost (unit cost of project cost) to determine the benefit–cost ratio.