Introduction

The DB2 Spatial Extender provides dedicated data types and related functions to store and process spatial data directly inside a database managed by IBM DB2 for Linux, UNIX, and Windows. It can be used to model point objects (for example, customer addresses), linestrings (for example, roads), and polygons (for example, zip codes). You can also manage collections of such objects as scalar values in the database. Figure 1 illustrates the hierarchy of spatial data types of the DB2 Spatial Extender. The types with shaded background are not abstract, that is, you cannot create values of those abstract types (only of more specific subtypes). The abstract types are used to attach specific methods to them and to further group other types.

More than 100 different functions and methods are supplied by the DB2 Spatial Extender to construct geometries, retrieve geometric properties, compare two geometries, or generate new geometries using spatial set operations. While you may think that this is a huge amount of functionality, you may find some specific functionality missing. Part 1 of this series explains the techniques you can use to add new spatial function to a spatially-enabled DB2 database. The basic idea is to write external user-defined functions (UDFs) that implement the desired functionality. Since spatial data types in DB2 LUW are implemented using structured types, you have to employ transform functions to break down spatial values to basic data types and provide it in a well-defined form to the external UDF.

This article explains how to enhance spatial functionality in a different way. Learn the techniques for adding new attributes to the existing types. Become familiar with how modifying the spatial types has implications on the functions and methods delivered by the DB2 Spatial Extender. Learn to add new spatial subtypes, and, finally, discover some of the troubles associated with extending the DB2 Spatial Extender.

Back to top

Adding new attributes to existing spatial types

Structured types -- as implemented by DB2 for Linux, UNIX, and Windows and exploited by the DB2 Spatial Extender -- can be used to group several attributes and collect them in a single scalar type. This works very much like the constructs struct and class in the C/C++ or Java programming languages, where structured type attributes can be compared to class members. DB2's SQL dialect allows you to apply schema modifications to existing database objects, which includes the addition of new attributes to existing structured types. This feature can be exploited to store additional information together with geometries by enhancing the spatial data types.

The first thing you have to do is to become familiar with the spatial type hierarchy. It is important that you understand both the types and their relationships with each other as well as which attributes are defined for which type. Listing 1 shows the db2look tool being used to determine the exact type definitions.

Notice that none of the spatial data types defines any attributes, except for ST_Geometry. All proper subtypes of ST_Geometry have a very similar definition to the ST_Point type shown. The reason here is that the majority of the spatial operations is implemented in an external library and not with native SQL. Therefore, each spatial value must be transformed and its attributes flattened-out before DB2 can pass it to the external code (or receive it from there). Using a number of different attributes in all the types implies specific transform functions for each type, and more importantly, specific entry points for each combination of spatial function and spatial data type in the shared library. Implementing all those interfaces was deemed prohibitive and did not provide enough additional value to justify such an approach. Therefore, the DB2 Spatial Extender has all attributes encapsulated in the ST_Geometry type along with a couple transform functions and common, type-independent function entry points in the DB2-external code. Internally, the extender determines the geometry type and calls the respective functions.

$ db2look -d spatial -e -z db2gse

...

CREATE TYPE "DB2GSE  "."SE_EXTENSION"
         NOT INSTANTIABLE
         INLINE LENGTH 32
         WITHOUT COMPARISONS
         NOT FINAL
         REF USING VARCHAR(16) FOR BIT DATA
         MODE DB2SQL;

CREATE TYPE "DB2GSE  "."ST_GEOMETRY"  AS
                ("SRID" INTEGER,
                 "NUMPOINTS" INTEGER,
                 "GEOMETRY_TYPE" SMALLINT,
                 "XMIN" DOUBLE,
                 "YMIN" DOUBLE,
                 "XMAX" DOUBLE,
                 "YMAX" DOUBLE,
                 "ZMIN" DOUBLE,
                 "ZMAX" DOUBLE,
                 "MMIN" DOUBLE,
                 "MMAX" DOUBLE,
                 "AREA" DOUBLE,
                 "LENGTH" DOUBLE,
                 "ANNO_TEXT" VARCHAR(256),
                 "POINTS" BLOB(4190000) LOGGED NOT COMPACT,
                 "EXT" "DB2GSE  "."SE_EXTENSION")
         NOT INSTANTIABLE
         INLINE LENGTH 659
         WITHOUT COMPARISONS
         NOT FINAL
         REF USING VARCHAR(16) FOR BIT DATA
         MODE DB2SQL
         WITH FUNCTION ACCESS;

