View Source

Data Model

The AgileAssets system requires users to define a base linear referencing method. The system stores all event locations in this base referencing system. However, a user can use an alternate referencing method to specify / enter the location of an event in the AgileAssets system. The information (event location data) entered using the alternate referencing method is automatically converted by the AgileAssets software to the equivalent values in the base referencing system. However, in order to do this automatic conversion, the mapping between the base and the alternate referencing system has to be defined and set at the time of setting up the LRS in AgileAssets software.
For example, in some agencies there is a statewide mile-point linear referencing system that identifies each route and the distance along the route from the start of the route within the state to the end of the route within the state. This measure is continuous across all boundaries within the state. The AgileAssets system may utilize a statewide method as its "base" linear referencing system.

However, in some agencies the asset management office and other users of the system might normally work with a county mile-point based linear reference that resets the measure of distance along the route at each county boundary. In the Agile Assets system this (and possibly other location referencing methods) would therefore be termed an alternate linear referencing method. To define the alternate linear referencing method, we first need to identify the attributes needed to map the state mile-point based reference to the county mile-point based reference. For example, if there is a route (Route A) in the system that crosses 1 county boundary in the state from south to north, and it passes from county 1 into county 2. In county 1 the route is 10 miles long and in county 2 the route is 5 miles long. Given this example, we can create a simple table to map all the state mile-points to the county mile-points:

Example Mapping between State and County Milepoints

Route	State Begin	State End	County	County Begin	County End
A	0	10	1	0	10
A	10	15	2	0	5

Note: Out of the box, the system is only configured with a Base LRM. This is due to the substantial differences in Alt LRMs from different transportation agencies.
The following sections describe the information that is needed to define the base and alternate referencing systems. An agency can choose to define multiple (can be more than two) Alternate Referencing Systems/Methods. However, only one Base Referencing System/Method can be defined.
When identifying the various linear referencing systems / methods that will be used by the users of the AgileAssets system to locate events (maintenance work, crashes, etc.) on the map, following information needs to be specified:

Name of all Location Referencing Systems / Methods (Base & Alternate)
List of Column / fields that Define the Attributes & Measures of the Base & Alternate Referencing Systems / Methods

When defining a base / alternate referencing system in the Agile System the user configures how the references will be listed. A base / alternate reference consists of two types of information:

Attribute values – these are one or more sets of fields that are used to define the location of an event. Examples of these labels are District Name, Route Name, and County Name. Since a location event can start in one area and end in a different area, a location event can have separate labels for start and end. For example, COUNTY_FROM and COUNTY_END are used to record name of the county that the location event starts and ends respectively.
Measurements – There is always a pair of columns in the alternate referencing definition (as defined previously) that defines a "from" and a "to" measurement for the linear reference, these could be
1. Mile-point, mile marker,
2. street address, offsets, stations etc.

These columns always are a pair. Point locations are identified by the "from" and "to" measures being equal.

In AgileAssets system, LRMs are configured in the SETUP_LOC_REF and SETUP_LOC_REF_COLUMN tables in the database. This list of all referencing systems is maintained in the SETUP_LOC_REF database table, whereas the list of all the columns / fields associated with each base / alternate referencing system is stored in the SETUP_LOC_REF_COLUMN database table. The following present the data structure and sample data of these tables.

SETUP_LOC_REF Structure

Column Name	PK	Nullable?	Data Type	Comment
LOC_REF_ID	Y	N	INTEGER	Unique identifier of LRM
LOC_REF_NAME		N	VARCHAR2(400)	LRM Name
NETWORK_ID			INTEGER	LRM Network ID
IS_BASIC_LOC_REF			INTEGER	Whether LRM is Base or Alternate
NET_LRM_ID			INTEGER
ALLOW_ROUTE_SPANNING			INTEGER
SPAN_COLUMNS			INTEGER

Example SETUP_LOC_REF Data with a County-based Alt LRM
AgileAssets Learning Center > Base & Alt. LRMs Setup > worddav0e183dd4b539b799713be5a63c99ef2d.png

SETUP_LOC_REF_COLUMN Structure

Column Name	Nullable?	Data Type	Comment
LOC_REF_ID	N	INTEGER	LRM corresponding to the column listed in COLUMN_ID field
COLUMN_ID	N	VARCHAR2(200)	LRM column unique identifier
ORDER_ID		INTEGER	Order of the fields when displayed on the screen
INITIAL_VALUE		VARCHAR2(48)	Default value on insert of a new record in the system
LOC_REF_COLUMN_TYPE_ID	N	INTEGER	Type of LRM Column
SHOW_TO_FLAG		INTEGER	1/0 Field that indicates if the "To" field is shown on screen. If this is set to 0 then the "To" field is automatically set to the value of the "From" field
COLUMN_ID_TO		VARCHAR2(200)	Name of the Column that contains the TO data
NET_LOOKUP_TABLE		VARCHAR2(120)	If the LRS column is a list of values this is the table that defines the list. This is used to connect list columns to the AgileAssets Network Manager Product if network manager is not being used these three field may be left blank.
NET_LOOKUP_ID_COLUMN		VARCHAR2(120)	This is the ID field within the NET_LOOKUP_TABLE
NET_LOOKUP_NAME_COLUMN		VARCHAR2(120)	This is the label field within the NET_LOOKUP_TABLE
NET_ORDER_ID		INTEGER
ORDER_ID_TO		INTEGER	The Display Order for the "To" column
HIDDEN		INTEGER	If equal to 1 will not be displayed by default in newly created windows

Example SETUP_LOC_REF_COLUMN Data with a County-based Alt. LRM
AgileAssets Learning Center > Base & Alt. LRMs Setup > worddav3aff39cbc819bd9c799ce7c3d7ebb9a8.png

