Home » Server Options » Spatial » shifting of coordiantes by adding offset value (windows xp,oracle 11g)
shifting of coordiantes by adding offset value [message #513423] Mon, 27 June 2011 07:45 Go to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Hi,
The script below shows shifting of coordinates for point features by adding offset value to each coordinate.I had considered only one table 'M' in the script which is working fine.But the thing is I need to use the script for many point features which exists in the lkp table apart from table 'M'.The script is taking forever since each feature has thousands of records.I need to improve performance of the script.I heard that collections will improve performance.But i have no idea about collections.Please help me in converting the following script into collections.
SQL> CREATE OR REPLACE PROCEDURE SP_SHIFT_POINT_COORD(X_OLD FLOAT,Y_OLD FLOAT,X_NEW FLOAT,Y_NEW FLOAT,V_EXCH VARCHAR2)
  2  AS
  3     v_geom          MDSYS.SDO_GEOMETRY ;
  4     ann_x           FLOAT ;
  5     ann_y           FLOAT ;
  6     x_offset        FLOAT;
  7     y_offset        FLOAT;
  8     cur_2           SYS_REFCURSOR;
  9     v_sqlstr        VARCHAR2(1000);
 10     cur_3           SYS_REFCURSOR;
 11
 12
 13  BEGIN
 14
 15     IF((X_OLD>X_NEW) AND (Y_OLD>Y_NEW))THEN
 16             x_offset :=(X_OLD-X_NEW);
 17             y_offset :=(Y_OLD-Y_NEW);
 18
 19     ELSIF((X_OLD<X_NEW) AND (Y_OLD<Y_NEW))THEN
 20             x_offset :=(X_NEW-X_OLD);
 21             y_offset :=(Y_NEW-Y_OLD);
 22     END IF;
 23
 24  FOR cur_1 IN ( SELECT table_name FROM lkp WHERE (feature_geom_type = 'POINT' OR feature_geom_type='TEXT') AND FEATURE_CLASS IS
NOT NULL AND TABLE_NAME='M')
 25  LOOP
 26     --dbms_output.put_line(1);
 27     OPEN cur_2 FOR 'SELECT  a.GEOM from '||cur_1.table_name||' a WHERE a.EXCHANGE_CODE='||''''||V_EXCH||''''||'';
 28
 29             LOOP
 30             --dbms_output.put_line(2);
 31
 32                     FETCH cur_2 INTO  v_geom;
 33                     EXIT WHEN cur_2%NOTFOUND;
 34
 35                     ann_x := v_geom.sdo_point.x + x_offset ;
 36                     ann_y := v_geom.sdo_point.y + y_offset ;
 37
 38                     v_geom := MDSYS.SDO_GEOMETRY
 39                     (2001,
 40                     NULL,
 41                     MDSYS.SDO_POINT_TYPE(ann_x,ann_y,NULL),
 42                     NULL,
 43                     NULL
 44                     );
 45
 46                     v_sqlstr:= 'update '||cur_1.table_name||' A set A.GEOM = :1';
 47                     EXECUTE IMMEDIATE v_sqlstr USING v_geom;
 48
 49             END LOOP;
 50
 51
 52     OPEN cur_3 FOR 'SELECT  a.TEXT_LOCATION from '||cur_1.table_name||'_ann a WHERE '||cur_1.table_name||'_ref in (select '
 53     ||cur_1.table_name||'_id from '||cur_1.table_name||' where exchange_code = '||''''||V_EXCH||''''||')';
 54             LOOP
 55                     --dbms_output.put_line(3);
 56                     FETCH cur_3 INTO v_geom;
 57                     EXIT WHEN cur_3%NOTFOUND;
 58                     --dbms_output.put_line(4);
 59                     ann_x := v_geom.sdo_point.x + x_offset ;
 60                     ann_y := v_geom.sdo_point.y + y_offset ;
 61                     --dbms_output.put_line(5);
 62                     v_geom := MDSYS.SDO_GEOMETRY
 63                     (2001,
 64                     NULL,
 65                     MDSYS.SDO_POINT_TYPE(ann_x,ann_y,NULL),
 66                     NULL,
 67                     NULL
 68                     );
 69                     --dbms_output.put_line(6);
 70                     v_sqlstr:= 'update '||cur_1.table_name||'_ann A set A.TEXT_LOCATION = :1';
 71                     EXECUTE IMMEDIATE v_sqlstr USING v_geom;
 72                     --dbms_output.put_line(7);
 73
 74             END LOOP;
 75  END LOOP;
 76
 77  commit;
 78  EXCEPTION WHEN OTHERS THEN
 79     RAISE_APPLICATION_ERROR(-20010,SQLERRM);
 80  END SP_SHIFT_POINT_COORD;
 81  /

Procedure created.

SQL> exec SP_SHIFT_POINT_COORD(2886054.105351,538100.958364,2261392.566000,210137.926100,'11');

PL/SQL procedure successfully completed.



Or is there any other method apart from collections....

Thanks........
Re: shifting of coordiantes by adding offset value [message #513464 is a reply to message #513423] Mon, 27 June 2011 11:20 Go to previous message
_jum
Messages: 486
Registered: February 2008
Senior Member
Don't know if I understand You completely.
Here is a little example for modifying tables containing POINT-geometries:
SET SERVEROUTPUT ON SIZE 900000;

--create needed types and table types
CREATE OR REPLACE TYPE fg_type IS OBJECT ( FID NUMBER, geom MDSYS.SDO_GEOMETRY);

CREATE OR REPLACE TYPE fg_table IS TABLE OF fg_type;

--create test table
CREATE TABLE pgeoms 
(ID   NUMBER,
 geom MDSYS.SDO_GEOMETRY);
 
INSERT INTO pgeoms VALUES
 (1, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(  1,  5,  0), NULL,NULL));

INSERT INTO pgeoms VALUES
 (2, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(  3,  7,  0), NULL,NULL));

COMMIT;

SELECT * FROM pgeoms;

ID      GEOM
---------------------------------
1	(2001; ; (1; 5; 0); ; )
2	(2001; ; (3; 7; 0); ; )

--example procedure
DECLARE
   sdpt fg_table;

BEGIN
   
   --BULK COLLECT geometry into collection
   SELECT fg_type(id, geom) BULK COLLECT INTO sdpt FROM pgeoms;
   
   --modify X, Y
   FOR i IN sdpt.first .. sdpt.last 
   LOOP
     NULL;
     sdpt(i).geom.SDO_POINT.X := sdpt(i).geom.SDO_POINT.X + 100;
     sdpt(i).geom.SDO_POINT.Y := sdpt(i).geom.SDO_POINT.Y + 200;
   END LOOP;
   
   --update table
   UPDATE pgeoms pg SET geom =(SELECT geom FROM table(sdpt) WHERE pg.id=fid);
   
END;

SELECT * FROM pgeoms;

ID      GEOM
---------------------------------
1	(2001; ; (101; 205; 0); ; )
2	(2001; ; (103; 207; 0); ; )

ROLLBACK;
Previous Topic: how to create spatial index
Next Topic: error while updating SRID to null
Goto Forum:
  


Current Time: Wed Jul 30 00:35:35 CDT 2014

Total time taken to generate the page: 0.13930 seconds