For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Hi All,
Unable to open the OML Notebooks in my Oracle Always Free Cloud account.
It Says "Notebook editor is not Available". Any pointers would help.
It was working fine last week.
Custom Aggregate functions are implemented via Oracle Data Cartridge Interface (ODCI). They're UDTs that have a specific set of functions. (ODCIAggregate) User-Defined Aggregate Functions Interface (0 Bytes)
Not exactly sure what you need:
with vertices as ( select '001' line_id,1 part_num,1 vertex_num,0 x,5 y,0 m from dual union all select '001',1,2,10,10,11.18 from dual union all select '001',1,3,30,0,33.54 from dual union all select '001',2,1,50,10,33.54 from dual union all select '001',2,2,60,10,43.54 from dual ), t1 as ( select line_id, part_num, vertex_num, x x_from, y y_from, count(*) over(partition by line_id,part_num) cnt, lead(x) over(partition by line_id,part_num order by vertex_num) x_to, lead(y) over(partition by line_id,part_num order by vertex_num) y_to from vertices ), t2 as ( select line_id, part_num, vertex_num, sdo_geometry( 2002, null, null, sdo_elem_info_array(1,2,1), sdo_ordinate_array(x_from,y_from,x_to,y_to) ) section from t1 where vertex_num < cnt ) select line_id, part_num, sdo_aggr_concat_lines(section) line from t2 group by line_id, part_num order by line_id, part_num / LINE_ID PART_NUM LINE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) ------- ---------- ----------------------------------------------------------------------------------------------------- 001 1 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 10, 10, 30, 0)) 001 2 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 60, 10)) SQL>
SY.
Another approach:
WITH vertices (line_id,part_num,vertex_num,x,y,m) AS (SELECT '001',1,1, 0, 5, 0 FROM dual UNION ALL SELECT '001',1,2,10,10,11.18 FROM dual UNION ALL SELECT '001',1,3,30, 0,33.54 FROM dual UNION ALL SELECT '001',2,1,50,10,33.54 FROM dual UNION ALL SELECT '001',2,2,60,10,43.54 FROM dual), t1 AS (SELECT x coord, line_id, part_num, vertex_num FROM vertices UNION ALL SELECT y , line_id, part_num, vertex_num FROM vertices) SELECT line_id, part_num , sdo_geometry(2002, NULL, NULL , sdo_elem_info_array(1,2,1) , CAST(collect (coord ORDER BY line_id, part_num, vertex_num) AS sdo_ordinate_array)) line FROM t1 GROUP BY line_id, part_num; LINE_ID PART_NUM LINE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) ------- ---------- ----------------------------------------------------------------------------------------------------- 001 1 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 10, 10, 30, 0)) 001 2 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 60, 10))
@jum3 Thanks! Does that approach assume that we'd need to do an additional step afterwards: merge the two single-part geometries into a multi-part geometry?
Yes, with an additional merge step, based on an adapted and extended (for M) approach of @SY or mine. The present draft was ready This collapse is certainly possible in one very smart step...
@_jum: Another approach: Careful, you need to weigh X & Y, othersise you get non-deterministic results and Y can end up first:
WITH vertices (line_id,part_num,vertex_num,x,y,m) AS (SELECT '001',1,1, 0, 5, 0 FROM dual UNION ALL SELECT '001',1,2,10,10,11.18 FROM dual UNION ALL SELECT '001',1,3,30, 0,33.54 FROM dual UNION ALL SELECT '001',2,1,50,10,33.54 FROM dual UNION ALL SELECT '001',2,2,60,10,43.54 FROM dual), t1 AS (SELECT x coord, line_id, part_num, vertex_num, 1 weight FROM vertices UNION ALL SELECT y , line_id, part_num, vertex_num,2 weight FROM vertices) SELECT line_id, part_num , sdo_geometry(2002, NULL, NULL , sdo_elem_info_array(1,2,1) , CAST(collect (coord ORDER BY line_id, part_num, vertex_num,weight) AS sdo_ordinate_array)) line FROM t1 GROUP BY line_id, part_num;
Of course, I need a break - Thanks @Solomon Yakobson!
@jum3 This collapse is certainly possible in one very smart step... What's the smart step for merging?
I honestly have no idea currently!
Last step could be:
SELECT sdo_geom.sdo_union( SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 11.18, 30, 0, 33.54)) , SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 33.54, 60, 10, 43.54)) , 0.01) ugeom FROM dual; UGEOM --------------------------------- MDSYS.SDO_GEOMETRY(3006, NULL, NULL , MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1) , MDSYS.SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 11.18, 30, 0, 33.54, 50, 10, 33.54, 60, 10, 43.54))
Isn't it just a matter of CONCAT them together?
WITH vertices (line_id,part_num,vertex_num,x,y,m) AS (SELECT '001',1,1, 0, 5, 0 FROM dual UNION ALL SELECT '001',1,2,10,10,11.18 FROM dual UNION ALL SELECT '001',1,3,30, 0,33.54 FROM dual UNION ALL SELECT '001',2,1,50,10,33.54 FROM dual UNION ALL SELECT '001',2,2,60,10,43.54 FROM dual), t1 AS (SELECT x coord, line_id, part_num, vertex_num, 1 weight FROM vertices UNION ALL SELECT y , line_id, part_num, vertex_num,2 weight FROM vertices UNION ALL SELECT m , line_id, part_num, vertex_num,3 weight FROM vertices ) , t2 as ( SELECT line_id, part_num , sdo_geometry(2002, NULL, NULL , sdo_elem_info_array(1,2,1) , CAST(collect (coord ORDER BY line_id, part_num, vertex_num,weight) AS sdo_ordinate_array)) line FROM t1 GROUP BY line_id, part_num ) select line_id , sdo_aggr_concat_lines(line) from t2 group by line_id ;