Oracle Spatial User's Guide and ReferenceRelease 8.1.7Part Number A85337-01

E Linear Referencing System

Linear referencing is a natural and convenient means to associate attributes or events to locations or portions of a linear feature. It has been widely used in transportation applications (such as for highways, railroads, and transit routes) and utilities applications (such as for gas and oil pipelines). The major advantage of linear referencing is its capability of locating attributes and events along a linear feature with only one parameter (usually known as measure) instead of two (such as latitude/longitude or x/y in Cartesian space). Sections of a linear feature can be referenced and created dynamically by indicating the start and end locations along the feature without explicitly storing them.

The linear referencing system (LRS) application programming interface (API) in Oracle Spatial provides server-side LRS capabilities at the cartographic level. The linear measure information is directly integrated into the Oracle Spatial geometry structure. The Oracle Spatial LRS API provides support for dynamic segmentation, and it serves as a groundwork for third-party or middle-tier application development virtually for any linear referencing methods and models in any coordinate systems.

For an example of LRS, see Section E.5. However, you may want to read the rest of this appendix first, to understand the concepts that the example illustrates.

For reference information about LRS functions, see Chapter 9.

E.1 Terms and Concepts

This section explains important terms and concepts related to linear referencing support in Oracle Spatial.

E.1.1 Geometric Segments (LRS Segments)

Geometric segments are basic LRS elements in Oracle Spatial. They are Oracle line string geometries. An Oracle line string is an ordered, non-branching, and continuous geometry. A geometric segment must contain at least start and end measures for its start and end points. Measures of points of interest (such as highway exits) on the geometric segments can also be assigned. These measures are either assigned by users or derived from existing geometric segments. Figure E-1 shows a geometric segment with four line segments and one arc. Points on the geometric segment are represented by triplets (x, y, m), where x and y describe the location and m denotes the measure (with each measure value underlined in Figure E-1).

E.1.2 Shape Points

Shape points are points that are specified when an LRS segment is constructed, and that are assigned measure information. In Oracle Spatial, a line segment is represented by its start and end points, and an arc is represented by three points: start, middle, and end points of the arc. You must specify these points as shape points, but you can also specify other points as shape points if you need measure information stored for these points (for example, an exit in the middle of a straight part of the highway).

Thus, shape points can serve one or both of the following purposes: to indicate the direction of the segment (for example, a turn or curve), and to identify a point of interest for which measure information is to be stored.

Shape points might not directly relate to mileposts or reference posts in LRS; they are used as internal reference points. The measure information of shape points is automatically populated when the LRS segment is defined.

E.1.3 Direction of a Geometric Segment

The direction of a geometric segment is indicated from the start point of the geometric segment to the end point. Measures of points on a geometric segment always increase along the direction of the geometric segment.

E.1.4 Measure (Linear Measure)

The measure of a point along a geometric segment is the linear distance (in the measure dimension) measured from the start point of the geometric segment. The measure information does not necessarily have to be of the same scale as their Euclidean distance. However, the linear mapping relationship between measure and distance is always preserved.

Some LRS functions use offset instead of measure to represent measured distance along linear features. Although some other linear referencing systems might use offset to mean what the Oracle Spatial LRS refers to as measure, offset has a different meaning in Oracle Spatial from measure, as explained in Section E.1.5.

E.1.5 Offset

The offset of a point along a geometric segment is the perpendicular distance between the point and the geometric segment. Offsets are positive if points are on the left side along the segment direction and are negative if they are on the right side. Points are on a geometric segment if their offsets to the segment are zero.

Figure E-2 shows how a point can be located along a geometric segment with measure and offset information. By assigning an offset together with a measure, it is possible to locate not only points that are on the geometric segment, but also points that are perpendicular to the geometric segment.

E.1.6 Measure Populating

Any unassigned measures of a geometric segment are automatically populated based upon their distance distribution. This is done before any LRS operations for geometric segments with unknown measures (NULL in Oracle Spatial). The resulting geometric segments from any LRS operations return the measure information associated with geometric segments. The measure of a point on the geometric segment can be obtained based upon a linear mapping relationship between its previous and next known measures or locations. See the algorithm representation in Figure E-3 and the example in Figure E-4.

Figure E-4 Measure Populating of a Geometric Segment Measures are evenly spaced between assigned measures. However, the assigned measures for points of interest on a geometric segment do not need to be evenly spaced. This could eliminate the problem of error accumulation and account for inaccuracy of data source.

