Oracle Spatial 建表完整教學：SDO_GEOMETRY 結構、空間索引、元資料註冊與空間查詢實戰

Oracle Spatial 核心物件：MDSYS.SDO_GEOMETRY

所有空間資料儲存在 SDO_GEOMETRY 物件型別中，結構定義：

CREATE TYPE sdo_geometry AS OBJECT (
    SDO_GTYPE     NUMBER,      -- 幾何類型：DLTT (D=維度, L=線性引用, TT=類型)
    SDO_SRID      NUMBER,      -- 空間參考系統 ID (NULL=笛卡爾平面)
    SDO_POINT     SDO_POINT_TYPE,      -- 單點幾何 (X,Y,Z)
    SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY, -- 元素資訊陣列 (每 3 值一組)
    SDO_ORDINATES SDO_ORDINATE_ARRAY   -- 座標陣列 (所有頂點扁平化)
);

關鍵屬性詳解

四步驟建立空間表完整流程

Step 1：建立業務表含 SDO_GEOMETRY 欄位

CREATE TABLE mylake (
    feature_id NUMBER PRIMARY KEY,
    name       VARCHAR2(32),
    shape      MDSYS.SDO_GEOMETRY
);

Step 2：向 USER_SDO_GEOM_METADATA 註冊空間元資料（必須）

INSERT INTO USER_SDO_GEOM_METADATA VALUES (
    'MYLAKE',           -- 表名 (大寫)
    'SHAPE',            -- 空間欄位名 (大寫)
    MDSYS.SDO_DIM_ARRAY(
        MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.05),  -- X 維：最小、最大、容忍度
        MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.05)   -- Y 維：最小、最大、容忍度
    ),
    NULL                -- SRID：NULL=笛卡爾座標；如 4326=WGS84
);
COMMIT;

容忍度：空間運算判斷相等/相交的最小距離閾值。單位與座標系一致。太小→運算失敗/效能差；太大→精度遺失。建議：地理座標 0.000005 度(~0.5m)，投影座標 0.05-0.5 米。

Step 3：建立空間索引（R-Tree，必須）

CREATE INDEX mylake_idx ON mylake(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('LAYER_GTYPE=POLYGON SDO_INDX_DIMS=2');

PARAMETERS 進階選項：WORK_TABLESPACE、PARALLEL、SDO_RTR_PCTFREE（節點填充因子）、SDO_DML_BATCH_SIZE（DML 批次更新閾值）。

Step 4：插入空間資料

4.1 含島嶼的湖泊（多邊形 + 內環）

INSERT INTO mylake VALUES (
    10, 'Lake Calhoun',
    MDSYS.SDO_GEOMETRY(
        2003,  -- 2D 多邊形
        NULL,  -- SRID
        NULL,  -- POINT
        MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1),  -- 外環+內環
        MDSYS.SDO_ORDINATE_ARRAY(
            0,0, 10,0, 10,10, 0,10, 0,0,   -- 外環 5 點 (首尾閉合)
            4,4, 6,4, 6,6, 4,6, 4,4         -- 內環 5 點 (島嶼)
        )
    )
);

4.2 兩艘小船（簡單多邊形）

INSERT INTO mylake VALUES (
    11, 'The Windswept',
    MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
        MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
        MDSYS.SDO_ORDINATE_ARRAY(2,2, 3,2, 3,3, 2,3, 2,2)
    )
);

INSERT INTO mylake VALUES (
    12, 'Blue Crest',
    MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
        MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
        MDSYS.SDO_ORDINATE_ARRAY(7,7, 8,7, 8,8, 7,8, 7,7)
    )
);

空間查詢兩階段：Primary Filter → Secondary Filter

階段一：Primary Filter - SDO_FILTER（僅用索引，快速篩選候選集）

-- 查詢窗口：矩形 (2,2)-(5,5)
SELECT name boat_name
FROM mylake t
WHERE t.feature_id != 10  -- 排除湖泊本身
  AND SDO_FILTER(
        t.shape,
        MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
            MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
            MDSYS.SDO_ORDINATE_ARRAY(2,2, 5,2, 5,5, 2,5, 2,2)
        ),
        'QUERYTYPE=WINDOW'  -- 窗口查詢，幾何不需來自表
    ) = 'TRUE';

階段二：Secondary Filter - SDO_RELATE（精確拓撲關係驗證）

-- 組合：先 FILTER 再 RELATE，獲得精確結果
SELECT name boat_name
FROM mylake t
WHERE t.feature_id != 10
  AND SDO_FILTER(t.shape, , 'QUERYTYPE=WINDOW') = 'TRUE'
  AND SDO_RELATE(t.shape, , 'MASK=INSIDE QUERYTYPE=WINDOW') = 'TRUE';

常用 MASK 拓撲關係

MASK 組合：INSIDE+TOUCH = 內含或接觸邊界

SDO_RELATE(..., 'MASK=INSIDE+TOUCH QUERYTYPE=WINDOW')

進階空間操作函數

座標系統轉換：CS_TRANSFORM

-- 將幾何從 SRID 4326 (WGS84) 轉為 3857 (Web Mercator)
SELECT SDO_CS.TRANSFORM(shape, 3857) FROM mylake WHERE feature_id = 10;

-- 需預先載入 EPSG 定義 (通常預設已載入)
SELECT * FROM MDSYS.CS_SRS WHERE SRID IN (4326, 3857);

效能優化關鍵點

1. 元資料準確：USER_SDO_GEOM_METADATA 範圍必須涵蓋所有資料，容忍度適中
2. 空間索引維護：大量 DML 後 ALTER INDEX ... REBUILD 或設 SDO_DML_BATCH_SIZE 自動批次更新
3. 查詢模式：永遠 SDO_FILTER 先行，再 SDO_RELATE 精確；避免單獨用 SDO_RELATE 導致全表掃描
4. SRID 一致：查詢幾何與資料 SRID 必須一致，否則隱性轉換失效索引
5. 平行查詢：大範圍空間查詢啟用 PARALLEL 索引建立與查詢

總結

Oracle Spatial 以 SDO_GEOMETRY 統一表示點/線/面/多元素幾何，**四步驟（建表→註冊元資料→建空間索引→插入資料）建立完整空間資料庫**。查詢遵循「Primary Filter (SDO_FILTER) → Secondary Filter (SDO_RELATE)」兩階段模式，配合豐富的 MASK 拓撲關係與空間分析函數，支援企業級 GIS 應用。掌握 SDO_ELEM_INFO 編碼規則與座標系統管理，是玩轉 Oracle Spatial 的核心技能。