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()
?
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
.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