oracle spatial

2011-11-02

第二章主要內容

幾何引擎：它提供了分析，比較和操作幾何體的函式。

幾何引擎函式：SDO_GEOM.SDO_DISTANCE

索引引擎：為了提高查詢效率，ORACLE SPATIAL提供了空間索引引擎

幾何引擎函式：SDO_NN(nearest neighbor),SDO_ANYINTERACT

檢查在ORACLE DATABASE 中是否安裝了 ORACLE SPATIAL

SQL> col comp_name format a10;

SQL> select comp_name,status from dba_registry where comp_name='Spatial';

COMP_NAME STATUS

---------- ----------------------

Spatial VALID

SQL>

說明成功安裝了，狀態為VALID或是LOADED都可以

檢查SPATIAL安裝的版本

SQL> select sdo_version from dual;

SDO_VERSION

-------------------------------------

11.2.0.1.0

ORACLE Spatial技術分佈在兩層上：資料庫伺服器（DATABASE SERVER）和應用伺服器（APPLICATION SERVER）

資料庫伺服器（DATABASE SERVER）:

l 高階空間引擎

Ø 路由

Ø 網路資料模型（NDM）

Ø 線性參考系統 (LRS)

Ø 分析挖掘

Ø 拓撲和GEORASTER型別

Ø 拓撲資料模型

Ø GEORASTER

l 查詢和分析

Ø 所有引擎查詢操作

Ø 幾何引擎

l 資料模型

Ø SDO_GEOMETRY樣式

l 位置使能

Ø ORACLE 使用工具

Ø 地理編碼器

應用伺服器（APPLICATION SERVER）：

l 視覺化

Ø 地圖緩衝伺服器

Ø FOI（feature-of-interrest,興趣特性）伺服器

Ø 渲染引擎

ORACLE Spatia支援以下的空間資料操作：

l 使用SDO_GEOMETRY資料型別儲存資料模型

l 使用索引引擎和幾何引擎進行查詢分析

l 使用地理編碼器通過轉換地址資料到SDO_GEOMETRY資料實現位置使能

l 使用MAPVIEWER和ORACLE MAPS進行視覺化

l 高階空間引擎功能，如網路分析和路由

LOCATOR的核心功能：

l 使用SDO_GEOMETRY資料型別儲存儲存空間資料的資料模型

l 使用索引引擎和幾何引擎進行查詢分析

l SEO_GEOM.SDO_DISTANCE和SDO_GEOM.VALIDATE_GEOMETRY_XXX函式，這些函式也是LOCATOR的一部分

LOCATOR的的典型應用：

l 簡單的GIS應用程式

l 簡單的商業應用

l CAD/CAM和類似的應用程式

LOCATO提供了SPATIAL技術的一個核心子集

第三章主要內容 2011/12/6

Spatial將一個表的SDO_GEOMETRY列的所有物件作為一個空間層

空間後設資料的字典檢視

SQL> desc user_sdo_geom_metadata;

名稱是否為空? 型別

----------------------------------------- -------- -------------------------

TABLE_NAME NOT NULL VARCHAR2(32)

COLUMN_NAME NOT NULL VARCHAR2(1024)

DIMINFO MDSYS.SDO_DIM_ARRAY

SRID NUMBER

SRID的屬性

大地座標系（GEODETIC）:角座標，用來對應地球表面的“經度，維度”來表示

投影座標系（PROJECTED）:直角座標系

本地座標系（LOCAL）:直角座標系，與地球表面無關，有時是某一應用專用的。

大地座標系的SRID屬性

SQL> select srid from mdsys.cs_srs where wktext like 'GEOGCS%';

投影座標系的SRID屬性

SQL> select srid from mdsys.cs_srs where wktext like 'PROJCS%';

本地座標系的SRID屬性

SQL> select srid from mdsys.cs_srs where wktext like'LOCAL_CS%';

DIMINFO的屬性

空間資料庫本質上是多維的

SQL> desc sdo_dim_array;

SDO_DIM_ARRAY VARRAY(4) OF MDSYS.SDO_DIM_ELEMENT

名稱是否為空? 型別

----------------------------------------- -------- ------------------------

SDO_DIMNAME VARCHAR2(64)

SDO_LB NUMBER

SDO_UB NUMBER

SDO_TOLERANCE NUMBER

SDO_DIM_ARRAY是一個可變長度的SDO_DIM_ELEMENT型別的陣列。

每一個SDO_DIM_ELEMENT型別儲存一個指定維度的資訊

容差和容差值

容差值應該被設定為應用程式中的最小可區別的距離

2011/12/6

第四章主要內容

點：可以用來儲存實體的位置座標

線串：用來存貯某一路段的位置及形狀

多邊形：用來儲存城市的邊界，商業區等

複雜的幾何體：多重多邊形

點事最簡單的幾何體

一個線串連線多個不同的點

元素陣列描述了SDO_GEOMETRY物件的形狀和位置。這個元素組構成了SDO_GEOMETRY物件。

SQL> conn spatial/spatial@QILIN

已連線。

SQL> desc sdo_geometry;

名稱是否為空? 型別

----------------------------------------- -------- ---------------------------

SDO_GTYPE NUMBER

SDO_SRID NUMBER

SDO_POINT MDSYS.SDO_POINT_TYPE

SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY

SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY

SDO_GTYPE 屬性：表示幾何體實際形狀的型別（點，線串，多邊形，集合，多重點，多重線串或多重多邊形）

SDO_SRID 屬性：指定空間參考系的ID

SDO_GTYPE是一個4位的數字，結構為D00T。第一位和最後一位根據幾何體的維數和形狀採用不同的值。第二位和第三位通常被設定成0

• SDO_POINT:如果幾何體是一個點，可以把它的座標儲存到SDO_GEOETRY 的SDO_POINT屬性中

• 如果幾何體是一個任意形狀(如街區網路和城市邊界),則可以把它的座標儲存到SDO_ORDINATES和SDO_ELEM_INFO陣列屬性:

SDO_ORDINATES:儲存所有幾何體元素的座標

SDO_ELEM_INFO:規定在SDO_ORDINATES陣列中，一個新的元素從什麼地方開始，怎麼連線，是點，線串還是多邊形

