自定义聚合函数将顶点折叠为SDO_GEOMETRY

Question

我在Oracle 18c表中将多部分的polyline顶点存储为单独的行。

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

​

​

我想将顶点转换为多部分的SDO_GEOMETRY polyline (折叠成一行)。

我尝试过几种不同的方法(即利斯塔格和PL/SQL块)。此外，作为一个学习练习，我还想探索如何创建一个自定义聚合函数作为解决方案。

看起来可能是这样的：

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

是否有一种方法可以创建一个自定义聚合函数来完成这个任务？

Answer 1

创建一个类型来存储点：

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 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 https://dbfiddle.uk/?rdbms=oracle_21&fiddle=b331dbef54e29a1fff612a6fe67f3620

Answer 2

这将构建单独的行字符串。

with cte as (
select 001 as asset_id, 1 as part_num,1 as vertex_num,0  as x,5 as y, 0 as m from dual union all
select 001 as asset_id, 1 as part_num,2 as vertex_num,10 as x,10 as y,11.18 as m from dual union all
select 001 as asset_id, 1 as part_num,3 as vertex_num,30 as x,0 as y, 33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,1 as vertex_num,50 as x,10 as y,33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,2 as vertex_num,60 as x,10 as y,43.54 as m from dual 
)
SELECT asset_id,
       part_num,
       mdsys.sdo_geometry(
             3302,
             null,
             null,
             mdsys.sdo_elem_info_array(1,2,1),
             CAST(MULTISET( select case when r.rin = 1 then x
                                        when r.rin = 2 then y
                                        when r.rin = 3 then m
                                    end
                              from cte b,
                                   (select level rin from dual connect by level < 4) r
                            where b.asset_id = a.asset_id
                              and b.part_num = a.part_num
                            order by b.vertex_num, r.rin
                          ) as mdsys.sdo_ordinate_array 
            ) 
        ) as geom
  from cte a
  group by asset_id, part_num
  order by part_num;

请注意如何使用多集操作符将X、Y和M坐标“序列化”到数组(类型为mdsys.sdo_ordinate_array)中。结果是：

  ASSET_ID   PART_NUM GEOM
---------- ---------- ----
         1          1 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))
         1          2 SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 33.54, 60, 10, 43.54))

创建多行字符串涉及使用asset_id属性聚合行字符串。

with cte as (
select 001 as asset_id, 1 as part_num,1 as vertex_num,0  as x,5 as y, 0 as m from dual union all
select 001 as asset_id, 1 as part_num,2 as vertex_num,10 as x,10 as y,11.18 as m from dual union all
select 001 as asset_id, 1 as part_num,3 as vertex_num,30 as x,0 as y, 33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,1 as vertex_num,50 as x,10 as y,33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,2 as vertex_num,60 as x,10 as y,43.54 as m from dual 
)
SELECT asset_id,
       SDO_AGGR_UNION(SDOAGGRTYPE(geom,0.005)) as mGeom
  FROM (SELECT asset_id,
               part_num,
               mdsys.sdo_geometry(
                     3302,
                     null,
                     null,
                     mdsys.sdo_elem_info_array(1,2,1),
                     CAST(MULTISET( select case when r.rin = 1 then x
                                                when r.rin = 2 then y
                                                when r.rin = 3 then m
                                            end
                                      from cte b,
                                           (select level rin from dual connect by level < 4) r
                                    where b.asset_id = a.asset_id
                                      and b.part_num = a.part_num
                                    order by b.vertex_num, r.rin
                                  ) as mdsys.sdo_ordinate_array 
                    ) 
                ) as geom
          from cte a
          group by asset_id, part_num
          order by part_num
        ) f
  GROUP BY asset_id;

结果：

  ASSET_ID MGEOM
---------- -----
         1 SDO_GEOMETRY(3006, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 11.18, 30, 0, 33.54, 50, 10, 33.54, 60, 10, 43.54))

还请参阅我的文章“从GPX数据构建行字符串：https://www.spdba.com.au/loading-and-processing-gpx-1-1-files-using-oracle-xmldb-2/”

Answer 3

您可以将其连接到多行的部件字符串中，然后从该字符串生成SDO_GEOMETRY：

SELECT asset_id,
       SDO_GEOMETRY(
         'MULTILINESTRING (' || LISTAGG(part, ',') WITHIN GROUP (ORDER BY part_num) || ')'
       ) AS geom
FROM   (
  SELECT asset_id,
         part_num,
         '(' || LISTAGG(x || ' ' || y || ' ' || m, ',') WITHIN GROUP (ORDER BY vertex_num) || ')'
           AS part
  FROM   vertices
  GROUP BY asset_id, part_num
)
GROUP BY asset_id

db<>fiddle https://dbfiddle.uk/?rdbms=oracle_21&fiddle=111f53be928101987cddf82f2a9aa961