Network Master Configuration
With Management Sections developed, we are ready to perform network analysis. In Pavement, the Network Master table is the starting point for all network analyses. These analyses depend on the columns included in the table. The columns to be included can be decided based on decision-making and reporting requirements for each customer.
Network Master table is a key table in Pavement, which combines data from the various sources (asset data, pavement condition, traffic data, pavement structure). It is the starting point for all network analyses and should contain the latest information for all pavement sections. It represents the current structure, traffic, classification, and condition for the network as a whole. See an example of possible columns in Network Master below. Since Network Master provides input to analysis, it must contain information for the full network for planning and reporting purposes.
| Column Category | Example Columns |
| Location | PMS Section #, LOC_IDENT (Route, Begin / End Milepoint, Direction, Lane) |
| Jurisdiction | Statewide Data Owner, Maintenance Jurisdiction, District, County |
| Roadway Inventory | Length, Number of Lanes, Lane Width, Shoulder Width, Total Lane Miles, Functional Class |
| Pavement Data | Pavement Type, Condition Data Year, IRI, Rutting, Faulting, Cracking |
| Traffic | Traffic Data Year, AADT, VMT, ESAL |
| Maintenance History | Last Treatment Type, Number of Years since Last Treatment, Last Rehab Type, Number of Years since Last Rehab, Most Recent Contract (Link to history), Pavement Age |
| Structure | Total Surface Depth, Structural Class |
Two things need to be noted regarding the columns in Network Master:
- At a minimum, Network Master must include the PMS_SECTION_ID, LOC_IDENT, and PAVE_AGE columns.
- During optimization analysis, if EFF_YEAR (Effective Year) column doesn’t exist in Network Master, the analysis will use all the data in Network Master. Otherwise, the analysis will only use the data of which the EFF_YEAR field is equal to the “Year of Condition Data” field as defined in the optimization analysis. In other words, if NMF doesn’t have EFF_YEAR column, when setting up the optimization analysis, the value of “Year of Condition Data” field does not affect the results.
Because Network Master is the input data stream for network analysis, it must be re-calculated at least once per year for the current dataset to be used in network analysis, and it should also be re-calculated each time any of the condition indicator settings have changed.
Network Master typically uses the current pavement management sections as the master for pavement section definitions. Other data (such as pavement management inventory, traffic, condition in the form of performance index, and construction history) are then assigned to the Network Master when it is filled. Data for the Network Master is aggregated according to rules developed for each client, using the Update Target Table function.
The Network Master Data window shows all road sections in the Network Master and provides descriptive information about each road section. All the columns in Network Master that are calculated using the formulas can be configured by right click and select “Update Target Table” command (pic below).
Out of the box, Network Master Data window is located in Analysis > Network Analysis > Network Master > Network Master Data menu. The table is named NETWORK_MASTER. Out of the box, the table has the following columns:
| COLUMN_NAME | DATA_TYPE | DATA_LENGTH | NULLABLE | COMMENTS |
|---|---|---|---|---|
| LOC_IDENT | NUMBER | 22 | N | Location # used in SETUP_LOC_IDENT table to store the record’s location in the LRS. This is a system column. |
| PMS_SECTION_ID | NUMBER | 22 | Y | PMS Section ID. Usually set to same value as LOC_IDENT |
| LANE_MILES | NUMBER | 22 | Y | Lane-miles of the section |
| LENGTH | NUMBER | 22 | Y | Length of the section |
| DISTRICT_ID | NUMBER | 22 | Y | District |
| FUNCTIONAL_CLASS_ID | NUMBER | 22 | Y | Functional class |
| WC_ID | NUMBER | 22 | Y | Pavement type |
| NHS_TYPE_ID | NUMBER | 22 | Y | NHS type |
| SHOULDER_WIDTH | NUMBER | 22 | Y | Shoulder width |
| SEC_WIDTH | NUMBER | 22 | Y | Width of the section |
| NUMBER_OF_LANES | NUMBER | 22 | Y | Number of lanes |
| WC_ID | NUMBER | 22 | Y | Pavement type |
| OWNER_ID | NUMBER | 22 | Y | Owner |
| STATE_OWNER_ID | NUMBER | 22 | Y | State-wide owner |
| IS_INTERSTATE | NUMBER | 22 | Y | Interstate flag |
| IS_NHS | NUMBER | 22 | Y | National Highway System (NHS) flag |
| PMS_SEGMENTATION_TYPE_ID | NUMBER | 22 | Y | Pavement segmentation set, e.g. Management Sections, MAP-21 sections. This allows the Network Master to have multiple sets of segmentations. When running analysis in the Optimization Analysis window, users can choose which set of Run Analysis windows. |
| PAVE_AGE | NUMBER | 22 | Y | Pavement age |
| TRAFFIC_DATA_YEAR | NUMBER | 22 | Y | Traffic Data Year |
| AADT | NUMBER | 22 | Y | AADT |
| PCT_TRUCK | NUMBER | 22 | Y | Truck percent |
| PMS_WORK_CODE_ID | NUMBER | 22 | Y | Last treatment work code |
| PMS_TREATMENT_ID | NUMBER | 22 | Y | Last treatment type |
| PMS_AUTO_DATA_YEAR | NUMBER | 22 | Y | Condition data year |
| PMS_IRI_VALUE | NUMBER | 22 | Y | IRI value |
| PMS_RUTTING_VALUE | NUMBER | 22 | Y | Rutting value |
| PMS_FAULTING_VALUE | NUMBER | 22 | Y | Faulting value |
| PMS_PCT_CRACKING | NUMBER | 22 | Y | Percent cracking |
| STATE_COND_CAT_ID | NUMBER | 22 | Y | Condition category using agency criteria |
| MAP21_COND_CAT_ID | NUMBER | 22 | Y | Condition category using FHWA criteria |
| COMMENT_STR | VARCHAR2 | 4000 | Y | Comments |
| DATE_UPDATE | DATE | 7 | Y | Last update user of the record |
| USER_UPDATE | VARCHAR2 | 400 | Y | Last update date of the record |
The data in Network Master does not change during optimization analysis even if the analysis is multi-year. Network Master data is copied to USER_OPTIMIZATION_ENGINE table where data is processed and changed by the application to provide analysis results.
Configuration of Network Master includes two main steps:
- Expanding the table to include the needed columns.
- Similar to other pavement data, the configuration of Network Master dataset starts by expanding the table structure to include customer-specific data element and then add those columns to the window.
- Follow the instruction detailed in “Create and Import Lookup Values” and “Add Columns to a Table” Chapter in “AgileAssets System Foundation Configuration Guide” and add them into the corresponding table.
- After all the columns have been added to the table, follow the instruction detailed in “Add Columns to a Data Window” section in “AgileAssets System Foundation Configuration Guide” to add those columns to the window.
- Create the data aggregation rule on each column.
- Update Target Table function is where this step is used.
- In every single table, the user has the option to update the data in any given column with the use of SQL statements. It is this functionality that is used to copy the data from the various data tables and aggregate it into the NMF table. These statements are created/written by the user directly in the front-end of the system.
- When a particular Table window is opened (System > Utilities > Tables), and the Columns tab is clicked upon, the Update Source column, the Where Clause column, and the Update Order column can be found – you may have to scroll to the right for these to become visible in the window. These fields are used to update that particular table by utilizing SQL statements.
NOTE: It is expected that some prior knowledge of writing SQL statements exists in order to complete this element.
- Update_Source: This field, when filled, indicates that this record in the Columns pane is a calculated column and is the SELECT SQL statement that performs that calculation.
- Where_Clause: This field applies a conditional argument to the calculation and subsequent field update.
- Update_Order: This field configures the order in which calculated columns are calculated. This order is important whenever one calculated column is based upon the results of a second; in this case, the first column's Update Order must be larger than the second's. This field is ignored whenever the Update Source field is blank.
The SQL statements are placed in the Update_Source and Where_Clause fields. For the data to be successfully and correctly updated, these fields must be completed correctly.
A general form for the SQL statement is:
SELECT (update_source) AS column_name FROM table_name A WHERE where_clause
Such that:
- The SELECT statement is legal and when run without the where_clause selects one and only one record for every record in the table_name.
- The table_name is the appropriate table being updated (as identified in the currently selected record in the top pane of the window you're using).
- The column_name is the appropriate column being updated (as identified in the currently selected record in the Columns pane of the window you're using).
- The update_source is the text in the UPDATE_SOURCE field. If filled, then this record in the Columns pane is a calculated column.
- The where_clause is the text in the WHERE_CLAUSE field. It need not be filled, but if filled, then the calculation is only applied to those records in the table that pass the where clause criteria.
NOTE: The best way to learn to write Update Source SQL is to find several existing examples and place them into the SELECT statement configuration shown above.
The example below details how a full Update Source SQL statement is constructed.
- The example below details how a full Update Source SQL statement is constructed.
- The Select statement states that the data will be derived from the NUMBER_OF_LANES column.
- The From statement states that the data will be derived from the PMS_ROADWAY_INVENTORY table.
- The Where statement details the location elements to match each individual road segment, e.g.
- ROUTE_ID
- LANE_DIR
- LANE_ID
- OFFSET_FROM and OFFSET_TO
The detailed steps for the process are as follows:
- Navigate to the Network Master File table in System > Utilities > Tables.
- Click the Columns
- Locate the Update Source, Update Order, and Where Clause columns in the table.
- For each field to be updated from a source table:
- Enter the SQL statement as described above into the Update Source field.
- Complete the Update Order field with a numerical value to define the order of update. The lower the value the earlier in the update process this field is updated.
- Complete the Where_Clause column if required to act as a filter on the field data being loaded/updated.
- Click Save to save the changes.
- To update the data in the table according to these SQL statements, navigate to the window that shows the current table (for example on the picture below we navigated to the Current Pavement Conditions window within the Pavement module - Linear Assets > Database > Current Pavement Conditions - which shows the Network Master File table).
Right click in this window will open drop-down menu. Select Update Target Table.
- In the opened pop-up window select the columns to update using the check box in the Select column.
- Click OK and the selected calculated columns are updated using the current data from which it is calculated.
After all the configuration is done, when it is time to update Network every year, the system has provided a system to update Network Master out of the box. It includes both “copying management sections into Network Master” and “Run Update Target Table on Network Master”. The system job is named Pavement: Create and Update Network Master using Management Sections.
Once the Management Sections are created, another similar system job Pavement: Create and Update Network Master using 0.1-Mile Sections can be used to create 0.1-mile uniform-length sections into Network Master. This can be used to achieve the reporting needs from MAP-21 Act.
