Custom aggregate function to collapse vertices to SDO_GEOMETRY

Question

I have multi-part polyline vertices stored as individual rows in an Oracle 18c table.

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

DDL db<>fiddle

CTE db<>fiddle

I want to convert the vertices to a multi-part SDO_GEOMETRY polyline (collapsed into a single row).

---

I've tried a few different ways of doing that (i.e. listagg and PL/SQL block). Additionally, as a learning exercise, I would also like to explore creating a custom aggregate function as a solution.

It might look like this:

select
    asset_id,
    sdo_geometry(partition by id, part num, vertex order, x, y, m, gtype, srid) as sdo_geom
from
    vertices
group by
    asset_id 

Output:
ASSET_ID: 001
SDO_GEOM: SDO_GEOMETRY(3306, 26917, 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))

--SDO_GEOMETRY docs: https://docs.oracle.com/en/database/oracle/oracle-database/19/spatl/spatial-datatypes-metadata.html
--Info about multi-part lines: https://community.oracle.com/tech/apps-infra/discussion/4497547/sdo-geometry-output-how-to-know-if-geometry-is-multi-part

---

Is there a way to create a custom aggregate function to do that?

Answer 1

Create a type to store the point:

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

Then create a user-defined aggregation type:

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;
/

Then define a custom aggregation function:

CREATE FUNCTION Line3DAgg( point PointLRS )
RETURN SDO_GEOMETRY
PARALLEL_ENABLE AGGREGATE USING Line3DAggType;
/

Then you can aggregate the points for each part into a line and then concatenate the lines:

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

db<>fiddle here