Projecting SDO_GEOM_METADATA DIMINFO XY ordinates

Just had need to turn project only the 2D (X/Y) ordinates of a DIMINFO value from USER_SDO_GEOM_METADATA into a project different from its current value.

The original values are:

 SELECT b.srid,b.minx,b.miny,b.maxx,b.maxy
   FROM (SELECT a.rin, a.srid,
                 case when a.rin = 1 then a.sdo_lb else null end as minx,
                 case when a.rin = 1 then LEAD(a.sdo_lb,1) OVER (ORDER BY a.RIN) else null end as miny,
                 case when a.rin = 1 then a.sdo_ub else null end as maxx,
                 case when a.rin = 1 then LEAD(a.sdo_ub,1) OVER (ORDER BY a.RIN) else null end as maxy
          FROM ( SELECT rownum as rin, asgm.srid, dim.*
                   FROM all_sdo_geom_metadata asgm,
                        TABLE(asgm.diminfo) dim
                  WHERE asgm.owner       = UPPER(NVL(NULL,SYS_CONTEXT('USERENV','SESSION_USER'))) 
                    AND asgm.table_name  = UPPER('WW_LINE') 
                    AND asgm.column_name = UPPER('GEOM') ) a
         ) b
  WHERE b.rin = 1;
 
 SRID  MINX   MINY    MAXX   MAXY
 ----- ------ ------- ------ -------
 82469 325000 6370000 498600 6550000

Here’s the SQL I used to do it:

 SELECT SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.GEOM,1) as minx,
         SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.GEOM,2) as miny,
         SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.GEOM,1) as minx,
         SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.GEOM,2) as miny
   FROM (SELECT SDO_CS.TRANSFORM(SDO_GEOMETRY(2003,b.srid,null,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(b.minx,b.miny,b.maxx,b.maxy)),41014) as geom
           FROM (SELECT a.rin, a.srid,
                         case when a.rin = 1 then a.sdo_lb else null end as minx,
                         case when a.rin = 1 then LEAD(a.sdo_lb,1) OVER (ORDER BY a.RIN) else null end as miny,
                         case when a.rin = 1 then a.sdo_ub else null end as maxx,
                         case when a.rin = 1 then LEAD(a.sdo_ub,1) OVER (ORDER BY a.RIN) else null end as maxy
                  FROM ( SELECT rownum as rin, asgm.srid, dim.*
                           FROM all_sdo_geom_metadata asgm,
                                TABLE(asgm.diminfo) dim
                          WHERE asgm.owner       = UPPER(NVL(NULL,SYS_CONTEXT('USERENV','SESSION_USER'))) 
                            AND asgm.table_name  = UPPER('WW_LINE') 
                            AND asgm.column_name = UPPER('GEOM') ) a
                ) b
          WHERE b.rin = 1
       ) c;
 
 MINX             MINY            MINX             MINY
 ---------------- --------------- ---------------- ----------------
 17341585.1098337 13841876.460748 17889863.1143863 14380561.3860746

Hope that helps someone out there.

(A lot of these ideas is being wrapped into GeoRaptor. Download and try it.)