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.
43 C H A P T E R 4 Pavement-Maintenance Database Software Application The PMDb framework includes a two-part record processing system and a single page web application acting as the user interface to filter, search, and download records. In the first phase of ingestion, source files are archived to the server and records are then extracted from the files to be put into the source record database. During the second phase, archival records are used to generate logical normalized segments from which analysis and query activities can take place. This flow maintains the data integrity of the original source data while allowing for long-term flexibility and scalability in how the PMDb system filters, summarizes, and combines datasets for the end user. An overview of the architecture can be seen in Figure 6. Functional Elements Each functional element of the system is contained within the remote procedure call (RPC) application programming interface (API) and its purpose can be summarized as follows: ⢠Bulk data importing. This functional element is responsible for validating users and data sources prior to storing data on the server filesystem. The uploaded files are stored in a flat file system within the server using a hash-defined folder system per organization. Within this scheme, when files with the same name are uploaded, the last file uploaded always takes precedence, although the original file is maintained in the file system for archival purposes. This allows users to update a file they uploaded onto PMDb in the past and then override the existing file with updates while preserving the original file. ⢠Record archiving. This functional element is responsible for processing uploaded data source files from the server filesystem and processing them into the source record archive. During this step, validation routines are run on the source data and a source record index transla- tion is performed to match records to existing route records within PMDb. When this step is complete, the source records are ready for processing into the system records. ⢠Record processing. This functional element encapsulates the process by which the records in the source record archive are transformed and dynamically segmented into system records. System records are discrete segments of defined routes that contain maintenance, inventory, and condition data for filtering, analyzing, and reporting. To produce uniform segments, all source data is transformed through a data field mapping process. The end result is that data is stored within PMDb for subsequent search or aggregation. ⢠System record access. The system record access functional element provides API endpoints for the web browser application to view, filter, query, report, and download data within PMDb. The access functions do not manipulate or transform data; the access functions only package and present data quickly and efficiently to the user. To enable quick retrieval of data
44 Framework for a Pavement-Maintenance Database System and aggregations, data views and cache tables will be created by the database during the system record processing. The following section describes the key elements of the system in further detail. System Components Server Filesystem The server filesystem is used to store all uploaded data files for subsequent processing into the source record archive. The server filesystem maintains the integrity of the original data from the source file throughout the chain of processing to ensure that no valid data is ever lost during transformation. Each source data file and corresponding record in the source record archive is linked dynamically to the system records allowing end users to always access the original data source provided by an agency when filtering or downloading data. Data Sources The PMDb framework provides the ability to load data from existing sources at DOTs, includ- ing PMSs, design and construction data systems, MMSs, and reporting systems for labor, materi- als, and equipment used during maintenance events. For the purposes of PMDb, this is defined as source record data. Figure 6. PMDb framework and system overview.
Pavement-Maintenance Database Software Application 45 Source Record Data Source record data exists in a multitude of formats and structures across and within agencies. To provide a common language to ingest the data into PMDb, a standardized source record input format was developed. Each record with the source record data contains key attributes required within the database in a well-formatted tab, csv, or JavaScript Object Notation (JSON) structured flat file. This format provides a mechanism to have a uniform index for archiving and ultimately normalizing the data contained within system records. Source record data for each category of data (maintenance, inventory, and condition) is com- monly different pavement section lengths. A schematic to illustrate how each of these layers of data can have different segment lengths by which their measures are recorded is seen in Figure 7. Well-formatted source records are defined as any record, or a set of records, that contains the index fields described in Table 48. The index fields are used to indicate the location and time of a given data event consistently throughout the database system. Each data source accepted by the PMDb framework must contain source index fields although not all index fields require values to be accepted. Data type and format of the input field is also indicated. Every other column in the source record data file will be put in the PMDb_DataBag column in the database which is a JSON blob data type. An example of a properly formatted source record index data is displayed in Figure 8. Pavement-Maintenance Database Data Bag To ensure that the database system is scalable over time to accommodate existing and new fields as well as terms, PMDb stores all attribute data in a scalable data bag of key attributes and values that allows the metadata for each source record to be flexible. The attributes contained within the data bag are subsequently mapped to PMDb defined fields during the system record field mapping process described later. The benefit of using a data bag is that PMDb will retain all data source field names and values and provide links back to those values when searching so that common terminology and values can still be accessed and used. For example, if an agency described a condition using a field titled âAgency_Fieldâ that mapped to the data field of âPMDb_Field1,â users will still be able to ref- erence the data they are searching for by the âPMDb_Field1â moniker or by using the original Figure 7. Schematic of PMDb source record segments.
46 Framework for a Pavement-Maintenance Database System source data âAgency_Fieldâ moniker in the filter interface. An example data bag can be seen in Figure 9 for data that was generated from the National Highway Planning Network (NHPN) describing inventory elements. Once a source file has the appropriate source record index fields defined, it may be uploaded into the server filesystem for processing into the source record archive. During data ingestion the following steps are performed: 1. Ensure that the required index columns are provided. 2. Ensure that the required index columns are the data type we expect them to be. 3. Ensure the validity of the columns (e.g., PMDb_BRP ⤠PMDb_ERP, PMDb_Year ⤠PMDb_ EndYear). Data Field Description PMDb_State (REQUIRED) [string] State abbreviation where route is located PMDb_Route (REQUIRED) [string] Unique route number or name that describes where source record item took place PMDb_Direction [string] Direction of travel on route where source record item took place PMDb_Lane [string] Lane number or notation of lanes on route PMDb_BRP (REQUIRED) [number] Beginning reference point (milepost) on route PMDb_ERP (REQUIRED) [number] Ending reference point (milepost) on route PMDb_Year (REQUIRED) [YYYY] Year source record event started PMDb_Month [number, 0 for unknown] Month, if known, of source record event PMDb_Day [number, 0 for unknown] Day, if known, of source record event PMDb_EndYear [number, 0 will use PMDb_Year] End year, if known, for source record event that spans a period of time PMDb_EndMonth [number, 0 for unknown] End month, if known, for source record event that spans a period of time PMDb_EndDay [number, 0 for unknown] End day, if known, for source record event that spans a period of time PMDb_DataBag [text] All other attributes as defined in data source Table 48. Source index fields required for imported data source. PMDB_State PMDB_Route PMDB_Direcon PMDB_Lane PMDB_BRP PMDB_ERP PMDB_Year PMDB_Month PMDB_Day PMDB_EndYear PMDB_EndMonth PMDB_EndDay PMDB_DataBag WA 2 I R 71.16 71.22 2000 0 0 2000 0 0 ⦠WA 2 I R 71.42 71.67 2000 0 0 2000 0 0 ⦠WA 2 I R 71.77 71.89 2000 0 0 2000 0 0 ⦠WA 2 I R 71.99 72.07 2000 0 0 2000 0 0 ⦠WA 2 I R 72.39 72.49 2000 0 0 2000 0 0 ⦠WA 2 I R 73.66 73.81 2000 0 0 2000 0 0 ⦠WA 2 I R 74.32 74.56 2000 0 0 2000 0 0 ⦠WA 2 I R 74.87 75.02 2000 0 0 2000 0 0 ⦠WA 2 I R 78.96 78.99 2000 0 0 2000 0 0 ⦠WA 2 I R 81.2 81.3 2000 0 0 2000 0 0 ⦠WA 2 I R 81.35 81.44 2000 0 0 2000 0 0 ⦠WA 2 I R 81.95 82.14 2000 0 0 2000 0 0 ⦠WA 2 I R 82.2 82.33 2000 0 0 2000 0 0 ⦠WA 2 I R 83.47 83.65 2000 0 0 2000 0 0 ⦠WA 2 I R 83.71 83.75 2000 0 0 2000 0 0 ⦠WA 2 I R 83.9 83.97 2000 0 0 2000 0 0 ⦠WA 2 I R 84.35 84.65 2000 0 0 2000 0 0 ⦠WA 2 D L 71.12 71.35 2000 0 0 2000 0 0 ⦠WA 2 D L 71.39 71.72 2000 0 0 2000 0 0 ⦠Figure 8. Sample source record index data for input in PMDb.
Pavement-Maintenance Database Software Application 47 Following validation, the source record data file will be ingested into the source record archive database for subsequent processing steps to become the system data records. Source Record Archive The source record archive is a database representation of each source data record. It is important to maintain the integrity of all data that is provided to the database system, and the source record archive performs this function, while also serving as the basis for system record generation. Source Record Index Translation The source record index translation provides a mechanism to transform the source record index values as described earlier to match a consistent route index within the database in order to track and maintain a historical record of route segment maintenance activities and performance over time given that input data is routinely captured using different segment bounds. Route Index Data Table To maintain integrity of the historical data for a given section of the roadway, a common route index table, using the fields seen in Table 49, was created to serve as the master inventory of all routes in the database. Each route whereby pavement-maintenance activity is performed is assumed to have a unique name identifier for each roadway owner. This is necessary as route naming must be consistent among input datasets to achieve the desired goal of measuring the effectiveness of pavement-maintenance treatments. To achieve this consistency, source Figure 9. PMDb_DataBag field with sample data from NHPN.
48 Framework for a Pavement-Maintenance Database System records must either transform their route value to match an existing route during the index translation process or the route will not be archived and searchable within the database. System Record Database System record data for each category of data (maintenance, inventory, and condition) are data records generated from source record data for a normalized unit length of the pavement in order to effectively correlate the various layers of events stored within PMDb over time. Each of these layers of data can have different segment lengths by which their measures are recorded and compared to the schematic shown in Figure 10 of the resulting normalized PMDb system record that is generated. System Record Index The system record index defines each pavement segment of a route within PMDb that can contain data. It represents a normalized segment of roadway that contains a historical record of maintenance actions and conditions over time. In also includes inventory and material data and information. The length of the system record index is arbitrary to the design of the database although the initial index has been defined in one-mile segments to keep the database size manageable. Data Field Description IDRoutesIndex (REQUIRED) [number] Unique identifier for route autogenerated by PMDb Name (REQUIRED) [string] Name of route (all source record data must match existing route name) State (REQUIRED) [string] State where route is located RuralCode (REQUIRED) [string] Code indicating if current route is rural or urban FunctionalClass (REQUIRED) [string] Code for functional class of route (as provided by FHWA) MinimumBARM (REQUIRED) [number] Lowest ARM on route (most commonly this is 0, although it can be more for segmented routes) MaximumEARM (REQUIRED) [number] Highest ARM on route within current state Note: ARM = accumulated route mile, BARM = beginning accumulated route mile, EARM = ending accumulated route mile. Table 49. PMDb route index data field descriptions. Figure 10. Schematic of normalized PMDb system record segment.
Pavement-Maintenance Database Software Application 49 The system record index fields also define the segments by which all source record data is normalized using data field mappings and data field aggregations as described later. The primary system record index fields in the database system are described in Table 50. System Record Data System record data provides a mechanism to store all the data associated with a given system record for a particular time. This is a means of providing a record of the events in a linear fashion that have occurred over time on a particular section of the road. Certain attributes have been promoted to be indexable fields to enable summary results during filter and aggregation opera- tions (Table 51). The event-based model relies on a series of facts defined by a system record index and then utilizes the source record data table to describe each particular event that occurred on that route segment. A snapshot of the data table structure and event model can be seen in Figure 11. Data Field Description State (REQUIRED) [string] State abbreviation where route is located Route (REQUIRED) [string] Unique route number or name that describes where source record item took place BARM (REQUIRED) [number] Beginning accumulated route mile for system record EARM (REQUIRED) [number] Ending accumulated route mile for system record IDRoutesIndex (REQUIRED) [number] Pointer to route in route index data table Table 50. PMDb system record index data field descriptions. Data Field Description IDSystemRecords (REQUIRED) [integer] Link to specific system data record index IDSourceRecordArchive (REQUIRED) [integer] Link to original data in source record archive for which this data was generated Direction [string] Direction of travel on route where source record item took place Lane [string] Lane number or notation of lanes on route Category (REQUIRED) [number] Category by which data belong (e.g., maintenance, condition, inventory) DataElement (REQUIRED) [number] Data element associated with current data attribute DataAttribute (REQUIRED) [YYYY] Attribute name for current data record DataValue (REQUIRED) [string] Value for given data attribute for given event Year (REQUIRED) [YYYY] Year source record item started Month [number, 0 for unknown] Month, if known, of source record item Day [number, 0 for unknown] Day, if known, of source record item EndYear [number, 0 will use PMDb_Year] End year, if known, for source record item that spans a period of time EndMonth [number, 0 for unknown] End month, if known, for source record item that spans a period of time EndDay [number, 0 for unknown] End day, if known, for source record item that spans a period of time Metadata [string] Similar to data bag, key value pair store used to describe all elements of this event Table 51. PMDb system record data field descriptions.
50 Framework for a Pavement-Maintenance Database System Field Mappings Data field mappings represent how data elements are grouped within the system record database. It is where a user performs a function similar to that of the source record index translation although across different dimensions within the data. The primary dimension maps attribute names contained within source records and matches the existing data attribute names, in many cases as defined in the glossary. As seen in Figure 12, field mapping pro- vides users the ability to transform the attributes specified within the source to match defined attributes (as defined in the glossary and glossary categorization) within the existing PMDb framework. The second dimension of field mappings involves transforming values or groups of values through a data type aggregation process as described below. Data Type Aggregation As part of the field mapping process, values are normalized within the system record index segment according to the data type aggregation specified in the field mapping definition. The data type aggregation is used to consolidate values within the normalized segment length and represent the value of that data element within the segment. A diagram illustrating the concept of normalizing source data records into system records is seen in Figure 13. System Record Index System Record Data Figure 11. System record database model.
Pavement-Maintenance Database Software Application 51 Data type aggregations will be expanded once sample data is collected and tested within the system. However, only the following aggregations are currently supported: ⢠Average. This is the average of values within the event to get the representative value for the seg- ment. For example, if a user had 10 density readings that took place within a given segment, the average of those readings would be the representative number expressed in the system record. ⢠Weighted average by length. This refers to the average of values weighted by the length of the measurement or activity. For example, a distress measurement of 10% cracking over 500 feet added to a distress measurement of 25% cracking over 5,000 feet would more heavily weight the percentage cracking from the 25% cracking observation, since it was observed over a longer segment. ⢠Distribution. This occurs when the value is not aggregated but proportionally distributed along the length of the system record. This is common for values such as cost that are typically distributed over the entire segment. Figure 12. Field mapping workflow illustrating attribute name transformation. Figure 13. Conceptual illustration of data value normalization during segmentation process.
52 Framework for a Pavement-Maintenance Database System System Record Access Data within the system record database is used by the system record access function to quickly search, filter, and aggregate data across thousands of records to find specific result sets. The result sets can then be downloaded and subsequently analyzed using tools chosen by the user. The system record access function provides an interface to handle the various requests that will read the data in the system record database as well as the source record archive. Security and Access Control A role-based security scheme was developed to provide security and access control require- ments for the database system. In computer systems security, role-based access control is an approach to restrict access to system functions by authorized users. A description of the resulting user roles and a corresponding permission matrix can be seen in Table 52. For a description of the functional requirements that were developed for PMDb, refer to the appendices. User roles are described as the following: ⢠All users. This category includes all casual users who may come to the site. It is the âcatch all userâ level that includes all unregistered and registered users. ⢠Registered users. Registered users are those who have created an account in the database system that has been approved by an administrator or the validation process. The validation process is done by an email white list (a list of permitted email addresses). Once approved, User Roles Functional Requirement A ll U se rs R eg ist er ed U se rs C on tr ib ut or U se rs A dm in ist ra to r U se rs View resources/references View glossary View data dictionary View system statistics Register for access Browse and view pavement sections Query for pavement section datasets Download pavement section datasets Perform user account functions Submit new data Append data Publish data Append new data terms and elements Perform user management functions Modify and remove data Table 52. User roles defined by functional requirement.
Pavement-Maintenance Database Software Application 53 registered users are granted access to view and extract data within the system. It is anticipated that registered users will include state agency personnel and researchers who are interested in accessing the data for subsequent analysis. ⢠Contributor users. Contributor users can submit data to PMDb. It is anticipated that con- tributor users are users who have access to specific data and are responsible for uploading data at each agency. For example, this may be a pavement-maintenance manager or technician at a given agency. Only agency-approved personnel will be allowed to contribute to the database system. ⢠Administrator users. Administrator users are the stewards of PMDb and are capable of man- aging data within the database. They are responsible for managing user accounts and access and administering access policies created by the group that manages PMDb. Browser Application The diagram in Figure 14 identifies the different interfaces within PMDb to deliver the func- tions listed in Table 52. Each interface within the Browser Application interacts with the RPC API to generate data and present results to the user. All web interactions are done through a single webpage application which provides a quick, responsive display to the user. This is par- ticularly important with large datasets that are anticipated to be uploaded into PMDb during the implementation effort. Static Content PMDb contains several static content pages to aid user navigation and understanding of data. This includes the following pages: ⢠Home. This is the landing page for the site and contains links and navigation to the key inter- face elements and screens. A screenshot can be seen in Figure 15. ⢠Getting Started. This page contains information to help users navigate through the PMDb software. When clicked on, a document containing instructions in Adobe format will be opened. ⢠Glossary. This page contains glossary items that describe the intended definition of the data element or attribute. Further details can be found in the glossary on page 81. System Management To manage users within PMDb, a user management scheme was implemented to facilitate user sign up and access while providing a secure method of verifying the userâs identity. This involved the development of two data structures, one for organizations and the other for users as described below. Organizations Organizations are collection points for registered users and contributor users. Users of the system with elevated privileges, such as an administrator user, will have access to a mainte- nance screen to create organizations. An organization allows users from a particular agency to self-register with a white listed email address pattern defined per organization (Figure 16). For example, Acme agency has a white-listed email (*@acme.com) which allows users to self-register and confirm registration if they have a valid email address with the organizational domain (e.g., john.doe@acme.com). If there is no organization match during sign up, a customized organiza- tion creation form will be made available to the potential user.
54 Framework for a Pavement-Maintenance Database System Users Users are members of a particular organization who can then have additional rights granted to them to conduct the administrative actions of a contributor when their access is elevated by an administrator. The only attributes particular to users involve their username, name, and email. In addition to managing users, PMDb also tracks user activity on the site to determine who are uploading and downloading data. A screenshot of the edit user interface is seen in Figure 17. System Features Dashboard The dashboard is an interface that communicates the amount of data in PMDb that is cur- rently available to users. This is helpful because it shows what data is currently within PMDb and where the gaps exist. Currently PMDb supports data grouped by state. As such, the dashboard Figure 14. Browser application interface elements and navigational structure.
Pavement-Maintenance Database Software Application 55 Figure 15. Home page of the PMDb system. Figure 16. Screenshot of the organization list in the browser application.
56 Framework for a Pavement-Maintenance Database System Figure 17. Screenshot of the user modification interface. interface groups all routes with data by state and indicates the number of data records that reside within PMDb for that particular state. Create a Route Inventory PMDb allows users to browse all the routes in the system and also to add routes using a web interface workflow. A baseline route index must be established prior to a source record ingestion in order to link up the records. Users can also leverage geospatial route data from external sources such as NHPN as seen in Figure 9 to populate the route index. NHPN is a geo- spatial network database that contains just over 450,000 miles of highways in the United States. NHPN contains geospatially referenced information on the National Highway System (NHS), the Eisenhower Interstate System, the Strategic Highway Network (STRAHNET), and the NHS Intermodal Connectors. In terms of highway functional class, it covers all principal arterials and rural minor arterials. It also has an existing linear referencing system that allows HPMS and National Bridge Inventory data to be linked to the network (Figure 18). Upload Dataset PMDb provides a workflow interface to allow a user to upload and transform their data to align to PMDb standards. The workflow is described further in Chapter 5. A schematic diagram of the process can be seen in Figure 19. Source Records. To upload a dataset to PMDb and generate source records, one must first define a data source for their organization. This is done to maximize data integrity and control the data that is being loaded into PMDb. Once a data source has been defined within PMDb, data files may be uploaded directly to the server filesystem by authorized users. Users can consult the uploaded data file list interface within PMDb to view each of the files uploaded and determine whether or not it has been processed into a source record.
Pavement-Maintenance Database Software Application 57 Figure 18. Screenshot of the route inventory listing for PMDb with sample data from the NHPN route inventory. Route Inventory Data Field Deï¬nitions Index Translation Data Field Mapping Source Record Archive Source Records System Data Records PMDb Data Processing Workï¬ow Glossary Figure 19. Uploading a dataset workflow process.
58 Framework for a Pavement-Maintenance Database System Index Translation. To allow for translation of the key source index fields to map to an exist- ing route in the route index dataset, a source record transformation function has been developed. The function allows users to define distinct actions that will be performed on the source record archive data. Actions can be additive and several powerful translation functions are available including the use of regular expressions, find and replace, and forceful override. Filter a Result Set As the primary interface to extract meaning from data contained within PMDb, users can specify criteria to identify sections for further analysis as well as to examine the result set that was returned. Criteria can be specified as either a value filter whereby one or more specific values are searched for within the database or a range filter whereby a numeric range for a given attri- bute can be specified. All filters are summed to find the resulting system records that match all criteria as seen in Figure 20. The resulting interface is then intended to provide users with a summary of the data contained within their result set in order to determine if it is ready for download as seen in Figure 21. Export a Dataset System records and source records can be downloaded once they have been selected by the filter function. This allows end users to download both the system records that were generated as well as the source records that reflect the original data. The export function downloads data as an aggregate (all data for the given record set) and can be downloaded in numerous formats (csv, tab, JSON). Figure 20. Browser application interface for specifying the active filters.
Pavement-Maintenance Database Software Application 59 Figure 21. Browser application interface displaying the search results from the filter.
