Oracle Spatial操作手册

目录

一、准备工作... 2

1.安装Oracle数据库... 2

2.检查是否安装Spatial 2

二、表结构及索引... 2

1.创建空间数据表... 2

2.在user_sdo_geom_metadata 表中插入新记录，用于描述空间字段... 3

3.创建空间索引... 3

三、插入数据... 3

1.MDSYS.SDO_GEOMETRY介绍... 3

1.1.SDO_GTYPE几何类型... 4

1.2.SDO_SRID空间坐标系... 5

1.3. SDO_POIN几何点类型... 8

1.4. SDO_ELEM_INFO坐标序列... 9

1.5. SDO_ORDINATES实际坐标串... 11

2.插入点数据... 11

3.插入线段数据... 11

4.插入面数据(简单面) 11

5.插入多边形：环（也叫带“洞”的多边形或“江心岛”多边形）... 12

四、查询函数... 12

1.计算几何周长... 12

2.计算几何面积... 12

3.计算几何质心... 12

4.几何缓冲区... 13

5.几何关系... 13

一、准备工作

1.安装Oracle数据库

步骤省略

2.检查是否安装Spatial

Oracle11g默认安装了Spatial

select comp_id,comp_name,version from dba_registry;

执行上条sql, 如果看到有 SDO   Spatial即代表已经安装了Oracle Spatial 组件。

截图：

二、表结构及索引

示例以土整地块范围表SZGM_LAND_COOR为例（非空间字段仅摘取部分举例）

1.创建空间数据表

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

2.在user_sdo_geom_metadata 表中插入新记录，用于描述空间字段

INSERT INTO user_sdo_geom_metadata VALUES (
    'SZGM_LAND_COOR',    //---表名
    'shape',    //---字段名
    MDSYS.SDO_DIM_ARRAY(   
        MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.5),    

//---X维最小，最大值和容忍度。
     MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.5)    

//---Y维最小，最大值和容忍度
    ),
    4490000    //---坐标系，缺省为笛卡尔坐标系(本例定义为GCS2000坐标系)
);

3.创建空间索引

CREATE INDEX SZGM_LAND_COOR_idx ON SZGM_LAND_COOR (shape)
    INDEXTYPE IS MDSYS.SPATIAL_INDEX

三、插入数据

1.MDSYS.SDO_GEOMETRY介绍

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_geometry 类型中的各个参数简单的介绍：

1、SDO_GTYPE ：表示要存储的几何类型，如点线面。它是通过 NUMBER类型来表达的；

2、SDO_SRID ：几何的空间参考坐标系，类型也为 NUMBER；

3、SDO_POINT ：如果几何类型点类型的话，就是存储点坐标，否则为空。oracle自定义的SDO_POINT_TYPE类型；

4、SDO_ELEM_INFO ：定义要如何理解SDO_ORDINATES中的坐标串的；

5、SDO_ORDINATES ：存储实际坐标的，以X、Y以及不同点之间都是逗号隔开；

1.1.SDO_GTYPE几何类型
SDO_GTYPE：用四个数字定义了所有的形状
            第一位:维数
            第二位:线性表示。用于3，4维数据，二维为0
            最后两位:

SDO_GTYPE值是有四位数字组成的，它们的格式为：dltt 。

其中，d 表示几何的维数。如二维、三维对应的d=2和d=3。

l 定义了LRS。一般l=0。

tt 定义了地理对象的类型。现在使用从00到07，如tt=01代表为单点。

例如：SDO_GTYPE为2003即表示为二维的多边形

1.2.SDO_SRID空间坐标系

默认为null,参数类型为NUMBER

在设定坐标系之前，可通过sql查询你的SRID是否在Oracle Spatial 【MDSYS.CS_SRS】 表中。

select * from mdsys.cs_Srs where srid = 4490;

4490为你的SRID。

1.2.1.解决Oracle11g不支持3857墨卡托投影坐标系的方法

INSERT INTO sdo_coord_ref_system (

srid,

coord_ref_sys_name,

coord_ref_sys_kind,

coord_sys_id,

datum_id,

geog_crs_datum_id,

source_geog_srid,

projection_conv_id,

cmpd_horiz_srid,

cmpd_vert_srid,

information_source,

data_source,

is_legacy,

legacy_code,

legacy_wktext,

legacy_cs_bounds,

is_valid,

supports_sdo_geometry)

VALUES (

3857,

'Popular Visualisation CRS / Mercator',

'PROJECTED',

4499,

NULL,

6055,

4055,

19847,

NULL,

NULL,

NULL,

NULL,

'FALSE',

NULL,

NULL,

NULL,

'TRUE',

'TRUE');

1.2.2.解决Oracle11g不支持4490 GCS2000坐标系的方法

--自定义的CGCS 坐标参考系统SRSSRID为4490000，Oracle 12C自带的CGCS Geodetic CRS的SRID为4490

--自定义的SRS的SRID必须大于1000000 ，故设其值为4490再加3个0

--自定义的椭球的ID为1024000，也是在12C的CGCS椭球ID后面加3个0

--自定义的基准的ID为1043000，同样是在12C的CGCS基准的ID后面加3个0

