oracle 的 SDO_GEOMETRY

元数据定义

CREATE OR REPLACE TYPE MDSYS.SDO_GEOMETRY          AS OBJECT (
           SDO_GTYPE       NUMBER,
           SDO_SRID        NUMBER,
           SDO_POINT       SDO_POINT_TYPE,
           SDO_ELEM_INFO   SDO_ELEM_INFO_ARRAY,
           SDO_ORDINATES   SDO_ORDINATE_ARRAY,
           MEMBER FUNCTION  GET_GTYPE
           RETURN NUMBER DETERMINISTIC,
           MEMBER FUNCTION  GET_DIMS
           RETURN NUMBER DETERMINISTIC,
           MEMBER FUNCTION  GET_LRS_DIM
           RETURN NUMBER DETERMINISTIC)
 
  ALTER TYPE SDO_GEOMETRY
  ADD MEMBER FUNCTION GET_WKB RETURN BLOB DETERMINISTIC,
  ADD MEMBER FUNCTION GET_WKT RETURN CLOB DETERMINISTIC,
  ADD MEMBER FUNCTION ST_CoordDim RETURN SMALLINT DETERMINISTIC,
  ADD MEMBER FUNCTION ST_IsValid RETURN INTEGER DETERMINISTIC,
  ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN CLOB,
            srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT,
  ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN VARCHAR2,
            srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT,
  ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkb IN BLOB,
            srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT
  CASCADE

创建表

create table test_geom(id int,geom MDSYS.SDO_GEOMETRY)

使用wkt插入表

insert into test_geom(id,geom) values(2,SDO_GEOMETRY('point(116.6 24.343)',4326))

查看空间数据的wkt

select a.geom.GET_WKT() from test_geom a

 SDO_SRID定义了空间坐标参考系统。如果SDO_SRID为null，则没有指定坐标系统，如果SDO_SRID不为null，那么它的值必须在在MDSYS.CS_SRS 表中的 SRID 列有对应的值，而且它的值必须插入USER_SDO_GEOM_METADATA视图中。MDSYS.CS_SRS 表参考 图2

MDSYS.CS_SRS表
列名	类型	列名描述
CS_NAME	VARCHAR2(68)	坐标系统名称
SRID	NUMBER(38)	空间参考ID，为唯一值。1-999999为spatial使用的空间参考，1000000以后为用户自定义
AUTH_SRID	NUMBER(38)	可选的SRID，是个外键。另一个坐标系统的SRID
AUTH_NAME	VARCHAR2(256)
WKTEXT	VARCHAR2(2046)
CS_BOUNDS	MDSYS.SDO_GEOMETRY

select *from MDSYS.CS_SRS，居然没有3857

计算两点间的距离

declare
  v_point SDO_GEOMETRY;
  v_ret   number;
begin
  v_point := SDO_GEOMETRY('point(114.6 12.343)',4326);

  select SDO_GEOM.SDO_DISTANCE(a.geom, v_point, 0.005)
  into v_ret
  from test_geom a
  where id=2;
  
  dbms_output.put_line(v_ret);
end;

参考帮助

http://docs.oracle.com/database/121/SPATL/toc.htm

http://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_sql_mm.htm#SPATL025

http://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_objgeom.htm#SPATL120