Leverage the st_geom_union operator in Oracle to improve performance

Summary

Instructions provided demonstrate how to leverage the sde.st_geom_union operator in Oracle to improve a SQL statement's performance, when performing the combination of st_buffer and st_intersects in Oracle.

Procedure

1. The first example is the logical approach for writing a SQL statement to answer the question. The SQL statement is composed by specifying the parcels and major_roads tables, specifying an attribute filter where major_roads are named 'I-75', and performing a spatial intersects relation to identify which parcels intersect the half mile buffer around the highway line segments.
   
   

   Code:
   SQL> SELECT COUNT(DISTINCT parcels.parcel_id) 
     2  FROM parcels, major_roads
     3  WHERE major_roads.name = 'I-75'
     4  AND sde.st_intersects(parcels.shape,sde.st_buffer(major_roads.shape,2640)) = 1;
   
   COUNT(p.parcelid)
   -----------------
              106955
   
   Elapsed: 00:01:15.83

   
   The elapsed time to execute the query was 1 minute and 15 seconds.
   
   The majority of this time was spent in the spatial relation step, buffering each individual major_roads segment, where name equaled 'I-75' and then performing the intersection against the parcels table. The individual cost of performing each step over and over consumes the vast amount of elapsed time.
2. The second example demonstrates how to write a more efficient query to avoid the cost of buffering and intersecting each individual major_roads segment. By using a nested query and the sde.st_aggr_union operator, the nested query can obtain each major_roads segment, aggregate the individual geometries (shapes) into one geometry, and pass the individual geometry to the outer select statement to be buffered and intersected against the parcels once.
   
   

   Code:
   SQL> SELECT COUNT(p.parcel_id)
     2  FROM parcels p, 
     3   (SELECT sde.st_aggr_union(shape) shape
     4    FROM major_roads 
     5    WHERE name = 'I-75') r
     6  WHERE sde.st_intersects(a.shape,sde.st_buffer(r.shape,2640)) = 1
   
   COUNT(p.parcelid)
   -----------------
              106955
   
   Elapsed: 00:00:19.09

   
   With this optimization, the query is now able to be performed in under 20 seconds to return over 100,000 parcels.
   
   Executing the nested query by itself shows the st_aggr_union operator only requires 3 seconds to perform the aggregation.
   
   

   Code:
   SQL> SELECT COUNT(sde.st_aggr_union(shape)) GEOMS
     2  FROM major_roads 
     3  WHERE name = 'I-75';
   
        GEOMS
   ----------
            1
   
   Elapsed: 00:00:03.05

   
   The time to perform the aggregation is dependent on the number of input geometries to the operator.