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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/2df64ef8719499debd7688ce84dc4f97?s=64&d=mm&r=g) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
_jum
Messages: 577 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;
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:27:19 CDT 2024
|