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

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