CREATE TYPE "DB2GSE  "."ST_POINT"  UNDER "DB2GSE  "."ST_GEOMETRY"
         INSTANTIABLE
         INLINE LENGTH 659
         WITHOUT COMPARISONS
         NOT FINAL
         MODE DB2SQL;

...

There are 16 attributes in the ST_Geometry data type. The attribute SRID contains the numeric identifier of the spatial reference system (SRS) associated with the geometry. Thus, each geometry knows exactly in which SRS it is encoded. NUMPOINTS is a fast access method used to determine how many control points define the geometry. It is used as indicator for empty geometries, for which it is set to zero. The attribute GEOMETRY_TYPE contains a numerical value that encodes the specific type of the spatial value (for example, point, linestring, polygon, and so on). It also encodes whether Z and/or M coordinates are present. XMIN, XMAX; YMIN, YMAX; ZMIN, ZMAX and MMIN, MMAX are used to store the minimum and maximum coordinates for each dimension. Those values are used for the spatial grid index extension provided by the DB2 Spatial Extender. AREA and LENGTH are used for fast access to the area covered by the geometry (measured in the units of the underlying SRS) and the length of linestrings or the boundary of polygons, respectively. ANNO_TEXT is not used by the DB2 Spatial Extender itself. Please refer to the discussion below regarding this and similar attributes. The most important attribute, POINTS, encodes the geometry information itself in an internal binary format. Finally, the EXT attribute is intended for future extensions of the spatial type. I exploited this attribute to store additional information with each geometry as is further described below.

Subtyping SE_Extension

The infrastructure to add new attributes to a geometry is already in place. The EXT attribute in ST_Geometry is of type SE_Extension, which is a structured type in itself. Thus, you can create subtypes under SE_Extension and store values of those subtypes in the EXT attribute. This is no different than well-known, object-oriented concepts in other programming languages.

As an example, I added attributes required to render geometries as Scalable Vector Graphics (SVG). Another article, "Visualize DB2 Spatial Data in Web Applications," describes how to implement a small web application that extracts DB2 Spatial Extender geometries and renders them using SVG (see Resources).

Listing 2 shows how to create a type SVG_Attributes as a proper subtype of SE_Extension. Additionally, a constructor function SVG_Attributes is added to create values of the new type. I also enhanced ST_Geometry with the methods ST_SetSvgAttr and ST_GetSvgAttr that hide all those new internals from the user.

CREATE TYPE db2gse.SVG_Attributes UNDER db2gse.SE_Extension
   AS (
      fillColor    VARCHAR(16),
      strokeColor  VARCHAR(16),
      lineWidth    DOUBLE,
      fillRule     VARCHAR(16) )
   INSTANTIABLE  WITHOUT COMPARISONS
   NOT FINAL  MODE DB2SQL@

CREATE FUNCTION db2gse.SVG_Attributes (
      fillColor    VARCHAR(16),  strokeColor  VARCHAR(16),
      lineWidth    DOUBLE,       fillRule     VARCHAR(16) )
   RETURNS db2gse.SVG_Attributes
   LANGUAGE SQL  CONTAINS SQL
   DETERMINISTIC  NO EXTERNAL ACTION
   RETURN db2gse.SVG_Attributes()..fillColor(fillColor)..
             strokeColor(strokeColor)..lineWidth(lineWidth)..
             fillRule(fillRule)@

ALTER TYPE db2gse.ST_Geometry
   ADD METHOD ST_SetSvgAttr ( svgAttr db2gse.SVG_Attributes )
      RETURNS db2gse.ST_Geometry
      SELF AS RESULT
      LANGUAGE SQL  CONTAINS SQL
      DETERMINISTIC  NO EXTERNAL ACTION
   ADD METHOD ST_GetSvgAttr ( )
      RETURNS VARCHAR(200)
      LANGUAGE SQL  CONTAINS SQL
      DETERMINISTIC  NO EXTERNAL ACTION@

