0

Oracle 18c:

In a related post, I demonstrated how to use the GetVertices() function and the Table() function to extract the vertices of an SDO_GEOMETRY line:

Select SDO_GEOMETRY line vertices as rows

select  
    a.line_id, b.id as vertex_id, b.x, b.y
from
    a_sdo_geometry_tbl a, 
    table(sdo_util.getvertices(a.shape)) b       --<<--Here

   LINE_ID  VERTEX_ID          X          Y
---------- ---------- ---------- ----------
         1          1 671539.685 4863324.18
         1          2 671595.050 4863343.17
         1          3 671614.014 4863350.34
         1          4 671622.204 4863353.53

         2          1 71534.5567 4863119.99
         2          2 671640.738 4863157.13
         2          3 671684.862 4863172.02

As a learning exercise, I want to create my own version of the GetVertices() function using PL/SQL.

I'm open to ideas about what sort of mechanism to use; any of the following would be fine:

  • Basic SQL
  • MODEL clause
  • Custom function

The point is, I want to learn how to extract the vertices and put them into a nested table column in a query, just like the GetVertices() function does.

SDO_UTIL.GETVERTICES

This function returns an object of MDSYS.VERTEX_SET_TYPE, which consists of a table of objects of MDSYS.VERTEX_TYPE. Oracle Spatial and Graph defines the type VERTEX_SET_TYPE as:

CREATE TYPE vertex_set_type as TABLE OF vertex_type;

Oracle Spatial and Graph defines the object type VERTEX_TYPE as:

CREATE TYPE vertex_type AS OBJECT
   (x   NUMBER,
    y   NUMBER,
    z   NUMBER,
    w   NUMBER,
    v5  NUMBER,
    v6  NUMBER,
    v7  NUMBER,
    v8  NUMBER,
    v9  NUMBER,
    v10 NUMBER,
    v11 NUMBER,
    id  NUMBER);

Note: The VERTEX_SET_TYPE and VERTEX_TYPE types are intended for use by Oracle only. Do not use these types in column definitions or functions that you create.


Question:

Is there a way to extract an SDO_GEOMETRY line’s vertices into a nested table column in a query -- without using the GetVertices() function? The output would be a query resultset, not a static table.


Hints:

The following functions might be useful:

PointN: Returns a point that is the nth vertex in the collection of vertices

GetNumVertices: Returns the number of vertices in the input geometry.

1

1 Answer 1

0

It seems like the only way to do this is by creating a custom type and a custom function:

CREATE TYPE vertex_type_cust AS object
(
  x  NUMBER,
  y  NUMBER,
  z  NUMBER,
  w  NUMBER,
  id NUMBER );

CREATE TYPE vertex_set_type_cust AS TABLE OF vertex_type_cust;

CREATE OR replace FUNCTION getvertices_cust(geometry mdsys.sdo_geometry)
  RETURN vertex_set_type_cust
IS
  i      NUMBER;
  dims   NUMBER;
  coords NUMBER;
  result vertex_set_type_cust;
  dim mdsys.sdo_dim_array;
  is_zero BOOLEAN;
  etype   NUMBER;
BEGIN
  result := vertex_set_type_cust();
  -- handle the POINT case here
  IF (geometry.sdo_ordinates IS NULL) THEN
    result.extend;
    result(1) := vertex_type_cust(geometry.sdo_point.x, geometry.sdo_point.y, geometry.sdo_point.z,NULL,1);
    RETURN result;
  END IF;
  -- all other cases here
  coords := geometry.sdo_ordinates.count;
  dims := geometry.get_dims;
  IF (dims = 0) THEN
    RETURN result;
  END IF;
  coords := coords/dims;
  FOR i          IN 0 .. coords-1
  LOOP
    result.extend;
    IF (dims = 2) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(2*i+1), geometry.sdo_ordinates(2*i+2), NULL,NULL,i+1);
    ELSIF (dims = 3) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(3*i+1), geometry.sdo_ordinates(3*i+2), geometry.sdo_ordinates(3*i+3) ,NULL,i+1);
    ELSIF (dims = 4) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(4*i+1), geometry.sdo_ordinates(4*i+2), geometry.sdo_ordinates(4*i+3), geometry.sdo_ordinates(4*i+4), i+1);
    END IF;
  END LOOP;
  RETURN result;
END;

Test data:

create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry);

insert into a_sdo_geometry_tbl (line_id, shape) 
values (1, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671539.6852734378,4863324.181436138, 671595.0500703361,4863343.166556185, 671614.013553706,4863350.343483042, 671622.2044153381,4863353.525396131))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (2, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (71534.5567096211,4863119.991809748, 671640.7384688659,4863157.132745253, 671684.8621150404,4863172.022995591))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (3, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671622.2044153381,4863353.525396131, 671633.3267164109,4863357.846229106, 671904.0614077691,4863451.286166754))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (4, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671684.8620521119,4863172.022995591, 671892.1496144319,4863244.141440067, 671951.2156571196,4863264.824310392, 671957.4471461186,4863266.847617676, 671966.8243856924,4863269.146632658))  )

Use the custom function:

select
    a.line_id,
    b.id as vertex_id,
    b.x, 
    b.y
from
    a_sdo_geometry_tbl a
cross join
    table(getvertices_cust(a.shape)) b  
order by 
    a.line_id, b.id;

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.