Skip to end of metadata
Go to start of metadata

With inventory and condition data configured and imported, we are ready to perform network analysis. In the system, 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.

The Network Master table is a key table in the system, which combines data from the various sources (asset data, pavement condition, traffic data, pavement structure). It 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 CategoryExample Columns

Identifier (ID)

Segment ID

LocationBegin/End Address, Begin/End Description, Begin/End Distance

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 PMSX_SEGMENT_ID 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 data set 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 can be navigated to by following the steps below:

  1. Click Analysis in the top banner menu
  2. Click Network Analysis in the drop-down menu
  3. Click Network Master 
  4. Select Network Master Data

The table is named PMSX_NETWORK_MASTER. Out of the box, the table has the following columns:

Column LabelColumn NameData TypeRequired to Run PMS at MinimumDescription
Segment IDPMSX_SEGMENT_IDIntegerYID used through out the system to identify each segment. Primary Key of the table.
Segment NameSEGMENT_NAMEString
If there is a name assigned to the segment
Segment SequenceSEGMENT_SEQUENCEString
Allows the system to sort the segment
Road NameROAD_NAMEString
Street/Road name of the segment
From DescriptionDESCRIPTION_FROMString
Begin description of the segment
To DescriptionDESCRIPTION_TOString
End description of the segment
Start DistanceDISTANCE_STARTNumber (22,3)
Begin distance measure of the segment
End DistanceDISTANCE_ENDNumber (22,3)
End distance measure of the segment
Start LatitudeLATITUDE_STARTNumber (22,9)
Begin latitude of the segment
Start LongitudeLONGITUDE_STARTNumber (22,9)
Begin longitude of the segment
End LatitudeLATITUDE_ENDNumber (22,9)
End latitude of the segment
End LongitudeLONGITUDE_ENDNumber (22,9)
End longitude of the segment
AADTAADTIntegerYAADT on the segment
LengthLENGTHNumber (22,3)YLength of the segment
Number of LanesNUMBER_OF_LANESInteger
Number of lanes on the segment
Lane MilesLANE_MILESNumber (22,3)YLane-miles of the segment
Segment AreaSEGMENT_AREANumber (22,3)
Segment area
Lane Width (Per Lane)PER_LANE_WIDTHNumber (22,3)
Per lane width
Shoulder WidthSHOULDER_WIDTHNumber (22,3)
Shoulder width
Segment WidthSEGMENT_WIDTHNumber (22,3)
Segment width in total
Percent TruckPCT_TRUCKNumber (22,2)
Truck percentage
Last Rehab YearYEAR_LAST_REHABInteger
Last year of rehabliation on the segment
Last Preventive Maintenance YearYEAR_LAST_PMInteger
Last year of preventive treatment on the segment
Address: Left Side FromADDRESS_LEFT_FROMInteger
Left side of the segment: Begin address
Address: Left Side ToADDRESS_LEFT_TOInteger
Left side of the segment: End address
Address: Right Side FromADDRESS_RIGHT_FROMInteger
Right side of the segment: Begin address
Address: Right Side ToADDRESS_RIGHT_TOInteger
Right side of the segment: End address
Address: FromADDRESS_FROMInteger
Segment Begin Address
Address: ToADDRESS_TOString
Segment End Address
Zip CodeZIP_CODEString
Zip code
CityCITY_IDInteger
City
CountyCOUNTY_IDInteger
County
Maintenance JurisdictionMAINTENANCE_JUR_IDInteger
Maintenance Jurisdiction
NHS TypeNHS_TYPE_IDInteger
NHS Designation
Functional ClassFUNCTIONAL_CLASS_IDIntegerYFunctional Class
Pavement TypeWC_IDIntegerYPavement Type
One-way Street?ONEWAYInteger
Whether the segment is one-way
Divided Street (median)DIVIDEDInteger
Whether the segment is divided (with a median)
Condition Data YearPMS_CONDITION_DATA_YEARInteger
Condition Data Year
Pavement Condition Index (PCI)PMS_PCI_INDEXNumber(22,3)YPavement Condition Index
CommentCOMMENT_STRString
Comment
User UpdateUSER_UPDATEInteger
Last update user
Date UpdateDATE_UPDATEDate
Last update date/time
GeometryGEOMGeometry
Geometry. Hidden from the window.

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:

  1. 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.
  2. 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: When filled, this field indicates that this record in the Columns pane is a calculated column and is the SELECT SQL statement that performs that calculation.
  • Where_Clause: Applies a conditional argument to the calculation and subsequent field update.
  • Update_Order: 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.

  1. The Select statement states that the data will be derived from the DESCRIPTION_FROM column.
  2. The From statement states that the data will be derived from the PMSX_SEGMENT table.
  3. The Where statement details the location elements to match each individual road segment, i.e., PMSX_SEGMENT_ID

To complete this process, follow the steps below:

  1. Navigate to the Network Master File table by clicking System in top banner menu
  2. Click Utilities in the drop-down menu
  3. Select Tables
  4. Click Columns
  5. Locate the Update Source, Update Order, and Where Clause columns in the table
  6. 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
  7. Click Save to save the changes
  8. To update the data in the table according to these SQL statements, navigate to the window that shows the current table (in the example below we navigated to the Network Master window by clicking Analysis in the top banner menu, clicking Network Analysis in the drop-down menu, and then selecting Network Master)
    Right click in this window to open the drop-down menu
  9. Select Update Target Table.
  10. In the pop-up window select the columns to update using the check box in the Select column
  11. 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 the 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 Analyst Express: Create and Update Network Master.

  • No labels