Creating SDO_Geometry from geometric data recorded in the columns of a table

I was contacted recently by someone who wanted to know:

I am struggling to create a function where I can take multiple x and y columns and create a geometry from that. I have already created such an function for points. Attached is some of the rows within the table. As you can see there are 64 x and y columns but not all are populated. So the function has to know that it has to start from sdo_x1 and then end at sdo_y39 for example. We do not use the z columns at all.

This is the table (renamed) that was provided. I have removed some columns to make this blog easier to read:

 drop   table GEOM_IN_COLUMNS;
 create table GEOM_IN_COLUMNS (
 SDO_GID         NUMBER,
 SDO_ESEQ        NUMBER,
 SDO_ETYPE       NUMBER,
 SDO_SEQ         NUMBER,
 GDO_ATTRIBUTES  NUMBER,
 GDO_NORMAL1     FLOAT(126),
 GDO_NORMAL2     FLOAT(126),
 GDO_NORMAL3     FLOAT(126),
 GDO_RADIUS      FLOAT(126),
 SDO_ORIENTATION FLOAT(126),
 SDO_X1          FLOAT(126),
 SDO_Y1          FLOAT(126),
 SDO_Z1          FLOAT(126),
 SDO_X2          FLOAT(126),
 SDO_Y2          FLOAT(126),
 SDO_Z2          FLOAT(126),
 ......
 SDO_X63        FLOAT(126),
 SDO_Y63        FLOAT(126),
 SDO_Z63        FLOAT(126),
 SDO_X64        FLOAT(126),
 SDO_Y64        FLOAT(126),
 SDO_Z64        FLOAT(126),
 GEOMETRY       SDO_GEOMETRY
 );

The GEOMETRY column will hold the resultant generated geometry generated from possibly multiple rows of the same sdo_gid: the geometry is to be written to the first row (identified as sdo_seq = 0).

This method of normalised storage was used before the advent of spatial data types for the storage and manipulation in a single column (ie Oracle’s SDO_Geometry, SQL Server 2008’s/PostGIS’s geometry/geography types).

A sample linestring (sdo_etype = 2) was provided for testing. That linestring is occupies 15 rows of the GEOM_IN_COLUMNS table. The data has been cut down for display purposes. The full data is in the associated SQL script file.

 Insert into GEOM_IN_COLUMNS 
 ( SDO_GID,SDO_ESEQ,SDO_ETYPE,SDO_SEQ,GDO_ATTRIBUTES,GDO_NORMAL1,GDO_NORMAL2,GDO_NORMAL3,GDO_RADIUS,SDO_ORIENTATION,
   SDO_X1,SDO_Y1,SDO_Z1,SDO_X2,SDO_Y2,SDO_Z2,...SDO_X64,SDO_Y64,SDO_Z64,GEOMETRY 
 ) VALUES (49213537,0,2,0,196608,null,null,null,null,null,17.77571,-29.898399,0,17.775706,-29.89844,0,...17.774425,-29.90226,0,null);
 ...
 ) VALUES (49213537,0,2,1,Null,Null,Null,Null,Null,Null,17.774425,-29.90226,0,17.77442,-29.902333,0,...17.77289,-29.905261,0,Null);
 ...
 ) VALUES (49213537,0,2,2,Null,Null,Null,Null,Null,Null,17.77289,-29.905261,0,17.772852,-29.905314,0,...17.769348,-29.907942,0,Null);
 ...
 ) VALUES (49213537,0,2,3,Null,Null,Null,Null,Null,Null,17.769348,-29.907942,0,17.769317,-29.907999,0,...17.766493,-29.911012,0,Null);
 ...
 ) VALUES (49213537,0,2,4,Null,Null,Null,Null,Null,Null,17.766493,-29.911012,0,17.76649,-29.911047,0,...17.766742,-29.915025,0,Null);
 ...
 ) VALUES (49213537,0,2,5,null,null,null,null,null,null,17.766742,-29.915025,0,17.766751,-29.915058,0,...17.765259,-29.918183,0,null);
 ...
 ) VALUES (49213537,0,2,6,Null,Null,Null,Null,Null,Null,17.765259,-29.918183,0,17.765174,-29.918206,0,...17.761877,-29.921023,0,Null);
 ...
 ) VALUES (49213537,0,2,7,Null,Null,Null,Null,Null,Null,17.761877,-29.921023,0,17.761825,-29.921068,0,...17.757868,-29.923414,0,Null);
 ...
 ) VALUES (49213537,0,2,8,Null,Null,Null,Null,Null,Null,17.757868,-29.923414,0,17.757814,-29.923478,0,...17.754378,-29.925204,0,Null);
 ...
 ) VALUES (49213537,0,2,9,Null,Null,Null,Null,Null,Null,17.754378,-29.925204,0,17.754317,-29.925234,0,...17.751793,-29.926377,0,Null);
 ...
 ) VALUES (49213537,0,2,10,Null,Null,Null,Null,Null,Null,17.751793,-29.926377,0,17.751761,-29.926405,0,...17.752366,-29.928761,0,Null);
 ...
 ) VALUES (49213537,0,2,11,Null,Null,Null,Null,Null,Null,17.752366,-29.928761,0,17.752375,-29.928781,0,...17.751243,-29.93073,0,Null);
 ...
 ) VALUES (49213537,0,2,12,Null,Null,Null,Null,Null,Null,17.751243,-29.93073,0,17.751236,-29.930773,0,...17.75071,-29.934367,0,Null);
 ...
 ) VALUES (49213537,0,2,13,Null,Null,Null,Null,Null,Null,17.75071,-29.934367,0,17.7507,-29.934408,0,...17.750222,-29.937368,0,Null);
 ...
 ) VALUES (49213537,0,2,14,Null,Null,Null,Null,Null,Null,17.750222,-29.937368,0,17.750224,-29.937416,0,...Null,Null,Null,Null);
 commit;

