Functions that use indexes to optimize queries

A specialized group of spatial functions, called comparison functions, can improve query performance by exploiting a spatial grid index.

Comparison functions compare two geometries with one another. If the results of the comparison meet certain criteria, the function returns a value of 1; if the results fail to meet the criteria, the function returns a value of 0. If the comparison cannot be performed, the function can return a null value.

For example, the function ST_Overlaps compares two geometries that have the same dimension (for example, two linestrings or two polygons). If the geometries overlap partway, and if the space covered by the overlap has the same dimension as the geometries, ST_Overlaps returns a value of 1.

The following list shows the comparison functions that can use a spatial grid index:
  • EnvelopesIntersect
  • ST_Contains
  • ST_Crosses
  • ST_Distance
  • ST_Equals
  • ST_Intersects
  • ST_Overlaps
  • ST_Touches
  • ST_Within
Because of the time and memory required to execute a function, such execution can involve considerable processing. Furthermore, the more complex the geometries that are being compared, the more complex and time-intensive the comparison will be. The specialized functions listed above can complete their operations more quickly if they can use a spatial index to locate geometries. To enable such a function to use a spatial index, observe all of the following rules:
  • The function must be specified in a WHERE clause. If it is specified in a SELECT, HAVING, or GROUP BY clause, a spatial index cannot be used.
  • The function must be the expression on the left of the predicate.
  • The operator that is used in the predicate that compares the result of the function with another expression must be an equal sign, with one exception: the ST_Distance function must use the less than operator.
  • The expression on the right of the predicate must be the constant 1, except when ST_Distance is the function on the left.
  • The ST_Distance function takes two geometries and, optionally, a unit as input parameters. The value of the unit must be the same type of value as each geometry. For example, if you specify the values for the geometries in degrees, you also must specify the value for the unit in degrees.
  • The operation must involve a search in a spatial column on which a spatial index is defined.
For example: 

SELECT   c.name, c.address, c.phone
FROM customers AS c, bank_branches AS b
WHERE db2gse.ST_Distance(c.location, b.location) < 10000
  and b.branch_id = 3

If you have detailed information about your spatial data so that the selectivity of a particular query can be estimated accurately, you can add a SELECTIVITY clause with a numeric constant in the spatial predicate function specification. Db2 uses this value as the new filter factor for determining an access path for the SELECT statement.

The following table shows the correct and incorrect ways of creating spatial queries to utilize a spatial index.
Table 1. Demonstration of how spatial functions can adhere to and violate rules for utilizing a spatial index
Queries that reference spatial functions Rules violated 

SELECT *
FROM stores AS s
WHERE db2gse.ST_Contains(s.sales_zone,
   db2gse.ST_Point(-121.8,37.3, 1)) = 1
No condition is violated in this example. 

SELECT *
FROM stores AS s
WHERE db2gse.ST_Contains(s.sales_zone,
   db2gse.ST_Point(-121.8,37.3, 1)) = 1 SELECTIVITY 0.001
 No condition is violated in this example. 

SELECT *
FROM stores AS s
WHERE db2gse.ST_Length(s.location) > 10
The spatial function ST_Length does not compare geometries and cannot utilize a spatial index. 

SELECT *
FROM stores AS s
WHERE 1=db2gse.ST_Within(s.location,:BayArea)
The function must be an expression on the left side of the predicate. 

SELECT *
FROM stores AS s
WHERE db2gse.ST_Contains(s.sales_zone,
   db2gse.ST_Point(-121.8,37.3, 1)) <> 0
Equality comparisons must use the integer constant 1. 

SELECT *
FROM stores AS s
WHERE db2gse.ST_Contains(db2gse.ST_Polygon
  ('polygon((10 10, 10 20, 20 20, 20 10, 10 10))', 1), 
  db2gse.ST_Point(-121.8, 37.3, 1) = 1
No spatial index exists on either of the arguments for the function, so no index can be utilized.