Oracle Spatial详解

墨蓝 2022-03-26 08:34 282阅读 0赞

转自：[https://www.cnblogs.com/frogbag/archive/2007/10/17/926870.html][https_www.cnblogs.com_frogbag_archive_2007_10_17_926870.html]

**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             最后两位:`

<table> 
 <tbody> 
  <tr> 
   <th>Value</th> 
   <th>Geometry</th> 
   <th>Description</th> 
  </tr> 
  <tr> 
   <td style="border-color:#C0C0C0;">00</td> 
   <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">UNKNOWN_GEOMETRY</span></code></td> 
   <td style="border-color:#C0C0C0;">Spatial ignores this value</td> 
  </tr> 
  <tr> 
   <td style="border-color:#C0C0C0;">01</td> 
   <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">POINT</span></code></td> 
   <td style="border-color:#C0C0C0;">A single point element</td> 
  </tr> 
  <tr> 
   <td style="border-color:#C0C0C0;">02</td> 
   <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">LINE</span></code>&nbsp;or&nbsp;<code><span style="color:#003366;">CURVE</span></code></td> 
   <td style="border-color:#C0C0C0;">Contains one line string element that may be linear, curved or both</td> 
  </tr> 
  <tr> 
   <td style="border-color:#C0C0C0;">03</td> 
   <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">POLYGON</span></code></td> 
   <td style="border-color:#C0C0C0;">Contains one polygon element with or without other polygon elements in it</td> 
  </tr> 
  <tr> 
   <td style="border-color:#C0C0C0;">04</td> 
   <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">COLLECTION</span></code></td> 
   <td style="border-color:#C0C0C0;">A heterogeneous collection of elements</td> 
  </tr> 
  <tr> 
   <td style="border-color:#C0C0C0;">05</td> 
   <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">MULTIPOINT</span></code></td> 
   <td style="border-color:#C0C0C0;">Contains one or more points</td> 
  </tr> 
  <tr> 
   <td style="border-color:#C0C0C0;">06</td> 
   <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">MULTILINE</span></code>&nbsp;or&nbsp;<code><span style="color:#003366;">MULTICURVE</span></code></td> 
   <td style="border-color:#C0C0C0;">Contains one or more line string elements</td> 
  </tr> 
  <tr> 
   <td style="border-color:#C0C0C0;">07</td> 
   <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">MULTIPOLYGON</span></code></td> 
   <td style="border-color:#C0C0C0;">Contains multiple polygon elements that maybe disjoint</td> 
  </tr> 
 </tbody> 
</table>

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 提供的其他查询函数：`
    
    <table> 
     <tbody> 
      <tr> 
       <td style="border-color:#C0C0C0;"><strong>Query</strong></td> 
       <td style="border-color:#C0C0C0;"><strong>Description</strong></td> 
      </tr> 
      <tr> 
       <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">SDO_NN</span></code></td> 
       <td style="border-color:#C0C0C0;">Nearest neighbor</td> 
      </tr> 
      <tr> 
       <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">SDO_SDO_WITHIN_DISTANCE</span></code></td> 
       <td style="border-color:#C0C0C0;">All geometries with a certain distance</td> 
      </tr> 
      <tr> 
       <td colspan="2" style="border-color:#C0C0C0;">&nbsp;</td> 
      </tr> 
      <tr> 
       <td style="border-color:#C0C0C0;"><strong>Functions</strong></td> 
       <td style="border-color:#C0C0C0;"><strong>Description</strong></td> 
      </tr> 
      <tr> 
       <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">SDO_GEOM.SDO_MBR</span></code></td> 
       <td style="border-color:#C0C0C0;">The minimum bounding rectangle for a geometry</td> 
      </tr> 
      <tr> 
       <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">SDO_GEOM.SDO_DISTANCE</span></code></td> 
       <td style="border-color:#C0C0C0;">The distance between two geometries</td> 
      </tr> 
      <tr> 
       <td style="border-color:#C0C0C0;"><code><span style="color:#003366;">SDO_GEOM.SDO_INTERSECTION</span></code></td> 
       <td style="border-color:#C0C0C0;">Provides the intersection point of two geometries</td> 
      </tr> 
     </tbody> 
    </table>

[https_www.cnblogs.com_frogbag_archive_2007_10_17_926870.html]: https://www.cnblogs.com/frogbag/archive/2007/10/17/926870.html