D在D00T中用來儲存幾何體物件的維度。可以表示從2維到4維的空間物件

SDO_GTYPE中的T規定了幾何體的型別和形狀

在一個特定的基礎上，通過座標來定位地球表面上的資料，被稱為大地座標系或是大地空間參考系

等積投影保留了物體的面積，但是方向和距離都不再被保持。

等距投影在測量由投影區域中心到遠處目的的距離有較大優勢

通過選擇合適的投影技術和三維參考基準，地球表面的位置能夠在二維平面中表示，這種使用特定基準和合適投影的參考，被稱為投影座標系或是投影空間參考系

SQL> desc mdsys.cs_srs;

名稱是否為空? 型別

----------------------------------------- -------- ----------------------------

CS_NAME VARCHAR2(80)

SRID NOT NULL NUMBER(38)

AUTH_SRID NUMBER(38)

AUTH_NAME VARCHAR2(256)

WKTEXT VARCHAR2(2046)

CS_BOUNDS MDSYS.SDO_GEOMETRY

WKTEXT3D VARCHAR2(4000)

EPSG座標系的型別：

SQL> select distinct coord_ref_sys_kind from sdo_coord_ref_sys;

COORD_REF_SYS_KIND

------------------------

PROJECTED

GEOGRAPHIC2D

GEOCENTRIC

VERTICAL

ENGINEERING

COMPOUND

GEOGENTRIC

GEOGRAPHIC3D

已選擇8行。

一維座標系

二維座標系

3D座標系

本地座標系

SQL> select sdo_cs.find_proj_crs(41155,'FALSE') epsg_srid from dual;

EPSG_SRID

--------------------------------------------------------------------

SDO_SRID_LIST(32041)

SDO_PINT僅能夠儲存三個座標。也就是說在資料是三維或是低於三維的時候才合適。對於四維的點，只能使用SDO_ELEM_INFO和SDO_ORDINATES的屬性

SDO_POINT屬性

SQL> desc sdo_point_type;

名稱是否為空? 型別

----------------------------------------- -------- ---------------------------

X NUMBER

Y NUMBER

Z NUMBER

SDO_ORDINATES存貯幾何體中所有元素的頂點，而SDO_ELEM_INFO儲存的是上述元素的型別和在SDO_ORDINATES中的起始地址

當SRID對應於一個大地座標系的時候，你不能指定園或弧。園和弧只在投影或本地座標系中有效。在大地座標系中，可以通過增加圓周上點的密度和用SDO_UTIL.ARC_DENSIFY函式把這些點表示為線性多邊形來近似表示圓和弧。

Mapinfo 地圖在ORACLE中的儲存和管理方式

ORACLE 為管理空間資料庫提供了物件管理模式SDO(SPATIAL DATA OBJECT),同時提供優秀的空間索引機制

MDSYS是ORACLE SPATIAL的管理使用者

MDSYS方案中，表SDOGEOM-METADATA-TABLE 儲存所有上傳到ORACLE中的MAPINFO地圖資訊，

表SDO-INDEX-METADATA-TABLE儲存與索引相關的資訊

上傳到ORACLE 中的每一個地圖由兩個表表示，

一個與該地圖空間索引方式相關的表，叫做索引表，

另一個用來儲存地圖屬性資料和空間資料，稱為資料表。

MAPX控制元件有兩種方式訪問ORACLE中的空間資料，一種為ODBC方式，一種為OCI方式。

ODBC方式的缺點是通用介面，速度慢，需要配置資料來源DNS.

OCI方式的優點是底層介面，速度快。

一個典型的 MapInfo表是由5檔案組成

n TAB – 確定表的結構，如欄位名、排序、長度和型別

n DAT – 包含表中每一個欄位的資料

n MAP – 描述圖形物件

n ID – 聯接表和圖形資料的對照表檔案

n IND – 包含表中索引欄位資訊，索引欄位可以利用“查詢> 查詢”命令

2012/2/15

空間索引

l 空間索引能加快空間操作符在ORACLE表的SDO_GEOMETRY列上的執行速度

l 在建立空間索引之前必須要為空間層(table_name,column_table)插入後設資料

l 空間索引的後設資料儲存在檢視 USER_SDO_INDEX_METADATA中，本檢視中儲存空間索引名稱（SDO_INDEX_NAME），存放索引的表（SDO_INDEX_TABLE）

SQL> desc user_sdo_index_info;

SQL> desc user_sdo_index_metadata;

SQL> desc sdo_index_metadata;

1 建立索引之前為空間層插入後設資料

SQL> desc user_sdo_geom_metadata;

名稱是否為空? 型別

----------------------------------------- -------- ----------------------------

TABLE_NAME NOT NULL VARCHAR2(32)

COLUMN_NAME NOT NULL VARCHAR2(1024)

DIMINFO MDSYS.SDO_DIM_ARRAY

SRID NUMBER

SQL> select * from user_sdo_geom_metadata;

TABLE_NAME COLUMN_NAME DIMINFO SRID

---------- ----------------------------- ------- ----------

CUSTOMERS LOCATION 8307

GC_ROAD_SEGMENT_US GEOMETRY 8307

US_RESTAURANTS LOCATION 8307

US_INTERSTATES GEOM 8307

為對應於customer表的LOCATION列的空間層插入後設資料

user_sdo_geom_metadata 是個檢視

INSERT INTO user_sdo_geom_metadata

(table_name, column_name, srid, diminfo)

VALUES