Moreover, the assigned measures do not even need to reflect actual distances; they can be any valid values within the measure range. For example, Figure E-5 shows the measure population that results when assigned measure values are not proportional and reflect widely varying gaps.

Figure E-5 Measure Populating With Disproportional Assigned Measures In all cases, measure populating is done in an incremental fashion along the segment direction. This improves the performance of current and subsequent LRS operations.

E.1.7 Measure Range of a Geometric Segment

The start and end measures of a geometric segment define the linear measure range of the geometric segment. Any valid LRS measures of a geometric segment must fall within its linear measure range.

E.1.8 Projection

The projection of a point along a geometric segment is the point on the geometric segment with the minimum distance to the point. The measure information of the resulting point is also returned in the point geometry.

E.1.9 LRS Point

LRS points are points with linear measure information along a geometric segment. A valid LRS point is a point geometry with measure information.

E.1.10 Linear Features

Linear features are any spatial objects that can be treated as a logical set of linear segments. Examples of linear features are highways in transportation applications and pipelines in utility industry applications. The relationship of linear features, geometric segments, and LRS points is shown in Figure E-6.

E.2 LRS Data Model

The Oracle Spatial LRS data model incorporates measure information into its geometry representation at the point level. The measure information is directly integrated into the Oracle Spatial model. To accomplish this, an additional measure dimension must be added to the Oracle Spatial metadata.

Oracle Spatial LRS support affects the Spatial metadata and data (the geometries). Example E-1 shows how a measure dimension can be added to 2-dimensional geometries in the Spatial metadata. The measure dimension must be the last element of the SDO_DIM_ARRAY in a spatial object definition (shown in bold in Example E-1).

Example E-1 Including LRS Measure Dimension in Spatial Metadata

INSERT INTO user_sdo_geom_metadata VALUES(
'LRS_ROUTES',
'GEOMETRY',
MDSYS.SDO_DIM_ARRAY (
MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.005),
MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.005),
MDSYS.SDO_DIM_ELEMENT('M', 0, 100, 0.005)),
NULL);

After adding the new measure dimension, geometries with measure information such as geometric segments and LRS points can be represented. An example of creating a geometric segment with three line segments is shown in Figure E-7.

Figure E-7 Creating a Geometric Segment In Figure E-7, the geometric segment has the following definition (with measure values underlined):

SDO_GEOMETRY(3002, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),
MDSYS.SDO_ORDINATE_ARRAY(5,10,0, 20,5,NULL, 35,10,NULL, 55,10,100))

Whenever a geometric segment is defined or created, its start and end measures must be defined or derived from some existing geometric segment. The unsigned measures of all shape points on a geometric segment will be automatically populated.

The LRS API supports the object-relational model of Oracle Spatial. The LRS API works with geometries in formats of Oracle Spatial before release 8.1.6, but the resulting geometries will be converted to the Oracle Spatial release 8.1.6 or higher format, specifically with 4-digit SDO_GTYPE and SDO_ETYPE values.

For example, in Oracle Spatial release 8.1.6 and higher, the geometry type (SDO_GTYPE) of a spatial object includes the number of dimensions of the object as the first digit of the SDO_GTYPE value. Thus, the SDO_GTYPE value of a point is 1 in the pre-release 8.1.6 format but 2001 in the release 8.1.6 format (the number of dimensions of the point is 2). However, an LRS point (which includes measure information) has 3 dimensions, and thus the SDO_GTYPE of any point geometry used with an LRS function must be 3001.

E.3 Indexing of LRS Data

When LRS data is indexed using a spatial quadtree index, only the first two dimensions are indexed; the measure dimension and values are not indexed.

When LRS data is indexed using a spatial R-tree index, you must use the SDO_INDX_DIMS keyword in the CREATE INDEX statement in order to limit the number of dimensions to be indexed (for example, SDO_INDX_DIMS=2 to index only the X and Y dimensions and not the measure dimension, or SDO_INDX_DIMS=3 to index only the X, Y, and Z dimensions and not the measure dimension). There is no benefit to including the measure dimension in a spatial index, and there is additional processing overhead; therefore, you should use the SDO_INDX_DIMS keyword when spatially indexing LRS data.

Information about the CREATE INDEX statement and its parameters and keywords is in Chapter 5.

E.4 LRS Operations

This section describes several linear referencing operations supported by the Oracle Spatial LRS API.

E.4.1 Defining a Geometric Segment

