读取spatial空间数据表中的经纬度

1.获取一个空间要素的点数：

create or replace function get_num_points (g SDO_GEOMETRY)
RETURN NUMBER
IS
BEGIN
 RETURN g.SDO_ORDINATES.COUNT()/SUBSTR(g.SDO_GTYPE,1,1);
END;

2.获取一个空间要素某点的经度或纬度：

--参数type 值1为经度，2为纬度。
--参数point_number为空间要素中点的序列号。

CREATE OR REPLACE FUNCTION get_point(geom sdo_geometry, type NUMBER DEFAULT 1, point_number NUMBER DEFAULT 1)
RETURN NUMBER
IS
       d NUMBER;
       p NUMBER;
       px NUMBER;
       py NUMBER;
BEGIN
       d:=SUBSTR(geom.SDO_GTYPE,1,1);
       IF point_number<1
       OR point_number>geom.SDO_ORDINATES.COUNT()/d THEN
       RETURN NULL;
       END IF;

       P:=(point_number-1)*d+1;

       px := geom.SDO_ORDINATES(p);
       py := geom.SDO_ORDINATES(p+1);

       IF type = 1 THEN
         RETURN px;
       END IF;

       IF type = 2 THEN
         RETURN py;
       END IF;
END;

3.获取某表中的所有经纬度：

create or replace procedure proce_varray_0 as
  v_x number;   --经度
  v_y number;   --纬度
  vv_count number;   --空间对象数
  v_count number;    --每个空间对象的点数
  v_name varchar2(40);
begin
 
 DBMS_OUTPUT.ENABLE (200000000000000000000);
 select count(*) into vv_count from CHINA_SHENG t;
 --for n in 1..1
 --loop
   select  tt.num INTO v_count from (select GET_NUM_POINTS(t.geometry) num,rownum r from CHINA_SHENG t where t.rname='天津市')tt ;--tt.r=n;--WHERE FNODE_=929;
   for i in 1..v_count    --1..v_count/3,v_count/3+1..v_count/3*2,v_count/3*2+1..v_count
   loop
         --select  num INTO v_COUNT from (select GET_NUM_POINTS(t.geometry) num,rownum from CHINA_GUOJIE t)tt where rownum=n;--WHERE FNODE_=929;
         select tt.px,tt.py,tt.rname INTO v_x, v_y,v_name from (select GET_POINT(t.geometry,1,i) px,GET_POINT(t.geometry,2,i) py,rname,rownum r from CHINA_SHENG_ t where t.rname='天津市')tt ;--where tt.r=n;--WHERE FNODE_=929;
         --select tt.py INTO v_y from (select GET_POINT(t.geometry,2,i) py,rownum r from CHINA_SHENG t)tt where tt.r=n;--WHERE FNODE_=929;
         --select tt.rname INTO v_name from (select rname,rownum r from CHINA_SHENG t)tt where tt.r=n;--WHERE     
         dbms_output.put_line(v_x||' '||v_y||' '||v_name);
   end loop;
   dbms_output.put_line(v_count*2);
 --end loop;
end;