Using geospatial functions

Edit online

Db2® for i provides functions that perform various operations on geospatial data. Many of these functions can be categorized according to the type of operation that they perform.

This section lists the main functional categories and provides an example of each one.

Table 1. Geospatial function operations
Category of function	Example of operation
Derives new geometries from existing ones	Derive the sales area of a store from its location
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
Converts geometries to and from data exchange formats	Convert customer information in WKT format into a geometry, so that the information can be added to the database

Example 1: 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. The sales area is a circle of 10 km (10000 meters) around the store location.


UPDATE stores
  SET sales_area = QSYS2.ST_BUFFER(location, 10000)
  WHERE id = 10;

Example 2: 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 QSYS2.ST_AREA(sales_area)
  FROM stores
  WHERE id = 10;

Example 3: 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, QSYS2.ST_WITHIN(c.location, s.sales_area)
  FROM customers as c. stores AS s
  WHERE s.id = 10;

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

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


INSERT INTO customer (id, first_name, last_name, location)
VALUES ( 123, 'Mary', Smith', QSYS2.ST_POINT('point (-92.503 44.058)') );