SETUP_LOC_REF table contains the base / alternate referencing name and ID number. The SETUP_LOC_REF_COLUMN table defines the ATTRIBUTE, MEASURE and ACROSS columns for the base / alternative linear referencing method. It should be noted that as shown in exhibits above, these tables' records are a set of field names and not road data. The configuration columns are:

LOC_REF_ID - This is the identifier of the alternate referencing system, it is a unique system generated integer and it corresponds to the "XX" in the table LOC_REF_XX mentioned throughout this document. It should be noted that each referencing system (including base) has its own unique number for loc_ref_id.
COLUMN_ID - This is the defined location reference attribute column used for alternate referencing within the system for the beginning of each event. These column names will appear in the LOC_REF_XX table and as added columns to the SETUP_LOC_IDENT table (see the structure above).
COLUMN_ID_TO - This is the defined location reference attribute column used for alternate referencing within the system for the end of each linear event. These column names will appear in the LOC_REF_XX table and as added columns to the SETUP_LOC_IDENT table (see the structure above). XX is the corresponding LOC_REF_ID.
LOC_REF_COLUMN_TYPE_ID This can be one of three values:
1. Location Attribute: "1" indicates a location referencing attribute, which is a description of the location reference, for example a county number, street name, route name etc. In the example used in this guide, ROUTE_ID is used as the location attribute for the base LRM. COUNTY_ FROM, COUNTY_TO, COUNTY_ROUTE_FROM and COUNTY_ROUTE_TO are used as attributes for the alternate referencing system.
2. Location Measure: "2" indicates a location referencing measure, which is a measurement along a linear reference, for example a street address number, milepoint, kilometer point etc. In the example used in this guide, OFFSET_FROM / OFFSET_TO (statewide) are used as measures in the Base LRS, and COUNTY_MEASURE_FROM and COUNTY_MEASURE_TO are used to define measures in alternate referencing system.
3. Location Across: "3" indicates a field that is used to capture location referencing across the highway roadbed. Usually these fields are LANE_DIR, LANE_ID and PERPEN_OFFSET. They are used to capture the location information across the highway roadbed for both base and Alternate referencing methods
SHOW_TO_FLAG – This column indicates whether the COLUMN_ID and COLUMN_ID_TO columns may contain different values. For example, if a road section crosses over a county line, route name can be different for the start and end points of the section. Setting SHOW_TO_FLAG equal to 1 for ROUTE_NAME field will then allow the user to store different values for route name for the start (FROM point) and end (TO point) of the road section.
1. If this flag is 0 the system automatically hides the COLUMN_ID_TO value on all windows in the application and sets its value to be equal to the COLUMN_ID value whenever the user edits a record in alternate referencing. This forces the "from" and "to" attribute values to be equal at all times.
2. If this flag is 1 then the values contained in the COLUMN_ID and COLUMN_ID_TO columns may be different and the TO value is shown on screen for user editing.

Windows

Out of the box, the LRS setup window is located at System > LRS/GIS > Setup Location Reference Methods (LRMs).

User can use the Setup Referencing Location Methods window to see the columns that define the base referencing system (as denoted by a shadow check mark in the Basic LRS? column of the LRSs pane) and define the columns that constitute each alternate reference system. An example of the Setup LRS window is shown in Error! Reference source not found.below.

The Setup Reference Location Methods window contains two panes: the LRSs pane and the Columns pane.

LRSs Pane

The LRSs pane shows the base referencing system (as denoted by a shadow check mark in the Basic LRS? column) along with a record for each alternate referencing system. The text entered in the Loc Ref Name column in the LRSs pane is what will appear in the drop-down list in the Location Reference field on the side of the browser window. Example of the setup LRS window is shown in the screenshot below.
AgileAssets Learning Center > Base & Alt. LRMs Setup > worddavc50c737f1cea44ef936eee5923e14ea8.png

Bottom pane (Columns Pane) of this screen shows the fields that are associated to the LRM being highlighted in the upper pane. By right clicking on each pane, you will get a set of commands/functionalities on configured LRMs and their associated fields.

Columns Pane

Note: The values set in this pane for the base LRM should never be changed without first consulting AgileAssets.

The Columns pane shows the columns that define the reference system selected in the upper pane. The following columns appear in this pane:

Column ID – This column shows the internal column ID used in the system.
Column ID "To" – This column shows the internal column ID used in the system. The contents of this field are not used if the Show "To" check box is clear.
Column Label – This column shows the column heading label that appears for the column throughout the system.
Column Label "To" – This column shows the column heading label that appears for this column throughout the system. The contents of this field are not used if the Show "To" check box is clear.
Initial Value – If a value appears in this column, then the value will be automatically set to any record that is location-referenced when it is inserted into the system. The initial value can be overwritten later by import or manually.
LRS Column Type – This column indicates whether the column is the "location measure" column. In defining an alternate reference system, one and only one column must be set as the "location measure"; all other columns must be set as "location attribute".
Order – This column indicates the order (left to right) in which the columns are seen in data windows throughout the system. Note: "To" columns are always situated to the right of "From" columns regardless of what is specified here.
Show "To"? – If the check box in this column is checked, then this LRM field will be shown twice (once with_from and once with _to suffix) in any location-referenced window when an alternate LRM is selected. It is shown once for the "from" point and then again for the "to" point. If clear, then the LRM field appears once and applies to both "from" and "to" points. Note: This column must be selected for the column designated "location measure." The purpose of the "Show To" column is specifying the fields that can have different values for "From" and "To" fields. For example, depending on how an LRM is defined, route name can always be similar for beginning and end of a route section. In this case, "Show To" should be left unchecked. Conversely, route name can be different in the beginning and end of a route section (e.g. when county name is part of route name and route section crosses county line). In this case, "Show To" should be be checked.