HOW TO

Instructions provided describe how to obtain the spatial relation between two objects using SQL in Oracle and st_geometry attributes. The output from the query reports the total number of objects that are interior, boundary, and exterior between two shapes being compared.

- The query provides an example of how to understand the number of point_events that are interior to the input polygon or point_events located on the polygon's boundary or exterior to the polygon. The relation between the geometries can be obtained by using the sde.st_relate operator and specifying the various masks: 'T********' as interior, '********T' as exterior, and '****T****' as boundary.
**Code:**

SQL> SELECT COUNT(*) cnt, relation

2 FROM (SELECT CASE WHEN sde.st_relate(a.shape, b.shape, 'T********') = 1 THEN 'Interior'

3 WHEN sde.st_relate(a.shape, b.shape, '********T') = 1 THEN 'Exterior'

4 WHEN sde.st_relate(a.shape, b.shape, '****T****') = 1 THEN 'Boundary'

5 ELSE 'Unknown'

6 END "RELATION"

7 FROM point_events a, poly_events b

8 WHERE b.event_name = 'STORM 129'

9 AND sde.st_envintersects(a.shape, b.shape) = 1)

10 GROUP BY relation

11 ORDER BY cnt;

CNT RELATION

---------- --------

144 Exterior

137 Interior

2 Boundary

Because the relational operator st_envintersects leverages the spatial index from the point_events table, the result set only contains the point_events geometries of which their envelopes intersect the input polygon_events polygon's envelope. - To compare the spatial relationship of all point_events, not just the geometries for which their envelope intersects the input polygon_events polygon's envelope, one can either drop the point_events spatial index or use Oracle's optimizer hint NO_INDEX in the SQL statement to not leverage the point_events spatial index. If the spatial index is not used as the access path, then every point_events geometry are compared with the input polygon_events polygon.
**Code:**

SQL> SELECT COUNT(*) cnt, relation

2 FROM (SELECT /*+ NO_INDEX(a point_events_shape_idx) */ CASE WHEN sde.st_relate(a.shape, b.shape, 'T********') = 1 THEN 'Interior'

3 WHEN sde.st_relate(a.shape, b.shape, '********T') = 1 THEN 'Exterior'

4 WHEN sde.st_relate(a.shape, b.shape, '****T****') = 1 THEN 'Boundary'

5 ELSE 'Unknown'

6 END "RELATION"

7 FROM point_events a, poly_events b

8 WHERE b.event_name = 'STORM 129'

9 AND sde.st_envintersects(a.shape, b.shape) = 1)

10 GROUP BY relation

11 ORDER BY cnt;

CNT RELATION

---------- --------

5692 Exterior

137 Interior

2 Boundary**Note:**

Because the point_events spatial index is not the access path, all point_event geometries are compared, and the number of geometries that are exterior to the input polygon increases (the number of interior and boundary point_events will be the same).

Get help from ArcGIS experts

Download the Esri Support App