Functions that use indexes to optimize queries

A specialized group of spatial functions, called comparison functions, can improve query performance by exploiting spatial grid indexes. Each of these functions compares 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.

Table 1 shows which comparison functions can use a spatial grid index:
Table 1. Comparison functions that can use a spatial grid index
Comparison function Can use spatial grid index
EnvelopesIntersect Yes
ST_Contains Yes
ST_Crosses Yes
ST_Distance Yes
ST_EnvIntersects Yes
ST_Equals Yes
ST_Intersects Yes
ST_MBRIntersects Yes
ST_Overlaps Yes
ST_Touches Yes
ST_Within Yes
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 previously 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 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 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
Table 2 shows correct and incorrect ways of creating spatial queries to utilize a spatial index.
Table 2. 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,
   ST_Point(-121.8,37.3, 1)) = 1
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,
   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(
  ST_Polygon('POLYGON(
    (10 10, 10 20, 20 20, 20 10, 10 10))', 1), 
  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.