 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).`

Article ID:000010364

Software:
• Legacy Products

Get help from ArcGIS experts

Contact technical support