Link to home
Start Free TrialLog in
Avatar of IVL
IVL

asked on

Return MBR result as 5 point polygon in spatial Oracle 12.1 database with 3D objects

We need an MBR, but it needs to be returned as a 5 point polygon.

Test sample :

with OrientedPoint as
   (
      SELECT 1 AS OrientedPoint, sdo_geometry(3001,31370,NULL,sdo_elem_info_array(1,1,1,4,1,0),sdo_ordinate_array(1,11,0,1,0,0))  AS geom FROM dual union all
      SELECT 2 AS OrientedPoint, sdo_geometry(3001,31370,NULL,sdo_elem_info_array(1,1,1,4,1,0),sdo_ordinate_array(2,12,0,1,0,0))  AS geom FROM dual union all
      SELECT 3 AS OrientedPoint, sdo_geometry(3001,31370,NULL,sdo_elem_info_array(1,1,1,4,1,0),sdo_ordinate_array(3,13,0,1,0,0))  AS geom FROM dual union all
      SELECT 4 AS OrientedPoint, sdo_geometry(3001,31370,NULL,sdo_elem_info_array(1,1,1,4,1,0),sdo_ordinate_array(4,14,0,1,0,0))  AS geom FROM dual union all
      SELECT 5 AS OrientedPoint, sdo_geometry(3001,31370,NULL,sdo_elem_info_array(1,1,1,4,1,0),sdo_ordinate_array(5,15,0,1,0,0))  AS geom FROM dual union all
      SELECT 6 AS OrientedPoint, sdo_geometry(3001,31370,NULL,sdo_elem_info_array(1,1,1,4,1,0),sdo_ordinate_array(11,21,0,1,0,0)) AS geom FROM dual union all
      SELECT 7 AS OrientedPoint, sdo_geometry(3001,31370,NULL,sdo_elem_info_array(1,1,1,4,1,0),sdo_ordinate_array(11,31,0,1,0,0)) AS geom FROM dual union all
      SELECT 8 AS OrientedPoint, sdo_geometry(3001,31370,NULL,sdo_elem_info_array(1,1,1,4,1,0),sdo_ordinate_array(15,21,0,1,0,0)) AS geom FROM dual union all
      SELECT 9 AS OrientedPoint, sdo_geometry(3001,31370,NULL,sdo_elem_info_array(1,1,1,4,1,0),sdo_ordinate_array(15,31,0,1,0,0)) AS geom FROM dual
   ),
   
     Area as
   (
      SELECT 1 AS Area, 'Area 1' AS name FROM dual union all
      SELECT 2 AS Area, 'Area 3' AS name FROM dual union all
      SELECT 3 AS Area, 'Area 3' AS name FROM dual
   ),

     Rel as
   (
      SELECT 1 AS id, 2 AS OrientedPoint, 1 as Area FROM dual union all
      SELECT 2 AS id, 3 AS OrientedPoint, 1 as Area FROM dual union all
      SELECT 3 AS id, 4 AS OrientedPoint, 2 as Area FROM dual union all
      SELECT 4 AS id, 5 AS OrientedPoint, 2 as Area FROM dual union all
      SELECT 5 AS id, 6 AS OrientedPoint, 3 as Area FROM dual union all
      SELECT 6 AS id, 7 AS OrientedPoint, 3 as Area FROM dual union all
      SELECT 7 AS id, 8 AS OrientedPoint, 3 as Area FROM dual union all
      SELECT 8 AS id, 9 AS OrientedPoint, 3 as Area FROM dual
   )
   
   
select
   a.Area,
   SDO_AGGR_MBR(SDO_UTIL.EXTRACT(p.geom, 1)) geom
from
   Area a
   inner join Rel r             on r.Area          = a.Area
   inner join OrientedPoint p   on p.OrientedPoint = r.OrientedPoint
where
   a.Area = 3
group by
   a.Area;


The result returned looks like this :
   SELECT 3 AS Area, sdo_geometry(3002, 31370, NULL, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,21,0, 15,31,0))  AS geom FROM dual;

But we need it to look like this :
   SELECT 3 AS Area, sdo_geometry(3003, 31370, NULL, sdo_elem_info_array(1, 1003, 1), sdo_ordinate_array(15,21,0, 15,31,0, 11,31,0, 11,21,0, 15,21,0))  AS geom FROM dual;

What's the best way to achieve thos in Oracle 12.1

Regards,
Ivan
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial