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
The following example demonstrates two potential solutions for writing a SQL statement to identify all parcels that are within a half mile from a given highway, by buffering the input highway segment by 2640 feet and intersecting the output buffer with the parcels.
- 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. - 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.