Oracle Spatial User's Guide and Reference
Release 9.0.1

Part Number A88805-01

Library

Product

Contents

Index

Go to previous page Go to next page

6
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 6.6. However, you may want to read the rest of this chapter first, to understand the concepts that the example illustrates.

For reference information about LRS functions, see Chapter 15.

If you have LRS data from a previous release of Spatial, see Section A.5 for information about migrating LRS data.

6.1 Terms and Concepts

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

6.1.1 Geometric Segments (LRS Segments)

Geometric segments are basic LRS elements in Oracle Spatial. A geometric segment can be any of the following:

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 6-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 6-1).

Figure 6-1 Geometric Segment


Illustration of a geometric segment.

6.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 you define the LRS segment using the SDO_LRS.DEFINE_GEOM_SEGMENT procedure.

6.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. The direction is determined by the order of the vertices (from start point to end point) in the geometry definition. Measures of points on a geometric segment always either increase or decrease along the direction of the geometric segment.

6.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 (for increasing values) or end point (for decreasing values) 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 6.1.5.

6.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.

The unit of measurement for an offset is the same as for the coordinate system associated with the geometric segment. For geodetic data, the default unit of measurement is meters.

Figure 6-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.

Figure 6-2 Describing a Point Along a Segment with a Measure and an Offset


Illustration of describing a point along a segment with a measure and an offset.

6.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 6-3 and the example in Figure 6-4.

Figure 6-3 Measures, Distances, and Their Mapping Relationship


Illustration of measures, distances, and their mapping relationship.

Figure 6-4 Measure Populating of a Geometric Segment


Illustration of 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 6-5 shows the measure population that results when assigned measure values are not proportional and reflect widely varying gaps.

Figure 6-5 Measure Populating With Disproportional Assigned Measures


Illustration of measure populating with disproportional gaps between 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.

6.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.

6.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.

6.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.

6.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 6-6.

Figure 6-6 Linear Feature, Geometric Segments, and LRS Points


Illustration of a linear feature, geometric segments, and LRS points. The illustration includes direction of each segment.

6.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 6-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 6-1).

Example 6-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 6-7.

Figure 6-7 Creating a Geometric Segment


Illustration of creating a geometric segment.

In Figure 6-7, the geometric segment has the following definition (with measure values underlined):

SDO_GEOMETRY(3302, 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, 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 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 3301.

6.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.

Example 6-2 creates a spatial R-tree index on LRS data. It specifies SDO_INDX_DIMS=2 to index only the X and Y dimensions.

Example 6-2 Creating an Index on LRS Data

CREATE INDEX lrs_routes_idx ON lrs_routes(route_geometry)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX
  PARAMETERS('SDO_INDX_DIMS=2');

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

6.4 3D Formats of LRS Functions

Most LRS functions have formats that end in _3D: for example, DEFINE_GEOM_SEGMENT_3D, CLIP_GEOM_SEGMENT_3D, FIND_MEASURE_3D, and LOCATE_PT_3D. If a function has a 3D format, it is identified in the Usage Notes for the function in Chapter 15.

The 3D formats should be used only when the geometry object has 4 dimensions and the fourth dimension is the measure (for example, X, Y, Z, and M), and only when you want the function to consider the first 3 dimensions (for example, X, Y, and Z). If the standard format of a function (that is, without the _3D) is used on a geometry with 4 dimensions, the function considers only the first 2 dimensions (for example, X and Y).

For example, the following format considers the X, Y, and Z dimensions of the specified GEOM object in performing the clip operation:

SELECT  SDO_LRS.CLIP_GEOM_SEGMENT_3D(a.geom, m.diminfo, 5, 10)
  FROM routes r, user_sdo_geom_metadata m
  WHERE m.table_name = 'ROUTES' AND m.column_name = 'GEOM'
    AND r.route_id = 1;

However, the following format considers only the X and Y dimensions, and ignores the Z dimension, of the specified GEOM object in performing the clip operation:

SELECT  SDO_LRS.CLIP_GEOM_SEGMENT(a.geom, m.diminfo, 5, 10)
  FROM routes r, user_sdo_geom_metadata m
  WHERE m.table_name = 'ROUTES' AND m.column_name = 'GEOM'
    AND r.route_id = 1;

The parameters for the standard and 3D formats of any function are the same, and the usage notes apply to both formats.

The 3D formats are not supported with geodetic data.

6.5 LRS Operations

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

6.5.1 Defining a Geometric Segment

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

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

Figure 6-8 Defining a Geometric Segment


Illustration of defining a geometric segment, showing several options.

An LRS segment must be defined (or must already exist) 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.

6.5.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 6-9 shows the redefinition of a segment where the existing (before) assigned measure values are not proportional and reflect widely varying gaps.

Figure 6-9 Redefining a Geometric Segment


Illustration of redefining a geometric segment.

After the segment redefinition in Figure 6-9, the populated measures reflect proportional distances along the segment.

6.5.3 Clipping a Geometric Segment

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

Figure 6-10 Clipping, Splitting, and Concatenating Geometric Segments


Illustration of clipping (part a), splitting (part b), and concatenating (part c) a geometric segment.

6.5.4 Splitting a Geometric Segment

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


Note:

In Figure 6-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 6-10, parts b and c) are actually connected. The tolerance (see Section 1.5.5) is considered in determining whether or not segments are connected. 


