1

I'm using Oracle 12.1.0.2.0 and I am getting extrange results using sdo_util.getvertices.

This query returns 2 "vertex" instead of one (as expected)

 SELECT T.x, T.y
    FROM TABLE(
      SDO_UTIL.GETVERTICES(
        MDSYS.SDO_GEOMETRY(
          2001,23030,NULL,
          MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1,3,1,0),
          MDSYS.SDO_ORDINATE_ARRAY(662301.48,4203898.34,1,0)
        )
      )
   ) T;

         X          Y
---------- ----------
 662301,48 4203898,34
         1          0

The expected result would be only 1 vertex (the first one)

I'm afraid that I did not configure something well.

Does anyone know what could happen?

Maybe some incorrect database/session parameter.

1

You have a special type of point, an oriented point as defined in https://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_objrelschema.htm#SPATL533

2.7.6 Oriented Point

An oriented point is a special type of point geometry that includes coordinates representing the locations of the point and a virtual end point, to indicate an orientation vector that can be used for rotating a symbol at the point or extending a label from the point. The main use for an oriented point is in map visualization and display applications that include symbols, such as a shield symbol to indicate a highway.

To specify an oriented point:

Use an SDO_GTYPE value (explained in Section 2.2.1) for a point or multipoint geometry.

Specify a null value for the SDO_POINT attribute.

In the SDO_ELEM_INFO array (explained in Section 2.2.4), specify an additional triplet, with the second and third values (SDO_ETYPE and SDO_INTERPRETATION) as 1 and 0. For example, a triplet of 3,1,0 indicates that the point is an oriented point, with the third number in the SDO_ORDINATES array being the first coordinate, or x-axis value, of the end point reflecting the orientation vector for any symbol or label.

In the SDO_ORDINATES array (explained in Section 2.2.5), specify the coordinates of the end point for the orientation vector from the point, with values between -1 and 1. The orientation start point is assumed to be (0,0), and it is translated to the location of the physical point to which it corresponds.

Your geometry really has two vertices. The first one is the "real" vertex and the second one is the virtual end point.

There are two places where you can recognize this with bare eyes:

MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1,3,1,0)

Normal 2D points (code 2001) have

SDO_ELEM_INFO_ARRAY(1,1,1)

Also oriented point has four numbers in MDSYS.SDO_ORDINATE_ARRAY while a normal 2D point has only two.

  • My doubt was motivated because the same query in Oracle 11g returns only 1 vertex, while in Oracle 12c returns 2 vertices. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production SQL> SELECT T.x, T.y FROM TABLE( SDO_UTIL.GETVERTICES( MDSYS.SDO_GEOMETRY(2001,23030,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1,3,1,0),MDSYS.SDO_ORDINATE_ARRAY(662301.48,4203898.34,1,0))) ) T; X Y ---------- ---------- 662301,48 4203898,34 SQL> Do you know which could be the difference between 11g and 12c get_vertices()? Thanks in advance. – Philip_2000 Jan 31 '18 at 7:16
  • Obviously a bug in 11g is fixed for 12c. – user30184 Jan 31 '18 at 11:13
1

That is a bug in the SDO_UTIL.GETVERTICES() function.

As the other answer explains, this is an oriented point: the second set of numbers (1,0) actually represent the orientation of the point (662301.48,4203898.34). Function SDO_UTIL.GETVERTICES() does not seem to support that.

A simple workaround is to just keep the first point returned:

SELECT T.x, T.y
FROM TABLE(
  SDO_UTIL.GETVERTICES(
    MDSYS.SDO_GEOMETRY(
      2001,23030,NULL,
      MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1,3,1,0),
      MDSYS.SDO_ORDINATE_ARRAY(662301.48,4203898.34,1,0)
    )
  )
) T
where rownum <= 1;

Which returns:

         X          Y
---------- ----------
 662301.48 4203898.34

1 row selected.
  • Thank you Albert. But, as I mentioned in another comment, this bug is in 12c because the same query in 11g returns only 1 vertex. Thank you again for your workaround. – Philip_2000 Jan 31 '18 at 7:20
  • If 11g and 12c behaves in a different way, what makes you think that the bug is in 12c? Oriented point cannot be defined with only one point and I would say that the bug was in the old version. The missing support for oriented point was also noticed here spatialdbadvisor.com/oracle_spatial_tips_tricks/164/…. – user30184 Jan 31 '18 at 11:12
  • Well the second part of the point (the 1,0) are NOT point coordinates. They just add information to the point whose coordinates precede (662301.48,4203898.34). That shape has just one point. The 11.2.0.4 implementation is correct: it only returns that point. The one in 12.2 is not. At best we should return the orientation angle (0,1) as additional columns ... – Albert Godfrind Feb 1 '18 at 8:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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