Oracle Spacial（空间数据库）sdo_util包的函数

sdo_util包的函数

1、sdo_util.remove_duplicate_vertices

删除重复的顶点

SDO_UTIL.REMOVE_DUPLICATE_VERTICES

geometry IN SDO_GEOMETRY,

tolerance IN NUMBER

) RETURNSDO_GEOMETRY;

 

—更新一条有重复点记录

update i_exch_s t

            sett.gwm_geometry = (select SDO_UTIL.REMOVE_DUPLICATE_VERTICES(a.gwm_geometry,0.005)
fromi_exch_s a
where a.name = ‘北方装潢材料市场’)

          wheret.name =
‘北方装潢材料市场’;

—对整个表进行更新，去除重复点

update i_exch_s t

   set t.gwm_geometry = (selectSDO_UTIL.REMOVE_DUPLICATE_VERTICES(a.gwm_geometry,0.005)from
i_exch_sa

                          wheret.gwm_fid = a.gwm_fid);

2、sdo_util.extract（只能提取二维）

SDO_UTIL.EXTRACT(

geometry IN SDO_GEOMETRY, –提取的对象

element IN NUMBER       –提取哪个元素

[, ring IN NUMBER]        –环号，可选

) RETURNSDO_GEOMETRY;

3、sdo_util.append

    在给定的容差值内，这个函数将两个输入的几何体合成为单个几何体。

SDO_UTIL.APPEND(

geom1 INSDO_GEOMETRY,

geom2 INSDO_GEOMETRY

) RETURNSDO_GEOMETRY

4、sdo_util.GetNumElem

获取元素的数目（即由几个元素组成）

SDO_UTIL.GETNUMELEM(

geometry IN SDO_GEOMETRY

) RETURN NUMBER;

 

SELECT c.name, SDO_UTIL.GETNUMELEM(c.gwm_geometry)
FROMp_street_area c;

5、sdo_util.GetNumVertices

获取对象的顶点数目

SDO_UTIL.GETNUMVERTICES(

geometry IN SDO_GEOMETRY

) RETURN NUMBER;

 

SELECT c.name, SDO_UTIL.GETNUMVERTICES(c.gwm_geometry)
FROMp_street_area c;

6、sdo_util.getvertices

获取对象的点坐标

SDO_UTIL.GETVERTICES(

geometry IN SDO_GEOMETRY

) RETURNVERTEX_SET_TYPE;

 

SELECT c.name, t.X, t.Y, t.id

  FROM p_street_area c,
TABLE(SDO_UTIL.GETVERTICES(c.gwm_geometry))t;

7、SDO_UTIL.CIRCLE_POLYGON

返回一个圆

SDO_UTIL.CIRCLE_POLYGON(

center_longitude IN NUMBER,

center_latitude IN NUMBER,

radius IN NUMBER,

arc_tolerance IN NUMBER

) RETURNSDO_GEOMETRY;

SELECT SDO_UTIL.CIRCLE_POLYGON(-71.34937,
42.46101,
100, 5)
FROM DUAL;

 

8、SDO_UTIL.ELLIPSE_POLYGON

返回一个椭圆

SDO_UTIL.ELLIPSE_POLYGON(

center_longitude IN NUMBER,

center_latitude IN NUMBER,

semi_major_axis IN NUMBER,

semi_minor_axis IN NUMBER,

azimuth IN NUMBER,

arc_tolerance IN NUMBER

) RETURNSDO_GEOMETRY;

SELECT SDO_UTIL.ELLIPSE_POLYGON(-71.34937,42.46101, 100, 50, 90, 5)FROM DUAL;

9、SDO_UTIL.CONVERT_UNIT

单位转换

SDO_UTIL.CONVERT_UNIT(

input_value IN NUMBER,

from_unit IN VARCHAR2,

to_unit IN VARCHAR2

) RETURN NUMBER;

SELECT SDO_UTIL.CONVERT_UNIT(1,
‘Radian’,
‘Degree’) FROM DUAL;

10、wkt、wkb与geometry互转与验证

Ø  SDO_UTIL.FROM_WKBGEOMETRY

Ø  SDO_UTIL.FROM_WKTGEOMETRY

Ø  SDO_UTIL.TO_WKBGEOMETRY

Ø  SDO_UTIL.TO_WKTGEOMETRY

Ø  SDO_UTIL.VALIDATE_WKBGEOMETRY

Ø  SDO_UTIL.VALIDATE_WKTGEOMETRY

 

 

DECLARE

wkbgeom BLOB;

wktgeom CLOB;

val_result VARCHAR2(5);

geom_result SDO_GEOMETRY;

geom SDO_GEOMETRY;

BEGIN

SELECT c.shape INTO geom FROM cola_marketsc WHERE c.name = ‘cola_b’;

— To WBT/WKT geometry

wkbgeom := SDO_UTIL.TO_WKBGEOMETRY(geom);

