Linear Reference System 應用 II

之前的範例比較簡單, 只有考慮公車停靠站(Bus Stop). 如果您有注意到, 公車路線是由站與站之間以直線連接起來. 在大部分的情境下, 這樣的公車路線並不存在. 比較正常的路線應該是如下表所顯示的範例:

BUS_SERVICE: 表示服務的公車代號
ROUTE_NAME: 表示公車行駛的方向, 可能為"東向", "西向"; "去程", "回程"...這裏使用"busService1"表示
BUS_SERVICE+ROUTE_NAME 可視為一公車路徑的ID.
Point_Order: 表示路徑上點順序.
Point_Name: 有值的內容表示真正的站名(範例為stop1, stop2...), 沒有值(NULL)則表示為純綷為描述路徑上的站與站之間的銜接, 轉折..的點位
LAT, LON 則表示座標

為了更能夠彈性地表示/描述路徑應用, 我用三個資料表呈現. 其三個表之間ER-Diagram 概念範如下(請注意, 這裏的PK表示是有問題的, 真實的情況會隨需求而有變化):

ROUTES_INFO: 表示所有公車路徑的描述
STOPS_INFO: 表示真正有站的位置, 其值主要從資料表ROUTES_INFO中篩選.ROUTE_POINT_NAME 有值的資料(表示真正的站點). LRS_METERS則是計算距離公車路徑起點的距離.
LRS_ROUTES: 表示真正以LRS方弍記載的公車路徑. 由ROUTE_INFO的內容構建而成.

為方便操作, 這邊抄襲 "Pro Oracle Spatial"的二個函式function): point, add_to_line

desc point
引數名稱           類型     In/Out 預設      
-------------- ------ ------ ------- 
<return value> OBJECT OUT    unknown 
LON            NUMBER IN     unknown 
LAT            NUMBER IN     unknown 
SRID           NUMBER IN     unknown 

desc add_to_line
引數名稱           類型     In/Out 預設      
-------------- ------ ------ ------- 
<return value> OBJECT OUT    unknown 
GEOM           OBJECT IN     unknown 
POINT          OBJECT IN     unknown 
POINT_NUMBER   NUMBER IN     unknown 

以下的SQL為如何從ROUTES_INFO篩選真正有值的公車站點

insert into stops_info (bus_service, route_name, stop_name, road_name, lat,lon)
select bus_service, route_name, route_point_name, road_name, lat,lon from routes_info where route_point_name is not null;

接下來是以ROUTES_INFO構建LRS_ROUTES. 這邊的假設是在LRS_ROUTES已經有(BUS_SERVICE, ROUTE_NAME)=('138','busService1')的資料

Line 1 ~ 18: 為一 PL/SQL block
Line 3 ~  6 : 宣告一cursor, 其中直接使用point 來構建SDO_GEOMETRY 資料型態的點
Line 9 ~ 12: 為 FOR...LOOP
Line 11: 使用add_to_line 將點加入到線段中, 也就是將所有點, 以直線方式連接成一路徑的線段
Line 13 ~ 15: 為一UPDATE 指令, 異動BUS_SERVICE='138' and ROUTE_NAME='busService1' 的 ROUTE_GEOMETRY
Line 14: SDO_LRS.CONVERT_TO_LRS_GEOM為Oracle Spatial LRS所提供的功能, 主要是將一般平面的線段轉變成LRS的線段.

更新/異動空間資料庫的詮釋資料(Metadata)與建立空間資料索引
-- 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;

將資料表STOPS_INFO中的LRS_METERS值算出並填滿
首先先建立函數GET_LRS_METERS

CREATE OR REPLACE
FUNCTION GET_LRS_METERS
(
  LON IN NUMBER
, LAT 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(LON, LAT, 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.BUS_SERVICE = '138' and a.route_name = 'busService1';
  RETURN GET_METERS;
END GET_LRS_METERS;
以GET_LRS_METERS來異動資料表STOPS_INFO中的LRS_METERS欄位

update stops_info
set lrs_meters = get_lrs_meters(lon,lat);

因為資料表的名稱, 欄位都有變動. 所以查詢的範例就會改變成:
查詢公車(103.974676, 1.374007)距離各站還有多少距離
SELECT  STOP_NAME, LRS_METERS - GET_LRS_METERS(103.974676, 1.374007) AS METERS_TO_NEXT_STOP
FROM STOPS_INFO
where BUS_SERVICE='138' and ROUTE_NAME='busService1' ORDER BY 2;
查詢公車(103.974676, 1.374007)偏離公車路徑的距離
SELECT
SDO_LRS.FIND_OFFSET(ROUTE_GEOMETRY, SDO_GEOMETRY(3301, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(103.975298, 1.374007, NULL)))
FROM LRS_ROUTES
WHERE BUS_SERVICE='138' and ROUTE_NAME='busService1';