引自："http://blog.sina.com.cn/s/blog_439d2cef01009c4a.html"
一、Oracle Spatial简介
Oracle
支持自定义的数据类型，可以用数组，结构体或者带有构造函数，功能函数的类来定义自己的对象类型。这样的对象类型可以用于属性列的数据类型，也可以用来创建对象表。而Oracle
Spatial也正是基于此种特性所开发的一套空间数据处理系统。
Spatial
的自定义数据类型有很多，全部在MDSYS方案下，经常使用到的是SDO_GEOMETRY类型。SDO_GEOMETRY表示一个几何对象，可以是点、线、面、多点、多线、多面或混合对象。
Spatial
在此数据类型的基础上，实现了R树空间索引和四叉树空间索引，还以SQL函数的形式实现了多种空间分析功能。
二、测试表的建立与应用（点类型）
--
创建测试用表
CREATE TABLE "SPATIALTEST" (
"ID" VARCHAR2(20) NOT NULL,
"NAME" VARCHAR2(100),
"ADDRESS" VARCHAR2(200),
"TELEPHONE" VARCHAR2(50),
"LOCATION" "MDSYS"."SDO_GEOMETRY"
)LOGGING; |
-- 创建主键约束
ALTER TABLE "SPATIALTEST"
ADD CONSTRAINT "PK_SPATIAL" PRIMARY
KEY("ID"); |
-- 根据用户表填写空间元数据
INSERT INTO USER_SDO_GEOM_METADATA
VALUES(
'SPATIALTEST',
'location',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('Longitude',-180,180,10),
MDSYS.SDO_DIM_ELEMENT('Latitude',-90,90,10)
),
8307
); |
-- 建立空间索引
CREATE INDEX SPATIAL_IDX
ON SPATIALTEST(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX; |
-- 导入测试数据
INSERT INTO "SPATIALTEST"
VALUES(
'dbeb7ea11eaf2b53a9b7',
'小肥羊(天河店)',
'广州市天河区天寿路25号',
'020-38217746',
MDSYS.SDO_GEOMETRY(
2001,
8307,
MDSYS.SDO_POINT_TYPE(113.3293658,
23.14338586, 0),
NULL,
NULL
)
);
INSERT INTO "SPATIALTEST"
VALUES(
'ef8393ef6273a72b2f70',
'山东老家',
'广州市越秀区合群一马路43号',
'020-87778983',
MDSYS.SDO_GEOMETRY(
2001,
8307,
MDSYS.SDO_POINT_TYPE(113.2932474,
23.11883515, 0),
NULL,
NULL
)
);
--其他
INSERT INTO cola_markets VALUES(
2,
'cola_b',
MDSYS.SDO_GEOMETRY(
2003, --
2-dimensional polygon
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
-- one polygon (exterior polygon ring)
MDSYS.SDO_ORDINATE_ARRAY(5,1,
8,1, 8,6, 5,7, 5,1)
)
);
|
-- 属性信息查询
SQL> select location from spatialtest;
LOCATION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO,
SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(113.329366, 23.1433859, 0),
NULL, NULL)
SQL> select s.location.SDO_POINT.x langtitude
from spatialtest s;
LANGTITUDE
----------
113.329366
|
--
空间分析查询(113.2359818,23.16937253)周边十公里信息5条
SELECT
B.id id, B.name name, B.dist
dist
FROM (
SELECT
A.id id,
A.name name,
SDO_GEOM.SDO_DISTANCE(A.location,MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(113.2359818,23.16937253,0),NULL,NULL),1)
dist
FROM
spatialtest
A
WHERE
SDO_WITHIN_DISTANCE(A.LOCATION,MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(113.2359818,23.16937253,0),NULL,NULL),'distance=10000')
= 'TRUE'
ORDER BY A.name
) B
WHERE
ROWNUM <= 5
; |
--
空间分析查询(113.2359818,23.16937253)附近的5条信息
SELECT
A.id id, A.name name,A.location.SDO_POINT.x
langtitude, A.location.SDO_POINT.y
latitude,MDSYS.SDO_NN_DISTANCE(1) distance
FROM
spatialtest A
WHERE
SDO_NN(A.LOCATION,MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(113.2359818,23.16937253,0),null,null),'SDO_NUM_RES=5',1)
= 'TRUE'
; |
-- Return the topological difference of two geometries.
SELECT SDO_GEOM.SDO_DIFFERENCE(c_a.shape, m.diminfo, c_c.shape,
m.diminfo)
FROM cola_markets c_a, cola_markets c_c,
user_sdo_geom_metadata m
WHERE m.table_name = 'COLA_MARKETS' AND
m.column_name = 'SHAPE'
AND c_a.name = 'cola_a' AND c_c.name =
'cola_c'; |
三、MDYSYS.SDO_GEOMETRY结构
CREATE TYPE SDO_GEOMETRY AS OBJECT(
SDO_GTYPE
NUMBER, //表示几何实体的类型
SDO_SRID
NUMBER, //用来表示坐标系(空间参考坐标系)，与几何实体关联
SDO_POINT
MDSYS.SDO_POINT_TYPE,
//使用X,Y,Z属性值来定义对象类型
//它们都是NUMBER类型，
//如果SDO_GEOMETRY的SDO_ELEM_INFO及SDO_ORDINATES数组为
//空则SDO_POINT为非空,X和Y值就是点几何实体的坐标值
SDO_ELEM_INFO
MDSYS.SDO_ELEM_INFO_ARRAY, //使用变长NUMBER型数组来表示。
//该属性将告知如何解释SDO_ORDINATES里边的坐标值;
SDO_ORDINATES
MDSYS.SDO_ORDINATE_ARRAY
//存储坐标值
//形成空间对象的边界
);
|
MDSYS.SDO_GEOMETRY <--> OCCI
对照模型:
四、Oracle Spatial 几个自定义函数（转帖）
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
--得到线对象的终止点X坐标
create or replace function GetEndNodeX(coords in
SDO_ORDINATE_ARRAY) return varchar2 is
Result varchar2(100);
begin
if coords.count > 0 then
Result :=
to_char(coords(coords.count-1));
end if;
return(Result);
end GetEndNodeX;
|
--得到线对象的终止点的Y坐标
create or replace function GetEndNodeY(coords in SDO_ORDINATE_ARRAY
) return varchar2 is
Result varchar2(100);
begin
if
coords.count > 0 then
Result :=
to_char(coords(coords.count));
end if;
return(Result);
end GetEndNodeY;
|
--得到线对象的终止点坐标 X,Y
形式
create or replace function GetEndNodeXY(coords in
SDO_ORDINATE_ARRAY) return varchar2 is
Result varchar2(200);
begin
if coords.count > 0 then
Result :=
to_char(coords(coords.count-1)) || ',' ||
to_char(coords(coords.count));
end if;
return(Result);
end GetEndNodeXY;
|
--得到线对象的起始点X坐标
create or replace function GetStartNodeX(coords in
SDO_ORDINATE_ARRAY) return varchar2 is
Result varchar2(100);
begin
if coords.count > 0 then
Result :=
to_char(coords(1));
end if;
return(Result);
end GetStartNodeX;
|
--得到线对象的起始点的Y坐标
create or replace function GetStartNodeY(coords in
SDO_ORDINATE_ARRAY) return varchar2 is
Result varchar2(100);
begin
if coords.count > 0 then
Result :=
to_char(coords(2));
end if;
return(Result);
end GetStartNodeY;
|
--得到线对象的起始点XY坐标
create or replace function GetStartNodeXY(coords in
SDO_ORDINATE_ARRAY) return varchar2 is
Result varchar2(200);
begin
if coords.count > 0 then
Result := to_char(coords(1))||','||to_char(coords(2));
end if;
return(Result);
end GetStartNodeXY;
|
--把X，Y坐标转换成MDSYS.SDO_GEOMETRY的点对象
create or replace function To_Geomerty_Point(x in number,y in
number ) return MDSYS.SDO_GEOMETRY is
Result MDSYS.SDO_GEOMETRY;
begin
Result := MDSYS.SDO_GEOMETRY(2001,
8307,
null,
MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1),
MDSYS.SDO_ORDINATE_ARRAY(x, y));
return(Result);
end To_Geomerty_Point;
|
--------------------------------------------------------------------------
-------------------查询
及测试语句---------------------------------------
--求线路的长度
select c.routename,
sdo_geom.sdo_length(c.geoloc,m.DIMINFO,'unit=KM')
from tgs_route c,user_sdo_geom_metadata m
select * from tg_routev2
select a.geoloc.SDO_ORDINATES from tgs_route a
where routename = '宣武-6-1'
select GetStartNodeXY(a.geoloc.SDO_ORDINATES) from tgs_route
a
where routename = '宣武-6-1'
select GetEndNodeXY(a.geoloc.SDO_ORDINATES) from tgs_route
a
where routename = '宣武-6-1'
|
--计算线路的起始点和终止点坐标
create table routeNodeXY as
select a.routename,
GetStartNodeX(a.geoloc.SDO_ORDINATES) X1,
GetStartNodeY(a.geoloc.SDO_ORDINATES) Y1,
GetEndNodeX(a.geoloc.SDO_ORDINATES) X2,
GetEndNodeY(a.geoloc.SDO_ORDINATES) Y2
from tgs_route a
|
--更新tg_routev2 中的线起始点 终止点坐标
alter table routeNodeXY add primary
key(routename)
update (select a.*,b.* from tg_routev2 a,routeNodeXY b
where a.routename = b.routename)
set node1x = x1,node1y = Y1,node2x = X2,node2y =
Y2
where node1x is null
|
select sdo_geom.sdo_distance( from dual
--物流中心坐标
--116.635056
--39.869170
|
--node1距离物流中心的
“南北直角折线” 距离
select a.routename,
(sdo_geom.sdo_distance(To_Geomerty_Point(a.node1x, a.node1y),
To_Geomerty_Point(116.635056, a.node1y),
0.005,
'unit=KM') +
sdo_geom.sdo_distance(To_Geomerty_Point(a.node1x, a.node1y),
To_Geomerty_Point(a.node1x, 39.869170),
0.005,
'unit=KM')) dis1
from tg_routev2 a
order by dis1
--node2距离物流中心的
“南北直角折线” 距离
select a.routename,
(sdo_geom.sdo_distance(To_Geomerty_Point(a.node2x, a.node2y),
|