2

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:

0

1 Answer 1

2
+100

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.
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.