空间管理您的位置: VR168--虚拟现实起点站 » champingfor的个人空间 » 日志

Oracle Spatial 空间查询

上一篇 / 下一篇 2006-12-08 04:08:06

几个自定义函数

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

--得到线对象的终止点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),
                               To_Geomerty_Point(116.635056, a.node1y),
                               0.005,
                               'unit=KM') +
        sdo_geom.sdo_distance(To_Geomerty_Point(a.node2x, a.node2y),
                               To_Geomerty_Point(a.node1x, 39.869170),
                               0.005,
                               'unit=KM')) dis2
   from tg_routev2 a
order by dis2

--更新node1Distance 和 node2Distance 即线路起始点、终止点距离物流中心的 “南北直角折线” 距离
update tg_routev2
     set node1Distance = (sdo_geom.sdo_distance(To_Geomerty_Point(node1x,
                                                                  node1y),
                                                To_Geomerty_Point(116.635056,
                                                                  node1y),
                                                0.005,
                                                'unit=KM') +
                         sdo_geom.sdo_distance(To_Geomerty_Point(node1x,
                                                                  node1y),
                                                To_Geomerty_Point(node1x,
                                                                  39.869170),
                                                0.005,
                                                'unit=KM')),
         node2Distance = (sdo_geom.sdo_distance(To_Geomerty_Point(node2x,
                                                                  node2y),
                                                To_Geomerty_Point(116.635056,
                                                                  node2y),
                                                0.005,
                                                'unit=KM') +
                         sdo_geom.sdo_distance(To_Geomerty_Point(node2x,
                                                                  node2y),
                                                To_Geomerty_Point(node2x,
                                                                  39.869170),
                                                0.005,
                                                'unit=KM'))
   where node1Distance is null