sqloracle-databaseoracle18coracle-spatial

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


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(mdsys.st_geometry.from_wkt('LINESTRING(10 10, 20 20)',26917) as mdsys.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  mdsys.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()?


Solution

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

    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