Treating values of ST_Geometry as values of a subtype

If a spatial function returns a geometry whose static type is a super type, and if the geometry is passed to a function that accepts only geometries of a type that is subordinate to this super type, a compile-time exception is raised.

For example, the static type of the output parameter of the ST_Union function is ST_Geometry, the super type of all spatial data types. The static input parameter for the ST_PointOnSurface function can be either ST_Polygon or ST_MultiPolygon, two subtypes of ST_Geometry. If Db2® Spatial Extender attempts to pass geometries returned by ST_Union to ST_PointOnSurface, Db2 Spatial Extender raises the following compile-time exception:


SQL00440N No function by the name "ST_POINTONSURFACE" 
having compatible arguments was found in the function 
path.    SQLSTATE=42884

This message indicates that Db2 Spatial Extender could not find a function that is named ST_PointOnSurface and that has an input parameter of ST_Geometry.

To let geometries of a super type pass to functions that accept only subtypes of the super type, use the TREAT operator. As indicated earlier, ST_Union returns geometries of a static type of ST_Geometry. It can also return geometries of a dynamic subtype of ST_Geometry. Suppose, for example, that it returns a geometry with a dynamic type of ST_MultiPolygon. In that case, the TREAT operator requires that this geometry be used with the static type ST_MultiPolygon. This matches one of the data types of the input parameter of ST_PointOnSurface. If ST_Union does not return an ST_MultiPolygon value, Db2 Spatial Extender raises a runtime exception.

If a function returns a geometry of a super type, the TREAT operator generally can tell Db2 Spatial Extender to regard this geometry as a subtype of this super type. But be aware that this operation succeeds only if the subtype matches or is subordinate to a static subtype defined as an input parameter of the function to which the geometry is passed. If this condition is not met, Db2 Spatial Extender raises a run-time exception.

Consider another example: suppose that you want to determine the perpendicular points for a given point on the boundary of a polygon that has no holes. You use the ST_Boundary function to derive the boundary from the polygon. The static output parameter of ST_Boundary is ST_Geometry, but ST_PerpPoints accepts ST_Curve geometries. Because all polygons have a linestring (which is also a curve) as a boundary, and because the data type of linestrings (ST_LineString) is subordinate to ST_Curve, the following operation will let an ST_Geometry polygon returned by ST_Boundary pass to ST_PerpPoints:


SELECT ST_AsText(ST_PerpPoints(TREAT(ST_Boundary(polygon) as ST_Curve)),
       ST_Point(30.5, 65.3, 1)))
FROM   polygon_table

Instead of invoking ST_Boundary and ST_PerpPoints as functions, you can invoke them as methods. To do so, specify the following code:


SELECT TREAT(ST_Boundary(polygon) as ST_Curve)..
       ST_PerpPoints(St_Point(30.5, 65.3, ))..ST_AsText()
FROM   polygon_table