Book Image

Applying and Extending Oracle Spatial

By : Siva Ravada, Simon Greener
Book Image

Applying and Extending Oracle Spatial

By: Siva Ravada, Simon Greener

Overview of this book

Spatial applications should be developed in the same way that users develop other database applications: by starting with an integrated data model in which the SDO_GEOMETRY objects are just another attribute describing entities and by using as many of the database features as possible for managing the data. If a task can be done using a database feature like replication, then it should be done using the standard replication technology instead of inventing a new procedure for replicating spatial data. Sometimes solving a business problem using a PL/SQL function can be more powerful, accessible, and easier to use than trying to use external software. Because Oracle Spatial's offerings are standards compliant, this book shows you how Oracle Spatial technology can be used to build cross-vendor database solutions. Applying and Extending Oracle Spatial shows you the clever things that can be done not just with Oracle Spatial on its own, but in combination with other database technologies. This is a great resource book that will convince you to purchase other Oracle technology books on non-spatial specialist technologies because you will finally see that "spatial is not special: it is a small, fun, and clever part of a much larger whole".
Table of Contents (20 chapters)
Applying and Extending Oracle Spatial
About the Authors
About the Reviewers
Table Comparing Simple Feature Access/SQL and SQL/MM–Spatial

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;

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


Why is TREAT needed? The Oracle documentation (Object-Relational Developer's Guide defines the TREAT operator. The Puget Sound Oracle Users Group (PSOUG) website is an excellent additional source for documentation and help 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), 
 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));


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
  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

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,