(

'CUSTOMERS', -- TABLE_NAME

'LOCATION', -- COLUMN_NAME

8307, -- SRID specifying a geodetic coordinate system

SDO_DIM_ARRAY -- DIMINFO attribute for storing dimension bounds, tolerance

(

SDO_DIM_ELEMENT

(

'LONGITUDE', -- DIMENSION NAME for first dimension

-180, -- SDO_LB for the dimension: -180 degrees

180, -- SDO_UB for the dimension: 180 degrees

0.5 -- Tolerance of 0.5 meters (not 0.5 degrees: geodetic SRID)

),

SDO_DIM_ELEMENT

(

'LATITUDE', -- DIMENSION NAME for second dimension

-90, -- SDO_LB for the dimension: -90 degrees

90, -- SDO_UB for the dimension: 90 degrees

0.5 -- Tolerance of 0.5 meters (not 0.5 degrees: geodetic SRID)

)

);

DIMINFO域為每個維定義了邊界和容差(TOLERANE).它的值被設定為一個含有兩個元素的SDO_DIM_ARRAY物件.

2 建立空間索引

l 首先刪除索引:

DROP INDEX CUSTOMERS_SIDX;

DROP INDEX CUSTOMERS_SIDX FORCE;(強力刪除空間索引,由於該空間索引正在使用,需要強迫刪除它)

l 其次建立索引:

CREATE INDEX CUSTOMERS_SIDX ON CUSTOMERS(LOCATION) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

l 驗證空間索引是否建立成功:主要檢視檢視user_indexes中欄位domidx_statUs

SQL> select domidx_statUs,taBLE_name from user_indexes where table_name='SPOT_CDMA';

DOMIDX_STATUS TABLE_NAME

------------- ------------------------------

VALID SPOT_CDMA

SPOT_CDMA

l 重新命名空間索引:索引名稱不能超過18位

ALTER INDEX OLDINDEX RENAME TO NEWINDEX

SQL> SELECT SDO_INDEX_TABLE FROM USER_SDO_INDEX_INFO WHERE TABLE_NAME='SPOT_CDMA' AND COLUMN_NAME='GEOLOC';

SDO_INDEX_TABLE

--------------------------------

MDRT_1F7B5$

SDO_INDEX_TABLE(即空間索引表)的名稱為MDRT_1F7B5$。所有空間索引表的的明長城都是以字首MDRT開頭的。

SQL> SELECT SDO_INDEX_TABLE FROM USER_SDO_INDEX_INFO;

SDO_INDEX_TABLE

--------------------------------

MDRT_1F0B4$

MDRT_1F0AA$

MDRT_1F0A1$

MDRT_1F097$

MDRT_1F08D$

MDRT_1F083$

MDRT_1F079$

MDRT_1F072$

MDRT_1F068$

MDRT_1F05E$

MDRT_1F054$

MDRT_1F04A$

MDRT_1F040$

MDRT_1F039$

MDRT_1F032$

MDRT_1F029$

MDRT_1F01F$

MDRT_1F7B5$

MDRT_1EFDB$

19 rows selected

通過查詢USER_SDO_INDEX_INFO檢視的SDO_INDEX_TABLE列。可以得到所有的空間索引表。

3 空間索引的引數資訊

建立空間索引的語法：

CREATE INDEX ON ()

INDEXTYPE IS MDSYS.SPATIAL_INDEX

PARAMETERS ('parameter_string');

l TABLSPACE 引數

通過這個引數，可以指定用哪個表空間來儲存空間索引表。EG： tablesspace= TBS_3 會將空間索引表儲存在空間表空間TBS_3中
CREATE INDEX customers_sidx ON customers(location)

INDEXTYPE IS MDSYS.SPATIAL_INDEX

PARAMETERS ('TABLESPACE=TBS_3');

l WORK_TABLSPACE 引數

建立和刪除大量不同大小的表會使表空間產生很多的空間碎片。為了避免這種情況，可以使用WORK_TABLSPACE 引數來為這些工作表指定一個單獨的表空間

CREATE INDEX customers_sidx ON customers(location)

INDEXTYPE IS MDSYS.SPATIAL_INDEX

PARAMETERS ('WORK_TABLESPACE= TBS_4');

l SDO_DML_BATCH_SIZE 引數

在含有空間索引的表的插入和刪除操作並未直接納入該空間索引。相反，他們是在事務提交時被批量的納入該索引中。這個引數用於指定一個事務中批量插入刪除更新時的批量大小（對有大量插入的事務，該引數應該設定為50000或是10000）。

CREATE INDEX customers_sidx ON customers(location)

INDEXTYPE IS MDSYS.SPATIAL_INDEX

PARAMETERS ('SDO_DML_BATCH_SIZE=5000');

4 USER_SDO_INDEX_METADATA檢視

SQL> desc user_sdo_index_metadata;

名稱是否為空? 型別

----------------------------------------- -------- -------------------

SDO_INDEX_OWNER VARCHAR2(32)

SDO_INDEX_TYPE VARCHAR2(32)

SDO_LEVEL NUMBER

SDO_NUMTILES NUMBER

SDO_MAXLEVEL NUMBER

SDO_COMMIT_INTERVAL NUMBER

SDO_INDEX_TABLE VARCHAR2(32)

SDO_INDEX_NAME VARCHAR2(32)

SDO_INDEX_PRIMARY NUMBER

SDO_TSNAME VARCHAR2(32)

SDO_COLUMN_NAME VARCHAR2(2048)

SDO_RTREE_HEIGHT NUMBER

SDO_RTREE_NUM_NODES NUMBER

SDO_RTREE_DIMENSIONALITY NUMBER

SDO_RTREE_FANOUT NUMBER

SDO_RTREE_ROOT VARCHAR2(32)

SDO_RTREE_SEQ_NAME VARCHAR2(32)

SDO_FIXED_META RAW(255)

SDO_TABLESPACE VARCHAR2(32)

SDO_INITIAL_EXTENT VARCHAR2(32)

SDO_NEXT_EXTENT VARCHAR2(32)

SDO_PCTINCREASE NUMBER

SDO_MIN_EXTENTS NUMBER

SDO_MAX_EXTENTS NUMBER

SDO_INDEX_DIMS NUMBER

SDO_LAYER_GTYPE VARCHAR2(32)

SDO_RTREE_PCTFREE NUMBER

SDO_INDEX_PARTITION VARCHAR2(32)

SDO_PARTITIONED NUMBER

SDO_RTREE_QUALITY NUMBER

SDO_INDEX_VERSION NUMBER

SDO_INDEX_GEODETIC VARCHAR2(8)

SDO_INDEX_STATUS VARCHAR2(32)

SDO_NL_INDEX_TABLE VARCHAR2(33)

SDO_DML_BATCH_SIZE NUMBER

SDO_RTREE_ENT_XPND NUMBER

SDO_ROOT_MBR MDSYS.SDO_GEOMETRY

可以查詢引數資訊

SQL> select sdo_tablespace from user_sdo_index_metadata;

5 空間索引大小需求確定

SELECT sdo_tune.estimate_rtree_index_size

(

'SPATIAL', -- schema name

'CUSTOMERS', -- table name

'LOCATION' -- column name on which the spatial index is to be built

) sz

FROM dual;

6 向表中新增位置資訊

第一:建立普通表

CREATE TABLE customers

(

id NUMBER,

datasrc_id NUMBER,

name VARCHAR2(35),

category VARCHAR2(30),

street_number VARCHAR2(5),

street_name VARCHAR2(60),

city VARCHAR2(32),

postal_code VARCHAR2(16),

state VARCHAR2(32),

phone_number VARCHAR2(15),

customer_grade VARCHAR2(15)

);

第二向建立好的表中插入資料

INSERT INTO customers VALUES

(

1, -- id

1, -- datasrc_id

'Pizza Hut' , -- name

'Restaurant', -- restaurant

'134', -- street_number

'12TH STREET', -- street_name

'WASHINGTON', -- city

'20003', -- postal_code

'DC', -- state

NULL, -- phone_number

'GOLD' -- customer_grade’

);

第三向剛才建立好的普通表上新增位置資訊

SQL> alter table customers add (location sdo_geometry);

表已更改。

SQL> desc customers;

名稱是否為空? 型別

----------------------------------------- -------- ---------------------

ID NUMBER

DATASRC_ID NUMBER

NAME VARCHAR2(35)

CATEGORY VARCHAR2(30)

STREET_NUMBER VARCHAR2(5)

STREET_NAME VARCHAR2(60)

CITY VARCHAR2(32)

POSTAL_CODE VARCHAR2(16)

STATE VARCHAR2(32)

PHONE_NUMBER VARCHAR2(15)

CUSTOMER_GRADE VARCHAR2(15)

LOCATION PUBLIC.SDO_GEOMETRY

有了LOCATION列插入資料

INSERT INTO customers

(

ID,

DATASRC_ID,

NAME,

CATEGORY,

STREET_NUMBER,

STREET_NAME,

CITY,

POSTAL_CODE,

STATE,

PHONE_NUMBER,

CUSTOMER_GRADE

)

VALUES

(

1, -- id

1, -- datasrc_id

'Pizza Hut' , -- name

'Restaurant', -- restaurant

'134', -- street_number

'12TH STREET', -- street_name

'WASHINGTON', -- city

'20003', -- postal_code

'DC', -- state

NULL, -- phone_number

'GOLD' -- customer_grade’

);

第四查詢customers;表中的具體客戶的地址資訊

SQL> select street_number,street_name,city,state,postal_code from customers where id=1;

STREET_NUMBER STREET_NAME CITY STATE POSTAL_CODE

------------- -------------- ------------------ ----------------- ------------

134 12TH STREET WASHINGTON DC 20003

第五修改地理編碼地址以獲得顯示的空間資訊

UPDATE customers

SET location =

SDO_GCDR.GEOCODE_AS_GEOMETRY

(

'SPATIAL',

SDO_KEYWORDARRAY

(

street_number || '' || street_name, -- add whitespace between street_number and street_name

city || ',' || state || ' ' || postal_code

),

'US'

) ;

ORACLE SPATIAL 使你能夠轉換地址(street_number, street_name, city和postal_code)為一個在地球表面上的二維點位置

SDO_GCDR.GEOCODE_AS_GEOMETRY 這個函式分別採用模式名稱和地理編碼資料集名稱作為第一個和最後一個引數

第二個引數是一個SDO_KEYWORDARRAY物件,由地址部件street_number, street_name, city和postal_code構成

第六查詢顯示的結果

SQL> SELECT location;

2 FROM customers

3 WHERE id=1;

LOCATION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

--------------------------------------------------------------------------------

SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-77.01324, 38.8936, NULL), NULL, NULL)