The data looks like this:

 Select GIC.sdo_gid,GIC.sdo_etype,GIC.sdo_eseq,GIC.sdo_seq,
         GIC.SDO_X1,GIC.SDO_Y1,GIC.SDO_Z1,
         GIC.SDO_X2,GIC.SDO_Y2,GIC.SDO_Z2,
         GIC.SDO_X63,GIC.SDO_Y63,GIC.SDO_Z63,
         GIC.SDO_X64,GIC.SDO_Y64,GIC.SDO_Z64
   from GEOM_IN_COLUMNS GIC
  order by GIC.sdo_gid,GIC.sdo_etype,GIC.sdo_eseq,GIC.sdo_seq;

To turn columns of numbers into the ordinates of the sdo_geometry sdo_ordinate_array we need, first, to unpivot the SDO_Xnn columns of a single row into (up to) 64 * 3 rows (with no rows generated for NULL values).

Since we need to know how many columns need to be unpivoted, we could “hard code” the fact that we know this table has 64 columns of X, 64 columns of Y and 64 columns of Z values. Thankfully we can use the Oracle metadata catalog (INFORMATION_SCHEMA to non-Oracle people) to determine the maximum number of coordinates and to generate the final list of SDO_XYZ columns required in our unpivot query.

 Select To_Number(Substr(Utc.Column_Name,6,3)) As Coord,
        Max(Case When Utc.Column_Name Like 'SDO_X%' Then utc.Column_Name Else Null End) As Sdo_X,
        Max(Case When Utc.Column_Name Like 'SDO_Y%' Then utc.Column_Name Else Null End) As Sdo_Y,
        Max(Case When Utc.Column_Name Like 'SDO_Z%' Then Utc.Column_Name Else Null End) As Sdo_Z
   From User_Tab_Columns Utc
  Where utc.Table_Name = 'GEOM_IN_COLUMNS'
    And Regexp_Like(Utc.Column_Name,'^SDO_[XYZ]([[:digit:]]{1,2})') 
   GROUP BY to_number(Substr(utc.Column_Name,6,3))
   order by 1;

