Overview

The Import tool is a valuable tool to help user to keep their data in-sync with roads and highways as the collection process proceeds. The following diagram shows a typical process overview for the Time Delayed Import Tool (TDIT).



Step 1 – Collect and update data

In this step the field crews will collect data based on their data collection.  It is assumed that this process takes enough time that one or more LRS Interface runs will have been made during this step.  If not, a normal import process is sufficient. The data collected needs to have a date or indication of LRS version used for data collection.

Step 2 – Import data to a Staging table

Once the data has finished being collected it is imported via standard methods to a Staging table made for that purpose

NOTE: Because this data does not match AgileAssets system LRS, the staging table is not a "location" table (i.e., with LOC_IDENT column). It is a "general" table with explicit ROUTE_NAME, OFFSET_FROM, OFFSET_TO columns. Minimum required columns are described in the later sections.

Note that steps 3-7 are depicted in the same color.  This is to emphasize that these are all encapsulated within one automated command (System Job in AgileAssets system).  It is only broken into separate steps in this document for clarity.  Note that the automated portion of this import assumes that the Staging table has already been populated.

Step 3– Populate Events-To-Be-Relocated tables

Each Event classification configured in the Roads and Highways Interface has a pair of associated tables – one for point events and one for linear events.  These are already registered with Roads and Highways as part of the configuration of the LRS Interface.  In this step, AgileAssets will copy the location data from the Staging table to these Events-To-Be-Relocated tables. 

Step 4 – Run Relocate Events web services and populate Event buffer tables

AgileAssets will invoke the Relocate Events web services which returns CSV files with the updated location data.  AgileAssets will then use that data to populate the 2 Event buffer tables.  The “From” date given to the web services will be the date stored during Step 1.  The “To” date has 3 options: 1) Last successful LRS Gateway Date; 2) Today; 3) A fixed date.

Step 5 – Update Staging table with routes/measures from Event buffer tables

Next AgileAssets will update the location data in the Staging table with the location data in the Event buffer tables.

Step 6 – Process splits and deletions

In this step, AgileAssets will continue to update the Staging table with any events that were split or deleted.  Appropriate columns will be proportionally updated based on their Column settings.  Deleted records will be marked as such in the Staging table.

Step 7 – Import updated Staging table to Target Table

Because TDIT only processes the data in the staging table (not a LOC_IDENT table), it is usually followed by an separate standard import that uses the Staging table as the data source to the final target table (usually the table with LOC_IDENT column)

Setup

The TDIT tool uses ESRI's Relocate Events web services to update event locations prior to importing data into PMS. The web service works by making AgileAssets events available to the tool, by a combination of utilizing ArcGIS Server, Roads and Highways, and AgileAssets application.

Setup of the RelocateEvent REST services for TDIT on Roads and Highways is very similar to setting up the Relocate Event services for AgileAssets Roads and Highways interface, with the only difference being that they are using different external event tables.

Setup in Roads & Highways

Register External Events with Roads and Highways

Similar to the Standard R&H Interface, two External event tables from the AgileAssets application schema need to be registered to the Advanced Linear Reference System (ALRS). These two tables have already been created in AgileAssets schema. Use Default version of LRS network when registering as well.