6.5.5 Concatenating Geometric Segments

You can create a new geometric segment by concatenating two geometric segments (Figure 6-10, part c). Note that the geometric segments do not need to be spatially connected, although they are connected in the illustration in Figure 6-10, part c. 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 6-10 are shown in Figure 6-11. Measure information and segment direction are preserved in a consistent manner. The assignment is done automatically when the operations have completed.

Figure 6-11 Measure Assignment in Geometric Segment Operations


Illustration of measure assignment in geometric segment operations: splitting (part a), clipping (part b), and concatenation (part c).

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 6-12.

Figure 6-12 Segment Direction with Concatenation


Illustration of segment direction as a result of concatenation (always same as first segment).

In addition to explicitly concatenating two connected segments using the SDO_LRS.CONCATENATE_GEOM_SEGMENTS function, you can perform aggregate concatenation: that is, you can concatenate all connected geometric segments in a column (layer) using the SDO_AGGR_LRS_CONCAT spatial aggregate function. (See the description and example of the SDO_AGGR_LRS_CONCAT spatial aggregate function in Chapter 13.)

6.5.6 Scaling a Geometric Segment

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

Figure 6-13 Scaling a Geometric Segment


Illustration of 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:

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

6.5.7 Offsetting a Geometric Segment

You can create a new geometric segment by performing an offsetting operation on a geometric segment. Figure 6-14 shows the mapping relationship for geometric segment offsetting.

Figure 6-14 Offsetting a Geometric Segment


Illustration of creating a geometric segment by offsetting part of another geometric segment between specified start and end measures.

In the offsetting operation shown in Figure 6-14, the resulting geometric segment is offset by 5 units from the specified start and end measures of the original segment.

For more information, see usage notes and examples for the SDO_LRS.OFFSET_GEOM_SEGMENT function in Chapter 15.

6.5.8 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 6-15).

Figure 6-15 Locating a Point Along a Segment with a Measure and an Offset


Illustration of locating a point along a segment with a measure and an offset. The offset of the point to be located is positive if it is to the left along the segment direction and negative if it is to the right along the segment direction.

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 6-16).

Figure 6-16 Ambiguity in Location Referencing with Offsets


Illustration of ambiguity in location referencing with offsets.

As shown in Figure 6-16, 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.

6.5.9 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 6-15. 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 6-16). 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 6-17).

Figure 6-17 Multiple Projection Points


Illustration of multiple projection points.

6.5.10 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:

Figure 6-18 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 6-18.

Figure 6-18 Conversion from Standard to LRS Line String


Illustration of converting a standard line string to an LRS line string.

The conversion functions are listed in Table 15-3 in Chapter 15. See also the reference information in Chapter 15 about each conversion function.

6.6 Example of LRS Functions

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

This example uses the road that is illustrated in Figure 6-19.

Figure 6-19 Simplified LRS Example: Highway


Illustration of the highway used for the simplified LRS example in this section. This illustration and the example are referred to often in the reference chapter on LRS functions and procedures.

In Figure 6-19, 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 6-1 lists an actual highway-related feature and the LRS feature that corresponds to it or that can be used to represent it.

Table 6-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) 

SDO_LRS.CONCATENATE_GEOM_SEGMENTS function 

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 

Guard rail or fence alongside a road. 

SDO_LRS.OFFSET_GEOM_SEGMENT function 

Example 6-3 does the following:

Example 6-4 includes the output of the SELECT statements in Example 6-3.

Example 6-3 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(
    3302,  -- 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 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.
INSERT INTO USER_SDO_GEOM_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
);

