Examples of how spatial functions operate

Db2® Spatial Extender provides functions that perform various operations on spatial data. These functions can be categorized according to the type of operation that they perform.

Table 1 lists these categories, along with examples. The text following Table 1 shows coding for these examples.

Table 1. Spatial functions and operations
Category of function	Example of operation
Returns information about specific geometries.	Return the extent, in square miles, of the sales area of Store 10.
Makes comparisons.	Determine whether the location of a customer's home lies within the sales area of Store 10.
Derives new geometries from existing ones.	Derive the sales area of a store from its location.
Converts geometries to and from data exchange formats.	Convert customer information in GML format into a geometry, so that the information can be added to the database.

Example 1: Returns information about specific geometries

In this example, the ST_Area function returns a numeric value that represents the sales area of store 10. The function will return the area in the same units as the units of the coordinate system that is being used to define the area's location.


SELECT db2gse.ST_Area(sales_area)
FROM   stores
WHERE  id = 10

The following example shows the same operation as the preceding one, but with ST_Area invoked as a method and returning the area in units of square miles.


SELECT saleas_area..ST_Area('STATUTE MILE')
FROM   stores
WHERE  id = 10

Example 2: Makes comparisons

In this example, the ST_Within function compares the coordinates of the geometry representing a customer's residence with the coordinates of a geometry representing the sales area of store 10. The function's output will signify whether the residence lies within the sales area.


SELECT c.first_name, c.last_name, db2gse.ST_Within(c.location, s.sales_area)
FROM   customers as c. stores AS s
WHERE  s.id = 10

Example 3: Derives new geometries from existing ones

In this example, the function ST_Buffer derives a geometry representing a store's sales area from a geometry representing the store's location.


UPDATE stores
SET    sales_area = db2gse.ST_Buffer(location, 10, 'KILOMETERS')
WHERE  id = 10

The following example shows the same operation as the preceding one, but with ST_Buffer invoked as a method.


UPDATE stores
SET    sales_area = location..ST_Buffer(10, 'KILOMETERS')
WHERE  id = 10

Example 4: Converts geometries to and from data exchange formats.

In this example, customer information coded in GML is converted into a geometry, so that it can be stored in the database.


INSERT
INTO   c.name,c.phoneNo,c.address
VALUES ( 123, 'Mary Anne', Smith', db2gse.ST_Point('
<gml:Point><gml:coord><gml=X>-130.876</gml:X>
<gml:Y>41.120'</gml:Y></gml:coord></gml:Point>, 1) )