CREATE METHOD ST_SetSvgAttr ( svgAttr db2gse.SVG_Attributes )
   RETURNS db2gse.ST_Geometry
   FOR db2gse.ST_Geometry
   RETURN SELF..ext(svgAttr)@

CREATE METHOD ST_GetSvgAttr ( )
   RETURNS VARCHAR(200)
   FOR db2gse.ST_Geometry
   RETURN WITH svg(attr)
             AS ( VALUES ( TREAT(SELF..ext() AS db2gse.SVG_Attributes) ) )
          SELECT 'fill="' || attr..fillColor() || '" ' ||
                 'stroke="' || attr..strokeColor() || '" ' ||
                 'line-width="' || LTRIM(CHAR(attr..lineWidth())) || '" ' ||
                 'fill-rule="' || attr..fillRule() || '"'
          FROM   svg@

Listing 3 demonstrates how you use the new attributes. In Listing 3 is a table that stores point geometries. The UPDATE statement sets the SVG attributes for an existing geometry, and the INSERT statement creates a new point in the table and sets the SVG attributes already during the insertion. You will find the complete example, including the CREATE TABLE statement, in file svg-example.sql in the Download section.

UPDATE t
SET    p = p..ST_SetSvgAttr(db2gse.SVG_Attributes('red', 'blue', 1.0, 'evenodd'))
WHERE  id = 1@

INSERT
INTO   t
VALUES ( 2, db2gse.ST_Point(20, 20)..ST_SetSvgAttr('green', 'black', 1.0, 'evenodd') )@

SELECT p..ST_AsText() AS wkt,
       p..ST_GetSvgAttr() AS svgAttributes
FROM   t@

WKT                  SVGATTRIBUTES
-------------------- -------------------------------------------------------------------
POINT (10 10)        fill="red" stroke="blue" line-width="1.0E0" fill-rule="evenodd"
POINT (20 20)        fill="green" stroke="black" line-width="1.0E0" fill-rule="evenodd"

  2 record(s) selected.

You can now use the new attributes and access them as I have done in the ST_GetSvgAttr method. You do not have to write specific methods for the attributes and can use the SQL expressions from the method body directly in your SQL statements. Nevertheless, providing dedicated methods improves the user interface.

One issue you have to be aware of is the support of different sets of attributes, depending on the specific purpose. Your access methods have to be able to deal with it correctly. For example, I used a TREAT expression to re-interpret values of type SE_Extension as values of type SVG_Attributes so that DB2 knows the specific type and is aware of the presence of attributes in SVG_Attributes. This treat will fail during runtime if another, incompatible subtype of SE_Extension is encountered in a geometry. Thus, it may become necessary that you test for the specific subtype before attempting the re-interpretation (and any subsequent operations).

Dealing with existing spatial routines

You should be aware that the DB2 Spatial Extender cannot know if you created subtypes under SE_Extension. It also would not know the semantics of those attributes or whether they would still be needed after a spatial operation has been applied. For example, if the method ST_PointOnSurface is invoked to return some point that lies in the interior of a polygon, any of your additional attributes of the polygon may or may not be applicable to the returned point. Therefore, the DB2 Spatial Extender does not take any measures to carry new attributes across function calls. You must take care of this yourself.

Let's assume that you want to buffer a geometry and intend to apply the SVG attributes of the original geometry to the buffered one. You must first apply the method ST_Buffer. The result will not have any SVG attributes, so a second step needs to be applied to copy the SVG attributes. Note that SQL is a value-based programming language. That means the invocation of ST_Buffer on a geometry creates a new geometry and the subject parameter for the method is not changed or destroyed. Thus, you can still refer to the original geometry and use it in the same SQL statement. I take advantage of this by accessing the EXT attribute of the original geometry and setting its value in the EXT attribute of the buffered geometry. Listing 4 demonstrates how you have to phrase your SQL statements now:

SELECT geometry,
       geometry..ST_Buffer(10, 'METER')..ext(geometry..ext) AS buffer
FROM   t

Back to top

Subtyping