第七使用SDO_GEOMETRY建構函式更新LOCATION列

UPDATE customers

SET location =

SDO_GEOMETRY

(

2001, -- Specify that location is a point

8307, -- Specify coordinate system id

SDO_POINT_TYPE(-77.06, 38.94, NULL), -- Specify coordinates here

NULL,

NULL

)

WHERE id=1;

第八為CUSTOMERS表的LOCATION列相對應的空間層插入後設資料

INSERT INTO USER_SDO_GEOM_METADATA VALUES

(

'CUSTOMERS', -- TABLE_NAME

'LOCATION', -- COLUMN_NAME

SDO_DIM_ARRAY -- DIMINFO attribute for storing dimension bounds, tolerance

(

SDO_DIM_ELEMENT

(

'LONGITUDE', -- DIMENSION NAME for first dimension

-120, -- SDO_LB for the dimension

120, -- SDO_UB for the dimension

0.5 -- Tolerance of 0.5 meters

),

SDO_DIM_ELEMENT

(

'LATITUDE', -- DIMENSION NAME for second dimension

-70, -- SDO_LB for the dimension

70, -- SDO_UB for the dimension

0.5 -- Tolerance of 0.5 meters

)

),

8307 -- SRID value for specifying a geodetic coordinate system

);

第九

SQL> DESC SDO_GEOMETRY;

名稱是否為空? 型別

----------------------------------------- -------- -------------------------

SDO_GTYPE NUMBER

SDO_SRID NUMBER

SDO_POINT MDSYS.SDO_POINT_TYPE

SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY

SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY

SDO_POINT僅能夠儲存三個座標（X,Y和Z）.這就是說在資料是三維或低於三維的時候才適合。對於四維的點，只能使用SDO_ELEM_INFO和SDO_ORDINATES屬性。

l 查詢CUSTOMER表中LOCATION列的SDO_GTYPE

SQL> select ct.location.sdo_gtype from customers ct ;

LOCATION.SDO_GTYPE

------------------

2001

l SDO_SRID這個屬性為幾何體規定了空間參考系或是座標系，選擇一個合適的座標系需要看以下的表

SQL> desc mdsys.cs_srs;

名稱是否為空? 型別

