Understanding the TREAT operator

Chapter 11, SQL/MM – A Basis for Cross-platform, Inter-operable, and Reusable SQL introduced the Oracle MDSYS ST_GEOMETRY type hierarchy. In that chapter, the Oracle TREAT operator was required to ensure that a subtype object such as a point, when created by the ST_GEOMETRY type's GET_WKT method, was correctly understood to be an instance of that subtype (that is, ST_POINT), so that the methods particular to its subtype (for example, ST_X) can be called. This appendix examines the need for TREAT in more detail.

In the ST_GEOMETRY hierarchy, a POINT object can be created in the following two ways:

MDSYS.ST_GEOMETRY.FROM_WKT('POINT(6012578.005 2116495.361)',2872)
MDSYS.ST_POINT.FROM_WKT('POINT(6012578.005 2116495.361)',2872)

The result in both cases is not an ST_POINT, rather it is an ST_GEOMETRY object. Why is this? It happens in the first because the FROM_WKT method of the ST_GEOMETRY super type is called directly; and in the second, the ST_POINT subtype of ST_GEOMETRY does not have a FROM_WKT method, but its ST_GEOMETRY super type does and so its FROM_WKT method is called instead. Thus in neither case an ST_POINT is returned. The returned objects are not ST_POINT types and can be demonstrated by trying to execute the ST_POINT type's ST_X member function:

Select MDSYS.ST_Geometry.FROM_WKT(
               'POINT(6012578.005 2116495.361)',2872)
            .ST_X() as point
  From dual;
SQL Error: ORA-00904: "MDSYS"."ST_GEOMETRY"."ST_X": invalid identifier

Select MDSYS.ST_Point.FROM_WKT('POINT(6012578.005 2116495.361)',2872).ST_X()
        as point 
  From dual;
SQL Error: ORA-00904: "MDSYS"."ST_GEOMETRY"."ST_X": invalid identifier

To correct this, the TREAT function must be used as follows:

Select TREAT(MDSYS.ST_Geometry.FROM_WKT('POINT(6012578.005 2116495.361)',2872)
           As MDSYS.ST_Point).ST_X() as x 
  From dual;
         X
----------
6012578.005

Select TREAT(MDSYS.ST_Point.FROM_WKT('POINT(6012578.005 2116495.361)',2872) 
           As MDSYS.ST_Point).ST_X() as x 
  From dual

         X
----------
6012578.005

Why is TREAT needed? The Oracle documentation (Object-Relational Developer's Guide http://docs.oracle.com/cd/E16655_01/appdev.121/e16801/adobjbas.htm#i479093) defines the TREAT operator. The Puget Sound Oracle Users Group (PSOUG) website is an excellent additional source for documentation and help http://psoug.org/definition/TREAT.htm as follows:

[…] allow[ing] you to change the declared type of the expression used in TREAT. This function comes in handy when you have a subtype that is more specific to your data and you want to convert the parent type to the more specific one.

Whenever a subtype, such as ST_LINESTRING, calls methods, for example, FROM_WKT, that are inherited from the ST_GEOMETRY parent type, the returned geometry type will always be the generic ST_GEOMETRY even though its contents (LINESTRING WKT) are from a particular subtype. Thus the TREAT operator has to be used to ensure that the converted geometry is correctly interpreted as a ST_LINESTRING.

In the final example, drawn from Chapter 11, SQL/MM – A Basis for Cross-platform, Inter-operable, and Reusable SQL the insertion of an ST_LINESTRING object into a table whose geometry column is defined as ST_LineString (not its generic super type ST_GEOMETRY) requires use of the TREAT function:

Create table ST_ROAD (
 FID         Integer, 
 STREET_NAME Varchar2(1000), 
 CLASSCODE   Varchar2(1), 
 GEOM        MDSYS.ST_LINESTRING, 
 Constraint ST_ROAD_PK Primary Key (FID)
);
Insert into ST_ROAD Values (1,'Main St','C',
                          TREAT(MDSYS.ST_LINESTRING.FROM_WKT (
                                'LINESTRING(6012759.63041794 2116512.48842026, 
                                            6012420.59599103 2116464.90977527)',2872) 
                                As MDSYS.ST_LINESTRING));

Note

For some reason, Oracle chose not to implement ST_AsText() or ST_GeomFromText() as per the standard, instead exposing the SDO_GEOMETRY object methods GET_WKT(), FROM_WKT(). This is a minor problem for cross-database programming perspective which will be dealt with in Chapter 11, SQL/MM – A Basis for Cross-platform, Inter-operable, and Reusable SQL.

A way to correct these issues is to override the MDSYS.ST_GEOMETRY type's FROM_WKT method in every subtype that needs it (for example, MDSYS.ST_LINESTRING). Because the standard MDSYS.ST_GEOMETRY hierarchy does not do this, TREAT must be used to obtain the correct subtype identification. Only then can subtype methods such as ST_Length (ST_CURVE) be called. However, to avoid the continual use of TREAT, Chapter 11, SQL/MM – A Basis for Cross-platform, Inter-operable, and Reusable SQL, introduced its own ST_GEOMETRY type. This type implements subtype constructors and WKT additional conversion methods such as ST_LineFromText that avoid the need to use TREAT. The following snippet from the source code of the ST_GEOMETRY type shows the constructors and the ST_LineFromText method for the ST_LineString subtype as follows:

Create or Replace Type ST_CURVE
Under ST_GEOMETRY (
  Overriding Member Function ST_DIMENSION Return Integer Deterministic,
  Member Function ST_Points Return mdsys.ST_Point_Array Deterministic,
Member Function ST_NumPoints Return Integer Deterministic,
  Member Function ST_PointN(aposition integer) Return Book.ST_Point Deterministic,
[…]
  Member Function ST_Length Return Number Deterministic
) NOT FINAL;

Create or Replace Type ST_LINESTRING
Under ST_CURVE (
[…]
  Constructor Function ST_LINESTRING(AWKT varchar2, ASRID integer DEFAULT NULL) 
                 Return SELF As Result,
[…]
  Static Function ST_LineFromText(AWKT varchar2, ASRID integer DEFAULT NULL) 
           Return Book.ST_LINESTRING Deterministic,
[…]
);