There are two ways to create a geometric segment with measure information:

• Construct a geometric segment and assign measures explicitly.

• Define a geometric segment with specified start and end, and/or any other measures, in an ascending order. Measures of shape points with unknown (unassigned) measures (null values) in the geometric segment will be automatically populated according to their locations and distance distribution.

Figure E-8 shows different ways of defining a geometric segment.

Figure E-8 Defining a Geometric Segment An LRS segment must be defined before any LRS operations can proceed. That is, the start, end, and any other assigned measures must be present to derive the location from a specified measure. The measure information of intermediate shape points will automatically be populated if they are not assigned.

E.4.2 Redefining a Geometric Segment

You can redefine a geometric segment to replace the existing measures of all shape points between the start and end point with automatically calculated measures. Redefining a segment can be useful if errors have been made in one or more explicit measure assignments, and you want to start over with proportionally assigned measures.

Figure E-9 shows the redefinition of a segment where the existing (before) assigned measure values are not proportional and reflect widely varying gaps.

Figure E-9 Redefining a Geometric Segment After the segment redefinition in Figure E-9, the populated measures reflect proportional distances along the segment.

E.4.3 Clipping a Geometric Segment

You can clip a geometric segment to create a new geometric segment out of an existing geometric segment (Figure E-10, part a).

E.4.4 Splitting a Geometric Segment

You can create two new geometric segments by splitting a geometric segment (Figure E-10, part b).

 Note: In Figure E-10 and several that follow, small gaps between segments are used in illustrations of segment splitting and concatenation. Each gap simply reinforces the fact that two different segments are involved. However, the two segments (such as segment 1 and segment 2 in Figure E-10, parts b and c) are actually connected. The tolerance (see Section 1.5.4) is considered in determining whether or not segments are connected.

E.4.5 Concatenating Two Connected Geometric Segments

You can create a new geometric segment by concatenating two geometric segments (Figure E-10, part c). Note that the geometric segments must be spatially connected. The measures of the second geometric segment are shifted so that the end measure of the first segment is the same as the start measure of the second segment.

Measure assignments for the clipping, splitting, and concatenating operations in Figure E-10 are shown in Figure E-11. Measure information and segment direction are preserved in a consistent manner. The assignment is done automatically when the operations have completed.

Figure E-11 Measure Assignment in Geometric Segment Operations The direction of the geometric segment resulting from concatenation is always the direction of the first segment (geom_segment1 in the call to the SDO_LRS.CONCATENATE_GEOM_SEGMENTS function), as shown in Figure E-12.

E.4.6 Scaling a Geometric Segment

You can create a new geometric segment by performing a linear scaling on a geometric segment. Figure E-13 shows the mapping relationship for geometric segment scaling.

Figure E-13 Scaling a Geometric Segment In general, scaling a geometric segment only involves rearranging measures of the newly created geometric segment. However, if the scaling factor is negative, the order of the shape points needs to be reversed so that measures will increase along the geometric segment's direction (which is defined by the order of the shape points).

A scale operation can perform any combination of the following operations:

• Translating (shifting) measure information. (For example, add the same value to Ms and Me to get M's and M'e.)

• Reversing measure information. (Let M's = Me, M'e = Ms, and Mshift = 0.)

• Performing simple scaling of measure information. (Let Mshift = 0.)

For examples of these operations, see usage notes and examples for the SDO_LRS.SCALE_GEOM_SEGMENT function in Chapter 9.

E.4.7 Locating a Point on a Geometric Segment

You can find the position of a point described by a measure and an offset on a geometric segment (see Figure E-14).

Figure E-14 Locating a Point Along a Segment with a Measure and an Offset There is always a unique a location with a specific measure on a geometric segment. Ambiguity arises when offsets are given and the points described by the measures fall on shape points of the geometric segment (see Figure E-15).

Figure E-15 Ambiguity in Location Referencing with Offsets As shown in Figure E-15, an offset arc of a shape point on a geometric segment is an arc on which all points have the same minimum distance to the shape point. As a result, all points on the offset arc are represented by the same (measure, offset) pair. To resolve this one-to-many mapping problem, the middle point on the offset arc is returned.

E.4.8 Projecting a Point onto a Geometric Segment

You can find the projection point of a point with respect to a geometric segment. The point to be projected can be on or off the segment. If the point is on the segment, the point and its projection point are the same.