-- Create the spatial index.
-- Use SDO_INDX_DIMS parameter, to index only X, Y dimensions.
CREATE INDEX lrs_routes_idx ON lrs_routes(route_geometry)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX
  PARAMETERS('SDO_INDX_DIMS=2');

-- 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 
  user_sdo_geom_metadata m
  WHERE m.table_name = 'LRS_ROUTES' AND m.column_name = 'ROUTE_GEOMETRY';

-- Define the LRS segment for Route1. This will populate any null measures.
-- No need to specify start and end measures, because they're already defined 
-- in the geometry.
SDO_LRS.DEFINE_GEOM_SEGMENT (geom_segment, dim_array);

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);

-- Update and insert geometries into table, to display later.
UPDATE lrs_routes a SET a.route_geometry = geom_segment
   WHERE a.route_id = 1;

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,
           b.route_geometry, 0.005)
  FROM lrs_routes a, lrs_routes b
  WHERE a.route_id = 11 AND b.route_id = 12;

-- Is the Route1 segment valid?
SELECT  SDO_LRS.VALID_GEOM_SEGMENT(route_geometry)
  FROM lrs_routes WHERE route_id = 1;

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

-- Is the Route1 segment defined?
SELECT  SDO_LRS.IS_GEOM_SEGMENT_DEFINED(route_geometry)
  FROM lrs_routes WHERE route_id = 1;

-- How long is Route1?
SELECT  SDO_LRS.GEOM_SEGMENT_LENGTH(route_geometry)
  FROM lrs_routes WHERE route_id = 1;

-- What is the start measure of Route1?
SELECT  SDO_LRS.GEOM_SEGMENT_START_MEASURE(route_geometry)
  FROM lrs_routes WHERE route_id = 1;

-- What is the end measure of Route1?
SELECT  SDO_LRS.GEOM_SEGMENT_END_MEASURE(route_geometry)
  FROM lrs_routes WHERE route_id = 1;

-- What is the start point of Route1?
SELECT  SDO_LRS.GEOM_SEGMENT_START_PT(route_geometry)
  FROM lrs_routes WHERE route_id = 1;

-- What is the end point of Route1?
SELECT  SDO_LRS.GEOM_SEGMENT_END_PT(route_geometry)
  FROM lrs_routes WHERE 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;

-- "Convert" mile measures to kilometers (27 * 1.609 = 43.443)
SELECT  SDO_LRS.SCALE_GEOM_SEGMENT(route_geometry, 0, 43.443, 0)
  FROM lrs_routes WHERE route_id = 1;

-- Clip a piece of Route1.
SELECT  SDO_LRS.CLIP_GEOM_SEGMENT(route_geometry, 5, 10)
  FROM lrs_routes WHERE route_id = 1;

