Step1. 創建一張表，其中shape用來存放空間數據
CREATE TABLE mylake (
??? feature_id NUMBER PRIMARY KEY,
??? name VARCHAR2(32),
??? shape MDSYS.SDO_GEOMETRY);
Step2. 在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????//---坐標系，缺省為笛卡爾坐標系
);
Step3. 創建空間索引
CREATE INDEX mylake_idx ON mylake(shape)
??? INDEXTYPE IS MDSYS.SPATIAL_INDEX
Step4. 插入空間數據
Oracle Spatial用MDSYS.SDO_GEOMETRY來存儲空間數據,定義為：CREATE TYPE sdo_geometry AS OBJECT (
??? SDO_GTYPE NUMBER,
??? SDO_SRID NUMBER,
??? SDO_POINT SDO_POINT_TYPE,
??? SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY,
??? SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY);
SDO_GTYPE：用四個數字定義了所有的形狀
??????????? 第一位:維數
??????????? 第二位:線性表示。用于3，4維數據，二維為0
??????????? 最后兩位:| Value | Geometry | Description |
|---|
| 00 | UNKNOWN_GEOMETRY | Spatial ignores this value |
| 01 | POINT | A single point element |
| 02 | LINE or CURVE | Contains one line string element that may be linear, curved or both |
| 03 | POLYGON | Contains one polygon element with or without other polygon elements in it |
| 04 | COLLECTION | A heterogeneous collection of elements |
| 05 | MULTIPOINT | Contains one or more points |
| 06 | MULTILINE or MULTICURVE | Contains one or more line string elements |
| 07 | MULTIPOLYGON | Contains multiple polygon elements that maybe disjoint |
SDO_SRID：坐標系，NULL為笛卡爾坐標系。
SDO_POINT：Oracle Spatial也可定義單個的點，SDO_POINT的定義:
??? CREATE TYPE sdo_point_type AS OBJECT (X NUMBER,Y NUMBER,Z NUMBER);
??? 如何是二維，Z為NULL。
SDO_ELEM_INFO：每三個值描述一個元素。
????????????????第一個值：第一個頂點在SDO_ORDINATES_ARR開始位置
????????????????第二個值：元素類型
????????????????第三個值：頂點連接方式：1-通過直線連接，2-通過圓弧連接
??? 定義為
??? CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;
SDO_ORDINATES：幾何圖形所有頂點列表。定義為
??? CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;
FONT color=#003366>// 插入包含一個島嶼的湖泊
INSERT INTO mylake VALUES(
??? 10,?
??? 'Lake Calhoun',?
??? MDSYS.SDO_GEOMETRY(
??????? 2003,
??????? NULL,
??????? NULL,
??????? 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, 4,4, 6,4, 6,6, 4,6, 4,4)
??? ));
// 插入兩艘小船
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,2, 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,7, 7,8, 7,7)
??? )
);
Step4. 查詢
Oracle Spatial查詢數據包括二個處理過程：
1.只通過索引查詢候選項。通過函數SDO_FILTER實現：
SDO_FILTER(geometry1 MDSYS.SDO_GEOMETRY,?geometry2 MDSYS.SDO_GEOMETRY, params VARCHAR2)
geometry1：
必須是被索引的幾何數據
geometry2：不一定是表中的空間字段，也不要求被索引
params：Filter類型
??????? querytype=WINDOW：geometry2不要求來自表
??????? querytype=JOIN：geometry2必須來自表
SELECT name boat_name
FROM mylake t
WHERE feature_id = 12
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';
2.再檢查每個候選項是否和條件精確匹配。通過函數SDO_RELATE實現：
SDO_RELATE(geometry1 MDSYS.SDO_GEOMETRY, geometry2 MDSYS.SDO_GEOMETRY, params VARCHAR2)
params：masktype類型
DISJOINT
— the boundaries and interiors do not intersect
TOUCH
— the boundaries intersect but the interiors do not intersect
OVERLAPBDYDISJOINT
— the interior of one object intersects the boundary and interior of the other object, but the two boundaries do not intersect. This relationship occurs, for example, when a line originates outside a polygon and ends inside that polygon.
OVERLAPBDYINTERSECT
— the boundaries and interiors of the two objects intersect
EQUAL
— the two objects have the same boundary and interior
CONTAINS
— the interior and boundary of one object is completely contained in the interior of the other object
COVERS
— the interior of one object is completely contained in the interior of the other object and their boundaries intersect
INSIDE
— the opposite of CONTAINS. A INSIDE B implies B CONTAINS A.
COVEREDBY
— the opposite of COVERS. A COVEREDBY B implies B COVERS A.
ON
— the interior and boundary of one object is on the boundary of the other object (and the second object covers the first object). This relationship occurs, for example, when a line is on the boundary of a polygon.
ANYINTERACT
— the objects are non-disjoint.
// 選擇在定義矩形內的所有小船
SELECT name boat_name
FROM mylake t
WHERE feature_id = 12
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'
AND SDO_RELATE(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)),
??? 'masktype=INSIDE querytype=WINDOW') = 'TRUE'
// masktype可聯合使用
SELECT feature_id id
FROM mylake t
WHERE feature_id = 12
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'
AND SDO_RELATE(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)),
??? 'masktype=INSIDE+TOUCH querytype=WINDOW') = 'TRUE'
Oracle Spatial 提供的其他查詢函數：
| Query | Description |
SDO_NN | Nearest neighbor |
SDO_SDO_WITHIN_DISTANCE | All geometries with a certain distance |
|
| Functions | Description |
SDO_GEOM.SDO_MBR | The minimum bounding rectangle for a geometry |
SDO_GEOM.SDO_DISTANCE | The distance between two geometries |
SDO_GEOM.SDO_INTERSECTION | Provides the intersection point of two geometries |