Now this is a lot of columns for the construction of the DECODE statement that is required in our target SQL statement for unpivoting our data. It is much too tedious and error prone to do this manually! Let’s generate these DECODE clauses from the metadata via the following query:

 Select 'DECODE(coord.id,' || Replace((Ltrim(Max(Sys_Connect_By_Path(Coord_Name,',')) Keep (Dense_Rank Last Order By Curr),',')),
                                      ' ',
                                      ',') || ') as ' || col_name As pivot_clause
 From   (Select Substr(Utc.Column_Name,1,5) As Col_Name,
                 Substr(Utc.Column_Name,6,3) || ' ' || Utc.Column_Name As Coord_Name,
                 Row_Number() Over (Partition By Substr(Utc.Column_Name,1,5) Order By To_Number(Substr(utc.Column_Name,6,3)) ) As Curr,
                 Row_Number() Over (Partition By Substr(Utc.Column_Name,1,5) Order By To_Number(Substr(Utc.Column_Name,6,3)) ) -1 As Prev
          From  User_Tab_Columns utc
          Where Utc.Table_Name = 'GEOM_IN_COLUMNS'
            And Regexp_Like(Utc.Column_Name,'^SDO_[XYZ]([[:digit:]]{1,2})')   
         )
 Group By col_name
 Connect By Prev = Prior Curr And Substr(Col_Name,1,5) = Prior Substr(Col_Name,1,5) 
 Start With Curr = 1;

The result (cutdown for more readability) is:

 *PIVOT_CLAUSE*
 ------------
 DECODE(coord.id,1,SDO_X1,2,SDO_X2....63,SDO_X63,64,SDO_X64) as SDO_X
 DECODE(coord.id,1,SDO_Y1,2,SDO_Y2....63,SDO_Y63,64,SDO_Y64) as SDO_Y
 DECODE(coord.id,1,SDO_Z1,2,SDO_Z2....63,SDO_Z63,64,SDO_Z64) as SDO_Z

