User's Guide and Reference

Building a spatial query

This section discusses building spatial queries that utilize spatial functions and predicates.

Spatial functions and SQL

DB2 Spatial Extender includes functions that perform various operations on spatial data. The examples in this section show you how to use spatial functions to build your own spatial queries.

Table 3 provides a list of spatial functions and the types of operations they can perform.

Table 3. Spatial functions and operations

Function type	Operation example
Calculation	Calculate the distance between two points
Comparison	Find all customers located within a flood zone
Data exchange	Convert data into supported formats
Transformation	Add a five-mile radius to a point

For more information about spatial functions, see Geometries and associated spatial functions and Spatial functions for SQL queries.

Example 1: Comparison

The following query finds the average customer distance from each department store. The spatial functions used in this example are ST_Distance and ST_Within.

SELECT s.id, AVG(db2gse.ST_Distance(c.location,s.location))
FROM customers c, stores s
WHERE db2gse.ST_Within(c.location,s.zone)=1
GROUP BY s.id

Example 2: Data exchange

The following query finds the customer locations for those who live in the San Francisco Bay Area. The spatial functions used in this example are ST_AsText (data exchange) and ST_Within. ST_AsText converts the spatial data in the c.location column into the OGC TEXT format.

SELECT db2gse.ST_AsText(c.location,cordref(1))
FROM customers c
WHERE db2gse.ST_Within(c.location,:BayArea)=1
 

Example 3: Calculation

The following query finds all streets longer than 10.5 miles. The spatial function used in this example is ST_Length.

SELECT s.name,s.id
FROM street s
WHERE db2gse.ST_Length(s.path) > 10.5
 

Example 4: Transformation

This query finds the customers who live within the flood zone or within 2 miles from the boundary of the flood zone. The spatial functions used in this example are ST_Buffer (transformation) and ST_Within.

SELECT c.name,c.phoneNo,c.address
FROM customers c
WHERE db2gse.ST_Within(c.location,ST_Buffer(:floodzone,2))=1