Projection is a reverse operation of the point-locating operation shown in Figure E-14. Similar to a point-locating operation, all points on the offset arc of a shape point will have the same projection point (that is, the shape point itself), measure, and offset (see Figure E-15). If there are multiple projection points for a point, the first one from the start point is returned (projection pt 1 in both illustrations in Figure E-16).

E.4.9 Converting Geometric Segments

You can convert geometric segments from standard line string format to Linear Referencing System format, and vice versa. The main use of conversion functions will probably occur if you have a large amount of existing line string data, in which case conversion is a convenient alternative to creating all of the LRS segments manually. However, if you need to convert LRS segments to standard line strings for certain applications, that capability is provided also.

Functions are provided to convert:

• Individual line strings

For conversion from standard format to LRS format, a measure dimension (named M by default) is added, and measure information is provided for each point. For conversion from LRS format to standard format, the measure dimension and information are removed. In both cases, the dimensional information (DIMINFO) metadata in the USER_SDO_GEOM_METADATA view is not affected.

• Layers (all line strings in a column)

For conversion from standard format to LRS format, a measure dimension (named M by default) is added, but no measure information is provided for each point. For conversion from LRS format to standard format, the measure dimension and information are removed. In both cases, the dimensional information (DIMINFO) metadata in the USER_SDO_GEOM_METADATA view is modified as needed.

• Dimensional information (DIMINFO)

The dimensional information (DIMINFO) metadata in the USER_SDO_GEOM_METADATA view is modified as needed. For example, converting a standard dimensional array with X and Y dimensions (SDO_DIM_ELEMENT) to an LRS dimensional array causes an M dimension (SDO_DIM_ELEMENT) to be added.

Figure E-17 shows the addition of measure information when a standard line string is converted to an LRS line string (using the SDO_LRS.CONVERT_TO_LRS_GEOM function). The measure dimension values are underlined in Figure E-17.

Figure E-17 Conversion from Standard to LRS Line String The conversion functions are listed in Table 9-3 in Chapter 9. See also the reference information in Chapter 9 about each conversion function.

E.5 Example

This section presents a simplified example that uses LRS functions. It refers to concepts that were explained in this chapter and uses functions documented in Chapter 9.

This example uses the road that is illustrated in Figure E-18.

Figure E-18 Simplified LRS Example: Highway In Figure E-18, the highway (Route 1) starts at point 2,2 and ends at point 5,14, follows the path shown, and has six entrance-exit points (Exit 1 through Exit 6). For simplicity, each unit on the graph represents one unit of measure, and thus the measure from start to end is 27 (the segment from Exit 5 to Exit 6 being the hypotenuse of a 3-4-5 right triangle).

Each row in Table E-1 lists an actual highway-related feature and the LRS feature that corresponds to it or that can be used to represent it.

Table E-1 Highway Features and LRS Counterparts
Highway Feature  LRS Feature

Named route, road, or street

LRS segment, or linear feature (logical set of segments)

Mile or kilometer marker

Measure

Accident reporting and location tracking

SDO_LRS.LOCATE_PT function

Construction zone (portion of a road)

SDO_LRS.CLIP_GEOM_SEGMENT function

Road extension (adding at the beginning or end) or combination (designating or renaming two roads that meet as one road)

Road reconstruction or splitting (resulting in two named roads from one named road)

SDO_LRS.SPLIT_GEOM_SEGMENT function

Finding the closest point on the road to a point off the road (such as a building)

SDO_LRS.PROJECT_PT function

Example E-2 does the following:

• Creates a table to hold the segment

• Inserts the definition of the highway into the table

• Inserts the necessary metadata into the USER_SDO_GEOM_METADATA view

• Uses PL/SQL and SQL statements to define the segment and perform operations on it

Example E-3 includes the output of the SELECT statements in Example E-2.

Example E-2 Simplified Example: Highway

-- Create a table for routes (highways).
CREATE TABLE lrs_routes (
route_id  NUMBER PRIMARY KEY,
route_name  VARCHAR2(32),
route_geometry  MDSYS.SDO_GEOMETRY);

-- Populate table with just one route for this example.
INSERT INTO lrs_routes VALUES(
1,
'Route1',
MDSYS.SDO_GEOMETRY(
3002,  -- line string, 3 dimensions: X,Y,M
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
MDSYS.SDO_ORDINATE_ARRAY(
2,2,0,   -- Start point - Exit1; 0 is measure from start.
2,4,2,   -- Exit2; 2 is measure from start.
8,4,8,   -- Exit3; 8 is measure from start.
12,4,12,  -- Exit4; 12 is measure from start.
12,10,NULL,  -- Not an exit; measure will be automatically calculated and
filled.
8,10,22,  -- Exit5; 22 is measure from start.
5,14,27)  -- End point (Exit6); 27 is measure from start.
)
);

