English

How To: 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.

A similar query achieving the same results can be executed when the data is stored using st_geometry and using the st_intersects and st_buffer operators.

Procedure

The following example demonstrates how to execute a SQL statement in SQL*Plus, which performs a full table join to discover the spatial relationship between objects in the same table.

In this example, similar to the Oracle spatial example with cola markets, the objective is to discover all bus stops that are within 500 feet from each other for a specified bus route in one direction.


  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.