OK, let’s put it together for the one row we have… let’s see this as XY and Z

 With Coord As (
 Select To_Number(Substr(Utc.Column_Name,6,3)) As Id
   From User_Tab_Columns utc
  Where Table_Name = 'GEOM_IN_COLUMNS'
    And Regexp_Like(Utc.Column_Name,'^SDO_[XYZ]([[:digit:]]{1,2})') 
   GROUP BY to_number(Substr(utc.Column_Name,6,3))
   Order By 1
 )
 Select sdo_gid,sdo_etype,sdo_eseq,sdo_seq,id,sdo_x,sdo_y,sdo_z
   From (Select At.Sdo_Gid,
                 at.sdo_etype,
                 at.Sdo_Eseq,
                 at.sdo_seq,
                 Coord.id,
                 Decode(Coord.Id,1,Sdo_X1,2,Sdo_X2,3,Sdo_X3,4,Sdo_X4,5,Sdo_X5,6,Sdo_X6,7,Sdo_X7,8,Sdo_X8,9,Sdo_X9,10,Sdo_X10,11,Sdo_X11,12,Sdo_X12,13,Sdo_X13,14,Sdo_X14,15,Sdo_X15,16,Sdo_X16,17,Sdo_X17,18,Sdo_X18,19,Sdo_X19,20,Sdo_X20,21,Sdo_X21,22,Sdo_X22,23,Sdo_X23,24,Sdo_X24,25,Sdo_X25,26,Sdo_X26,27,Sdo_X27,28,Sdo_X28,29,Sdo_X29,30,Sdo_X30,31,Sdo_X31,32,Sdo_X32,33,Sdo_X33,34,Sdo_X34,35,Sdo_X35,36,Sdo_X36,37,Sdo_X37,38,Sdo_X38,39,Sdo_X39,40,Sdo_X40,41,Sdo_X41,42,Sdo_X42,43,Sdo_X43,44,Sdo_X44,45,Sdo_X45,46,Sdo_X46,47,Sdo_X47,48,Sdo_X48,49,Sdo_X49,50,Sdo_X50,51,Sdo_X51,52,Sdo_X52,53,Sdo_X53,54,Sdo_X54,55,Sdo_X55,56,Sdo_X56,57,Sdo_X57,58,Sdo_X58,59,Sdo_X59,60,Sdo_X60,61,Sdo_X61,62,Sdo_X62,63,Sdo_X63,64,Sdo_X64) As Sdo_X,
                 DECODE(coord.id,1,SDO_Y1,2,SDO_Y2,3,SDO_Y3,4,SDO_Y4,5,SDO_Y5,6,SDO_Y6,7,SDO_Y7,8,SDO_Y8,9,SDO_Y9,10,SDO_Y10,11,SDO_Y11,12,SDO_Y12,13,SDO_Y13,14,SDO_Y14,15,SDO_Y15,16,SDO_Y16,17,SDO_Y17,18,SDO_Y18,19,SDO_Y19,20,SDO_Y20,21,SDO_Y21,22,SDO_Y22,23,SDO_Y23,24,SDO_Y24,25,SDO_Y25,26,SDO_Y26,27,SDO_Y27,28,SDO_Y28,29,SDO_Y29,30,SDO_Y30,31,SDO_Y31,32,SDO_Y32,33,SDO_Y33,34,SDO_Y34,35,SDO_Y35,36,SDO_Y36,37,SDO_Y37,38,SDO_Y38,39,SDO_Y39,40,SDO_Y40,41,SDO_Y41,42,SDO_Y42,43,SDO_Y43,44,SDO_Y44,45,SDO_Y45,46,SDO_Y46,47,SDO_Y47,48,SDO_Y48,49,SDO_Y49,50,SDO_Y50,51,SDO_Y51,52,SDO_Y52,53,SDO_Y53,54,SDO_Y54,55,SDO_Y55,56,SDO_Y56,57,SDO_Y57,58,SDO_Y58,59,SDO_Y59,60,SDO_Y60,61,SDO_Y61,62,SDO_Y62,63,SDO_Y63,64,SDO_Y64) as SDO_Y,
                 Decode(Coord.Id,1,Sdo_Z1,2,Sdo_Z2,3,Sdo_Z3,4,Sdo_Z4,5,Sdo_Z5,6,Sdo_Z6,7,Sdo_Z7,8,Sdo_Z8,9,Sdo_Z9,10,Sdo_Z10,11,Sdo_Z11,12,Sdo_Z12,13,Sdo_Z13,14,Sdo_Z14,15,Sdo_Z15,16,Sdo_Z16,17,Sdo_Z17,18,Sdo_Z18,19,Sdo_Z19,20,Sdo_Z20,21,Sdo_Z21,22,Sdo_Z22,23,Sdo_Z23,24,Sdo_Z24,25,Sdo_Z25,26,Sdo_Z26,27,Sdo_Z27,28,Sdo_Z28,29,Sdo_Z29,30,Sdo_Z30,31,Sdo_Z31,32,Sdo_Z32,33,Sdo_Z33,34,Sdo_Z34,35,Sdo_Z35,36,Sdo_Z36,37,Sdo_Z37,38,Sdo_Z38,39,Sdo_Z39,40,Sdo_Z40,41,Sdo_Z41,42,Sdo_Z42,43,Sdo_Z43,44,Sdo_Z44,45,Sdo_Z45,46,Sdo_Z46,47,Sdo_Z47,48,Sdo_Z48,49,Sdo_Z49,50,Sdo_Z50,51,Sdo_Z51,52,Sdo_Z52,53,Sdo_Z53,54,Sdo_Z54,55,Sdo_Z55,56,Sdo_Z56,57,Sdo_Z57,58,Sdo_Z58,59,Sdo_Z59,60,Sdo_Z60,61,Sdo_Z61,62,Sdo_Z62,63,Sdo_Z63,64,Sdo_Z64) As Sdo_Z
           From GEOM_IN_COLUMNS At,
                Coord Coord
         )
   Where Sdo_X Is Not Null
 order by sdo_gid,sdo_seq,id;