-- Update the Spatial metadata.
VALUES (
'lrs_routes',
'route_geometry',
MDSYS.SDO_DIM_ARRAY(   -- 20X20 grid
MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005),
MDSYS.SDO_DIM_ELEMENT('M', 0, 20, 0.005) -- Measure dimension
),
NULL   -- SRID (reserved for future Spatial releases)
);

-- Test the LRS procedures.
DECLARE
geom_segment MDSYS.SDO_GEOMETRY;
line_string MDSYS.SDO_GEOMETRY;
dim_array MDSYS.SDO_DIM_ARRAY;
result_geom_1 MDSYS.SDO_GEOMETRY;
result_geom_2 MDSYS.SDO_GEOMETRY;
result_geom_3 MDSYS.SDO_GEOMETRY;

BEGIN

SELECT a.route_geometry into geom_segment FROM lrs_routes a
WHERE a.route_name = 'Route1';
SELECT m.diminfo into dim_array from
WHERE m.table_name = 'LRS_ROUTES';

-- Define the LRS segment for Route1.
SDO_LRS.DEFINE_GEOM_SEGMENT (geom_segment,
dim_array,
0,    -- Zero starting measure: LRS segment starts at start of route.
27);  -- End of LRS segment is at measure 27.

SELECT a.route_geometry INTO line_string FROM lrs_routes a
WHERE a.route_name = 'Route1';

-- Split Route1 into two segments.
SDO_LRS.SPLIT_GEOM_SEGMENT(line_string,dim_array,5,result_geom_1,result_geom_2);

-- Concatenate the segments that were just split.
result_geom_3 := SDO_LRS.CONCATENATE_GEOM_SEGMENTS(result_geom_1, dim_array,
result_geom_2, dim_array);

-- Insert geometries into table, to display later.
INSERT INTO lrs_routes VALUES(
11,
'result_geom_1',
result_geom_1
);
INSERT INTO lrs_routes VALUES(
12,
'result_geom_2',
result_geom_2
);
INSERT INTO lrs_routes VALUES(
13,
'result_geom_3',
result_geom_3
);

END;
/

-- First, display the data in the LRS table.
SELECT route_id, route_name, route_geometry from lrs_routes;

-- Are result_geom_1 and result_geom2 connected?
SELECT  SDO_LRS.CONNECTED_GEOM_SEGMENTS(a.route_geometry, m.diminfo,
b.route_geometry, m.diminfo)
FROM lrs_routes a, lrs_routes b, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 11 AND b.route_id = 12;

-- Is the Route1 segment valid?
SELECT  SDO_LRS.VALID_GEOM_SEGMENT(a.route_geometry, m.diminfo)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- Is 50 a valid measure on Route1? (Should return FALSE; highest Route1 measure
is 27.)
SELECT  SDO_LRS.VALID_MEASURE(a.route_geometry, m.diminfo, 50)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- Is the Route1 segment defined?
SELECT  SDO_LRS.IS_GEOM_SEGMENT_DEFINED(a.route_geometry, m.diminfo)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- How long is Route1?
SELECT  SDO_LRS.GEOM_SEGMENT_LENGTH(a.route_geometry, m.diminfo)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- What is the start measure of Route1?
SELECT  SDO_LRS.GEOM_SEGMENT_START_MEASURE(a.route_geometry, m.diminfo)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- What is the end measure of Route1?
SELECT  SDO_LRS.GEOM_SEGMENT_END_MEASURE(a.route_geometry, m.diminfo)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- What is the start point of Route1?
SELECT  SDO_LRS.GEOM_SEGMENT_START_PT(a.route_geometry, m.diminfo)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- What is the end point of Route1?
SELECT  SDO_LRS.GEOM_SEGMENT_END_PT(a.route_geometry, m.diminfo)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- Shift by 5 (for example, 5-mile segment added before original start)
SELECT  SDO_LRS.SCALE_GEOM_SEGMENT(a.route_geometry, m.diminfo, 0, 27, 5)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- Reverse direction (for example, to concatenate with another road)
SELECT  SDO_LRS.SCALE_GEOM_SEGMENT(a.route_geometry, m.diminfo,27, 0, 0)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- "Convert" mile measures to kilometers (27 * 1.609 = 43.443)
SELECT     SDO_LRS.SCALE_GEOM_SEGMENT(a.route_geometry, m.diminfo,
0, 43.443, 0)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- Clip a piece of Route1.
SELECT  SDO_LRS.CLIP_GEOM_SEGMENT(a.route_geometry, m.diminfo, 5, 10)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- Point (9,3,NULL) is off the road; should return (9,4,9).
SELECT  SDO_LRS.PROJECT_PT(a.route_geometry, m.diminfo,
MDSYS.SDO_GEOMETRY(3001, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1),
MDSYS.SDO_ORDINATE_ARRAY(9, 3, NULL)) )
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- Return the measure of the projected point.
SELECT  SDO_LRS.GET_MEASURE(
SDO_LRS.PROJECT_PT(a.route_geometry, m.diminfo,
MDSYS.SDO_GEOMETRY(3001, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1),
MDSYS.SDO_ORDINATE_ARRAY(9, 3, NULL)) ),
m.diminfo )
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- Is point (9,3,NULL) a valid LRS point? (Should return TRUE.)
SELECT  SDO_LRS.VALID_LRS_PT(
MDSYS.SDO_GEOMETRY(3001, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1),
MDSYS.SDO_ORDINATE_ARRAY(9, 3, NULL)),
m.diminfo)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

