Select vertices, including multi-part number

Question

I have an Oracle 18c table with an SDO_GEOEMTRY column:

create table a_test_sdo_geom (shape mdsys.sdo_geometry);

The table has a row that is a multi-part feature.

insert into a_test_sdo_geom (shape)
    values(sdo_util.from_wktgeometry (
    'MULTILINESTRING ((671834.096 4861699.7127, 671836.5099 4861701.9158),'||  --part 1
                     '(671838.2206 4861700.7607, 671842.2311 4861703.3157))'   --part 2
    ));
commit;

---

Get Vertices:

I can successfully select the individual vertices as rows using the GET_VERTICES() function:

select
    v.id as vertex_num,
    v.x,
    v.y
from
    a_test_sdo_geom a
    ,table(sdo_util.getvertices(a.shape)) v

VERTEX_NUM          X          Y
---------- ---------- ----------
         1 671834.096 4861699.71
         2 671836.510 4861701.92
         3 671838.221 4861700.76
         4 671842.231 4861703.32

---

Question:

When selecting the individual vertices, is there a way to also select the feature part number?

Example:

VERTEX_NUM          X          Y   PART_NUM
---------- ---------- ---------- ----------
         1 671834.096 4861699.71          1
         2 671836.510 4861701.92          1
         3 671838.221 4861700.76          2
         4 671842.231 4861703.32          2

---

[Edit] Related:

• Multi-part geometries: Get line & part number when extracting parts (via SDO_UTIL.EXTRACT_ALL)
• Generate rows from string of numbers
• A SDE.ST_GEOMETRY solution: Select feature parts and vertices as rows

Answer 1

Unfortunately, there is no function to do that out of the box. To achieve this, you need to write your own function. Luckily, it is pretty simple:

create or replace package split_geometry is
  type geometry_part is record (
    part_id    number,
    part_geom  sdo_geometry
  );
  type geometry_parts is table of geometry_part;
  function get_parts (g sdo_geometry) return geometry_parts pipelined;
end split_geometry;
/
show errors

create or replace package body split_geometry is
  function get_parts (g sdo_geometry) return geometry_parts
  pipelined
  as
  begin
    for i in 1..sdo_util.getnumelem(g) loop
      pipe row (
        geometry_part (
          i,
          sdo_util.extract(g,i)
        )
      );
    end loop;
    return;
  end;
end split_geometry;
/
show errors

That function splits a geometry into its parts. It loops over the elements (= parts), and uses built-in function sdo_util.extract() to extract each part as a separate geometry. Then the next step is to further split each of the parts into its constituent vertices.

Here is the example table for input. I used your example, but expanded it to include multiple shapes to make it more generic:

drop table t1 purge;
create table t1 (id number, shape sdo_geometry);
insert into t1 (id, shape)
values(
  1, 
  sdo_util.from_wktgeometry (
    'MULTILINESTRING ((671834.096 4861699.7127, 671836.5099 4861701.9158),(671838.2206 4861700.7607, 671842.2311 4861703.3157))'
  )
);
insert into t1 (id, shape)
values(
  2, 
  sdo_util.from_wktgeometry (
    'MULTILINESTRING ((671842.2311 4861703.3157, 671838.2206 4861700.7607), (671836.5099 4861701.9158, 671834.096 4861699.7127))'
  )
);
commit;

This splits all the shapes into their parts:

SQL> select g.id as feature_id, p.part_id, p.part_geom
  2  from t1 g, table(split_geometry.get_parts((g.shape))) p;

FEATURE_ID    PART_ID PART_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
---------- ---------- -------------------------------------------------------------------------------------------------------------
         1          1 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(671834.096, 4861699.71, 671836.51, 4861701.92))
         1          2 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(671838.221, 4861700.76, 671842.231, 4861703.32))
         2          1 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(671842.231, 4861703.32, 671838.221, 4861700.76))
         2          2 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(671836.51, 4861701.92, 671834.096, 4861699.71))

4 rows selected.

And finally, this splits them into the individual vertices for each part:

SQL> select g.id as feature_id, p.part_id, v.id as vertex_num, v.x, v.y
  2  from t1 g, table(split_geometry.get_parts((g.shape))) p, table(sdo_util.getvertices(p.part_geom)) v
  3  order by feature_id, part_id, vertex_num;

FEATURE_ID    PART_ID VERTEX_NUM          X          Y
---------- ---------- ---------- ---------- ----------
         1          1          1 671834.096 4861699.71
         1          1          2  671836.51 4861701.92
         1          2          1 671838.221 4861700.76
         1          2          2 671842.231 4861703.32
         2          1          1 671842.231 4861703.32
         2          1          2 671838.221 4861700.76
         2          2          1  671836.51 4861701.92
         2          2          2 671834.096 4861699.71

8 rows selected.

Notice that I added an ORDER BY to see the results in the order of feature id, part id and vertex id.

To select the results for just one shape, add a filter to just select that shape:

SQL> select g.id as feature_id, p.part_id, v.id as vertex_num, v.x, v.y
  2  from t1 g, table(split_geometry.get_parts((g.shape))) p, table(sdo_util.getvertices(p.part_geom)) v
  3  where g.id = 2
  4  order by part_id, vertex_num;

FEATURE_ID    PART_ID VERTEX_NUM          X          Y
---------- ---------- ---------- ---------- ----------
         2          1          1 671842.231 4861703.32
         2          1          2 671838.221 4861700.76
         2          2          1  671836.51 4861701.92
         2          2          2 671834.096 4861699.71

4 rows selected.