-- Point (9,3,NULL) is off the road; should return (9,4,9).
SELECT  SDO_LRS.PROJECT_PT(route_geometry, 
  MDSYS.SDO_GEOMETRY(3301, NULL, NULL, 
     MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1), 
     MDSYS.SDO_ORDINATE_ARRAY(9, 3, NULL)) )
  FROM lrs_routes WHERE 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(3301, 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(3301, 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(route_geometry, 9, 0)
  FROM lrs_routes WHERE route_id = 1;

Example 6-4 shows the output of the SELECT statements in Example 6-3.

Example 6-4 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(3302, 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, 18, 8, 10, 22, 5, 14, 27))        
                                                                                
        11 result_geom_1                                                        
SDO_GEOMETRY(3302, 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(3302, 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(3302, 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,
  2  		b.route_geometry, 0.005)
  3    FROM lrs_routes a, lrs_routes b
  4    WHERE a.route_id = 11 AND b.route_id = 12;

SDO_LRS.CONNECTED_GEOM_SEGMENTS(A.ROUTE_GEOMETRY,B.ROUTE_GEOMETRY,0.005)        
--------------------------------------------------------------------------------
TRUE

SQL> 
SQL> -- Is the Route1 segment valid?
SQL> SELECT  SDO_LRS.VALID_GEOM_SEGMENT(route_geometry)
  2    FROM lrs_routes WHERE route_id = 1;

SDO_LRS.VALID_GEOM_SEGMENT(ROUTE_GEOMETRY)                                      
--------------------------------------------------------------------------------
TRUE 

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

SDO_LRS.VALID_MEASURE(ROUTE_GEOMETRY,50)                                        
--------------------------------------------------------------------------------
FALSE 

SQL> 
SQL> -- Is the Route1 segment defined?
SQL> SELECT  SDO_LRS.IS_GEOM_SEGMENT_DEFINED(route_geometry)
  2    FROM lrs_routes WHERE route_id = 1;

SDO_LRS.IS_GEOM_SEGMENT_DEFINED(ROUTE_GEOMETRY)                                 
--------------------------------------------------------------------------------
TRUE 

SQL> 
SQL> -- How long is Route1?
SQL> SELECT  SDO_LRS.GEOM_SEGMENT_LENGTH(route_geometry)
  2    FROM lrs_routes WHERE route_id = 1;

SDO_LRS.GEOM_SEGMENT_LENGTH(ROUTE_GEOMETRY)                                     
-------------------------------------------                                     
                                         27  

SQL> 
SQL> -- What is the start measure of Route1?
SQL> -- What is the start measure of Route1?
SQL> SELECT  SDO_LRS.GEOM_SEGMENT_START_MEASURE(route_geometry)
  2    FROM lrs_routes WHERE route_id = 1;

SDO_LRS.GEOM_SEGMENT_START_MEASURE(ROUTE_GEOMETRY)                              
--------------------------------------------------                              
                                                 0  

SQL> 
SQL> -- What is the end measure of Route1?
SQL> SELECT  SDO_LRS.GEOM_SEGMENT_END_MEASURE(route_geometry)
  2    FROM lrs_routes WHERE route_id = 1;

SDO_LRS.GEOM_SEGMENT_END_MEASURE(ROUTE_GEOMETRY)                                
------------------------------------------------                                
                                              27  

SQL> 
SQL> -- What is the start point of Route1?
SQL> SELECT  SDO_LRS.GEOM_SEGMENT_START_PT(route_geometry)
  2    FROM lrs_routes WHERE route_id = 1;

SDO_LRS.GEOM_SEGMENT_START_PT(ROUTE_GEOMETRY)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, 
--------------------------------------------------------------------------------
SDO_GEOMETRY(3301, NULL, 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(route_geometry)
  2    FROM lrs_routes WHERE route_id = 1;

SDO_LRS.GEOM_SEGMENT_END_PT(ROUTE_GEOMETRY)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y,
--------------------------------------------------------------------------------
SDO_GEOMETRY(3301, NULL, 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 m.column_name = 'ROUTE_GEOMETRY'
  4  	 AND a.route_id = 1;

SDO_LRS.SCALE_GEOM_SEGMENT(A.ROUTE_GEOMETRY,M.DIMINFO,0,27,5)(SDO_GTYPE, SDO_SRI
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, 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> -- "Convert" mile measures to kilometers (27 * 1.609 = 43.443)
SQL> SELECT  SDO_LRS.SCALE_GEOM_SEGMENT(route_geometry, 0, 43.443, 0)
  2    FROM lrs_routes WHERE route_id = 1;

SDO_LRS.SCALE_GEOM_SEGMENT(ROUTE_GEOMETRY,0,43.443,0)(SDO_GTYPE, SDO_SRID, SDO_P
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, 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(route_geometry, 5, 10)
  2    FROM lrs_routes WHERE route_id = 1;

SDO_LRS.CLIP_GEOM_SEGMENT(ROUTE_GEOMETRY,5,10)(SDO_GTYPE, SDO_SRID, SDO_POINT(X,
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, 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(route_geometry,
  2    MDSYS.SDO_GEOMETRY(3301, NULL, NULL,
  3  	  MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1),
  4  	  MDSYS.SDO_ORDINATE_ARRAY(9, 3, NULL)) )
  5    FROM lrs_routes WHERE route_id = 1;

SDO_LRS.PROJECT_PT(ROUTE_GEOMETRY,MDSYS.SDO_GEOMETRY(3301,NULL,NULL,MDSYS.SDO_EL
--------------------------------------------------------------------------------
SDO_GEOMETRY(3301, 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(3301, 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 m.column_name = 'ROUTE_GEOMETRY'
  9  	 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(3301, 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 m.column_name = 'ROUTE_GEOMETRY'
  8  	 AND a.route_id = 1;

SDO_LRS.VALID_LRS_PT(MDSYS.SDO_GEOMETRY(3301,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(route_geometry, 9, 0)
  2    FROM lrs_routes WHERE route_id = 1;

SDO_LRS.LOCATE_PT(ROUTE_GEOMETRY,9,0)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), S
--------------------------------------------------------------------------------
SDO_GEOMETRY(3301, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(
9, 4, 9)) 


Go to previous page Go to next page
Oracle
Copyright © 2001 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index