st_contains in Oracle returns the wrong result

Description

Using the st_geometry operator, st_contains, returns incorrect results when the access path for the query uses the st_spatial_index, which is the st_geometry attribute's spatial index.

This issue has been fixed in ArcGIS 9.2 Service Pack 4.

Cause

When the operator st_contains is evaluated and the st_geometry attribute has a spatial index which is used for the access path, the input geometries are passed to the st_contains function in the reversed order as they are specified in the operator (geometry one, geometry two).

As a result of the two input geometries being passed to the function in the reversed order, the evaluation of the geometries spatial relations are not performed in the order for which they were specified in the operator.

For example, if the objective is to find all rivers which have a name containing the word 'MIAMI' and are contained in the state of Ohio, one would construct the following SQL statement:

Code:
SQL> SELECT rivers.name, rivers.length, rivers.volume 
  2  FROM rivers, states
  3  WHERE rivers.name LIKE '%MIAMI%'
  4  AND states.name = 'OHIO'
  5  AND st_contains(states.shape, rivers.shape) = 1;

Workaround

Code:
SQL> SELECT rivers.name, rivers.length, rivers.volume 
  2  FROM rivers, states
  3  WHERE rivers.name LIKE '%MIAMI%'
  4  AND states.name = 'OHIO'
  5  AND st_within(rivers.shape, states.shape) = 1;