The directions for registering External events to Roads and Highways can be found on the Esri website (https://desktop.arcgis.com/en/arcmap/latest/extensions/roads-and-highways/registering-an-external-event-source.htm).

The process is the same as creating the Relocate Event services for standard R&H Interface, except that:

  • When registering the external tables from AgileAssets application schema, the corresponding tables/views are NET_EXT_TRANS_EVENT_LN (for line events) and NET_EXT_TRANS_EVENT_PT (for point events). Name the event layers AA_TDIT_LN and AA_TDIT_PT, respectively.
  • The field mappings are depicted below:
  • The process is very similar to the ones created for running R&H Interface, but just with different tables/views from AgileAssets database schema.

Create R&H Relocate Events Web Service

The process of creating the web service from the Relocate Event tool is the same as those created for the R&H Interface, but with different instances and data sources for the services.

After creating the service, provide AgileAssets with the 2 URLs (one for Line event and one of Point event).

Create and Setup Columns on Staging Table

The system job for the time delayed import transformation requires the following specific columns to be available in the staging table for the job to process the data. The column names are specified in the job and so don’t need a static name.

Field

Recommended Column ID

Date Type

Column name containing values for ROUTE_NAME in SETUP_NETWORK_LINES (or ROUTE_ID field ins RnH).

ROUTE_NAME

Same as SETUP_NETWORK_LINES.ROUTE_NAME

Column name containing values for From Measure (milepoint) of the segment

OFFSET_FROM

Same as SETUP_NETWORK_LINES.OFFSET_FROM

Column name containing values for To Measure (milepoint) of the segment

OFFSET_TO

Same as SETUP_NETWORK_LINES.OFFSET_TO

Column name containing values for the date of the LRS that data was captured against

LRS_DATE

DATE

LRS update date column name. This column will be updated with the date any LRS changes were made

LRS_CHANGE_DATE

DATE

This column will be updated with the type of LRS change made to the data

LRS_CHANGE_STATUS

VARCHAR2(10)

Note: The staging table is NOT a location table (i.e., without LOC_IDENT column). It is usually configured with ROUTE_NAME, OFFSET_FROM, OFFSET_TO as the Primary Key or Unique Index.

Setup System Jobs in AgileAssets System

Each staging table will have its own system job to apply the TDIT transformation.

  1. Setup the table, window and import configuration to match the source data, using the normal AgileAssets system process.
  2. Go to the AgileAssets application and go to Tools > System Jobs > Schedules, and look for the System Job Executable called “ESRI RnH Time-Delayed Import Tool”, right click and Define Argument(s). Make sure to put in the URL of the rest services for the relocate events geoprocessing service as created in 1.2.
  3. Run the System Job to apply TDIT transformation.
  4. Run the separate import from the staging table to the target table. Filter out the records with “RETIRED” value in LRS_CHANGE_STATUS column.

In Step 2, be sure to use the columns specified below with the Source staging table you want updated. Below is an example configuration.

System Job Argument

Description

Source Staging table

The staging table where the data is imported to

Base external Esri RnH table names

The Base External Esri RnH tables name is the base name of the registered external event table. By default, it is NET_EXT_TRANS_EVENT. System will then use NET_EXT_TRANS_EVENT_PT table for point and NET_EXT_TRANS_EVENT_LN table for lines.

Web service URL for Esri RnH RelocateEvent (Point)

TDIT R&H Relocate Event service (Point) URL published on ArcGIS server

Web service URL for Esri RnH RelocateEvent (Line)

TDIT R&H Relocate Event service (Line) URL published on ArcGIS server

Column name containing values for ROUTE_NAME

Column name in staging table that stores Route ID from RnH

Column name containing values for OFFSET_FROM

Column name in staging table that stores Begin Milepoint (or From Measure)

Column name containing values for OFFSET_TO

Column name in staging table that stores End Milepoint (or To Measure)

Column name containing values for LRS_DATE

Column name in staging table that stores the LRS Date of the data, by default LRS_DATE

LRS update column name

Column name in staging table that will store the LRS Change Date returned from RnH, by default LRS_CHANGE_DATE

Column name containing event modification type

Column name in staging table that will store the type of change returned from RnH (such as “RETIRE”, “SPLIT” etc.), by default LRS_CHANGE_STATUS

LRS view date (optional)

Only used if “LRS View Date Type” is using “Forward data to a future LRS date – fixed date”. Otherwise it can be set to any date.

LRS view date type

“LRS View Date Type” parameter has 3 options:

  • Update historical data: This will relocate the external event to the latest successful run of LRS Gateway in AgileAssets, therefore matching AgileAssets’ LRS Date
  • Forward data to a future LRS date – current date: This will relocate the external event data to the current datetime in Roads & Highways.
  • Forward data to a future LRS date – fixed date: This will relocate the external event data to a fixed datetime in Roads & Highways (specified in LRS view date field).
  • No labels

Provide Feedback