----------------------------------------- -------- ---------------------

CS_NAME VARCHAR2(80)

SRID NOT NULL NUMBER(38)

AUTH_SRID NUMBER(38)

AUTH_NAME VARCHAR2(256)

WKTEXT VARCHAR2(2046)

CS_BOUNDS MDSYS.SDO_GEOMETRY

WKTEXT3D VARCHAR2(4000)

l SDO_POINT這個屬性定義了點的座標，例如客戶的位置。這個屬性的型別是另一種物件型別SDO_POINT_TYPE.

SQL> DESC SDO_POINT_TYPE;

名稱是否為空? 型別

----------------------------------------- -------- ---------

X NUMBER

Y NUMBER

Z NUMBER

2012/2/16

n 構造簡單二維幾何體的案例來插入資料（充分使用SDO_ELEM_INFO和SDO_ORDINATES屬性）

第一：建立一個儲存所有幾何示例的表

CREATE TABLE geometry_examples

(

name VARCHAR2(100),

description VARCHAR2(100),

geom SDO_GEOMETRY

);

SQL> desc geometry_examples;

名稱是否為空? 型別

----------------------------------------- -------- --------------------

NAME VARCHAR2(100)

DESCRIPTION VARCHAR2(100)

GEOM PUBLIC.SDO_GEOMETRY

第二：插入資料

INSERT INTO geometry_examples (name, description, geom) VALUES

(

'POINT',

'2-dimensional Point at coordinates (-79,37) with srid set to 8307',

SDO_GEOMETRY

(

2001, -- SDO_GTYPE format: D00T. Set to 2001 for a 2-dimensional point

8307, -- SDO_SRID (geodetic)

SDO_POINT_TYPE

(

-79, -- ordinate value for Longitude

37, -- ordinate value Latitude

NULL -- no third dimension (only 2 dimensions)

),

NULL,

NULL

)

);

ORACLE SPATIAL要求經度座標放在第一位，維度座標放在第二維。

第三：用熟知文字（SQL/MM）來構造一個點幾何體

SELECT SDO_GEOMETRY(' POINT(-79 37) ', 8307) geom FROM DUAL;

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

----------------------------------------------------------------------------

SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-79, 37, NULL), NULL, NULL)

第四:在SDO_ORDINATES陣列中（而不是SDO_POINT中）儲存點座標

INSERT INTO geometry_examples VALUES

