Using geospatial functions
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.
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)') );