Why does (SHAPE).SDO_ORDINATES(1) syntax fail, but (SHAPE).ST_PointN(1) succeeds?

Question

Oracle 18c:

In a related question, we determined:

The syntax of extracting collection elements by index is not supported in SQL.

So a query that uses this syntax will fail: (shape).sdo_ordinates(1),

select
  (shape).sdo_ordinates(1) as startpoint_x
from
  (select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape 
   from dual)

Error:
ORA-00904: "MDSYS"."SDO_GEOMETRY"."SDO_ORDINATES": invalid identifier

Source: Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in SQL?

However, I have a query that is similar (different datatype) that succeeds when I use seemingly similar syntax: (shape).st_pointn(1).

select
  (shape).st_pointn(1) as startpoint
from
  (select treat(st_geometry.from_wkt('Linestring(1 2, 3 4, 5 6)',26917) as st_linestring) as shape
   from dual)

Result:
MDSYS.ST_POINT(MDSYS.SDO_GEOMETRY(2001, 26917, MDSYS.SDO_POINT_TYPE(1, 2, NULL), NULL, NULL))

Source: Why do we need to Treat() MDSYS.ST_GEOMETRY as ST_LINESTRING to use ST_PointN(1)?

Why does (SHAPE).SDO_ORDINATES(1) fail, but (SHAPE).ST_PointN(1) succeeds?

Because [`St_PointN`](https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/st-pointn.htm) is a function that accepts a point index and returns the point with that index. This syntax is not a collection element access. — astentx, Jun 18 '22 at 07:57
See [db_fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8794555222162bac8c9d5369fded9589) — astentx, Jun 18 '22 at 08:13
@astentx That `St_PointN` link you shared points to a different datatype...a proprietary implementation of the generic ST_GEOMETRY standard. The question pertains to Oracle's MDSYS.ST_GEOMETRY datatype. It was implemented differently. Apples to oranges. — User1974, Jun 18 '22 at 08:13
Okay, let's scan the source code and find that `st_pointn` is a member function of type `st_curve` that takes a number as input and returns a `st_point` as output. See [db_fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=b0354d922eeceb61a75c6e1f4b0ef884) — astentx, Jun 18 '22 at 08:31

MT0 · Accepted Answer · 2022-06-18T21:15:51.930

[TL;DR]

SDO_ORDINATES is a collection attribute of the MDSYS.SDO_GEOMETRY data type.

ST_POINTN is a member function of (a super-type of) the MDSYS.ST_LINESTRING data type.

When you use:

select sdo_geometry('linestring(1 2, 3 4, 5 6)').sdo_ordinates(1)
from   dual

Then it the SQL engine processes the syntax as a call to a member function in the form of object_type.member_function(argument) and there is no SDO_ORDINATES member function of the SDO_GEOMETRY data type and the output is:

ORA-00904: "MDSYS"."SDO_GEOMETRY"."SDO_ORDINATES": invalid identifier

Because there is no SDO_ORDINATES member function on the MDSYS.SDO_GEOMETRY object.

If instead, you use:

select (sdo_geometry('linestring(1 2, 3 4, 5 6)').sdo_ordinates)(1)
from   dual

Then the SQL engine processes the syntax as (object_type.collection_attribute)(index) and the output is:

ORA-03001: unimplemented feature

Because extracting collection elements is not supported in SQL.

Finally:

select treat(
         st_geometry.from_wkt('Linestring(1 2, 3 4, 5 6)',26917)
         as st_linestring
       ).st_pointn(1)
from   dual

Returns an ST_LINESTRING object type and then you call the ST_POINTN member function with the argument 1. This works because there is a ST_POINTN member function declared on MDSYS.ST_CURVE which is the super-type of MDSYS.ST_LINESTRING.

You can see the object's source using:

select text
from   all_source
where  type  = 'TYPE'
and    owner = 'MDSYS'
and    name  = 'ST_LINESTRING'
ORDER BY line

and then, for it's parent:

select text
from   all_source
where  type  = 'TYPE'
and    owner = 'MDSYS'
and    name  = 'ST_CURVE'
ORDER BY line

Which includes the declaration:

 MEMBER FUNCTION ST_PointN(aposition INTEGER) RETURN ST_Point DETERMINISTIC,

db<>fiddle here

Why does (SHAPE).SDO_ORDINATES(1) syntax fail, but (SHAPE).ST_PointN(1) succeeds?

1 Answers1