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,NU LL,sdo_ele m_info_arr ay(1,1,1,4 ,1,0),sdo_ ordinate_a rray(1,11, 0,1,0,0)) AS geom FROM dual union all
SELECT 2 AS OrientedPoint, sdo_geometry(3001,31370,NU LL,sdo_ele m_info_arr ay(1,1,1,4 ,1,0),sdo_ ordinate_a rray(2,12, 0,1,0,0)) AS geom FROM dual union all
SELECT 3 AS OrientedPoint, sdo_geometry(3001,31370,NU LL,sdo_ele m_info_arr ay(1,1,1,4 ,1,0),sdo_ ordinate_a rray(3,13, 0,1,0,0)) AS geom FROM dual union all
SELECT 4 AS OrientedPoint, sdo_geometry(3001,31370,NU LL,sdo_ele m_info_arr ay(1,1,1,4 ,1,0),sdo_ ordinate_a rray(4,14, 0,1,0,0)) AS geom FROM dual union all
SELECT 5 AS OrientedPoint, sdo_geometry(3001,31370,NU LL,sdo_ele m_info_arr ay(1,1,1,4 ,1,0),sdo_ ordinate_a rray(5,15, 0,1,0,0)) AS geom FROM dual union all
SELECT 6 AS OrientedPoint, sdo_geometry(3001,31370,NU LL,sdo_ele m_info_arr ay(1,1,1,4 ,1,0),sdo_ ordinate_a rray(11,21 ,0,1,0,0)) AS geom FROM dual union all
SELECT 7 AS OrientedPoint, sdo_geometry(3001,31370,NU LL,sdo_ele m_info_arr ay(1,1,1,4 ,1,0),sdo_ ordinate_a rray(11,31 ,0,1,0,0)) AS geom FROM dual union all
SELECT 8 AS OrientedPoint, sdo_geometry(3001,31370,NU LL,sdo_ele m_info_arr ay(1,1,1,4 ,1,0),sdo_ ordinate_a rray(15,21 ,0,1,0,0)) AS geom FROM dual union all
SELECT 9 AS OrientedPoint, sdo_geometry(3001,31370,NU LL,sdo_ele m_info_arr ay(1,1,1,4 ,1,0),sdo_ ordinate_a rray(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.EXTR ACT(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
Test sample :
with OrientedPoint as
(
SELECT 1 AS OrientedPoint, sdo_geometry(3001,31370,NU
SELECT 2 AS OrientedPoint, sdo_geometry(3001,31370,NU
SELECT 3 AS OrientedPoint, sdo_geometry(3001,31370,NU
SELECT 4 AS OrientedPoint, sdo_geometry(3001,31370,NU
SELECT 5 AS OrientedPoint, sdo_geometry(3001,31370,NU
SELECT 6 AS OrientedPoint, sdo_geometry(3001,31370,NU
SELECT 7 AS OrientedPoint, sdo_geometry(3001,31370,NU
SELECT 8 AS OrientedPoint, sdo_geometry(3001,31370,NU
SELECT 9 AS OrientedPoint, sdo_geometry(3001,31370,NU
),
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.EXTR
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
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
What's the best way to achieve thos in Oracle 12.1
Regards,
Ivan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.