English

Bug: Incorrect or no results returned when using st_distance

Description

With ArcGIS 9.2 Service Pack 2 installed, when the st_distance operator is applied as a filter in a Where clause, SQL statements may return incorrect or no results.

Cause

ArcGIS 9.2 Service Pack 2 introduced the binding between relational operators, such as st_intersects, st_contains, st_within, and the st_spatial_index domain index. The binding associates the operators with the spatial index, improving performance.

The st_distance operator was mistakenly included in the binding to the spatial index. As a result of this incorrect binding, SQL statements containing the st_distance operator in the Where clause can return incorrect results or no results.

For example, the SQL statement below is attempting to discover all buildings that are less than 5000 feet from the input fire polygon feature.

SQL> SELECT b.name, b.address 
FROM buildings b, fire_perimeter f
WHERE st_distance(f.shape,b.shape) < 5000;

no rows selected


When the spatial index is used and the results are incorrectly filtered, no rows are returned from the query, even when there are buildings that are less than the 5000 unit value.

SQL statements using the st_distance operator in the SELECT list are not impacted by this issue.
SQL> SELECT b.name NAME, b.address ADDRESS, st_distance(f.shape,b.shape) DISTANCE  
FROM buildings b, fire_perimeter f
WHERE f.event_name = 'Sand Canyon';

Workaround

Alter the st_spatial_index domain index and remove the st_distance operator binding.

Connect to the Oracle instance using SQL*Plus as the SDE user and execute the ALTER INDEXTYPE command to remove the st_distance operator.

SQL> ALTER INDEXTYPE sde.st_spatial_index DROP st_distance(st_geometry,st_geometry);


Execute the SQL statement to validate that the correct results are returned.