Sometimes, you will find the need to implement more spatial data types to the DB2 Spatial Extender to model specific geometric objects. One example could be to further refine ST_Point objects to distinguish between oil or water wells and other point objects (like addresses). Other scenarios could be to add data types for lines connecting only two points, rectangles, squares, or circles to the spatial type hierarchy. Subsequently, this article focuses on those general new data types ST_Line, ST_Rectangle, ST_Square, and ST_Circle. Application-specific data types like OilWell can be created in the same way. Naturally, lines are more specific linestrings, and rectangles and squares are more specific polygons. Therefore, I introduced four new types: ST_Line as subtype of ST_LineString, ST_Rectangle as subtype of ST_Polygon, ST_Square as subtype of ST_Rectangle, and finally ST_Circle as new subtype under ST_Surface.

ST_Line

A very simple extension of the type hierarchy that requires only some small coding of SQL routines is the addition of the type ST_Line. Values of that type can be used to model straight line segments that are only terminated by two points: the start point and the end point. No intermediate points are allowed. Thus, a regular linestring could be described by a sequence of such line segments.

Adding new data types to an existing object-relational type hierarchy consists of three steps. First, the new data type has to be created. The next step is to add new functions or methods that work solely on the new types. For example, you could add constructor functions, creating instances of the new type. Finally, any existing routines have to be examined and you must determine whether they need adjustments for the new type. Oftentimes, it is not necessary to adjust functions -- but not always.

The new data type ST_Line is a specialization of ST_LineString where the values can have at most two points defining the line. This is just the addition of a constraint. All attributes required to store the information of a linestring are directly applicable to line segments. Listing 5 shows a type definition without any additional attributes:

CREATE TYPE db2gse.ST_Line UNDER db2gse.ST_LineString
    INSTANTIABLE  WITHOUT COMPARISONS
    NOT FINAL  MODE DB2SQL@

Next, I added a new method for linestrings that converts a value of type ST_LineString to a value of type ST_Line if the linestring happens to have two points only (or is empty). Otherwise, an exception is raised. Listing 6 demonstrates how to add such a method and its implementation. The method raises SQLSTATE '900L1' if the linestring is not empty and has more than two points, that is, if it is not a simple line segment.

ALTER TYPE db2gse.ST_LineString
   ADD METHOD ST_CastToLine ( )
      RETURNS db2gse.ST_Line
      LANGUAGE SQL  CONTAINS SQL
      DETERMINISTIC  NO EXTERNAL ACTION@

CREATE METHOD ST_CastToLine ( )
   RETURNS db2gse.ST_Line
   FOR db2gse.ST_LineString
   RETURN CASE
             WHEN SELF..numpoints = 2 OR SELF..numpoints = 0
             THEN db2gse.ST_Line()..srid(SELF..srid)..
                     numPoints(SELF..numPoints)..
                     geometry_type(SELF..geometry_type)..
                     xMin(SELF..xMin)..xMax(SELF..xMax)..
                     yMin(SELF..yMin)..yMax(SELF..yMax)..
                     zMin(SELF..zMin)..zMax(SELF..zMax)..
                     mMin(SELF..mMin)..mMax(SELF..mMax)..
                     area(SELF..area)..length(SELF..length)..
                     points(SELF..points)
             ELSE RAISE_ERROR('900L1', 'line has more than 2 points')
          END@
			  

The existing definitions for external data formats are retained and reused for the new data type. That includes the external representations like well-known text (WKT), well-known binary (WKB), geography markup language (GML), and ESRI shape format. Therefore, I can rely on the constructor functions for linestrings and use the previously added functionality to implement the constructor functions for the new data type. Listing 7 is an excerpt that demonstrates how to create such functions. One function takes the X and Y coordinates of the start and end points as well as the numeric identifier of the spatial reference system as input. The other function receives a string (CLOB) instead of coordinate values. The CLOB contains the WKT representation of the line. The full set of constructor functions can be found in file subtype-line.sql (see Download).

