Perform a query with st_geometry, which is similar to Oracle spatial's sdo_join operator

Summary

Oracle Spatial provides the sdo_join operator (table function) for performing full table spatial joins. The operator's arguments for performing the operation are table_name1, column_name1, table_name2, column_name2, params, and preserve_join_order.

The operator's purpose is to spatially join two input tables based on the params argument, such as anyinteract. Oracle's documentation example uses a table named cola_markets to identify any spatial relationships between objects by specifying the anyinteract mask.

Code:
SQL> SELECT /*+ ordered */ a.name, b.name
  2  FROM TABLE(SDO_JOIN ('COLA_MARKETS', 'SHAPE','COLA_MARKETS', 'SHAPE','mask=ANYINTERACT')) c,
  3  cola_markets a,
  4  cola_markets b
  5  WHERE c.rowid1 = a.rowid AND c.rowid2 = b.rowid
  6  ORDER BY a.name;

NAME      NAME  
------    ------   
cola_a    cola_c
cola_a    cola_b
cola_a    cola_a
cola_b    cola_c
cola_b    cola_b
cola_b    cola_a
cola_c    cola_c
cola_c    cola_b
cola_c    cola_a
cola_d    cola_d

10 rows selected.

Procedure

1. 
   

   Code:
   SQL> SELECT a.location, b.location
     2  FROM busstops a, busstops b
     3  WHERE sde.st_intersects(a.shape, sde.st_buffer(b.shape,500)) = 1
     4  AND a.route = 100 AND b.route = 100
     5  AND a.direction = 1 AND b.direction = 1;
   
   A_LOCATION                              B_LOCATION
   -----------------------------------     -----------------------------------
   PGE Park MAX Station                    PGE Park MAX Station
   PGE Park MAX Station                    Kings Hill/SW Salmon St MAX Station
   Kings Hill/SW Salmon St MAX Station     PGE Park MAX Station
   Kings Hill/SW Salmon St MAX Station     Kings Hill/SW Salmon St MAX Station
   Beaverton TC MAX Station                Beaverton TC MAX Station
   Beaverton Creek MAX Station             Beaverton Creek MAX Station
   Beaverton Central MAX Station           Beaverton Central MAX Station
   ...
   
   54 rows selected.

   
   The results are similar to the results with the cola markets in the Oracle spatial sdo_join example. But, the problem with the result set is each bus stop is returned as an intersection with itself (because the distance between a.busstops and b.busstops is equal to 0 or self-intersecting).
2. To eliminate the self-intersecting rows from the result set, add the additional filter 'a.rowid <> b.rowid' to remove identical rows.
   

   Code:
   A_LOCATION                              B_LOCATION
   -----------------------------------     -----------------------------------
   PGE Park MAX Station                    PGE Park MAX Station
   PGE Park MAX Station                    Kings Hill/SW Salmon St MAX Station
   Kings Hill/SW Salmon St MAX Station     PGE Park MAX Station
   Kings Hill/SW Salmon St MAX Station     Kings Hill/SW Salmon St MAX Station
   Beaverton TC MAX Station                Beaverton TC MAX Station
   Beaverton Creek MAX Station             Beaverton Creek MAX Station
   Beaverton Central MAX Station           Beaverton Central MAX Station
   ...
   
   54 rows selected.

   
   Also, to further refine the result set to eliminate the reverse pair values (the rows in bold above), include a filter to specify the b.stop must be greater than the a.stop value. Because each b.shape geometry is used as an input to the st_buffer/st_intersects operators, any two stops that are within the 500 units are returned twice - once for the first input b.shape and again when the next geometry is passed into the operator that intersects the previous input b.shape.
   
   Finally, include the st_distance function to list the actual distance between stops and return the result set in ascending distance order.
   
   

   Code:
   SQL> SELECT a.location, b.location, sde.st_distance(a.shape,b.shape) DISTANCE
     2  FROM busstops a, busstops b
     3  WHERE sde.st_intersects(a.shape, sde.st_buffer(b.shape,500)) = 1
     4  AND a.route = 100 AND b.route = 100
     5  AND a.direction = 1 AND b.direction = 1
     6  AND a.rowid <> b.rowid
     7  AND b.stop > a.stop
     8  ORDER BY distance;
   
   LOCATION                                LOCATION                              DISTANCE
   --------------------------------------- ------------------------------------- --------
   Lloyd Center/Dbltree & Vintage Trolley  Lloyd Center/NE 11th Ave MAX Station  378.53
   PGE Park MAX Station                    Kings Hill/SW Salmon St MAX Station   448.90

   
   The final result now only returns the two bus stop locations that meet the criteria of being located within 500 feet from each location.
   
   

   Note:
   With st_geometry attributes, there are no limitions as there are when using Oracle's sdo_join operator. The input tables do not require a spatial index, the tables can be partitioned, and any combination of entity types can be used as input and can be different spatial references.