# Oracle Spatial详解

Step1. 创建一张表，其中shape用来存放空间数据
CREATE TABLE mylake (
feature_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape MDSYS.SDO_GEOMETRY);

‘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：

• `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'```

```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