wktgeom := SDO_UTIL.TO_WKTGEOMETRY(geom);

DBMS_OUTPUT.PUT_LINE(‘To WKT geometryresult = ‘ || TO_CHAR(wktgeom));

— From WBT/WKT geometry

geom_result := SDO_UTIL.FROM_WKBGEOMETRY(wkbgeom);

geom_result := SDO_UTIL.FROM_WKTGEOMETRY(wktgeom);

— Validate WBT/WKT geometry

val_result := SDO_UTIL.VALIDATE_WKBGEOMETRY(wkbgeom);

DBMS_OUTPUT.PUT_LINE(‘WKB validation result= ‘ || val_result);

val_result := SDO_UTIL.VALIDATE_WKTGEOMETRY(wktgeom);

DBMS_OUTPUT.PUT_LINE(‘WKT validation result= ‘ || val_result);

END;

11、GML与GEOMETRY转换

 

SDO_UTIL.TO_GMLGEOMETRY(

thegeom IN SDO_GEOMETRY

) RETURN CLOB;

SELECT TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(shape))
ASGmlGeometry

  FROM COLA_MARKETS c

 WHERE c.name =
‘cola_b’;

12、SDO_UTIL.SIMPLIFY

根据输入的阈值来简化输入的对象

SDO_UTIL.SIMPLIFY(

geometry IN SDO_GEOMETRY,

threshold IN NUMBER

tolerance IN NUMBER DEFAULT 0.0000005

) RETURN SDO_GEOMETRY;

 

SELECT SDO_UTIL.SIMPLIFY(

SDO_GEOMETRY(

3302,
— linestring, 3 dimensions (X,Y,M), 3rd is linear ref. dimension

NULL,

NULL,

SDO_ELEM_INFO_ARRAY(1,2,1),
— one line string, straight segments

SDO_ORDINATE_ARRAY(

2,2,0,
–Starting point – Exit1; 0 is measure from start.

2,4,2,
–Exit2; 2 is measure from start.

8,4,8,
–Exit3; 8 is measure from start.

12,4,12,
–Exit4; 12 is measure from start.

12,10,NULL,
— Notan exit; measure automatically calculated and filled.

8,10,22,
–Exit5; 22 is measure from start.

5,14,27)
–Ending point (Exit6); 27 is measure from start.

),

6,
–threshold value for geometry simplification

0.5–tolerance

) FROM DUAL;

13、SDO_UTIL.RECTIFY_GEOMETRY

纠正有错误的对象并正确输出

SDO_UTIL.RECTIFY_GEOMETRY(

geometry IN SDO_GEOMETRY,

tolerance IN NUMBER

) RETURNSDO_GEOMETRY;

SELECT SDO_UTIL.RECTIFY_GEOMETRY(shape,
0.005)

FROM COLA_MARKETS c
WHERE c.name = ‘cola_b’;

 

14、SDO_UTIL.PREPARE_FOR_TTS与SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS

在不同的Oracle数据库之间传输数据可通过表空间。而为了保证表空间上的表的空间索引也被传输，需要执行这些函数SDO_UTIL.PREPARE_FOR_TTS（在传输表空间前执行）与SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS（在表空间导出后执行）

例子：创建一个dump文件，从一个源数据库中传输表空间tbs。

首先：从一个源数据库中传输表空间tbs

Sqlplus spatial/ spatial

Execute SDO_UTIL.PREPARE_FOR_TTS(‘TBS’)

Connectsystem/manager as sysdba

Executedbms_tts.transport_set_check(‘TBS’,true);

Altertablespace TBS read only;

Exit;

 

Expspatial/ spatialtransport_tablespace=y tablespaces=TBS file=trans_ts.dmp

 

然后导入

impspatial/ spatialtransport_tablespace=y file=trans_ts.dmp datafiles=’sdo_tts.dbf’ tablespaces=tbs

 

Sqlplus sys/password

Altertablespace TBS read write;

Connectspatial/ spatial;

Exec SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS

 

 

15、sdo_util.extract3d

提取三维对象的元素

16、sdo_util.sdo_concat_lines

连接两个线串

SDO_UTIL.CONCAT_LINES(

geom1 INSDO_GEOMETRY,

geom2 INSDO_GEOMETRY

) RETURNSDO_GEOMETRY;

17、sdo_util.sdo_reverse_linestring

倒转一条线串中的顶点的顺序

SDO_UTIL.REVERSE_LINESTRING(

geometry IN SDO_GEOMETRY

) RETURNSDO_GEOMETRY;

18、sdo_util.sdo_polygontoline

将多边形转换为线串几何体

SDO_UTIL.POLYGONTOLINE(

geometry IN SDO_GEOMETRY

) RETURNSDO_GEOMETRY;

 

15到18非免费

转载自：https://blog.csdn.net/gaoying1234/article/details/78672046