-- a)  向SDO_UNITS_OF_MEASURE 表中插入行，定义度量单位

       --定义椭球的时候需要UOM_ID 9001, 系统中已经有,不需要另外定义

       --Prime_Meridian_ID 8901需要UOM_ID 9110（以度为单位）,也已经有

-- b)  向SDO_COORD_AXES表中为坐标系的每个轴插入一行数据。

       --COORD_SYS_ID为6422的坐标系CS的坐标轴已经定义，不需要另外定义

-- c)  向SDO_COORD_SYS表插入数据，这个表的列基本是按EPSG的规定来的。

       -- 使用COORD_SYS_ID为6422的坐标系CS，不需要另外定义

-- d)  向SDO_ELLIPSOIDS表插入一行以定义椭球

INSERT INTO SDO_ELLIPSOIDS (

       ELLIPSOID_ID,

       ELLIPSOID_NAME,

       SEMI_MAJOR_AXIS,

       UOM_ID,

       INV_FLATTENING,

       SEMI_MINOR_AXIS,

       INFORMATION_SOURCE,

       DATA_SOURCE,

       IS_LEGACY,

       LEGACY_CODE)

VALUES(

       1024000,

       'CGCS2000',

       6378137,

       9001,

       298.257222101,

       6378137*(1-1/298.257222101),

       'Chinese Academy of Surveying and Mapping',

       'OGP',

       'FALSE',

       null);    

-- e)  SDO_PRIME_MERIDIANS 表中定义本初子午线

       --定义基准时需要PRIME_MERIDIAN_ID 8901，已经有，不需要另外定义

-- f)  SDO_DATUMS表中定义基准

INSERT INTO SDO_DATUMS (

       DATUM_ID,

       DATUM_NAME,

       DATUM_TYPE,

       ELLIPSOID_ID,

       PRIME_MERIDIAN_ID,

       INFORMATION_SOURCE,

       DATA_SOURCE,

       SHIFT_X,

       SHIFT_Y,

       SHIFT_Z,

       ROTATE_X,

       ROTATE_Y,

       ROTATE_Z,

       SCALE_ADJUST,

       IS_LEGACY,

       LEGACY_CODE )

VALUES (

       1043000,

       'China 2000',

       'GEODETIC',

       1024000,

       8901,

       'Chinese Academy of Surveying and Mapping',

       'OGP',

       null,

       null,

       null,

       null,

       null,

       null,

       null,

       'FALSE',

       null);

-- g)  插入CGCS 大地 空间参考系（Geodetic CRS）

INSERT INTO SDO_COORD_REF_SYSTEM (

       SRID,

       COORD_REF_SYS_NAME,

       COORD_REF_SYS_KIND,

       COORD_SYS_ID,

       DATUM_ID,

       GEOG_CRS_DATUM_ID,

       SOURCE_GEOG_SRID,

       PROJECTION_CONV_ID,

       CMPD_HORIZ_SRID,

       CMPD_VERT_SRID,

       INFORMATION_SOURCE,

       DATA_SOURCE,

       IS_LEGACY,

       LEGACY_CODE,

       LEGACY_WKTEXT,

       LEGACY_CS_BOUNDS,

       IS_VALID,

       SUPPORTS_SDO_GEOMETRY)

 VALUES (

        4490000,

        'China Geodetic Coordinate System 2000',

        'GEOGRAPHIC2D',

        6422,

        1043000,

        1043000,

        NULL,

        NULL,

        NULL,

        NULL,

        'EPSG. See 3D CRS for original information source.',      

        'OGP',

        'FALSE',

        NULL,

        NULL,

        NULL,

       'TRUE',

       'TRUE');

1.3. SDO_POIN几何点类型

非几何点类型，可为null

SDO_POINT类型的构造方法为：sdo_point_type(x,y,z)，其中x,y,z类型为Double和Int都可

1.4. SDO_ELEM_INFO坐标序列

SDO_ELEM_INFO类型的构造方法为：sdo_elem_info_array（a,b.c)，其中a,b.c为Number类型。SDO_ELEM_INFO为一个三元数组。

每个SDO_ELEM_INFO属性单元由：SDO_STARTING_OFFSET、SDO_ETYPE 和SDO_INTERPRETATION 组成。

1.4.1.简单几何体SDO_ELEM_INFO对应值

（图1）

1.4.1. SDO_ETYPE 和SDO_INTERPRETATION 对应表

（图2）

注意：SDO_ETYPE 值 = 1003 ，假如几何类型为面，则表示为外多边形环（以逆时针顺序）

SDO_ETYPE 值 = 2003 ，假如几何类型为面，则表示为内多边形环（以顺时针顺序）

1.4.3.SDO_ELEM_INFO示例

1.4.3.1.简单多边形

坐标为：(113.5533,22.2233, 113.5335,22.4533, 113.5225,22.3113, 113.5115,22.1133, 113.5775,22.3388)

则SDO_ELEM_INFO=(1,1003,1)。

1.4.3.2复杂多边形（江心岛多边形）

（图3）

坐标串为(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4,7,5, 7,10, 10,10, 10,5, 7,5)

则SDO_ELEM_INFO=( 1,1003,1, 19,2003,1)。

其中19表示几何B的起始坐标点7,5中7在坐标串里的序列号

1.5. SDO_ORDINATES实际坐标串

实际坐标字符串

2.插入点数据

INSERT INTO SZGM_LAND_COOR VALUES(13, 'XXX', MDSYS.SDO_GEOMETRY( 

2001 , --多边形类型表示一个点 

null,       --坐标系可默认使用建表时定义的坐标系

MDSYS.SDO_POINT_TYPE (x坐标,y坐标,NULL),--添加一个点，

NULL , 

NULL

));

3.插入线段数据

INSERT INTO SZGM_LAND_COOR VALUES(13, 'XXX', MDSYS.SDO_GEOMETRY( 

2002 , --几何类型表示一线段

null,   --坐标系可默认使用建表时定义的坐标系

null,   --添加一个点，

MDSYS.SDO_ELEM_INFO_ARRAY (1,2,1), 

MDSYS.SDO_ORDINATE_ARRAY(63918.69368683,39300.6724619204,63918.7296493314,39302.5029543953,63918.757610323,39304.3335865351,63918.777577428,39306.1643233876,63918.789421375,39307.9951299983)

));

4.插入面数据(简单面)

INSERT INTO SZGM_LAND_COOR VALUES(13, 'XXX', MDSYS.SDO_GEOMETRY( 

2003 , --几何类型表示简单面

null,   --坐标系可默认使用建表时定义的坐标系

null,   --添加一个点，

MDSYS.SDO_ELEM_INFO_ARRAY (1,1003,1), 

MDSYS.SDO_ORDINATE_ARRAY(58184.2949999999,39390.5210000016,58208.6500000013,39291.8900000025,58499.00999998,39310.00000003,58482.4699999997,39409.8360000011,58184.29499999,39390.5210000016)

));

5.插入多边形：环（也叫带“洞”的多边形或“江心岛”多边形）

INSERT INTO SZGM_LAND_COOR VALUES(13, 'XXX', MDSYS.SDO_GEOMETRY( 

2003 , --几何类型表示简单面

null,   --坐标系可默认使用建表时定义的坐标系

null,   --添加一个点，

MDSYS.SDO_ELEM_INFO_ARRAY ( 1,1003,1, 19,2003,1), 

MDSYS.SDO_ORDINATE_ARRAY(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4,7,5, 7,10, 10,10, 10,5, 7,5)

));

四、查询函数

1.计算几何周长

函数：SDO_GEOM.SDO_LENGTH

示例：查询地块001的周长

select c.dkcode, SDO_GEOM.SDO_LENGTH(c.shape, m.diminfo)

from SZGM_LAND_COOR c, MDSYS.USER_SDO_GEOM_METADATA m

where m.table_name = 'SZGM_LAND_COOR'

and m.COLUMN_NAME = 'shape'

and c.dkcode = '地块001';--若没有这条件，则范围所有地块的周长

2.计算几何面积

函数：SDO_GEOM.SDO_AREA

示例：查询地块001的面积

select c.dkcode, SDO_GEOM.SDO_AREA (c.shape, m.diminfo)

from SZGM_LAND_COOR c, MDSYS.USER_SDO_GEOM_METADATA m

where m.table_name = 'SZGM_LAND_COOR'

and m.COLUMN_NAME = 'shape'

and c.dkcode = '地块001';--若没有这条件，则范围所有地块的面积

3.计算几何质心

函数：SDO_CENTROID 

示例：查询地块001的质心

select c.dkcode, SDO_GEOM.SDO_CENTROID  (c.shape, m.diminfo)

from SZGM_LAND_COOR c, MDSYS.USER_SDO_GEOM_METADATA m

where m.table_name = 'SZGM_LAND_COOR'

and m.COLUMN_NAME = 'shape'

and c.dkcode = '地块001';--若没有这条件，则范围所有地块的质心

4.几何缓冲区

函数：SDO_BUFFER

示例：对地块1做50m缓冲

select c.dkcode, SDO_GEOM.SDO_BUFFER (c.shape, 50, 0.5, 'unit= M')

from SZGM_LAND_COOR c, MDSYS.USER_SDO_GEOM_METADATA m

where m.table_name = 'SZGM_LAND_COOR'

and m.COLUMN_NAME = 'shape'

其中50表示距离

0.5表示容差

‘unit=M’表示单位为米

5.几何关系

函数：sdo_Geom.Relate

公式：sdo_Geom.Relate(sdo_Geometry1, ‘MASK’, sod_Geometry2, tolerance )

参数说明：sdo_Geometry1几何1；sod_Geometry2几何2；MASK几何关系；tolerance容差

几何关系参考表：

示例：选择地块001上所有的公交站点

SELECT p.id, p.bname, p.address

  FROM SZGM_LAND_COOR r, SZ_BUS p

WHERE r.dkcode = '地块001'

   AND sdo_Geom.relate(r.geoloc,'ANYINTERACT',p.geoloc, 0.5)= 'ANYINTERACT'