Oracle Geodesic to Cartesian Geometry Line Migration

Question

I have a table ( granule) with about 4 million unique geometry objects that currently have SRID = 8307.

I am trying to create a SECOND table with the same data, but using a Cartesian coordinate system.

I created a table,

create table granule_cartesian  (
        granule varchar(64) not null,
        SHAPE sdo_geometry NOT NULL );

and insert the correct geometrical metadata

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values ( 'GRANULE_CARTESIAN', 'SHAPE',
        mdsys.sdo_dim_array(
                mdsys.sdo_dim_element('longitude', -180, 180, .5),
                mdsys.sdo_dim_element('latitude', -90, 90, .5)),
        null);

And now I want to copy the contents of the granule geometry to granule_cartesian.

Obviously a direct copy will not work due to SRID mismatch.

I can copy several at a time by going to wkt and back to the geometry, dividing the SRID:

insert into granule_cartesian
select  granule, 
        SDO_GEOMETRY(SDO_UTIL.TO_WKTGEOMETRY(shape), null) as shape
from    granule
where   platform = 'ZZ'; -- granule has a few other columns... 

This works if I select a subset of the pellet table that is less than ~ 10k (about +/- 10 minutes). More than 10K and works for several hours, several times unconditionally disconnecting me.

, < 10K . , FOREVER , . SDO_CS.TRANSFORM :

SDO_CS.TRANSFORM(geom => shape, to_srid => null )

... NULL SRID :

 12:57:49  [SELECT - 0 row(s), 0.000 secs]  [Error Code: 1405, SQL State: 22002]  ORA-01405: fetched column value is NULL
ORA-06512: at "MDSYS.SDO_CS", line 114
ORA-06512: at "MDSYS.SDO_CS", line 152
ORA-06512: at "MDSYS.SDO_CS", line 5588
ORA-06512: at "MDSYS.SDO_CS", line 3064

SDO_CS.TRANSFORM_LAYER NULL SRID.

- → (SRID = NULL). - - , ?

1) , , , PL/SQL 450 10K . @~ 470 , 2.5 . . / update granule set shape.srid = 8307 - FAST EASY. insert into granule select SDO_CS.TRANSFORM(geom => shape, to_srid => 8307 ) .... FAST EASY. , , - / .

2) 300K . 10 :

 20:06:59  [INSERT - 0 row(s), 0.000 secs]  [Error Code: 4030, SQL State: 61000]  ORA-04030: out of process memory when trying to allocate 8080 bytes (joxcx callheap,f:CDUnscanned)
ORA-04030: out of process memory when trying to allocate 8080 bytes (joxcx callheap,f:CDUnscanned)
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,kgmtlbdl)
ORA-06512: at "MDSYS.SDO_UTIL", line 2484
ORA-06512: at "MDSYS.SDO_UTIL", line 2511

beefy . Oracle ( Oracle), ( ). .

Answer 1

- . geom 64 ( - , , ), sdo_anyinteract/sdo_contains 200 5 .

, . , , :

alter table [table] nologging ; or alter tablespace [tablesspace] nologging ;

, , , , .

, SRID WKT, SRID SDO.

declare

newGeom sdo_geometry ;

begin

 for rec in ( select statement ) loop

    newGeom := sdo_util.to_wktgeometry(rec.geom);
    newGeom.sdo_srid := [srid that matches the target ] ;

   insert into [table] (geom column, ... )values( newGeom, ... );

 end loop;

 commit ;

end ;

4 , - .

, DBA

, . . , 64 , 3 . , R- , , root .

, BULK COLLECT, . , , , Oracle ( ) Oracle →

Answer 2

BrianB, , , SDO_GEOMETRY (SDO_UTIL.TO_WKTGEOMETRY (shape), null). , , , , . , , :

create table granule_cartesian  (
    granule varchar(64) not null,
    SHAPE sdo_geometry NOT NULL );

insert into granule_cartesian
select  granule, shape
from    granule
where   platform = 'ZZ'; -- granule has a few other columns... 

update granule_cartesian t
set t.shape.sdo_srid = null;

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values ( 'GRANULE_CARTESIAN', 'SHAPE',
    mdsys.sdo_dim_array(
            mdsys.sdo_dim_element('longitude', -180, 180, .5),
            mdsys.sdo_dim_element('latitude', -90, 90, .5)),
    null); -- add metadata after all rows are updated to null srid

, - , :

insert into granule_cartesian
select  granule, mdsys.sdo_geometry (t.shape.SDO_GTYPE, null, t.shape.SDO_POINT, t.shape.SDO_ELEM_INFO, t.shape.SDO_ORDINATES)
from    granule t
where   platform = 'ZZ'; -- granule has a few other columns... 

user_sdo_geom_metadata , granule_cartesian.

. .