-- Locate the point on Route1 at measure 9, offset 0.
SELECT  SDO_LRS.LOCATE_PT(a.route_geometry, m.diminfo, 9, 0)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

Example E-3 shows the output of the SELECT statements in Example E-2.

Example E-3 Simplified Example: Output of SELECT Statements

SQL> -- First, display the data in the LRS table.
SQL> SELECT route_id, route_name, route_geometry from lrs_routes;

ROUTE_ID ROUTE_NAME
---------- --------------------------------
ROUTE_GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDIN
--------------------------------------------------------------------------------
1 Route1
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
2, 2, 0, 2, 4, 2, 8, 4, 8, 12, 4, 12, 12, 10, NULL, 8, 10, 22, 5, 14, 27))

11 result_geom_1
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
2, 2, 0, 2, 4, 2, 5, 4, 5))

12 result_geom_2

ROUTE_ID ROUTE_NAME
---------- --------------------------------
ROUTE_GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDIN
--------------------------------------------------------------------------------
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
5, 4, 5, 8, 4, 8, 12, 4, 12, 12, 10, 18, 8, 10, 22, 5, 14, 27))

13 result_geom_3
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
2, 2, 0, 2, 4, 2, 5, 4, 5, 8, 4, 8, 12, 4, 12, 12, 10, 18, 8, 10, 22, 5, 14, 27)
)

SQL>
SQL> -- Are result_geom_1 and result_geom2 connected?
SQL> SELECT  SDO_LRS.CONNECTED_GEOM_SEGMENTS(a.route_geometry, m.diminfo,
2  										      b.route_geometry, m.diminfo)
3    FROM lrs_routes a, lrs_routes b, user_sdo_geom_metadata m
4    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 11 AND b.route_id =
12;

