原文鏈接：?http://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

最后兩位:

ValueGeometryDescription

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

總結

以上是生活随笔為你收集整理的oracle spatial 数据导入_【转】 Oracle Spatial 基本操作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。