CREATE FUNCTION db2gse.ST_Line (
      p1 db2gse.ST_Point, p2 db2gse.ST_Point )
   RETURNS db2gse.ST_Line
   LANGUAGE SQL  READS SQL DATA
   DETERMINISTIC  NO EXTERNAL ACTION
   RETURN CASE
             WHEN p1..ST_SrsId() == p2.ST_SrsId()
             THEN db2gse.ST_LineString('linestring(' ||
                        CHAR(p1..ST_X()) || ' ' || 
                        CHAR(p1..ST_Y()) || ', ' ||
                        CHAR(p2..ST_X()) || ' ' || 
                        CHAR(p2..ST_Y()) || ')', p1..ST_SrsId())..
                     ST_CastToLine()
             ELSE RAISE_ERROR('900L2', 'points use different SRS')
          END@

CREATE FUNCTION db2gse.ST_Line (
      x1 DOUBLE, y1 DOUBLE,
      x2 DOUBLE, y2 DOUBLE,
      srsId INTEGER )
   RETURNS db2gse.ST_Line
   LANGUAGE SQL  READS SQL DATA
   DETERMINISTIC  NO EXTERNAL ACTION
   RETURN db2gse.ST_LineString('linestring(' ||
                CHAR(x1) || ' ' || CHAR(y1) || ', ' ||
                CHAR(x2) || ' ' || CHAR(y2) || ')',srsId)..
             ST_CastToLine()@

CREATE FUNCTION db2gse.ST_Line (
      wkt CLOB(2G), srsId INTEGER )
   RETURNS db2gse.ST_Line
   LANGUAGE SQL  READS SQL DATA
   DETERMINISTIC  NO EXTERNAL ACTION
   RETURN db2gse.ST_LineString(wkt, srsId)..ST_CastToLine()@

Since all values of the new data type ST_Line are treated exactly like regular linestrings, it is not necessary to adjust any of the existing routines. It is also not necessary to add any further specific methods for line segments. Methods like ST_StartPoint already exist for linestrings and are inherited automatically.

ST_Rectangle and ST_Square

Introducing a further restriction on polygons to support dedicated types for rectangles and squares is very similar to the definitions required for the data type ST_Line. The major difference is that a more complex condition is required to test whether a polygon is a rectangle or a square. I implemented an external UDF GseIsRectangle that performs those checks. The function receives the geometry in its well-known binary (WKB) representation and also a parameter that indicates whether to test for rectangle or square properties. You can find the C code for those tests in the Download section (file subtype-rectangle.sqc). Listing 8 shows how the UDF is wrapped into the method ST_IsRectangle for the ST_Polygon data type. The method is used to test whether a polygon is a rectangle and returns 1 (one) if this is the case; otherwise, the result is 0 (zero). The test method is used in a cast method that actually converts the value's type to ST_Rectangle. A similar implementation for ST_IsSquare can be found in file subtype-rectangle.sql in the Download section.

CREATE FUNCTION db2gse.GseIsRectangle (
      wkb BLOB(2G) AS LOCATOR,
      checkSquare INTEGER )
   RETURNS INTEGER
   LANGUAGE C  READS SQL DATA
   EXTERNAL NAME 'subtype-rectangle!checkRectangleSquare'
   PROGRAM TYPE SUB  PARAMETER STYLE SQL
   DETERMINISTIC  NO EXTERNAL ACTION
   FENCED  THREADSAFE
   RETURNS NULL ON NULL INPUT
   FINAL CALL  ALLOW PARALLEL
   NO SCRATCHPAD@

ALTER TYPE db2gse.ST_Polygon
   ADD METHOD ST_IsRectangle ( )
      RETURNS INTEGER
      LANGUAGE SQL  READS SQL DATA
      DETERMINISTIC  NO EXTERNAL ACTION@

CREATE METHOD ST_IsRectangle ( )
   RETURNS INTEGER
   FOR db2gse.ST_Polygon
   RETURN db2gse.GseIsRectangle(SELF..ST_AsBinary(), 0)@

ALTER TYPE db2gse.ST_Polygon
   ADD METHOD ST_CastToRectangle ( )
      RETURNS db2gse.ST_Rectangle
      LANGUAGE SQL  READS SQL DATA
      DETERMINISTIC  NO EXTERNAL ACTION@

