MDSYS.ST_GEOMETRY; Oracle 18c:

The following query works. It extracts the first point from an MDSYS.ST_GEOMETRY:

--Source: https://www.spdba.com.au/using-oracles-st_geometry-type-hierarchy-with-sdo_geometry-st_pointn-and-st_numpoints/

with    cte as (
select  treat(st_geometry.from_wkt('LINESTRING(10 10, 20 20)',26917) as st_linestring) as shape
from    dual
)
select 
    (shape).st_pointn(1) as first_point
from 
    cte

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

I don't understand why we need to Treat() the ST_GEOMETRY supertype as an ST_LINESTRING subtype in order to use ST_PointN to get the point.

For example, if I remove the Treat(... as ST_LINESTRING), then I get an error:

with    cte as (
select  st_geometry.from_wkt('LINESTRING(10 10, 20 20)',26917) as shape
from    dual
)
select 
    (shape).st_pointn(1) as first_point
from 
    cte

Error:
ORA-00904: "MDSYS"."ST_GEOMETRY"."ST_POINTN": invalid identifier

Why do I get that error when I remove Treat()?

CodePudding user response：

Why do I get that error when I remove Treat()?

• ST_LINESTRING is a sub-type of ST_CURVE which, in turn, is a sub-type of ST_GEOMETRY.
• ST_POINTN is a member function declared on the sub-type ST_CURVE and ST_LINESTRING inherits this function.
• ST_POINTN is not declared as a member function on the parent type ST_GEOMETRY.
• The ST_GEOMETRY.FROM_WKT() function returns an ST_GEOMETRY instance that, in this case is a actually a ST_LINESTRING sub-type but the return type of the function is ST_GEOMETRY as it could return any child sub-type.

When you remove TREAT() then you are trying to call the ST_POINTN member function on the parent type ST_GEOMETRY and, as the error message states "MDSYS"."ST_GEOMETRY"."ST_POINTN" is an invalid identifier because the type does not have that member function.

When you include TREAT() then you cast the super-type to the sub-type and then call the member function on that sub-type and the member function does exist so it works.

A similar example is:

CREATE TYPE parent_type IS OBJECT (
 x NUMBER,
 y NUMBER
) NOT FINAL;

CREATE TYPE child_type UNDER parent_type (
  MEMBER FUNCTION get_x RETURN NUMBER
);

CREATE TYPE BODY child_type IS
  MEMBER FUNCTION get_x RETURN NUMBER
  IS
  BEGIN
    RETURN self.x;
  END;
END;
/

Then:

CREATE FUNCTION create_parent RETURN PARENT_TYPE
IS
BEGIN
  RETURN child_type(1, 2);
END;
/

If you use:

SELECT create_parent().get_x() FROM DUAL;

Then the function declares it returns a PARENT_TYPE and so the member function is called on that type, even though the actual returned value is a CHILD_TYPE, so raises the error:

ORA-00904: "SCHEMA_NAME"."PARENT_TYPE"."GET_X": invalid identifier

If you use TREAT to cast the returned parent to its actual child type:

SELECT TREAT(create_parent() AS child_type).get_x() FROM DUAL;

Then the output is:

TREAT(CREATE_PARENT()ASCHILD_TYPE).GET_X()
1

db<>fiddle here