How can I return x1, y1, x2, y2 from SDO_GEOM.SDO_MBR in Oracle?

Question

I have the requirement to return the MBR of a geometry column in the form of four fields in a record. I need x1, y1, x2, y2. How can this be achieved? Here is my current statement with select rows removed for clarity. I want to return the x1, y1, x2, y2 of the MBR of the A.CELL_GEOM field.

select SDO_GEOM.SDO_MBR(A.CELL_GEOM) AS CELL_GEOM_VERTS
from (SELECT CLUSTER_ID, 
      NUM_POINTS,
      FEATURE_PK, 
      SDO_CS.transform (CLUSTER_CENTROID, 4326) cluster_centroid,  
      SDO_CS.TRANSFORM(NVL(CLUSTER_EXTENT, MDSYS.SDO_GEOMETRY(2001,4326 ,MDSYS.SDO_POINT_TYPE(-999,-999,NULL),NULL,NULL)),4326) CLUSTER_EXTENT, 
      SDO_CS.transform (CELL_CENTROID, 4326) cell_centroid,
      SDO_CS.TRANSFORM(NVL(CELL_GEOM, MDSYS.SDO_GEOMETRY(2001,4326 ,MDSYS.SDO_POINT_TYPE(-999,-999,NULL),NULL,NULL)),4326) CELL_GEOM, 
      CELL_GEOM CG FROM V_CLUSTER_1000M) a 
LEFT JOIN RWOL_TMA_ROADWORKS 
ON a.FEATURE_PK = RWOL_TMA_ROADWORKS.TPHS_PHASE_ID   
where  sdo_filter(A.CG, SDO_CS.transform(mdsys.sdo_geometry(2003, 4326, NULL, mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(-25.43623984375,44.257784519021, 21.62918984375, 60.752403080295)),81989)) = 'TRUE'

EDIT: It is worth noting this must be done in a single statement.

Answer 1

You can do it manually by selecting the sdo_ordinates as a nested table, or use the sdo_util.getvertices.. but that doesn't really gain you much.

with mbr as 
(select rownum coord_seq, column_value coord from table (select sdo_aggr_mbr(shape).sdo_ordinates 
  from <table_name>)) 
 select (select coord x1 from mbr where coord_seq = 1) x1
      , (select coord y1 from mbr where coord_seq = 2) y1
      , (select coord x2 from mbr where coord_seq = 3) x2
      , (select coord y2 from mbr where coord_seq = 4) y2
   from dual;

Obviously you'd need to adapt this to incorporate your SQL.

This is not very pretty - I'm sure there are better ways of doing it. Certainly in PL/SQL or create a database view/MV for the best performance.