CREATE METHOD ST_CastToRectangle ( )
   RETURNS db2gse.ST_Rectangle
   FOR db2gse.ST_Polygon
   RETURN CASE
             WHEN SELF..ST_IsRectangle() = 1
             THEN db2gse.ST_Rectangle()..srid(SELF..srid)..
                     numPoints(SELF..numPoints)..
                     geometry_type(SELF..geometry_type)..
                     xMin(SELF..xMin)..xMax(SELF..xMax)..
                     yMin(SELF..yMin)..yMax(SELF..yMax)..
                     zMin(SELF..zMin)..zMax(SELF..zMax)..
                     mMin(SELF..mMin)..mMax(SELF..mMax)..
                     area(SELF..area)..length(SELF..length)..
                     points(SELF..points)
             ELSE RAISE_ERROR('900R1', 'polygon is not a rectangle')
          END@

The situation for rectangles and squares is the same as for ST_Line values: no further specific methods are needed and all methods applicable for polygons are directly applicable for values of the two new types. Applications can exploit the DB2 built-in function TYPE_NAME or the DB2 Spatial Extender method ST_GeometryType to determine the most specific dynamic type of polygons and, thus, determine if a regular polygon, a rectangle, or a square is being processed.

ST_Circle

The previous examples were straight-forward because existing spatial data types were further refined by creating subtypes that apply additional constraints on the values of such types. Adding a data type like ST_Circle deviates from that because the new data type becomes a subtype of the abstract type ST_Surface. Thus, a new class of geometric objects is supported by it -- namely surfaces with curved boundaries.

The whole processing logic of the DB2 Spatial Extender is not tailored to deal with circles or geometries with circular shapes. All spatial calculations like overlap, intersection, or the computation of the area covered by a geometry are implemented for points, linestrings, polygons, and collections thereof. Thus, the choice is either to re-implement all affected functions or to approximate circles internally with polygons. I adopted the latter approach because it is far easier to handle. Nevertheless, I did not want to treat circles as specialized polygons because this deviates from any common understanding for polygons and circles.

The DB2 Spatial Extender function ST_Buffer can be used to construct circle objects. The radius is applied as a buffer distance on the given center point. Internally, the DB2 Spatial Extender uses the center point and shoots rays into all directions around the point. Originating from the point, the buffer distance (radius) is measured along each ray and a point set at the specific distance, very much like the ST_PointAtDistance function, which is described in Part 1 of this series. Finally, all points determined that way are connected. The resulting polygon has an approximated circular shape and can be used as an internal representation of the circle.

Listing 9 shows how to create the type ST_Circle as a direct subtype of ST_Surface. The new data type comes with all the attributes shared by all geometry types of the DB2 Spatial Extender. Thus, information about the shape of the geometry can be stored exactly as it is done for ST_Polygon values. Additionally, I wanted to store the center point of the circle to avoid calculating it again and potentially having to deal with rounding issues during those calculations. The new attribute is added as shown before in the Adding new attributes section.

CREATE TYPE db2gse.ST_Circle UNDER db2gse.ST_Surface
    AS ( center db2gse.ST_Point )
    INSTANTIABLE  WITHOUT COMPARISONS
    NOT FINAL  MODE DB2SQL@

A circle has two components defining it: a center point and a radius. I added methods to the new type to return those properties. Listing 10 summarizes how those methods are implemented. The radius can either be returned in the unit of measure for the underlying spatial reference system or in a user-supplied unit of measure. The new data type ST_Line is used here to calculate the length. A line segment between the center point and any point on the boundary of the approximating polygon is created and the length of this line returned. The center point of the circle is retrieved from the new attribute and returned as an ST_Point value. As before, you can find the full implementation in the Download section (file subtype-circle.sql).

ALTER TYPE db2gse.ST_Circle
   ADD METHOD ST_CenterPoint ( )
      RETURNS db2gse.ST_Point
      LANGUAGE SQL  CONTAINS SQL
      DETERMINISTIC  NO EXTERNAL ACTION
   ADD METHOD ST_Radius ( unit VARCHAR(128) )
      RETURNS DOUBLE
      LANGUAGE SQL  READS SQL DATA
      DETERMINISTIC  NO EXTERNAL ACTION
   ADD METHOD ST_Diameter ( unit VARCHAR(128) )
      RETURNS DOUBLE
      LANGUAGE SQL  READS SQL DATA
      DETERMINISTIC  NO EXTERNAL ACTION@