SDO_LRS.CONNECTED_GEOM_SEGMENTS(A.ROUTE_GEOMETRY,M.DIMINFO,B.ROUTE_GEOMETRY,M.DI
--------------------------------------------------------------------------------
TRUE

SQL>
SQL> -- Is the Route1 segment valid?
SQL> SELECT  SDO_LRS.VALID_GEOM_SEGMENT(a.route_geometry, m.diminfo)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.VALID_GEOM_SEGMENT(A.ROUTE_GEOMETRY,M.DIMINFO)
--------------------------------------------------------------------------------
TRUE

SQL>
SQL> -- Is 50 a valid measure on Route1? (Should return FALSE; highest Route1
measure is 27.)
SQL> SELECT  SDO_LRS.VALID_MEASURE(a.route_geometry, m.diminfo, 50)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.VALID_MEASURE(A.ROUTE_GEOMETRY,M.DIMINFO,50)
--------------------------------------------------------------------------------
FALSE

SQL>
SQL> -- Is the Route1 segment defined?
SQL> SELECT  SDO_LRS.IS_GEOM_SEGMENT_DEFINED(a.route_geometry, m.diminfo)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.IS_GEOM_SEGMENT_DEFINED(A.ROUTE_GEOMETRY,M.DIMINFO)
--------------------------------------------------------------------------------
TRUE

SQL>
SQL> -- How long is Route1?
SQL> SELECT  SDO_LRS.GEOM_SEGMENT_LENGTH(a.route_geometry, m.diminfo)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.GEOM_SEGMENT_LENGTH(A.ROUTE_GEOMETRY,M.DIMINFO)
-------------------------------------------------------
27

SQL>
SQL> -- What is the start measure of Route1?
SQL> SELECT  SDO_LRS.GEOM_SEGMENT_START_MEASURE(a.route_geometry, m.diminfo)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.GEOM_SEGMENT_START_MEASURE(A.ROUTE_GEOMETRY,M.DIMINFO)
--------------------------------------------------------------
0

SQL>
SQL> -- What is the end measure of Route1?
SQL> SELECT  SDO_LRS.GEOM_SEGMENT_END_MEASURE(a.route_geometry, m.diminfo)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.GEOM_SEGMENT_END_MEASURE(A.ROUTE_GEOMETRY,M.DIMINFO)
------------------------------------------------------------
27

SQL>
SQL> -- What is the start point of Route1?
SQL> SELECT  SDO_LRS.GEOM_SEGMENT_START_PT(a.route_geometry, m.diminfo)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.GEOM_SEGMENT_START_PT(A.ROUTE_GEOMETRY,M.DIMINFO)(SDO_GTYPE, SDO_SRID, S
--------------------------------------------------------------------------------
SDO_GEOMETRY(3001, 0, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(2,
2, 0))

SQL>
SQL> -- What is the end point of Route1?
SQL> SELECT  SDO_LRS.GEOM_SEGMENT_END_PT(a.route_geometry, m.diminfo)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.GEOM_SEGMENT_END_PT(A.ROUTE_GEOMETRY,M.DIMINFO)(SDO_GTYPE, SDO_SRID, SDO
--------------------------------------------------------------------------------
SDO_GEOMETRY(3001, 0, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(5,
14, 27))

SQL>
SQL> -- Shift by 5 (for example, 5-mile segment added before original start)
SQL> SELECT  SDO_LRS.SCALE_GEOM_SEGMENT(a.route_geometry, m.diminfo, 0, 27, 5)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.SCALE_GEOM_SEGMENT(A.ROUTE_GEOMETRY,M.DIMINFO,0,27,5)(SDO_GTYPE, SDO_SRI
--------------------------------------------------------------------------------
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
2, 2, 5, 2, 4, 7, 8, 4, 13, 12, 4, 17, 12, 10, 23, 8, 10, 27, 5, 14, 32))

SQL>
SQL> -- Reverse direction (for example, to concatenate with another road)
SQL> SELECT  SDO_LRS.SCALE_GEOM_SEGMENT(a.route_geometry, m.diminfo,27, 0, 0)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.SCALE_GEOM_SEGMENT(A.ROUTE_GEOMETRY,M.DIMINFO,27,0,0)(SDO_GTYPE, SDO_SRI
--------------------------------------------------------------------------------
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
5, 14, 0, 8, 10, 5, 12, 10, 9, 12, 4, 15, 8, 4, 19, 2, 4, 25, 2, 2, 27))

SQL> -- "Convert" mile measures to kilometers (27 * 1.609 = 43.443)
SQL> SELECT     SDO_LRS.SCALE_GEOM_SEGMENT(a.route_geometry, m.diminfo, 0, 43.44
3, 0)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.SCALE_GEOM_SEGMENT(A.ROUTE_GEOMETRY,M.DIMINFO,0,43.443,0)(SDO_GTYPE, SDO
--------------------------------------------------------------------------------
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
2, 2, 0, 2, 4, 3.218, 8, 4, 12.872, 12, 4, 19.308, 12, 10, 28.962, 8, 10, 35.398
, 5, 14, 43.443))

SQL>
SQL> -- Clip a piece of Route1.
SQL> SELECT  SDO_LRS.CLIP_GEOM_SEGMENT(a.route_geometry, m.diminfo, 5, 10)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.CLIP_GEOM_SEGMENT(A.ROUTE_GEOMETRY,M.DIMINFO,5,10)(SDO_GTYPE, SDO_SRID,
--------------------------------------------------------------------------------
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
5, 4, 5, 8, 4, 8, 10, 4, 10))