To build the SDO_Geometry object we unpivot the XY columns (user doesn’t want Z or null values columns) as above, but we push them into a single column (representing the ordinates of the final sdo_ordinate array) via the use of a CASE statement. This column is then collected together via the CAST(MULTISET and fed to the SDO_ORDINATE_ARRAY constructor.

Because we only want to process a single, unique sdo_gid at a time, we get it by selecting, in the outer join the one associated with sdo_seq = 0.

Finally, to ensure what we are generating is valid we pump the output into the SDO_GEOM.VALIDATE_GEOMETRY() function.

 Select Sdo_Gid, Mdsys.Sdo_Geom.Validate_Geometry_with_Context(Geom, 0.05) As Vg, c.Geom.get_wkt()
   From (
 Select B.Sdo_Gid, 
        MdSys.sdo_Util.Remove_Duplicate_vertices(
            Mdsys.Sdo_Geometry(2000 + Sdo_Etype,
                               8307,
                               Null,
                               Mdsys.Sdo_Elem_Info_Array(1,decode(sdo_etype,3,1003,2,2,2),1),
                               Cast(Multiset(Select Case When L.Xy = 1 then
                                                         Decode(Coord.Id,1,Sdo_X1,2,Sdo_X2,3,Sdo_X3,4,Sdo_X4,5,Sdo_X5,6,Sdo_X6,7,Sdo_X7,8,Sdo_X8,9,Sdo_X9,10,Sdo_X10,11,Sdo_X11,12,Sdo_X12,13,Sdo_X13,14,Sdo_X14,15,Sdo_X15,16,Sdo_X16,17,Sdo_X17,18,Sdo_X18,19,Sdo_X19,20,Sdo_X20,21,Sdo_X21,22,Sdo_X22,23,Sdo_X23,24,Sdo_X24,25,Sdo_X25,26,Sdo_X26,27,Sdo_X27,28,Sdo_X28,29,Sdo_X29,30,Sdo_X30,31,Sdo_X31,32,Sdo_X32,33,Sdo_X33,34,Sdo_X34,35,Sdo_X35,36,Sdo_X36,37,Sdo_X37,38,Sdo_X38,39,Sdo_X39,40,Sdo_X40,41,Sdo_X41,42,Sdo_X42,43,Sdo_X43,44,Sdo_X44,45,Sdo_X45,46,Sdo_X46,47,Sdo_X47,48,Sdo_X48,49,Sdo_X49,50,Sdo_X50,51,Sdo_X51,52,Sdo_X52,53,Sdo_X53,54,Sdo_X54,55,Sdo_X55,56,Sdo_X56,57,Sdo_X57,58,Sdo_X58,59,Sdo_X59,60,Sdo_X60,61,Sdo_X61,62,Sdo_X62,63,Sdo_X63,64,Sdo_X64)
                                                         Else
                                                         Decode(Coord.Id,1,Sdo_Y1,2,Sdo_Y2,3,Sdo_Y3,4,Sdo_Y4,5,Sdo_Y5,6,Sdo_Y6,7,Sdo_Y7,8,Sdo_Y8,9,Sdo_Y9,10,Sdo_Y10,11,Sdo_Y11,12,Sdo_Y12,13,Sdo_Y13,14,Sdo_Y14,15,Sdo_Y15,16,Sdo_Y16,17,Sdo_Y17,18,Sdo_Y18,19,Sdo_Y19,20,Sdo_Y20,21,Sdo_Y21,22,Sdo_Y22,23,Sdo_Y23,24,Sdo_Y24,25,Sdo_Y25,26,Sdo_Y26,27,Sdo_Y27,28,Sdo_Y28,29,Sdo_Y29,30,Sdo_Y30,31,Sdo_Y31,32,Sdo_Y32,33,Sdo_Y33,34,Sdo_Y34,35,Sdo_Y35,36,Sdo_Y36,37,Sdo_Y37,38,Sdo_Y38,39,Sdo_Y39,40,Sdo_Y40,41,Sdo_Y41,42,Sdo_Y42,43,Sdo_Y43,44,Sdo_Y44,45,Sdo_Y45,46,Sdo_Y46,47,Sdo_Y47,48,Sdo_Y48,49,Sdo_Y49,50,Sdo_Y50,51,Sdo_Y51,52,Sdo_Y52,53,Sdo_Y53,54,Sdo_Y54,55,Sdo_Y55,56,Sdo_Y56,57,Sdo_Y57,58,Sdo_Y58,59,Sdo_Y59,60,Sdo_Y60,61,Sdo_Y61,62,Sdo_Y62,63,Sdo_Y63,64,Sdo_Y64) 
                                                     End As Ordinate
                                               From GEOM_IN_COLUMNS At,
                                                    (Select To_Number(Substr(utc.Column_Name,6,3)) As Id
                                                       From User_Tab_Columns Utc
                                                      Where Utc.Table_Name = 'GEOM_IN_COLUMNS'
                                                        And Regexp_Like(Utc.Column_Name,'^SDO_[XYZ]([[:digit:]]{1,2})') 
                                                      GROUP BY to_number(Substr(utc.Column_Name,6,3))
                                                      Order By 1
                                                     ) Coord,
                                                     (select level xy from dual connect by level < 3) l
                                               Where At.Sdo_Gid = B.Sdo_Gid
                                                 And Decode(Coord.Id,1,Sdo_X1,2,Sdo_X2,3,Sdo_X3,4,Sdo_X4,5,Sdo_X5,6,Sdo_X6,7,Sdo_X7,8,Sdo_X8,9,Sdo_X9,10,Sdo_X10,11,Sdo_X11,12,Sdo_X12,13,Sdo_X13,14,Sdo_X14,15,Sdo_X15,16,Sdo_X16,17,Sdo_X17,18,Sdo_X18,19,Sdo_X19,20,Sdo_X20,21,Sdo_X21,22,Sdo_X22,23,Sdo_X23,24,Sdo_X24,25,Sdo_X25,26,Sdo_X26,27,Sdo_X27,28,Sdo_X28,29,Sdo_X29,30,Sdo_X30,31,Sdo_X31,32,Sdo_X32,33,Sdo_X33,34,Sdo_X34,35,Sdo_X35,36,Sdo_X36,37,Sdo_X37,38,Sdo_X38,39,Sdo_X39,40,Sdo_X40,41,Sdo_X41,42,Sdo_X42,43,Sdo_X43,44,Sdo_X44,45,Sdo_X45,46,Sdo_X46,47,Sdo_X47,48,Sdo_X48,49,Sdo_X49,50,Sdo_X50,51,Sdo_X51,52,Sdo_X52,53,Sdo_X53,54,Sdo_X54,55,Sdo_X55,56,Sdo_X56,57,Sdo_X57,58,Sdo_X58,59,Sdo_X59,60,Sdo_X60,61,Sdo_X61,62,Sdo_X62,63,Sdo_X63,64,Sdo_X64) Is Not Null 
                                            Order By at.Sdo_Seq,Coord.id,l.xy
                                            ) As Mdsys.Sdo_Ordinate_Array)
                               ),
                   0.05) As Geom 
   From GEOM_IN_COLUMNS B
  Where b.sdo_seq = 0
 ) c;

We are now all good to go with the update for sdo_seq = 0.

 Update GEOM_IN_COLUMNS B
   set geometry = (Select MdSys.sdo_Util.Remove_Duplicate_vertices(
                            Mdsys.Sdo_Geometry(2000 + Sdo_Etype,
                                               8307,
                                               Null,
                                               Mdsys.Sdo_Elem_Info_Array(1,decode(sdo_etype,3,1003,2,2,2),1),
                                               Cast(Multiset(Select Case When L.Xy = 1 then
                                                                         Decode(Coord.Id,1,Sdo_X1,2,Sdo_X2,3,Sdo_X3,4,Sdo_X4,5,Sdo_X5,6,Sdo_X6,7,Sdo_X7,8,Sdo_X8,9,Sdo_X9,10,Sdo_X10,11,Sdo_X11,12,Sdo_X12,13,Sdo_X13,14,Sdo_X14,15,Sdo_X15,16,Sdo_X16,17,Sdo_X17,18,Sdo_X18,19,Sdo_X19,20,Sdo_X20,21,Sdo_X21,22,Sdo_X22,23,Sdo_X23,24,Sdo_X24,25,Sdo_X25,26,Sdo_X26,27,Sdo_X27,28,Sdo_X28,29,Sdo_X29,30,Sdo_X30,31,Sdo_X31,32,Sdo_X32,33,Sdo_X33,34,Sdo_X34,35,Sdo_X35,36,Sdo_X36,37,Sdo_X37,38,Sdo_X38,39,Sdo_X39,40,Sdo_X40,41,Sdo_X41,42,Sdo_X42,43,Sdo_X43,44,Sdo_X44,45,Sdo_X45,46,Sdo_X46,47,Sdo_X47,48,Sdo_X48,49,Sdo_X49,50,Sdo_X50,51,Sdo_X51,52,Sdo_X52,53,Sdo_X53,54,Sdo_X54,55,Sdo_X55,56,Sdo_X56,57,Sdo_X57,58,Sdo_X58,59,Sdo_X59,60,Sdo_X60,61,Sdo_X61,62,Sdo_X62,63,Sdo_X63,64,Sdo_X64)
                                                                         Else
                                                                         Decode(Coord.Id,1,Sdo_Y1,2,Sdo_Y2,3,Sdo_Y3,4,Sdo_Y4,5,Sdo_Y5,6,Sdo_Y6,7,Sdo_Y7,8,Sdo_Y8,9,Sdo_Y9,10,Sdo_Y10,11,Sdo_Y11,12,Sdo_Y12,13,Sdo_Y13,14,Sdo_Y14,15,Sdo_Y15,16,Sdo_Y16,17,Sdo_Y17,18,Sdo_Y18,19,Sdo_Y19,20,Sdo_Y20,21,Sdo_Y21,22,Sdo_Y22,23,Sdo_Y23,24,Sdo_Y24,25,Sdo_Y25,26,Sdo_Y26,27,Sdo_Y27,28,Sdo_Y28,29,Sdo_Y29,30,Sdo_Y30,31,Sdo_Y31,32,Sdo_Y32,33,Sdo_Y33,34,Sdo_Y34,35,Sdo_Y35,36,Sdo_Y36,37,Sdo_Y37,38,Sdo_Y38,39,Sdo_Y39,40,Sdo_Y40,41,Sdo_Y41,42,Sdo_Y42,43,Sdo_Y43,44,Sdo_Y44,45,Sdo_Y45,46,Sdo_Y46,47,Sdo_Y47,48,Sdo_Y48,49,Sdo_Y49,50,Sdo_Y50,51,Sdo_Y51,52,Sdo_Y52,53,Sdo_Y53,54,Sdo_Y54,55,Sdo_Y55,56,Sdo_Y56,57,Sdo_Y57,58,Sdo_Y58,59,Sdo_Y59,60,Sdo_Y60,61,Sdo_Y61,62,Sdo_Y62,63,Sdo_Y63,64,Sdo_Y64) 
                                                                     End As Ordinate
                                                               From GEOM_IN_COLUMNS At,
                                                                    (Select To_Number(Substr(utc.Column_Name,6,3)) As Id
                                                                       From User_Tab_Columns Utc
                                                                      Where Utc.Table_Name = 'GEOM_IN_COLUMNS'
                                                                        And Regexp_Like(Utc.Column_Name,'^SDO_[XYZ]([[:digit:]]{1,2})') 
                                                                      GROUP BY to_number(Substr(utc.Column_Name,6,3))
                                                                      Order By 1
                                                                     ) Coord,
                                                                     (select level xy from dual connect by level < 3) l
                                                               Where At.Sdo_Gid = B.Sdo_Gid
                                                                 And Decode(Coord.Id,1,Sdo_X1,2,Sdo_X2,3,Sdo_X3,4,Sdo_X4,5,Sdo_X5,6,Sdo_X6,7,Sdo_X7,8,Sdo_X8,9,Sdo_X9,10,Sdo_X10,11,Sdo_X11,12,Sdo_X12,13,Sdo_X13,14,Sdo_X14,15,Sdo_X15,16,Sdo_X16,17,Sdo_X17,18,Sdo_X18,19,Sdo_X19,20,Sdo_X20,21,Sdo_X21,22,Sdo_X22,23,Sdo_X23,24,Sdo_X24,25,Sdo_X25,26,Sdo_X26,27,Sdo_X27,28,Sdo_X28,29,Sdo_X29,30,Sdo_X30,31,Sdo_X31,32,Sdo_X32,33,Sdo_X33,34,Sdo_X34,35,Sdo_X35,36,Sdo_X36,37,Sdo_X37,38,Sdo_X38,39,Sdo_X39,40,Sdo_X40,41,Sdo_X41,42,Sdo_X42,43,Sdo_X43,44,Sdo_X44,45,Sdo_X45,46,Sdo_X46,47,Sdo_X47,48,Sdo_X48,49,Sdo_X49,50,Sdo_X50,51,Sdo_X51,52,Sdo_X52,53,Sdo_X53,54,Sdo_X54,55,Sdo_X55,56,Sdo_X56,57,Sdo_X57,58,Sdo_X58,59,Sdo_X59,60,Sdo_X60,61,Sdo_X61,62,Sdo_X62,63,Sdo_X63,64,Sdo_X64) Is Not Null 
                                                            Order By at.Sdo_Seq,Coord.id,l.xy
                                                            ) As Mdsys.Sdo_Ordinate_Array)
                                               ),
                            0.05) As Geom 
                    from dual
                   )
 Where sdo_seq = 0;
 
 commit;

Mapping the geometry in GeoRaptor produces the following:

The SQL file containing all the unedited commands is available here

I hope this is of use to someone.