CREATE METHOD ST_CenterPoint ( )
   RETURNS db2gse.ST_Point
   FOR db2gse.ST_Circle
   RETURN SELF..center@

CREATE METHOD ST_Radius ( unit VARCHAR(128) )
   RETURNS DOUBLE
   FOR db2gse.ST_Circle
   RETURN db2gse.ST_Line(SELF..center, SELF..ST_CastToPolygon()..
                ST_ExteriorRing()..ST_StartPoint())..
             ST_Length(unit)@

CREATE METHOD ST_Diameter ( unit VARCHAR(128) )
   RETURNS DOUBLE
   FOR db2gse.ST_Circle
   RETURN 2 * SELF..ST_Radius(unit)@

The definition of the constructor functions is similar to those for the previous types. An ST_Point value is created and the ST_Buffer method applied. This returns an ST_Polygon value, which is converted to an ST_Circle value. Listing 11 illustrates the primary constructor function that accepts all possible input parameters: x and y coordinates, radius, unit of measure for the radius, and the spatial reference system id. This function is overloaded with other combinations that use the default SRS and/or the units of measure of the SRS for the center point.

CREATE FUNCTION db2gse.ST_Circle (
      x DOUBLE, y DOUBLE,
      radius DOUBLE, unit VARCHAR(128),
      srsId INTEGER )
   RETURNS db2gse.ST_Circle
   LANGUAGE SQL  READS SQL DATA
   DETERMINISTIC  NO EXTERNAL ACTION
   RETURN WITH g(v)
             AS ( VALUES db2gse.ST_Point(x, y, srsId)..ST_Buffer(radius, unit) )
          SELECT db2gse.ST_Circle()..srid(v..srid)..
                    numPoints(v..numPoints)..geometry_type(v..geometry_type)..
                    xMin(v..xMin)..xMax(v..xMax)..
                    yMin(v..yMin)..yMax(v..yMax)..
                    zMin(v..zMin)..zMax(v..zMax)..
                    mMin(v..mMin)..mMax(v..mMax)..
                    area(v..area)..length(v..length)..points(v..points)..
                    center(db2gse.ST_Point(x, y, srsId))
          FROM   g@

Now that all mandatory pieces are in place to deal with circles, take a look at what input and output functions produce. Listing 12 shows how an ST_Circle value is constructed where the center point is (100, 100) and a radius of 32 units applies. The result of the constructor function, the circle, is piped to the DB2 Spatial Extender function ST_AsText, which returns the well-known text (WKT) representation for the circle. Since I used polygons to approximate circles internally, the WKT of the circle is actually the same as for a polygon. (The output in the listing is formatted for illustration purposes only.)

db2 => VALUES db2gse.ST_Circle(100, 100, 32)..ST_AsText()

1
----------------------------------------------------------------------------------
POLYGON ((132 100, 131 108, 128 116, 121 124, 112 130, 104 132,  96 132,  88 130,
           79 124,  72 116,  69 108,  68 100,  69  92,  72  84,  79  76,  88  70,
           96  68, 104  68, 112  70, 121  76, 128  84, 131  92, 132  100))

  1 record(s) selected.
			

Figure 2 shows the rendered geometry and you can see the circular shape. Notice that it is just an approximated circle and you will find some slight bends and linear interpolation between those.

There are additional changes related to the spatial method ST_Buffer. Applying a buffer to a circle results again in a circle being returned. However, since an approximating polygon is used to represent circles internally, buffering such a polygon may lead to a worse approximation. Therefore, Listing 13 shows how to override the ST_Buffer method for the ST_Circle data type. The radius of the circle is determined with the previously defined ST_Radius method. The buffer distance is added to the radius and finally a new circle is created with this new radius.

ALTER TYPE db2gse.ST_Circle
   ADD OVERRIDING METHOD ST_Buffer ( distance DOUBLE, unitName VARCHAR(128) )
      RETURNS db2gse.ST_Circle@

CREATE METHOD ST_Buffer ( distance DOUBLE, unitName VARCHAR(128) )
   RETURNS db2gse.ST_Circle
   FOR db2gse.ST_Circle
   RETURN db2gse.ST_Circle(SELF..center, SELF..ST_Radius(unitName) + distance,
             unitName, SELF..ST_SrsId())@

