English

# How To: Report which geometries are interior, exterior, and boundary relations

## Summary

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.

## Procedure

The example below demonstrates the SQL syntax for returning the relation between two geometry attributes. The SQL statement returns the spatial relationship between all the point_events geometries for which their envelopes intersect the polygon from the polygon_events table, whose event_name equals STORM 129.

1. 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.
2. 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).