oracle spatial之基礎知識之四空間索引

空間索引

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 建立空間索引

首先刪除索引:

DROP INDEX CUSTOMERS_SIDX;

其次建立索引:

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

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行。

修改相關屬性值的內容

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

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;

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

oracle spatial之基礎知識之四空間索引

相關文章