Staging tables

This topic discusses the use and implementation of staging tables in the advanced linear referencing system (ALRS). Staging tables are used to manage changes to event data when the event source data, called business tables, is stored and managed outside of the GIS.

Local and external event layers

Event layers in the ALRS can be either local or external. The use of the terms local and external here refer to whether they are stored within or external to the GIS that manages your linear referencing system (LRS). When a business table that resides within the same GIS as your LRS is registered as an event layer, a local event layer is created. If the business table resides outside of the GIS, an external event layer is created. Local and external event layers appear to behave exactly the same to the end user, but what happens behind the scenes when events are modified is quite different.

Local events

As with almost everything in ArcGIS, managing data that is stored within the GIS is more straightforward than managing data that is not. When you register a business table as a local event layer, editing the event layer is exactly the same as editing the business table. Changes made to fields in the layer automatically update changes made in the business table. This is because the event layer is merely a graphic representation of the table itself. When edits made to the route have an impact on event layers, the changes are made directly to the business tables. When the event layer is refreshed in your map, which is automatically triggered by the Roads and Highways edit activity, you can see the updates immediately.

Note:

Before you can create a local event layer in an SDE geodatabase, the business table must be registered as versioned.

External events

External events appear to exhibit the exact same behavior as local events. When edits are made or event behavior triggered, the event layer is refreshed and you can see the changes immediately. How this happens, however, is quite different. The Roads and Highways assumption with respect to external business tables is that the user does not have the proper privileges to modify them. When a business table is registered as an external event layer, the ALRS assumes a read-only connection and never writes directly to the business table. The ALRS makes this assumption to protect business systems from potentially harmful manipulation through the connection to the GIS.

When an external event layer is created in the ALRS Properties dialog box, the ALRS creates a copy of the business table’s schema in the GIS and names it LRSST_<event layer name>. This table is called a staging table. When you add an event layer to your map, the ALRS merges the staging table with the business table to create a merged table. The merged table is then used to create the dynamic events that display in the map as graphical features.

When you make changes to the event layer, whether by direct editing or through triggered event behavior from modifying a route, the ALRS writes them to the staging tables rather than the business table. By modifying only the staging tables, Roads and Highways protects the external business table from direct editing.

Managing staging tables

Staging tables require very little management. Most of it is done for you by the ALRS. They are created automatically when external events are created, and they are updated automatically when changes are made to the event layer. The staging tables even keep track of multiple updates to the same event record so temporality of staged events is also managed automatically.

What is not managed automatically is the communication that must happen between the GIS and the owner of the external business table so that external business systems can be updated appropriately when changes occur in the LRS. This is managed through customized web services using the Roads and Highways Representational State Transfer (REST) application programming interface (API). The Roads and Highways REST API includes two methods for managing staged event records: the Event Changes Query method and the Event Changes Acknowledged method. You can find more information about the Roads and Highways REST API here.

Event Changes Query

The Event Changes Query method allows external systems to poll the LRS and determine whether any events have been modified. When the query is invoked, the web service provides information about the records that have been changed to the external system. To add the changed records to the external system, it is necessary to either write custom code within the business system itself or for a user of the external system to open the staging table and manually enter the changes. Because Roads and Highways knows very little about the external system itself, it is impossible for the ALRS to automate these changes. Such automation must happen on the business system side.

Event Changes Acknowledged

The Event Changes Acknowledged method allows external systems to inform the LRS that event changes have been either accepted and incorporated into the business tables or rejected. Once the ALRS receives acknowledgment that the changes have either been incorporated or rejected, Roads and Highways purges the staging tables. This ensures that the most current, accepted records from the business system are being rendered as events.

Merging event tables

Roads and Highways treats connections to external event data sources, such as tables and views, as read-only. All event modification made by Roads and Highways are stored in the staging tables, which resides inside the ALRS geodatabase. During runtime, Roads and Highways merges external tables with staging tables to present the accurate state of event data for a given date and time. See Advanced linear referencing—Time-aware LRS for more information.

Note:

Table indexes do not affect merged logic, however, it is recommended to add indexes to the following external event tables to improve performance.

• EventID
• RouteID
• Measure
• FromDate
• ToDate

The figure below illustrates the schema and the relationships between the external event tables, staging tables, and merged tables:

The merged event table is a union of the external table and the staging table according to the following rules:

• If an event exists only in the external table, the record in the merged table is a copy of the record from the external table.
• If the staging table contains records with particular event IDs, it is assumed that this event was modified and the data in the staging table more accurately represents the state of the event. In this case, the record from the staging table overrides records from the external table.