oracle spatial 数据导入_oracle spatial之基础知识之四空间索引

空间索引

1建立索引之前为空间层插入元数据

SQL> desc user_sdo_geom_metadata;

名称是否为空?类型

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

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

TABLE_NAMENOT NULL VARCHAR2(32)

COLUMN_NAMENOT NULL

VARCHAR2(1024)

DIMINFOMDSYS.SDO_DIM_ARRAY

SRIDNUMBER

SQL> select *

from user_sdo_geom_metadata;

TABLE_NAMECOLUMN_NAMEDIMINFOSRID

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

CUSTOMERSLOCATION8307

GC_ROAD_SEGMENT_USGEOMETRY8307

US_RESTAURANTSLOCATION8307

US_INTERSTATESGEOM8307

为对应于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');

lTABLSPACE参数

通过这个参数，可以指定用哪个表空间来存储空间索引表。EG：tablesspace= TBS_3会将空间索引表存储在空间表空间TBS_3中CREATE INDEX customers_sidx ON customers(location)

INDEXTYPE IS MDSYS.SPATIAL_INDEX

PARAMETERS ('TABLESPACE=TBS_3');

lWORK_TABLSPACE参数

创建和删除大量不同大小的表会使表空间产生很多的空间碎片。为了避免这种情况，可以使用WORK_TABLSPACE参数来为这些工作表指定一个单独的表空间

CREATE

INDEX customers_sidx ON customers(location)

INDEXTYPE

IS MDSYS.SPATIAL_INDEX

PARAMETERS

('WORK_TABLESPACE= TBS_4');

lSDO_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_OWNERVARCHAR2(32)

SDO_INDEX_TYPEVARCHAR2(32)

SDO_LEVELNUMBER

SDO_NUMTILESNUMBER

SDO_MAXLEVELNUMBER

SDO_COMMIT_INTERVALNUMBER

SDO_INDEX_TABLEVARCHAR2(32)

SDO_INDEX_NAMEVARCHAR2(32)

SDO_INDEX_PRIMARYNUMBER

SDO_TSNAMEVARCHAR2(32)

SDO_COLUMN_NAMEVARCHAR2(2048)

SDO_RTREE_HEIGHTNUMBER

SDO_RTREE_NUM_NODESNUMBER

SDO_RTREE_DIMENSIONALITYNUMBER

SDO_RTREE_FANOUTNUMBER

SDO_RTREE_ROOTVARCHAR2(32)

SDO_RTREE_SEQ_NAMEVARCHAR2(32)

SDO_FIXED_METARAW(255)

SDO_TABLESPACEVARCHAR2(32)

SDO_INITIAL_EXTENTVARCHAR2(32)

SDO_NEXT_EXTENTVARCHAR2(32)

SDO_PCTINCREASENUMBER

SDO_MIN_EXTENTSNUMBER

SDO_MAX_EXTENTSNUMBER

SDO_INDEX_DIMSNUMBER

SDO_LAYER_GTYPEVARCHAR2(32)

SDO_RTREE_PCTFREENUMBER

SDO_INDEX_PARTITIONVARCHAR2(32)

SDO_PARTITIONEDNUMBER

SDO_RTREE_QUALITYNUMBER

SDO_INDEX_VERSIONNUMBER

SDO_INDEX_GEODETICVARCHAR2(8)

SDO_INDEX_STATUSVARCHAR2(32)

SDO_NL_INDEX_TABLEVARCHAR2(33)

SDO_DML_BATCH_SIZENUMBER

SDO_RTREE_ENT_XPNDNUMBER

SDO_ROOT_MBRMDSYS.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;

名称是否为空?类型

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

IDNUMBER

DATASRC_IDNUMBER

NAMEVARCHAR2(35)

CATEGORYVARCHAR2(30)

STREET_NUMBERVARCHAR2(5)

STREET_NAMEVARCHAR2(60)

CITYVARCHAR2(32)

POSTAL_CODEVARCHAR2(16)

STATEVARCHAR2(32)

PHONE_NUMBERVARCHAR2(15)

CUSTOMER_GRADEVARCHAR2(15)

LOCATIONPUBLIC.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_NAMECITYSTATEPOSTAL_CODE

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

13412TH STREETWASHINGTONDC20003

第五修改地理编码地址以获得显示的空间信息

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;

2FROM

customers

3WHERE 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_GTYPENUMBER

SDO_SRIDNUMBER

SDO_POINTMDSYS.SDO_POINT_TYPE

SDO_ELEM_INFOMDSYS.SDO_ELEM_INFO_ARRAY

SDO_ORDINATESMDSYS.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

lSDO_SRID这个属性为几何体规定了空间参考系或是坐标系，选择一个合适的坐标系需要看以下的表

SQL>

desc mdsys.cs_srs;

名称是否为空?类型

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

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

CS_NAMEVARCHAR2(80)

SRIDNOT NULL

NUMBER(38)

AUTH_SRIDNUMBER(38)

AUTH_NAMEVARCHAR2(256)

WKTEXTVARCHAR2(2046)

CS_BOUNDSMDSYS.SDO_GEOMETRY

WKTEXT3DVARCHAR2(4000)

lSDO_POINT这个属性定义了点的坐标，例如客户的位置。这个属性的类型是另一种对象类型SDO_POINT_TYPE.

SQL>

DESC SDO_POINT_TYPE;

名称是否为空?类型

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

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

XNUMBER

YNUMBER

ZNUMBER

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;

名称是否为空?类型

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

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

NAMEVARCHAR2(100)

DESCRIPTIONVARCHAR2(100)

GEOMPUBLIC.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

fromgeometry_examples ge;

GEOM.SDO_ELEM_INFO

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

SDO_ELEM_INFO_ARRAY(1, 1, 1)

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

fromgeometry_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, 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 fromgeometry_examples ge;

GEOM.SDO_POINT(X, Y, Z)

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

SDO_POINT_TYPE(-79, 37, NULL)

已选择7行。

SQL> select ge.geom.sdo_srid fromgeometry_examples ge;

GEOM.SDO_SRID

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

8307

8307

32774

32774

32774

32774

已选择7行。

SQL> select ge.geom.sdo_gtype fromgeometry_examples ge;

GEOM.SDO_GTYPE

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

2001

2001

4001

2002

2002

2003

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;