SQL>
SQL> -- Point (9,3,NULL) is off the road; should return (9,4,9).
SQL> SELECT  SDO_LRS.PROJECT_PT(a.route_geometry, m.diminfo,
2    MDSYS.SDO_GEOMETRY(3001, NULL, NULL,
3  	  MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1),
4  	  MDSYS.SDO_ORDINATE_ARRAY(9, 3, NULL)) )
5    FROM lrs_routes a, user_sdo_geom_metadata m
6    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.PROJECT_PT(A.ROUTE_GEOMETRY,M.DIMINFO,MDSYS.SDO_GEOMETRY(3001,NULL,NULL,
--------------------------------------------------------------------------------
SDO_GEOMETRY(3001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(
9, 4, 9))

SQL>
SQL> -- Return the measure of the projected point.
SQL> SELECT  SDO_LRS.GET_MEASURE(
2   SDO_LRS.PROJECT_PT(a.route_geometry, m.diminfo,
3    MDSYS.SDO_GEOMETRY(3001, NULL, NULL,
4  	  MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1),
5  	  MDSYS.SDO_ORDINATE_ARRAY(9, 3, NULL)) ),
6   m.diminfo )
7   FROM lrs_routes a, user_sdo_geom_metadata m
8   WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.GET_MEASURE(SDO_LRS.PROJECT_PT(A.ROUTE_GEOMETRY,M.DIMINFO,MDSYS.SDO_GEOM
--------------------------------------------------------------------------------
9
SQL>
SQL> -- Is point (9,3,NULL) a valid LRS point? (Should return TRUE.)
SQL> SELECT  SDO_LRS.VALID_LRS_PT(
2    MDSYS.SDO_GEOMETRY(3001, NULL, NULL,
3  	  MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1),
4  	  MDSYS.SDO_ORDINATE_ARRAY(9, 3, NULL)),
5    m.diminfo)
6    FROM lrs_routes a, user_sdo_geom_metadata m
7    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.VALID_LRS_PT(MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY
--------------------------------------------------------------------------------
TRUE

SQL>
SQL> -- Locate the point on Route1 at measure 9, offset 0.
SQL> SELECT  SDO_LRS.LOCATE_PT(a.route_geometry, m.diminfo, 9, 0)
2    FROM lrs_routes a, user_sdo_geom_metadata m
3    WHERE m.table_name = 'LRS_ROUTES' AND a.route_id = 1;

SDO_LRS.LOCATE_PT(A.ROUTE_GEOMETRY,M.DIMINFO,9,0)(SDO_GTYPE, SDO_SRID, SDO_POINT
--------------------------------------------------------------------------------
SDO_GEOMETRY(3001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(
9, 4, 9))

E.6 Error Messages for Linear Referencing System

This section lists the LRS error messages, including the cause and recommended user action for each.

ORA-13331 invalid LRS segment

Cause: The given LRS segment was not a valid line string.

Action: A valid LRS geometric segment is a line string geometry in Oracle Spatial. It could be a simple or compound line string (made of lines or arcs, or both). The dimension information must include the measure dimension as the last element in Oracle Spatial metadata. Currently, the number of dimensions for an LRS segment must be greater than 2 (x/y or latitude/longitude plus measure).

ORA-13332 invalid LRS point

Cause: The given LRS point was not a valid LRS point.

Action: A valid LRS point is a point geometry in Oracle Spatial with additional measure dimension. The dimension information must include the measure dimension as the last element in the Spatial metadata. Currently, the number of dimensions for an LRS segment must be greater than 2 (x/y or latitude/longitude plus measure).

ORA-13333 invalid LRS measure

Cause: The given measure for linear referencing was out of linear measure range.

Action: The start and end measures of a geometric segment define the measure range of the segment. Any valid measures for a geometric segment must be within its measure range. The measures are always in an ascending order from the start to the end point.

ORA-13334 LRS segments not connected

Cause: The given geometric segments were not connected.

Action: For concatenating two geometric segments, the two segments must be spatially connected. That is, the end point of the first segment must be the same as the start point of the second segment.

ORA-13335 LRS segment is not defined

Cause: The given start or end measures are not defined, or some assigned measures in between are not in an ascending order.

Action: An LRS geometric segment is defined if its start and end measure are assigned (non-null). Any other measures assigned on the segment must be in an ascending order.

ORA-13336 LRS conversion failure

Cause: The conversion of an LRS geometry or layer was not successful.

Action: Check the following to see if they are valid: the geometry type for a geometry conversion, or the geometry type and dimensional information (diminfo) for a layer conversion. For example, polygon geometries are invalid as input to LRS functions.