English

FAQ: Should st_relate be used with a matrix that emulates st_disjoint in Oracle?

Question

Should st_relate be used with a matrix that emulates st_disjoint in Oracle?

Answer

Because the sde.st_relate operator is associated with the sde.st_spatial_index, meaning the Oracle optimizer can choose to use the spatial index to satisfy the operator's predicate value, incorrect results can be returned if the st_relate matrix is defined as disjoint.

The st_relate matrix for disjoint is defined as 'FF*FF****'. The matrix specifies that the boundaries and interiors between the input a shape and input b shape’s geometry must not intersect.

In the following example, because the Oracle optimizer chooses to use the roads spatial index as the access path, the wrong results are returned by the query.

Code:
SQL> SELECT COUNT(*)
2 FROM roads, lakes
3 WHERE lakes.name = 'Jenks Lake'
4 AND sde.st_relate(roads.shape, lakes.shape,'FF*FF****') = 1
5 AND sde.st_envintersects(roads.shape,-114.4,32.7,-113.6,33.4) = 1;

COUNT(*)
----------
0


If the query is rewritten to use the st_disjoint operator instead of the st_relate operator, the correct results are returned.

Code:
SQL> SELECT COUNT(*)
2 FROM roads, lakes
3 WHERE lakes.name = 'Jenks Lake'
4 AND sde.st_disjoint(roads.shape, lakes.shape) = 1
5 AND sde.st_envintersects(roads.shape,-114.4,32.7,-113.6,33.4) = 1;

COUNT(*)
----------
1