The SQL/MM standard defines spatial data support in terms of user-defined extended types (UDTs) built on the ANSI/SQL CREATE TYPE statement. Although SAP Sybase IQ does not support user-defined types, the SAP Sybase IQ spatial data support has been implemented as though they are supported.
You can instantiate a value of a user-defined type by calling a constructor as follows:
NEW type-name( argument-list)
For example, a query could contain the following to instantiate two ST_Point values:
SELECT NEW ST_Point(), NEW ST_Point(3,4)
SAP Sybase IQ matches argument-list against defined constructors using the normal overload resolution rules. An error is returned in the following situations:
If NEW is used with a type that is not a user-defined type
If the user-defined type is not instantiable (for example, ST_Geometry is not an instantiable type).
If there is no overload that matches the supplied argument types
User defined types can have instance methods defined. Instance methods are invoked on a value of the type as follows:
value-expression.method-name( argument-list )
For example, the following fictitious example selects the X coordinate of the Massdata.CenterPoint column:
SELECT CenterPoint.ST_X() FROM Massdata;
If there was a user ID called CenterPoint, the database server would consider CenterPoint.ST_X() to be ambiguous. This is because the statement could mean "call the user-defined function ST_X owned by user CenterPoint" (the incorrect intention of the statement), or it could mean "call the ST_X method on the Massdata.CenterPoint column" (the correct meaning). The database server resolves the ambiguity by first performing a case-insensitive search for a user named CenterPoint. If one is found, the database server proceeds as though a user-defined function called ST_X and owned by user CenterPoint is being called. If no user is found, the database server treats the construct as a method call and calls the ST_X method on the Massdata.CenterPoint column.
An instance method invocation gives an error in the following cases:
If the declared type of the value-expression is not a user-defined type
If the named method is not defined in the declared type of value-expression or one of its supertypes
If argument-list does not match one of the defined overloads for the named method.
In addition to instance methods, the ANSI/SQL standard allows user-defined types to have static methods associated with them. These are invoked using the following syntax:
type-name::method-name( argument-list )
For example, the following instantiates an ST_Point by parsing text:
SELECT ST_Geometry::ST_GeomFromText('POINT( 5 6 )')
A static method invocation gives an error in the following cases:
If the declared type of value-expression is not a user-defined type
If the named method is not defined in the declared type of value expression or one of its supertypes
If argument-list does not match one of the defined overloads for the named method
As an extension to ANSI/SQL, SAP Sybase IQ supports static methods that implement user-defined aggregates. For example:
SELECT ST_Geometry::ST_AsSVGAggr(T.geo) FROM table T
All of the overloads for a static method must be aggregate or none of them may be aggregate.
A static aggregate method invocation gives an error in the following cases:
If a static method invocation would give an error
If a built-in aggregate function would give an error
If a WINDOW clause is specified
The ANSI/SQL standard defines type predicates that allow a statement to examine the concrete type (also called object type in other languages) of a value. The syntax is as follows:
value IS [ NOT ] OF ( [ ONLY ] type-name,...)
If value is NULL, the predicate returns UNKNOWN. Otherwise, the concrete type of value is compared to each of the elements in the type-name list. If ONLY is specified, there is a match if the concrete type is exactly the specified type. Otherwise, there is a match if the concrete type is the specified type or any derived type (subtype).
If the concrete type of value matches one of the elements in the list, TRUE is returned, otherwise FALSE.
The following example returns all rows where the Shape column value has the concrete type ST_Curve or one if its subtypes (ST_LineString, ST_CircularString, or ST_CompoundCurve):
SELECT * FROM SpatialShapes WHERE Shape IS OF ( ST_Curve );
The ANSI/SQL standard defines a subtype treatment expression that allows the declared type of an expression to be efficiently changed from a supertype to a subtype. This can be used when you know the concrete type (also called object type in other languages) of the expression is the specified subtype or a subtype of the specified subtype. This is more efficient that using the CAST function since the CAST function makes a copy of the value, while TREAT does not make a copy. The syntax is as follows:
TREAT( value-expression AS target-subtype )
If no error condition is raised, the result is the value-expression with declared type of target-subtype.
The subtype treatment expression gives an error in the following cases:
If value-expression is not a user-defined type
If target-subtype is not a subtype of the declared type of value-expression
If the dynamic type of value-expression is not a subtype of target-subtype
The following example effectively changes the declared type of the ST_Geometry Shape column to the ST_Curve subtype so that the ST_Curve type's ST_Length method can be called:
SELECT ShapeID, TREAT( Shape AS ST_Curve ).ST_Length() FROM SpatialShapes WHERE Shape IS OF ( ST_Curve );