空间坐标字段批量生成

--删除原始安装点地理信息表数据
DELETE FROM MONITOR_POINT_GEOMETRY;
COMMIT;

--插入新数据
INSERT INTO MONITOR_POINT_GEOMETRY
  (POINTCODE, GEOMETRY)
  SELECT T.POINTCODE,
         MDSYS.SDO_GEOMETRY(2001,
                            8307,
                            MDSYS.SDO_POINT_TYPE(T.LONGITUDE,
                                                 T.LATITUDE,
                                                 NULL),
                            NULL,
                            NULL)
              FROM MONITOR_POINT T
   WHERE T.LONGITUDE > 0
     AND T.LATITUDE > 0;
  COMMIT;

--   创建新空间索引，通用配置
DELETE FROM USER_SDO_GEOM_METADATA
 WHERE TABLE_NAME = 'MONITOR_POINT_GEOMETRY'
   AND COLUMN_NAME = 'GEOMETRY';
INSERT INTO USER_SDO_GEOM_METADATA
  (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
  ('MONITOR_POINT_GEOMETRY',
   'GEOMETRY',
   MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 100, 130, 0.000000050),
                       MDSYS.SDO_DIM_ELEMENT('Y', 30, 40, 0.000000050)),
   8307);
  
DROP INDEX MONITOR_POINT_GEOMETRY_IDX;

CREATE INDEX MONITOR_POINT_GEOMETRY_IDX ON MONITOR_POINT_GEOMETRY(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

--更新地理信息字段
update monitor_point t
set t.GEOMETRY =  MDSYS.SDO_GEOMETRY(2001,
                                                     8307,            
                                         MDSYS.SDO_POINT_TYPE(T.LONGITUDE,
                                                                                   T.LATITUDE,
                                                                                   NULL),

                                                     NULL,
                                                    NULL);
  COMMIT;
  

--   创建新空间索引，通用配置
DELETE FROM USER_SDO_GEOM_METADATA
 WHERE TABLE_NAME = 'MONITOR_POINT'
   AND COLUMN_NAME = 'GEOMETRY';
INSERT INTO USER_SDO_GEOM_METADATA
  (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
  ('MONITOR_POINT',
   'GEOMETRY',
   MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 100, 130, 0.000000050),
                       MDSYS.SDO_DIM_ELEMENT('Y', 30, 40, 0.000000050)),
   8307);
  
DROP INDEX MONITOR_POINT_IDX;

CREATE INDEX MONITOR_POINT_IDX ON MONITOR_POINT(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

--1.建立clob转geometry的函数
create or replace function F_SPLITCLOB4(V_CLOB_IN CLOB,V_DELIMER VARCHAR2)
 return MDSYS.SDO_ORDINATE_ARRAY PIPELINED is
  L_LOB    CLOB;
  l_vchars varchar2(4000):='';
  l_num  NUMBER(15,8);
  n_length NUMBER;
  n_end  number(10);
  n_offset number(10):=1;
begin
  L_LOB:=V_CLOB_IN;
  n_length := DBMS_LOB.GETLENGTH(L_LOB);
  loop
    n_end:=DBMS_LOB.instr(lob_loc => L_LOB,pattern => V_DELIMER,offset =>n_offset);
    if nvl(n_end,0)>0 then
      l_vchars:=DBMS_LOB.substr(L_LOB,n_end-n_offset,n_offset);
      l_num:=to_number(l_vchars);
      n_offset:=n_end+1;
    ELSE
      l_vchars:=DBMS_LOB.substr(L_LOB,n_length+1-n_offset,n_offset);
      l_num:=to_number(l_vchars);
    end if;
    pipe row( l_num );
    exit when n_end=0;
  end loop;
  RETURN;
end F_SPLITCLOB4;

--2.增加clob字段，为循环使用

--如果存储坐标的字段已经是clob字段，则可直接使用，不需新建
alter table monitor_section_positions add (strcoords clob);

--批量将postion字段中的坐标置更新入clob字段strcoords
update monitor_section_positions set strcoords=positions;
COMMIT;
--3.循环批量更新geometry字段，数据量越多则越慢，STRCOORDS具体为表的clob字段，SECTIONID 为表的UUID
DECLARE
  CURSOR C_TNAME IS
    SELECT *
      FROM MONITOR_SECTION_POSITIONS A
     WHERE A.STRCOORDS IS NOT NULL;
BEGIN
  FOR TNAME IN C_TNAME LOOP
    UPDATE MONITOR_SECTION_POSITIONS T
       SET T.GEOMETRY = MDSYS.SDO_GEOMETRY(2002,
                                           8307,
                                           NULL,
                                           MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1),
                                           F_SPLITCLOB4((SELECT T.STRCOORDS
                                                          FROM MONITOR_SECTION_POSITIONS T
                                                         WHERE T.SECTIONID =
                                                               TNAME.SECTIONID),
                                                        ','))
     WHERE T.SECTIONID = TNAME.SECTIONID;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE);
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
--4.删除新加的clob字段，如果没建立，则不用执行

alter table MONITOR_SECTION_POSITIONS drop column STRCOORDS;
--5.重新建立空间索引
DELETE FROM USER_SDO_GEOM_METADATA
WHERE TABLE_NAME = 'MONITOR_SECTION_POSITIONS'
   AND COLUMN_NAME = 'GEOMETRY';
INSERT INTO USER_SDO_GEOM_METADATA
  (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
  ('MONITOR_SECTION_POSITIONS',
   'GEOMETRY',
   MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 100, 130, 0.000000050),
                       MDSYS.SDO_DIM_ELEMENT('Y', 30, 40, 0.000000050)),
   8307);
drop index MONITOR_SECTION_POSITIONS_IDX;
CREATE INDEX MONITOR_SECTION_POSITIONS_IDX ON MONITOR_SECTION_POSITIONS(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;