假設有下面的資訊(簡化)
一條行車路徑，有14個站(含起迄站)，如下圖：
假設有一公車從"stop1" 開往 "stop14"，想知道到該公車到剩餘的每一站還要多少時間，LRS可以幫忙解決這樣的問題。
以下為將14個 bus stop轉成LRS
-- 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
8307,
NULL,
SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
SDO_ORDINATE_ARRAY(
103.837624, 1.311726, 0, -- stop1
103.83462 , 1.309709, NULL, -- stop2
103.829298, 1.3007 , NULL, -- stop3
103.83153 , 1.295894, NULL, -- stop4
103.83728 , 1.295379, NULL, -- stop5
103.842387, 1.293835, NULL, -- stop6
103.844662, 1.298082, NULL, -- stop7
103.84552 , 1.299026, NULL, -- stop8
103.847431, 1.298721, NULL, -- stop9
103.848782, 1.298426, NULL, -- stop10
103.851485, 1.29628 , NULL, -- stop11
103.862472, 1.301429, NULL, -- stop12
103.863931, 1.307993, NULL, -- stop13
103.860734, 1.305588, 7347.42786 -- stop14 Assume From stop1 to stop14 is 7347.42786 meters
)));
-- 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', -180, 180, 0.00005),
SDO_DIM_ELEMENT('Y', -90, 90, 0.00005),
SDO_DIM_ELEMENT('M', 0, 100000, 0.000005) -- meters
),
8307 -- SRID
);
-- Create the spatial index.
CREATE INDEX lrs_routes_idx ON lrs_routes(route_geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
--
-- 在 SQL*Plus 查看該LRS相關資訊， 請注意 measures 的部分皆為 NULL
--
SQL> select * 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, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
103.837624, 1.311726, 0, 103.83462, 1.309709, NULL, 103.829298, 1.3007, NULL,
103.83153, 1.295894, NULL, 103.83728, 1.295379, NULL, 103.842387, 1.293835, NUL
L, 103.844662, 1.298082, NULL, 103.84552, 1.299026, NULL, 103.847431, 1.298721,
NULL, 103.848782, 1.298426, NULL, 103.851485, 1.29628, NULL, 103.862472, 1.30142
9, NULL, 103.863931, 1.307993, NULL, 103.860734, 1.305588, 7347.42786))
SQL>
-- 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);
END;
/
--
-- 計算點(X,Y)=(103.93232, 1.31996) 投射(project) 於 LRS 線段上的位置
-- 因為 GPS 回報的點並不一定(應該不會)剛好落在 LRS 線上，為計算距離, 所以先進行投射
--
SELECT SDO_LRS.PROJECT_PT(a.route_geometry,
SDO_GEOMETRY(3301, 8307, NULL,
SDO_ELEM_INFO_ARRAY(1, 1, 1),
SDO_ORDINATE_ARRAY(103.93232, 1.31996, NULL))
)
from lrs_routes a WHERE a.route_id = 1;
SQL> SELECT SDO_LRS.PROJECT_PT(a.route_geometry, SDO_GEOMETRY(3301, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(103.93232, 1.31996, NULL)) ) from lrs_routes a WHERE a.route_id = 1; 2 3 4 5 6 SDO_LRS.PROJECT_PT(A.ROUTE_GEOMETRY,SDO_GEOMETRY(3301,8307,NULL,SDO_ELEM_INFO_AR -------------------------------------------------------------------------------- SDO_GEOMETRY(3301, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY( 103.863931, 1.307993, 6903.23056))
--
-- 計算點(X,Y)=(103.93232, 1.31996) 已經離 stop1 行駛了多少距離
--
SQL> SELECT SDO_LRS.GET_MEASURE(
2 SDO_LRS.PROJECT_PT(a.route_geometry, m.diminfo,
3 SDO_GEOMETRY(3301, 8307, NULL,
4 SDO_ELEM_INFO_ARRAY(1, 1, 1),
5 SDO_ORDINATE_ARRAY(103.93232, 1.31996, 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_GEOMETRY(3
--------------------------------------------------------------------------------
6903.23056
--
-- 接下來，我們要計算該公車距離各站還有多少距離
-- 因為是公車路線，各站(stop)皆為固定位置，所以我們要先算出各站離起點的距離
-- 計算距離為常用的用途，為方便計算我們先建立 GET_METERS 的函式
--
CREATE OR REPLACE
FUNCTION GET_LRS_METERS
(
X IN NUMBER
, Y IN NUMBER
) RETURN NUMBER AS
GET_METERS number;
BEGIN
SELECT SDO_LRS.GET_MEASURE(
SDO_LRS.PROJECT_PT(a.route_geometry, m.diminfo,
SDO_GEOMETRY(3301, 8307, NULL,
SDO_ELEM_INFO_ARRAY(1, 1, 1),
SDO_ORDINATE_ARRAY(X, Y, NULL)) ),
m.diminfo )
INTO GET_METERS
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;
RETURN GET_METERS;
END GET_LRS_METERS;
--
-- 接下來 要在原來的表格(ROUTES_INFO TABLE) 增加 存放離起點距離的欄位
--
ALTER TABLE ROUTES_INFO
ADD (LRS_METERS NUMBER(12, 6) );
--
-- 更新 ROUTES_INFO(LRS_METERS) 的內容
--
update
ROUTES_INFO
SET lrs_meters = get_lrs_meters(X, Y);
--
-- 更新後的結果
--
--
-- 所以公車位置(X,Y)=(103.93232, 1.31996) 離各站的距離
--
SELECT STOP_ID, LRS_METERS - GET_LRS_METERS(103.93232, 1.31996) AS METERS_TO_NEXT_STOP
FROM ROUTES_INFO
where ROUTE_ID = 1;
SQL> SELECT STOP_ID, LRS_METERS - GET_LRS_METERS(103.93232, 1.31996) AS METERS_TO_NEXT_STOP
2 FROM ROUTES_INFO
3 where ROUTE_ID = 1;
STOP_I METERS_TO_NEXT_STOP
------ -------------------
stop1 -6903.2306
stop2 -6501.3476
stop3 -5342.4001
stop4 -4755.7882
stop5 -4113.3349
stop6 -3519.8822
stop7 -2986.3667
stop8 -2844.8971
stop9 -2629.5625
stop10 -2475.7104
stop11 -2092.5644
stop12 -743.75404
stop13 3.8952E-07
stop14 444.197298
14 rows selected.
SQL>
以上可以看出來，該車已接stop 13(或已到站)，下一站為stop 14
--
-- 查詢公車點(103.840048, 1.29494)偏離路線有多遠的距離
-- 使用SDO_LRS.FIND_OFFSET進行查詢 單位: meter
--
SELECT
SDO_LRS.FIND_OFFSET(ROUTE_GEOMETRY, SDO_GEOMETRY(3301, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(103.840048, 1.29494, NULL)))
FROM LRS_ROUTES
WHERE ROUTE_ID = 1;
SQL> SELECT
2 SDO_LRS.FIND_OFFSET(ROUTE_GEOMETRY, SDO_GEOMETRY(3301, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(103.840048, 1.29494, NULL)))
3 FROM LRS_ROUTES
4 WHERE ROUTE_ID = 1;
SDO_LRS.FIND_OFFSET(ROUTE_GEOMETRY,SDO_GEOMETRY(3301,8307,NULL,SDO_ELEM_INFO_ARR
--------------------------------------------------------------------------------
42.1325652
SQL>
