Frequently asked question

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

Last Published: April 25, 2020

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


Article ID:000010051

Software:
  • Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Discover more on this topic