title: Migrating Oracle geometry rows from geodetic to cartesian
body: <p>I have a table (<code>granule</code>) with about 4 million unique geometry objects that currently have SRID = 8307. </p>
  
  <p>I am trying to create a SECOND table, with the same data, but using a cartesian coordinate system. </p>
  
  <p>I created the table, </p>
  
  <pre><code>create table granule_cartesian  (
          granule varchar(64) not null,
          SHAPE sdo_geometry NOT NULL );
  </code></pre>
  
  <p>and insert the proper geom_metadata</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>And now I want to copy the geometry contents of granule into granule_cartesian.</p>
  
  <p>Obviously, the straight copy won't work because of SRID mismatch.</p>
  
  <p>I can copy a few at a time by converting to wkt and back to geometry, stripping SRID:</p>
  
  <pre><code>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... 
  </code></pre>
  
  <p>This works if I select a subset of granule table that is less than ~ 10k (about +/-10 minutes). Any more than 10K and the runs for hours, some times ungracefully disconnecting me. </p>
  
  <p>It seems like there should be a way to do this WITHOUT doing &lt;10K chunks. Besides taking FOREVER to actually migrate, this would pose a serious logistical nightmare on our active and dynamic production DB.  I've tried using SDO_CS.TRANSFORM like this: </p>
  
  <pre><code>SDO_CS.TRANSFORM(geom =&gt; shape, to_srid =&gt; null )
  </code></pre>
  
  <p>... But oracle will not accept a NULL SRID here:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>SDO_CS.TRANSFORM_LAYER will refuse to accept a NULL SRID. </p>
  
  <p>After extensive searching, I cannot find any method to do a streamline geodetic -> cartesian (SRID=NULL) conversion. Does anyone have any ideas besides brute force small batching? </p>
  
  <p><strong><em>EDITS</em></strong></p>
  
  <p>1) For Clarity, I understand that I could probably break it up using PL/SQL and do 450 blocks of 10K rows. But @ ~470 seconds per block, that is still 2.5 DAYS of execution. And that is a BEST case scenario. Changing projections/coordinate systems using <code>update granule set shape.srid = 8307</code> is FAST and EASY. Changing coordinate system from cartesian to geodetic using <code>insert into granule select SDO_CS.TRANSFORM(geom =&gt; shape, to_srid =&gt; 8307 ) ....</code> is FAST and EASY. What I'm looking for is an equally as simple/fast solution to go from geodetic to cartesian.</p>
  
  <p>2) Tried to insert 300K as a test. It ran for approximately 10 hours and died like this: </p>
  
  <pre><code> 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
  </code></pre>
  
  <p>This is a <strong>beefy</strong> enterprise level server with nothing but oracle. We recently had a Oracle Consultant (From Oracle) analyze all our DB systems (including this one). It was given a clean bill of health. </p>