1 x1 y1
1 x2 y2
1 x3 y3
1 x4 y4
1 x5 y5
2 x1 y1
2 x2 y2
2 x3 y3
With points As (
SELECT trunc(level / 5) as geom_id,
row_number() over (partition by trunc(level / 5) order by rownum) as point_id,
round(dbms_random.value(1,10000),3) as X,
round(dbms_random.value(1,10000),3) as Y
FROM dual
CONNECT BY LEVEL < 30
)
SELECT mdsys.sdo_geometry(2002,NULL,NULL,mdsys.sdo_elem_info_array(1,2,1),CAST(MULTISET(SELECT b.COLUMN_VALUE FROM points a,table(mdsys.sdo_ordinate_array(a.x,a.y)) b where a.geom_id = c.geom_id order by a.point_id) as mdsys.sdo_ordinate_array)) as geom
FROM points c
GROUP BY c.geom_id;
-- Results
--
GEOM
----
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(5144.458,3199.919,2251.939,6849.938,6919.348,1134.661,565.804,4384.515,1628.731,6092.055))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(122.862,3006.545,9581.443,9379.387,7659.869,6859.322,7936.498,151.507,926.478,4598.965))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(6373.324,759.294,44.012,9814.083,8125.855,612.249,2699.456,8318.277,1715,7349.652))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(8923.524,6996.764,6518.515,3501.614,6053.453,8277.119,7652.991,9403.059,8728.289,9505.907))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(6828.892,7137.751,3420.856,8590.158,1346.581,6701.412,9105.74,951.904,951.429,1062.246))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1467.562,8577.782,8580.429,8866.923,3676.022,6519.497,8639.309,5164.398))
Isn't SQL grand!SELECT mdsys.sdo_geometry(2002,NULL,NULL,mdsys.sdo_elem_info_array(1,2,1),CAST(MULTISET(SELECT b.COLUMN_VALUE FROM rawpoints a,table(mdsys.sdo_ordinate_array(a.x,a.y)) b where a.pointid = c.pointid order by a.pointid) as mdsys.sdo_ordinate_array)) as geom
FROM linepoints c
GROUP BY c.feature_id;
Note: For those of us who endeavour to help posters on this forum it does help to have properly defined data sources.create table linepoints as
SELECT case when trunc(level / 7) = 0 then 398639 else 2215595 end as feature_id,
row_number() over (partition by 1 order by rownum) as point_id,
sdo_geometry(2001,null,
sdo_point_type(round(dbms_random.value(1,10000),3),
round(dbms_random.value(1,10000),3),
NULL),NULL,NULL) as pointGeom
FROM dual
CONNECT BY LEVEL < 13;
-- Result
table LINEPOINTS created.
This is what linepoints contains:select * from linepoints;
-- Results
FEATURE_ID POINT_ID POINTGEOM
---------- -------- ------------------------------------------------------------------------------------
398639 1 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(512.505,9909.19,NULL),NULL,NULL)
398639 2 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(701.488,8150.579,NULL),NULL,NULL)
398639 3 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(753.581,2449.058,NULL),NULL,NULL)
398639 4 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(7160.47,6238.475,NULL),NULL,NULL)
398639 5 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(4550.681,1422.242,NULL),NULL,NULL)
398639 6 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1972.044,6790.043,NULL),NULL,NULL)
2215595 7 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1204.709,1973.255,NULL),NULL,NULL)
2215595 8 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(7485.635,2439.01,NULL),NULL,NULL)
2215595 9 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(5005.396,5022.274,NULL),NULL,NULL)
2215595 10 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1180.856,6256.96,NULL),NULL,NULL)
2215595 11 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(6425.442,7360.927,NULL),NULL,NULL)
2215595 12 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(4200.348,5369.366,NULL),NULL,NULL)
12 rows selected
Secondly, given this table linepoints here is how I would create the linear (2002) sdo_geometry objects:SELECT c.feature_id,
mdsys.sdo_geometry(2002,NULL,NULL,
mdsys.sdo_elem_info_array(1,2,1),
CAST(MULTISET(SELECT b.COLUMN_VALUE FROM linepoints a,table(mdsys.sdo_ordinate_array(a.pointGeom.sdo_point.x,a.pointGeom.sdo_point.y)) b where a.feature_id = c.feature_id order by a.point_id) as mdsys.sdo_ordinate_array))
as geom
FROM linepoints c
GROUP BY c.feature_id;
-- Results
FEATURE_ID GEOM
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2215595 MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1204.709,1973.255,7485.635,2439.01,5005.396,5022.274,1180.856,6256.96,6425.442,7360.927,4200.348,5369.366))
398639 MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(512.505,9909.19,701.488,8150.579,753.581,2449.058,7160.47,6238.475,4550.681,1422.242,1972.044,6790.043))
If this is accurate and gives you the result you want, please mark my answer as being correct.(1) In your SQL, are you casting SDO_POINT as SDO_ORDINATE_ARRAY ??No, I am casting the X and Y ordinates of the SDO_POINT type as an SDO_ORDINATE_ARRAY so that the CAST(COLLECT()) operates on SDO_ORDINATE_ARRAYs and "appends" them together.
(2) Can I use sdo_util.getvertices and convert as SDO_ORDINATE_ARRAY ??Yes, of course you can. Here is the last SELECT in my answer redone...
SELECT c.feature_id,
mdsys.sdo_geometry(2002,NULL,NULL,
mdsys.sdo_elem_info_array(1,2,1),
CAST(MULTISET(SELECT b.COLUMN_VALUE
FROM linepoints a,
table(sdo_util.getVertices(a.pointGeom)) v,
table(mdsys.sdo_ordinate_array(v.x,v.y)) b
WHERE a.feature_id = c.feature_id
ORDER BY a.point_id)
as mdsys.sdo_ordinate_array))
as geom
FROM linepoints c
GROUP BY c.feature_id;
-- Results
FEATURE_ID GEOM
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2215595 MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1204.709,1973.255,7485.635,2439.01,5005.396,5022.274,1180.856,6256.96,6425.442,7360.927,4200.348,5369.366))
398639 MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(512.505,9909.19,701.488,8150.579,753.581,2449.058,7160.47,6238.475,4550.681,1422.242,1972.044,6790.043))
HTH