Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table vertices (
    asset_id    varchar2(10),
    part_num    number,
    vertex_num  number,
    x           number,
    y           number,
    m           number);
insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',1, 1, 0,   5, 0);
1 rows affected

insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',1, 2, 10, 10, 11.18);
1 rows affected

insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',1, 3, 30,  0, 33.54);
1 rows affected

insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',2, 1, 50, 10, 33.54);
1 rows affected

insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',2, 2, 60, 10, 43.54);
1 rows affected

select * from vertices


ASSET_ID
PART_NUM
VERTEX_NUM
X
Y
M


001
1
1
0
5
0

001
1
2
10
10
11.18

001
1
3
30
0
33.54

001
2
1
50
10
33.54

001
2
2
60
10
43.54


CREATE TYPE PointLRS AS OBJECT(
  X NUMBER,
  Y NUMBER,
  M NUMBER
);
CREATE TYPE Line3DAggType AS OBJECT(
  ordinates SDO_ORDINATE_ARRAY,

  STATIC FUNCTION ODCIAggregateInitialize(
    ctx  IN OUT Line3DAggType
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self  IN OUT Line3DAggType,
    point IN     PointLRS
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT Line3DAggType,
    returnValue    OUT SDO_GEOMETRY,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT Line3DAggType,
    ctx         IN OUT Line3DAggType
  ) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY Line3DAggType
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT Line3DAggType
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := Line3DAggType( SDO_ORDINATE_ARRAY() );
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self  IN OUT Line3DAggType,
    point IN     PointLRS
  ) RETURN NUMBER
  IS
  BEGIN
    IF     point IS NOT NULL
       AND point.X IS NOT NULL
       AND point.Y IS NOT NULL
       AND point.M IS NOT NULL
    THEN
      self.ordinates.EXTEND(3);
      self.ordinates(self.ordinates.COUNT - 2) := point.X;
      self.ordinates(self.ordinates.COUNT - 1) := point.Y;
      self.ordinates(self.ordinates.COUNT - 0) := point.M;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT Line3DAggType,
    returnValue    OUT SDO_GEOMETRY,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    IF self.ordinates.COUNT > 0 THEN
      returnValue := SDO_GEOMETRY(
        3302,
        NULL,
        NULL, 
        SDO_ELEM_INFO_ARRAY(1,2,1),
        self.ordinates
      );
    ELSE
      returnValue := NULL;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT Line3DAggType,
    ctx         IN OUT Line3DAggType
  ) RETURN NUMBER
  IS
  BEGIN
    FOR i IN 1 .. ctx.ordinates.COUNT LOOP
      self.ordinates.EXTEND;
      self.ordinates(self.ordinates.COUNT) := ctx.ordinates(i);
    END LOOP;
    RETURN ODCIConst.SUCCESS;
  END;
END;
/
CREATE FUNCTION Line3DAgg( point PointLRS )
RETURN SDO_GEOMETRY
PARALLEL_ENABLE AGGREGATE USING Line3DAggType;
/
SELECT * FROM USER_ERRORS;
SELECT asset_id,
       JSON_OBJECT(
         SDO_AGGR_LRS_CONCAT(SDOAGGRTYPE(part, 0.005))
       ) AS geom
FROM   (
  SELECT asset_id,
         part_num,
         Line3DAgg(PointLRS(x, y, m)) AS part
  FROM   vertices
  GROUP BY asset_id, part_num
)
GROUP BY asset_id


ASSET_ID
GEOM


001
{"SDO_GTYPE":3306,"SDO_SRID":null,"SDO_POINT":{},"SDO_ELEM_INFO":[1,2,1,10,2,1],"SDO_ORDINATES":[0,5,0,30,0,33.54,10,10,11.18,50,10,33.54,60,10,43.54]}


SELECT asset_id,
       SDO_AGGR_LRS_CONCAT(SDOAGGRTYPE(part, 0.005)) AS geom
FROM   (
  SELECT asset_id,
         part_num,
         Line3DAgg(PointLRS(x, y, m)) AS part
  FROM   vertices
  GROUP BY asset_id, part_num
)
GROUP BY asset_id
ASSET_ID	PART_NUM	VERTEX_NUM	X	Y	M
001	1	1	0	5	0
001	1	2	10	10	11.18
001	1	3	30	0	33.54
001	2	1	50	10	33.54
001	2	2	60	10	43.54