English

Bug: 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;


The result set for the statement should return all the rivers which are completely contained by the state of Ohio and have the word 'MIAMI' in their name.

Because the input geometries are reversed when passed to the st_contains function, no results are returned. This is because a river cannot contain a state.

If the spatial index is not used as the access path for the operator, the results will be correct.

When the access path does not use the spatial index, the operator becomes a filter and the geometries are passed to the st_contains function in the correct order.

Workaround

To avoid inconsistent results and workaround the issue with st_contains, one can apply the st_within operator.

st_within tests for the exact opposite result of st_contains.

Instead of using the st_contains operator in the previous example, the st_within operator can be applied, but the input geometries must be specified in reverse order to meet the definition of st_within. Because st_within states the first geometry, it must be completely within the second geometry.

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;