Notice in Listing 13 that only the version of ST_Buffer that receives the buffering distance and the name of the unit of measure was overrode. The overloaded version that operates implicitly with the units of measure of the underlying SRS is not overridden for ST_Circle. The reason is that the DB2 Spatial Extender defines this method as CONTAINS SQL, but I had to use READS SQL DATA for ST_Radius and ST_Circle, for example. An overriding method inherits this property from the overridden one and cannot change it. Thus, an external UDF is required, or -- as illustrated here -- the original implementation of the DB2 Spatial Extender can be exploited and any computation differences accepted.

Back to top

Relational modeling vs. new attributes or subtypes

Every time you plan to add new subtypes or new attributes to existing spatial types, consider carefully your design decisions. There may be more appropriate ways to represent the desired information in your relational model instead of extending the spatial types.

For example, each geometry has an attribute ANNO_TEXT where annotations and comments can be stored as an integral part of the geometry itself. However, a much better fit from a relational perspective is to maintain such annotations in an additional column in the table -- after all, such annotations do not describe a property of the geometry but rather a property of the real-world object being represented. Another disadvantage of modeling annotations that way is that the existing ANNO_TEXT attribute increases the size of each geometry required when stored on disk. This happens even if there are no annotations applicable to the geometry. While this is not a serious concern today, it is still something to keep in mind when implementing efficient applications.

The same considerations should always be applied to all your design decisions -- ANNO_TEXT is just an example. Storing SVG-specific information in geometries, as discussed above, should be carefully considered. Usually, all geometries in a layer will be rendered with the same fill color, border color, and line width. So it may be beneficial not to store those properties in each geometry but rather keep those properties in true, normalized fashion in a separate, relational table or even in the application only.

Back to top

Summary

The DB2 Spatial Extender provides an extensive set of spatial data types and related functions. Sometimes, this functionality is not sufficient and you may want to extend the DB2 Spatial Extender yourself. This article explained what is necessary to add new attributes to the existing spatial data types and how and when to use those attributes. The second part focused on adding new spatial data types either to apply further constraints like representing rectangles or squares with more specific types. It also described what is necessary to introduce a type ST_Circle to model such geometries explicitly as specific surfaces. The third section of the article draws your attention to weight extending the DB2 Spatial Extender with other approaches to represent the same information in your system.

The described enhancements are supplemental to the enhancements I presented in other parts of this series (see Resources ). Combining all those, you will find that it is comparably easy to extend the DB2 Spatial Extender and customize it according to your needs.

Back to top

Download

Information about download methods

Resources

• Participate in the discussion forum.
  
  
• DB2 Spatial Extender: Learn more about the DB2 Spatial Extender.
  
  
• DB2 Information Center: Discover complete documentation for DB2 UDB.
  
  
• "Extending the Spatial Extender" (developerWorks, Nov 2005): Get an introduction on how to add new functionality to the DB2 Spatial Extender.
  
  
• "Visualize DB2 Spatial Data in Web Applications" (developerWorks, Nov 2007): Implement a light-weight PHP application to render spatial data from a DB2 database in your web browser.
  
  
• DB2 Spatial Extender User's Guide and Reference: Get help and direction on using the spatial functionality for DB2.
  
  
• "O-O, What Have They Done to DB2?": Learn about the implementation of structured types in DB2 LUW.
  
  
• The W3C consortium defined the SVG specification and made it publicly available.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  

About the author

Knut Stolze started his work with DB2 when he joined IBM as a visiting scientist at the Silicon Valley Lab, USA, in 1999 where he worked on the DB2 Image Extender. He moved on to the DB2 Spatial Extender Development team and was responsible for several enhancements to improve usability, performance, and standard-conformance of the Extender. From 2002 through 2006 he was a PhD student and teaching assistant at the University of Jena, Germany. At the same time he continued his work for IBM in the area of the Information Integrator development. Today, Knut Stolze is a member of the development team responsible for DB2 z/OS Utilities, DB2 z/OS Autonomic Functions, and Data Warehousing on DB2 for z/OS.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Adding new attributes to existing spatial types
• Subtyping
• Relational modeling vs. new attributes or subtypes
• Summary
• Download
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers