oracle空间查询定义,ORACLE SDO_GEOMETRY 空间数据创建查询

一、Oracle Spatial简介

Oracle 支持自定义的数据类型，可以用数组，结构体或者带有构造函数，功能函数的类来定义自己的对象类型。这样的对象类型可以用于属性列的数据类型，也可以用来创建对象表。而Oracle Spatial也正是基于此种特性所开发的一套空间数据处理系统。

Spatial 的自定义数据类型有很多，全部在MDSYS方案下，经常使用到的是SDO_GEOMETRY类型。SDO_GEOMETRY表示一个几何对象，可以是点、线、面、多点、多线、多面或混合对象。

Spatial 在此数据类型的基础上，实现了R树空间索引和四叉树空间索引，还以SQL函数的形式实现了多种空间分析功能。

二、测试表的建立与应用(点类型)

– 创建测试用表

CREATE TABLE “SPATIALTEST” (

“ID” VARCHAR2(20) NOT NULL,

“NAME” VARCHAR2(100),

“ADDRESS” VARCHAR2(200),

“TELEPHONE” VARCHAR2(50),

“LOCATION” “MDSYS”.”SDO_GEOMETRY”

)LOGGING;

– 创建主键约束

ALTER TABLE “SPATIALTEST”

ADD CONSTRAINT “PK_SPATIAL” PRIMARY KEY(”ID”);

– 根据用户表填写空间元数据

INSERT INTO USER_SDO_GEOM_METADATA

VALUES(

‘SPATIALTEST’,

‘location’,

MDSYS.SDO_DIM_ARRAY(

MDSYS.SDO_DIM_ELEMENT(’Longitude’,-180,180,10),

MDSYS.SDO_DIM_ELEMENT(’Latitude’,-90,90,10)

),

8307

);

– 建立空间索引

CREATE INDEX SPATIAL_IDX

ON SPATIALTEST(location)

INDEXTYPE IS MDSYS.SPATIAL_INDEX;

– 导入测试数据

INSERT INTO “SPATIALTEST”

VALUES(

‘dbeb7ea11eaf2b53a9b7′,

‘小肥羊(天河店)’,

‘广州市天河区天寿路25号’,

‘020-38217746′,

MDSYS.SDO_GEOMETRY(

2001,

8307,

MDSYS.SDO_POINT_TYPE(113.3293658, 23.14338586, 0),

NULL,

NULL

)

);

INSERT INTO “SPATIALTEST”

VALUES(

‘ef8393ef6273a72b2f70′,

‘山东老家’,

‘广州市越秀区合群一马路43号’,

‘020-87778983′,

MDSYS.SDO_GEOMETRY(

2001,

8307,

MDSYS.SDO_POINT_TYPE(113.2932474, 23.11883515, 0),

NULL,

NULL

)

); –其他INSERT INTO cola_markets VALUES(

2,

‘cola_b’,

MDSYS.SDO_GEOMETRY(

2003, – 2-dimensional polygon

NULL,

NULL,

MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), — one polygon (exterior polygon ring)

MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)

)

);

– 属性信息查询

SQL> select location from spatialtest;LOCATION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

——————————————————————————–

SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(113.329366, 23.1433859, 0), NULL, NULL)SQL> select s.location.SDO_POINT.x langtitude from spatialtest s;LANGTITUDE

———-

113.329366

– 空间分析查询(113.2359818,23.16937253)周边十公里信息5条

SELECT

B.id id, B.name name, B.dist dist

FROM (

SELECT

A.id id, A.name name, SDO_GEOM.SDO_DISTANCE(A.location,MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(113.2359818,23.16937253,0),NULL,NULL),1) dist

FROM

spatialtest A

WHERE

SDO_WITHIN_DISTANCE(A.LOCATION,MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(113.2359818,23.16937253,0),NULL,NULL),’distance=10000′) = ‘TRUE’

ORDER BY A.name

) B

WHERE

ROWNUM <= 5

;

– 空间分析查询(113.2359818,23.16937253)附近的5条信息

SELECT

A.id id, A.name name,A.location.SDO_POINT.x langtitude, A.location.SDO_POINT.y latitude,MDSYS.SDO_NN_DISTANCE(1) distance

FROM

spatialtest A

WHERE

SDO_NN(A.LOCATION,MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(113.2359818,23.16937253,0),null,null),’SDO_NUM_RES=5′,1) = ‘TRUE’

;

– Return the topological difference of two geometries.

SELECT SDO_GEOM.SDO_DIFFERENCE(c_a.shape, m.diminfo, c_c.shape, m.diminfo)

FROM cola_markets c_a, cola_markets c_c, user_sdo_geom_metadata m

WHERE m.table_name = ‘COLA_MARKETS’ AND m.column_name = ‘SHAPE’

AND c_a.name = ‘cola_a’ AND c_c.name = ‘cola_c’;

三、MDYSYS.SDO_GEOMETRY结构

CREATE TYPE SDO_GEOMETRY AS OBJECT(

SDO_GTYPE NUMBER, //表示几何实体的类型

SDO_SRID NUMBER, //用来表示坐标系(空间参考坐标系)，与几何实体关联

SDO_POINT MDSYS.SDO_POINT_TYPE, //使用X,Y,Z属性值来定义对象类型 //它们都是NUMBER类型，

//如果SDO_GEOMETRY的SDO_ELEM_INFO及SDO_ORDINATES数组为

//空则SDO_POINT为非空,X和Y值就是点几何实体的坐标值

SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY, //使用变长NUMBER型数组来表示。

//该属性将告知如何解释SDO_ORDINATES里边的坐标值;

SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY //存储坐标值 //形成空间对象的边界

);

//myself

INSERT INTO USER_SDO_GEOM_METADATA   VALUES (   'IX_POI',   'GEOMETRY',   MDSYS.SDO_DIM_ARRAY(     MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 10),     MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 10)      ),   8307 ); INSERT INTO USER_SDO_GEOM_METADATA   VALUES (   'AD_FACE',   'GEOMETRY',   MDSYS.SDO_DIM_ARRAY(     MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 10),     MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 10)      ),   8307 ); CREATE INDEX IDX_POI_GEOMETRY ON IX_POI(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;