Home » Server Options » Spatial » oracle spatial coordinates to relational (2 threads merged by bb) (windows xp)
oracle spatial coordinates to relational (2 threads merged by bb) [message #511256] Fri, 10 June 2011 08:24 Go to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Hi,
I tried inserting spatial coordiantes into relational type ,i.e into SDO_X1,SDO_Y1 individually.this is of point feature type.So the following is working fine for point features.

SQL> CREATE OR REPLACE procedure migration
  2  AS
  3  BEGIN
  4     FOR cur_1 IN(SELECT a.GEOM.sdo_point v_ord from pole a)
  5     LOOP
  6           insert into B$GC_P_S_SDOGEOM(SDO_X1,SDO_Y1)values(cur_1.v_ord.X,cur_1.v_ord.Y);
  7          END LOOP;
  8  END migration;
  9  /



But the problem is with linear features since it will have more coordinates,but in target I had only SDO_X1,SDO_Y1,SDO_X2,.....SDO_X4,SDO_Y4 i.e only 8 coordinates are available for each record.So we need to insert into next record for the remaining coorrdinates in source.For example if in source first record has 10 coordinates,8 out of 10 need to be inserted into first record of target,then remaining two into next record.likewise it will take for other records of source to target.I tried writing as follows,but could not proceed further with the logic

SQL> CREATE OR REPLACE procedure sp_migration_lnr
  2  AS
  3  v_coord            SDO_ORDINATE_ARRAY;
  4  BEGIN
  5     FOR cur_1 IN(SELECT a.GEOM.sdo_ordinates v_coord from cable)
  6     LOOP
  7             v_count := v_coord.COUNT;
  8             FOR p IN 1..v_count
  9             IF (MOD(p,8) = 0 ) THEN
 10             insert into B$GC_CBL_L_SDOGEOM()values();
       --------how to write logic please help me----------------
 11              ELSE
 12
 13    END LOOP;
 14  END sp_migration_lnr;
 15 /



Also target table structure is as follows



SQL> CREATE TABLE B$GC_CBL_L_SDOGEOM
  2  (
  3  G3E_ID           NUMBER(10),
  4    G3E_FID          NUMBER(10),
  5    SDO_X1           FLOAT(126),
  6    SDO_Y1           FLOAT(126),
  7    SDO_X2           FLOAT(126),
  8    SDO_Y2           FLOAT(126),
  9    SDO_X3           FLOAT(126),
 10    SDO_Y3           FLOAT(126),
 11    SDO_X4           FLOAT(126),
 12    SDO_Y4           FLOAT(126)
 13    );


Please help me with the logic.....
Thanks.......



Re: oracle spatial coordinates to relational (2 threads merged by bb) [message #511419 is a reply to message #511256] Sun, 12 June 2011 23:32 Go to previous messageGo to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
The above requirement is urgent,can anyone please help me out..
Re: oracle spatial coordinates to relational (2 threads merged by bb) [message #511504 is a reply to message #511419] Mon, 13 June 2011 08:50 Go to previous message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
I don't understand from your explanation what you are trying to achieve. Are the pole and cable table the same table in two different examples or are they different tables and both related to the problem? Please post create table statement(s) for your pole and/or cable table(s) and insert statements for some sample data, and how you want that same data to end up in the B$GC_CBL_L_SDOGEOM target table. I don't understand the part about putting 8 out of 10 in one record and 2 in the next. It seems you will be corrupting your data. Are you sure this is what you want to do? It might help if you describe the overall goal and not just what you are trying to do here, as there might be a better way.


Previous Topic: how to use collections and forall insert
Next Topic: how to create spatial index
Goto Forum:
  


Current Time: Sat Dec 20 06:43:29 CST 2014

Total time taken to generate the page: 0.09976 seconds