7.7 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 SDO_LRS Package (Linear Referencing System) .
This example uses the road that is illustrated in Figure 7-20.
Figure 7-20 Simplified LRS Example: Highway
Description of "Figure 7-20 Simplified LRS Example: Highway"
In Figure 7-20, 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 7-1 lists an actual highway-related feature and the LRS feature that corresponds to it or that can be used to represent it.
Table 7-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 procedure |
|
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 7-2 does the following:
-
Creates a table to hold the segment depicted in Figure 7-20
-
Inserts the definition of the highway depicted in Figure 7-20 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 7-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 SDO_GEOMETRY);
-- Populate table with just one route for this example.
INSERT INTO lrs_routes VALUES(
1,
'Route1',
SDO_GEOMETRY(
3302, -- line string, 3 dimensions: X,Y,M
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
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
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
VALUES (
'lrs_routes',
'route_geometry',
SDO_DIM_ARRAY( -- 20X20 grid
SDO_DIM_ELEMENT('X', 0, 20, 0.005),
SDO_DIM_ELEMENT('Y', 0, 20, 0.005),
SDO_DIM_ELEMENT('M', 0, 20, 0.005) -- Measure dimension
),
NULL -- SRID
);
-- Create the spatial index.
CREATE INDEX lrs_routes_idx ON lrs_routes(route_geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;
-- Test the LRS procedures.
DECLARE
geom_segment SDO_GEOMETRY;
line_string SDO_GEOMETRY;
dim_array SDO_DIM_ARRAY;
result_geom_1 SDO_GEOMETRY;
result_geom_2 SDO_GEOMETRY;
result_geom_3 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 are 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;
-- Translate (shift measure values) (+10).
-- First, display the original segment; then, translate.
SELECT a.route_geometry FROM lrs_routes a WHERE a.route_id = 1;
SELECT SDO_LRS.TRANSLATE_MEASURE(a.route_geometry, m.diminfo, 10)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND m.column_name = 'ROUTE_GEOMETRY'
AND a.route_id = 1;
-- Redefine geometric segment to "convert" miles to kilometers
DECLARE
geom_segment SDO_GEOMETRY;
dim_array SDO_DIM_ARRAY;
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';
-- "Convert" mile measures to kilometers (27 * 1.609 = 43.443).
SDO_LRS.REDEFINE_GEOM_SEGMENT (geom_segment,
dim_array,
0, -- Zero starting measure: LRS segment starts at start of route.
43.443); -- End of LRS segment. 27 miles = 43.443 kilometers.
-- Update and insert geometries into table, to display later.
UPDATE lrs_routes a SET a.route_geometry = geom_segment
WHERE a.route_id = 1;
END;/
-- Display the redefined segment, with all measures "converted."
SELECT a.route_geometry FROM lrs_routes a WHERE a.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,
SDO_GEOMETRY(3301, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1, 1, 1),
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,
SDO_GEOMETRY(3301, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1, 1, 1),
SDO_ORDINATE_ARRAY(9, 3, NULL)) ),
m.diminfo )
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND m.column_name = 'ROUTE_GEOMETRY'
AND a.route_id = 1;
-- Is point (9,3,NULL) a valid LRS point? (Should return TRUE.)
SELECT SDO_LRS.VALID_LRS_PT(
SDO_GEOMETRY(3301, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1, 1, 1),
SDO_ORDINATE_ARRAY(9, 3, NULL)),
m.diminfo)
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND m.column_name = 'ROUTE_GEOMETRY'
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 7-3 shows the output of the SELECT statements in Example 7-2.
Example 7-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(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> -- 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> -- 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> -- 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> -- 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> -- 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> -- 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> -- 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> -- 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> -- 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> -- Translate (shift measure values) (+10).
SQL> -- First, display the original segment; then, translate.
SQL> SELECT a.route_geometry FROM lrs_routes a WHERE a.route_id = 1;
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(
2, 2, 0, 2, 4, 2, 8, 4, 8, 12, 4, 12, 12, 10, 18, 8, 10, 22, 5, 14, 27))
SQL> SELECT SDO_LRS.TRANSLATE_MEASURE(a.route_geometry, m.diminfo, 10)
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.TRANSLATE_MEASURE(A.ROUTE_GEOMETRY,M.DIMINFO,10)(SDO_GTYPE, SDO_SRID, SD
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
2, 2, 10, 2, 4, 12, 8, 4, 18, 12, 4, 22, 12, 10, 28, 8, 10, 32, 5, 14, 37))
SQL> -- Redefine geometric segment to "convert" miles to kilometers
SQL> DECLARE
2 geom_segment SDO_GEOMETRY;
3 dim_array SDO_DIM_ARRAY;
4
5 BEGIN
6
7 SELECT a.route_geometry into geom_segment FROM lrs_routes a
8 WHERE a.route_name = 'Route1';
9 SELECT m.diminfo into dim_array from
10 user_sdo_geom_metadata m
11 WHERE m.table_name = 'LRS_ROUTES' AND m.column_name = 'ROUTE_GEOMETRY';
12
13 -- "Convert" mile measures to kilometers (27 * 1.609 = 43.443).
14 SDO_LRS.REDEFINE_GEOM_SEGMENT (geom_segment,
15 dim_array,
16 0, -- Zero starting measure: LRS segment starts at start of route.
17 43.443); -- End of LRS segment. 27 miles = 43.443 kilometers.
18
19 -- Update and insert geometries into table, to display later.
20 UPDATE lrs_routes a SET a.route_geometry = geom_segment
21 WHERE a.route_id = 1;
22
23 END;
24 /
PL/SQL procedure successfully completed.
SQL> -- Display the redefined segment, with all measures "converted."
SQL> SELECT a.route_geometry FROM lrs_routes a WHERE a.route_id = 1;
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(
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> -- 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> -- Point (9,3,NULL) is off the road; should return (9,4,9).
SQL> SELECT SDO_LRS.PROJECT_PT(route_geometry,
2 SDO_GEOMETRY(3301, NULL, NULL,
3 SDO_ELEM_INFO_ARRAY(1, 1, 1),
4 SDO_ORDINATE_ARRAY(9, 3, NULL)) )
5 FROM lrs_routes WHERE route_id = 1;
SDO_LRS.PROJECT_PT(ROUTE_GEOMETRY,SDO_GEOMETRY(3301,NULL,NULL,SDO_EL
--------------------------------------------------------------------------------
SDO_GEOMETRY(3301, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(
9, 4, 9))
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 SDO_GEOMETRY(3301, NULL, NULL,
4 SDO_ELEM_INFO_ARRAY(1, 1, 1),
5 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,SDO_GEOM
--------------------------------------------------------------------------------
9
SQL> -- Is point (9,3,NULL) a valid LRS point? (Should return TRUE.)
SQL> SELECT SDO_LRS.VALID_LRS_PT(
2 SDO_GEOMETRY(3301, NULL, NULL,
3 SDO_ELEM_INFO_ARRAY(1, 1, 1),
4 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(SDO_GEOMETRY(3301,NULL,NULL,SDO_ELEM_INFO_ARRAY
------------------------------------------------------------------------------
TRUE
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))
Parent topic: Linear Referencing System