(

'2-D POINT stored in SDO_ORDINATES',

'2-dimensional Point at coordinates (-79, 37) with srid set to 8307',

SDO_GEOMETRY

(

2001, -- SDO_GTYPE format: D00T. Set to 2001 for as a 2-dimensional point

8307, -- SDO_SRID

NULL, -- SDO_POINT attribute set to NULL

SDO_ELEM_INFO_ARRAY -- SDO_ELEM_INFO attribute (see Table 4-2 for values)

(

1, -- Offset is 1

1, -- Element-type is 1 for a point

1 -- Interpretation specifies # of points. In this case 1.

),

SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute

(

-79, -- Ordinate value for Longitude

37 -- Ordinate value for Latitude

)

);

Offset：永遠設定為1，因為在SDO_ORDINATES中只有一個元素

Element-type：與幾何體的SDO_GTYPE型別中的T值直接對應

Interpretation：表示一個元素更細微的資訊

第五：認識SDO_ELEM_INFO和SDO_ORDINATES屬性

SDO_ELEM_INFO屬性是SDO_ELEM_INFO_ARRAY型別的，這個型別同樣是一個數字型VARRAY，最大容量是1048576個數字。

SDO_ORDINATES屬性是SDO_ORDINATE_ARRAY型別的，這個想是一個數字型VARRAY(可變長度陣列)

第五：瞭解儲存四維點的案例

INSERT INTO geometry_examples VALUES

(

'4-D POINT',

'4-dimensional Point at (Xa=>2, Ya=>2, Za=>2, La=>2) with srid set to NULL',

SDO_GEOMETRY

(

4001, -- SDO_GTYPE: D00T. Set to 4001 as it is a 4-dimensional point

NULL, -- SDO_SRID

NULL, -- SDO_POINT_TYPE is null

SDO_ELEM_INFO_ARRAY(1,1,1), -- Indicates a point element

SDO_ORDINATE_ARRAY(2,2,2,2) -- Store the four ordinates here

)

);

n 通過直線連線的線串（充分使用SDO_ELEM_INFO和SDO_ORDINATES屬性）

為二維的線串插入值

INSERT INTO geometry_examples VALUES

(

'LINE STRING',

'2-D line string connecting A(Xa=>1,Ya=>1),B(Xb=>2, Yb=>2), C(Xc=>2,Yc=>1)',

SDO_GEOMETRY

(

2002, -- SDO_GTYPE: D00T. Set to 2002 as it is a 2-dimensional line string

32774, -- SDO_SRID

NULL, -- SDO_POINT_TYPE is null

SDO_ELEM_INFO_ARRAY -- SDO_ELEM_INFO attribute (see Table 4-2 for values)

(

1, -- Offset is 1

2, -- Element-type is 2 for a LINE STRING

1 -- Interpretation is 1 if line string is connected by straight lines.

),

SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute

(

1,1, -- Xa, Ya values

2,2, -- Xb, Yb values

2,1 -- Xc, Yc values

)

);

n 通過弧線連線的線串（充分使用SDO_ELEM_INFO和SDO_ORDINATES屬性）

為二維的弧線插入值

INSERT INTO geometry_examples VALUES

(

'ARCSTRING',

'2-D arc connecting A(Xa=>1,Ya=>1),B(Xb=>2, Yb=>2), C(Xc=>2,Yc=>1)',

SDO_GEOMETRY

(

2002, -- SDO_GTYPE: D00T. Set to 2002 as it is a 2-dimensional line string

32774, -- SDO_SRID

NULL, -- SDO_POINT_TYPE is null

SDO_ELEM_INFO_ARRAY -- SDO_ELEM_INFO attribute (see Table 4-2 for values)

(

1, -- Offset is 1

2, -- Element-type is 2 for a LINE STRING

2 -- Interpretation is 2 if line string is connected by ARCs.

),

SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute

(

1,1, -- Xa, Ya values

2,2, -- Xb, Yb values

2,1 -- Xc, Yc values

)

);

n 多邊形，邊界通過直線連線的環（充分使用SDO_ELEM_INFO和SDO_ORDINATES屬性）

為直線連線的多邊形插入值

INSERT INTO geometry_examples VALUES

(

'POLYGON',

'2-D polygon connecting A(Xa, Ya), B(Xb, Yb), C(Xc, Yc), D(Xd, Yd)',

SDO_GEOMETRY

(

2003, -- SDO_GTYPE: D00T. Set to 2003 as it is a 2-dimensional polygon

32774, -- SDO_SRID

NULL, -- SDO_POINT_TYPE is null

SDO_ELEM_INFO_ARRAY -- SDO_ELEM_INFO attribute (see Table 4-2 for values)

(

1, -- Offset is 1

1003, -- Element-type is 1003 for an outer POLYGON element

1 -- Interpretation is 1 if boundary is connected by straight lines.

),

SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute

(

1,1, -- Xa, Ya values

2,-1, -- Xb, Yb values

3,1, -- Xc, Yc values

2,2, -- Xd, Yd values

1,1 -- Xa, Ya values : Repeat first vertex to close the ring

)

);

n 圓（充分使用SDO_ELEM_INFO和SDO_ORDINATES屬性）

為圓插入值

INSERT INTO geometry_examples VALUES

(

'CIRCLE POLYGON',

'2-D circle polygon with 3 boundary points A(Xa,Ya), B(Xb,Yb), C(Xc,Yc)',

SDO_GEOMETRY

(

2003, -- SDO_GTYPE: D00T. Set to 2003 as it is a 2-dimensional polygon

32774, -- SDO_SRID

NULL, -- SDO_POINT_TYPE is null

SDO_ELEM_INFO_ARRAY -- SDO_ELEM_INFO attribute (see Table 4-2 for values)

(

1, -- Offset is 1

1003, -- Element-type is 1003 for (an outer) POLYGON

4 -- Interpretation is 4 if polygon is a CIRCLE

),

SDO_ORDINATE_ARRAY -- SDO_ORDINATES attribute

(

1,1, -- Xa, Ya values

3,1, -- Xb, Yb values

2,2 -- Xc, Yc values

)

);

查詢相關屬性值的內容

SQL> select ge.geom.sdo_elem_info from geometry_examples ge;

GEOM.SDO_ELEM_INFO

--------------------------------------------------------------------------------

SDO_ELEM_INFO_ARRAY(1, 1, 1)

SDO_ELEM_INFO_ARRAY(1, 2, 1)

SDO_ELEM_INFO_ARRAY(1, 2, 2)

SDO_ELEM_INFO_ARRAY(1, 1003, 1)

SDO_ELEM_INFO_ARRAY(1, 1003, 4)

已選擇7行。

SQL> select ge.geom.sdo_ordinates from geometry_examples ge;

GEOM.SDO_ORDINATES

--------------------------------------------------------------------------------

SDO_ORDINATE_ARRAY(-79, 37)

SDO_ORDINATE_ARRAY(2, 2, 2, 2)

SDO_ORDINATE_ARRAY(1, 1, 2, 2, 2, 1)

SDO_ORDINATE_ARRAY(1, 1, 2, -1, 3, 1, 2, 2, 1, 1)

SDO_ORDINATE_ARRAY(1, 1, 3, 1, 2, 2)

已選擇7行。

SQL> select ge.geom.sdo_point from geometry_examples ge;

GEOM.SDO_POINT(X, Y, Z)

--------------------------------------------------------------------------------

SDO_POINT_TYPE(-79, 37, NULL)

已選擇7行。

SQL> select ge.geom.sdo_srid from geometry_examples ge;

GEOM.SDO_SRID

-------------

8307

32774

已選擇7行。

SQL> select ge.geom.sdo_gtype from geometry_examples ge;

GEOM.SDO_GTYPE

--------------

2001

4001

2002

2003

已選擇7行。

SDO_GEOMETRY中的方法
SDO_GEOMETRY型別有自己的方法，可以使用它們來得到SDO_GEOMOTRY中的一些資訊：
Get_Dims Number 返回物件的維度與ST_CoordDim返回相同的結果
Get_GType Number 返回物件的型別
Get_LRS_Dim Number 返回物件使用LRS地理座標系統
Get_WKB BLOB 返回眾所周知的二進位制（WKB）的地理物件
Get_WKT CLOB 返回眾所周知的文字（WKT）格式的地理物件
ST_CoordDim Number 返回座標維度
ST_IsValid Number 如果地理物件有效返回0,否則返回1，這個方法使用0.001作為公差，使用SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT函式來指定其它的公差值。

獲取2001後面最後一位的數字,使用GET_GTYPE方法

SQL> select ge.geom.get_gtype() from geometry_examples ge;。

獲取2001後面第一位的數字,使用get_lrs_dim()方法。該方法用來獲取一個空間物件的維度數，比如2維，3維等

SQL> select ge.geom.get_dims() from geometry_examples ge;

獲取2001後面第二位的數字,使用get_lrs_dim()方法

SQL> select ge.geom.get_lrs_dim() from geometry_examples ge;

獲取座標維度與Get_Dims返回相同的結果

select ge.geom.st_coorddim() from geometry_examples ge;

獲取地理物件的有效返回值

select ge.geom.st_isvalid() from geometry_examples ge;

select ge.geom.Get_WKT() from geometry_examples ge where name='POINT';

select TO_CHAR(ge.geom.Get_WKT()) from geometry_examples ge where name='POINT';

select ge.geom.Get_WKB() from geometry_examples ge where name='POINT';

修改相關屬性值的內容

除錯空間資料庫運用的函式

Getnumelem

Getnumverticces

Getvertices

SELECT SDO_UTIL.GETNUMELEM(geom) nelem

FROM sales_regions

WHERE id=10000;

SELECT SDO_UTIL.GETNUMVERTICES(geom) nverts

FROM sales_regions

WHERE id=10000;

2012/2/24

空間操作符

SDO_WITHIN_DISTANCE 操作符

語法:

SDO_WITHIN_DISTANCE

(

Table_geom in sdo_geometry,

Query_geom in sdo_geometry

Parameter_string in varchar2

)

=’TRUE’

Table_geom 為被檢索表的SDO_GEOMETRY列

Query_geom 為指定查詢位置的SDO_GEOMETRY,它可以是另外一個表的一列一個繫結變數，或一個動態構造的物件

Parameter_string 指定引數DISTANCE及可選引數UNIT(對應於指定的距離)

SDO_WITHIN_DISTANCE 操作符獲取在一個競爭對手商店的0.25英里範圍內的所有客戶:

SELECT ct.id, ct.name

FROM competitors comp, customers ct

WHERE comp.id=1

AND SDO_WITHIN_DISTANCE

(ct.location, comp.location, 'DISTANCE=0.25 UNIT=MILE ' )='TRUE' ORDER BY ct.id;

SDO_NN

(

Table_geom in sdo_geometry,

Query_geom in sdo_geometry

[, Parameter_string in varchar2

[,TAG in varchar2]]

)

=’TRUE’

select ct.id, ct.name from competitors co ,customers ct

where co.id=1

and sdo_nn(ct.location,co.location)='TRUE'

and rownum<=5

order by ct.id;

ID NAME

---------- -----------------------------------

809 LINCOLN SUITES

1044 MUSEUM OF THE THIRD DIMENSION

1526 INTERNATIONAL FINANCE

1538 MCKENNA AND CUNEO

8792 DESTINATION HOTEL & RESORTS

select ct.id, ct.name,ct.customer_grade from competitors co ,customers ct

where co.id=1

and ct.customer_grade='GOLD'

and sdo_nn(ct.location,co.location)='TRUE'

and rownum<=5

order by ct.id;

ID NAME CUSTOMER_GRADE

---------- ----------------------------------- ---------------

809 LINCOLN SUITES GOLD

810 HOTEL LOMBARDY GOLD

6326 HOTEL LOMBARDY GOLD

7821 RENAISSANCE MAYFLOWER HOTEL GOLD

8792 DESTINATION HOTEL & RESORTS GOLD

sdo_batch_size效能調優引數

select ct.id, ct.name,ct.customer_grade from competitors co ,customers ct

where co.id=1

and ct.customer_grade='GOLD'

and sdo_nn(ct.location,co.location, 'sdo_batch_size=100')='TRUE'

and rownum<=5

order by ct.id;

ID NAME CUSTOMER_GRADE

---------- ----------------------------------- ---------------

809 LINCOLN SUITES GOLD

810 HOTEL LOMBARDY GOLD

6326 HOTEL LOMBARDY GOLD

7821 RENAISSANCE MAYFLOWER HOTEL GOLD

8792 DESTINATION HOTEL & RESORTS GOLD

sdo_num_res效能調優引數

select ct.id, ct.name,ct.customer_grade from competitors co ,customers ct

where co.id=1

and sdo_nn(ct.location,co.location, 'sdo_num_res=5')='TRUE';

ID NAME CUSTOMER_GRADE

---------- ----------------------------------- ---------------

1044 MUSEUM OF THE THIRD DIMENSION SILVER

809 LINCOLN SUITES GOLD

1526 INTERNATIONAL FINANCE SILVER

1538 MCKENNA AND CUNEO SILVER

8792 DESTINATION HOTEL & RESORTS GOLD

2012/3/21

查詢相關的內容

Sdo_geometry型別，屬性和值

SQL> desc sdo_geometry;

Element Type

------------- -------------------------

SDO_GTYPE NUMBER

SDO_SRID NUMBER

SDO_POINT MDSYS.SDO_POINT_TYPE

SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY

SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY

GET_GTYPE FUNCTION

GET_DIMS FUNCTION

GET_LRS_DIM FUNCTION

GET_WKB FUNCTION

GET_WKT FUNCTION

ST_COORDDIM FUNCTION

ST_ISVALID FUNCTION

SDO_GEOMETRY FUNCTION

l SDO_GTYPE屬性

SQL> select tb.geoloc.SDO_GTYPE from tbgrid tb;

SQL> select distinct tb.geoloc.SDO_GTYPE from tbgrid tb;

GEOLOC.SDO_GTYPE

----------------

2003

2003 表示是一個2維多重面或是多重邊

l SDO_SRID屬性

SQL> select distinct tb.geoloc.SDO_SRID from tbgrid tb;

GEOLOC.SDO_SRID

---------------

8307

8307表示是大地座標系，是一個固定的值

l SDO_POINT屬性

SQL> desc sdo_point_type

名稱是否為空? 型別

X NUMBER

Y NUMBER

Z NUMBER

SQL>select tb.geoloc.SDO_POINT from tbgrid tb;

SQL>select tb.geoloc.SDO_POINT.x from tbgrid tb;

SQL>select tb.geoloc.SDO_POINT.y from tbgrid tb;

SQL>select tb.geoloc.SDO_POINT.z from tbgrid tb;

l SDO_ELEM_INFO屬性

SQL> desc sdo_elem_info_array;

SDO_ELEM_INFO_ARRAY VARRAY(1048576) OF NUMBER

SQL>select tb.geoloc.SDO_ELEM_INFO from tbgrid tb;

l SDO_ORDINATES屬性

SQL> desc sdo_ordinate_array;

SDO_ORDINATE_ARRAY VARRAY(1048576) OF NUMBER

SQL>select tb.geoloc.SDO_ORDINATES from tbgrid tb;

SQL>select to_char(tb.geoloc.GET_WKT()) from tbgrid tb where mi_prinx=1217597;

SQL>select tb.geoloc.GET_WKB() from tbgrid tb where mi_prinx=1217597；

l

2012/3/27 空間操作符

SDO_WITHIN_DISTANCE操作符，獲取在一個競爭對手商店的0.25英里範圍內的所有客戶：

select ct.id, ct.name

from competitors comp, customers ct

where comp.id = 1

and SDO_WITHIN_DISTANCE(ct.location,

comp.location,

'DISTANCE=0.25 UNIT=MILE') = 'TRUE'

ORDER BY ct.id;

SDO_WITHIN_DISTANCE操作符，獲取在一個競爭對手商店的0.25英里範圍內的所有客戶，並返回他們與該商店的距離：

SELECT ct.id,

ct.name,

SDO_GEOM.SDO_DISTANCE(ct.location, comp.location, 0.5, ' UNIT=YARD ') dist

FROM competitors comp, customers ct

WHERE comp.id = 1

AND SDO_WITHIN_DISTANCE(ct.location,

comp.location,

'DISTANCE=0.25 UNIT=MILE') = 'TRUE'

ORDER BY ct.id;

獲取競爭對手商店周圍0.25英里內的街道名，並且返回街道最小為200米的街道：

select s.street_name

from competitors comp, map_streets s

where comp.id = 1

and SDO_WITHIN_DISTANCE(s.geometry,

comp.location,

'DISTANCE=0.25 UNIT=MILE min_resolution=200') =

'TRUE'

order by s.street_name;

獲取競爭對手商店周圍0.25英里內的街道名，並且返回街道最小為200米的街道，同時返回街道最大為500米的街道：

select s.street_name

from competitors comp, map_streets s

where comp.id = 1

and SDO_WITHIN_DISTANCE(s.geometry,

comp.location,

'DISTANCE=0.25 UNIT=MILE min_resolution=200 max_resolution=500') =

'TRUE'

order by s.street_name;

引數min_resolution和max_resolution僅用於SDO_FILTER,SDO_WITHIN_DISTANCE和SDO_RELATE操作符。而不能用於SDO_NN操作符

l SDO_NN操作符：獲取離指定競爭對手最近的5個客戶

select ct.id, ct.name, ct.customer_grade

from competitors comp, customers ct

where comp.id = 1

and SDO_NN(ct.location, comp.location) = 'TRUE'

and rownum <= 5

order by ct.id;

l SDO_NN操作符：獲取離指定競爭對手最近的5個GOLD客戶

select ct.id, ct.name, ct.customer_grade

from competitors comp, customers ct

where comp.id = 1

and ct.customer_grade = 'GOLD'

and SDO_NN(ct.location, comp.location) = 'TRUE'

and rownum <= 5

order by ct.id;

l SDO_NN操作符：獲取離指定競爭對手最近的5個GOLD客戶，使用效能調優引數sdo_batch_size

select ct.id, ct.name, ct.customer_grade

from competitors comp, customers ct

where comp.id = 1

and ct.customer_grade = 'GOLD'

and SDO_NN(ct.location, comp.location,'sdo_batch_size=100') = 'TRUE'

and rownum <= 5

order by ct.id;

l SDO_NN操作符：獲取離指定競爭對手最近的5個客戶，使用效能調優引數sdo_num_res

select ct.id, ct.name, ct.customer_grade

from competitors comp, customers ct

where comp.id = 1

and SDO_NN(ct.location, comp.location,'sdo_num_res=5') = 'TRUE'

order by ct.id;

SELECT COUNT(DISTINCT ct.id)

FROM competitors comp, customers ct,

TABLE

(

SDO_JOIN

(

'competitors', 'location', -- first table and the SDO_GEOMETRY column

'customers', 'location', -- second table and the SDO_GEOMETRY column

'DISTANCE=200 UNIT=METER' -- specify mask relationship

)

) jn

WHERE ct.rowid=jn.rowid2

AND comp.rowid = jn.rowid1;

SDO_JOIN函式的執行速度比與之等價的SDO_RELATE和SDO_WITHIN_DISTANCE操作符要快，因為它使用了兩個空間索引。

alter index customers_sidx rebuild parameters('layer_gtype=POINT');

當對一個含有空間索引的表如CUSTOMERS進行大量的（典型值為30%）刪除操作後，對相關空間索引進行重建將使得該索引相對緊湊，從而可以更加有效的服務隨後的查詢。

SQL> alter index customers_sidx rebuild parameters('layer_gtype=POINT');

索引已更改。

SQL>SELECT SDO_INDEX_NAME,sdo_index_type,SDO_INDEX_TABLE,SDO_NL_INDEX_TABLE FROM USER_SDO_INDEX_METADATA WHERE SDO_INDEX_NAME='CUSTOMERS_SIDX';

SDO_INDEX_NAME SDO_INDEX_ SDO_INDEX_TABLE SDO_NL_INDEX_T

---------------- ---------- ---------------- --------------

CUSTOMERS_SIDX RTREE MDOT_1472C$ MDOT_1472C$

SQL> alter index customers_sidx rebuild ONLINE parameters('layer_gtype=POINT');

索引已更改。

一個可使查詢在索引重建時不被阻塞的方法是，在ALTER INDEX….REBUILD語句中指定關鍵詞ONLINE.隨後的重建可將索引表從MDOT字首轉回為MDRT字首。當重建一個空間列上的索引並指定哦關鍵詞ONLINE時，這個特性確保查詢永遠不會被阻塞。

l 把SDO_GEOMETRY轉換成WKT格式

SQL> select t.location.GET_WKT() wkt from customers t where id=1;

WKT

--------------------------------------------------------------------------------

POINT (-76.9773898 38.8886508)

l 使用TO_WKTGEOMETRY把SDO_GEOMETRY轉換成WKT格式

SQL> select sdo_util.to_wktgeometry(a.location) wkt from customers a where id=1;

WKT

--------------------------------------------------------------------------------

POINT (-76.9773898 38.8886508)

空間層：要對每個空間層進行驗證，建立索引和查詢，需要為每個層指定適當的後設資料，該後設資料包含如下內容：

1 維數

2 每個維度的邊界

3 每個維度的容差

4 座標系

新增空間資料列

ALTER TABLE CUSTOMERS ADD (LOCATION SDO_GEOMETRY):

對地址進行地理編碼

UPDATE branches

SET location = SDO_GCDR.GEOCODE_AS_GEOMETRY

(

'SPATIAL',

SDO_KEYWORDARRAY

( street_number || ' ' || street_name, city || ' ' || state || ' '

|| postal_code),

'US'

);

COMMIT;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20976446/viewspace-721049/，如需轉載，請註明出處，否則將追究法律責任。

oracle spatial

相關文章