English

How To: Select distinct geometries based on multiple predicate values and a spatial relationship with st_geometry in Oracle

Summary

The following procedure provides an example of how to select a set of distinct objects that have a spatial relationship between two tables and a predicate filter based on multiple input values.

Procedure

Question: What interstate highways intersect both Indiana and Ohio?

How to answer this question using SQL may appear to be trivial, but it turns out the SQL is not as intuitive as may be assumed.

If there are two tables, one representing all the major interstates and the second representing the United States, we could execute the following SQL statement to identify which interstates intersect Indiana and Ohio.

Code:
SQL> SELECT DISTINCT a.route, b.state_name
FROM interstates a, states b
WHERE sde.st_intersects(a.shape,b.shape) = 1
AND b.state_name IN ('Indiana','Ohio')
ORDER BY 1;

ROUTE STATE_NAME
---------------------------------------- -------------
Interstate 64 Indiana
Interstate 65 Indiana
Interstate 69 Indiana
Interstate 70 Indiana
Interstate 70 Ohio
Interstate 71 Ohio
Interstate 74 Indiana
Interstate 74 Ohio
Interstate 75 Ohio
Interstate 76 Ohio
Interstate 77 Ohio
Interstate 80 Indiana
Interstate 80 Ohio
Interstate 90 Indiana
Interstate 90 Ohio
Interstate 94 Indiana
Interstate 271 Ohio


The above results include every interstate that intersects either state. Based upon the question being asked, this is not the correct result. The question is specifically asking which interstates intersect both Indiana and Ohio, not one or the other.

  • To identify which interstates specifically intersect Ohio, execute the following SQL statement.

    Code:
    SQL> SELECT DISTINCT a.route
    FROM interstates a, states b
    WHERE sde.st_intersects(a.shape,b.shape) = 1
    AND b.state_name = 'Ohio'
    ORDER BY 1;

    ROUTE
    -------------------
    Interstate 70
    Interstate 71
    Interstate 74
    Interstate 75
    Interstate 76
    Interstate 77
    Interstate 80
    Interstate 90
    Interstate 271

  • To identify the interstates that intersect Indiana, one would execute the following SQL statement.

    Code:
    SQL> SELECT DISTINCT a.route
    FROM interstates a, states b
    WHERE sde.st_intersects(a.shape,b.shape) = 1
    AND b.state_name = 'Indiana'
    ORDER BY 1;

    ROUTE
    ----------------------
    Interstate 64
    Interstate 65
    Interstate 69
    Interstate 70
    Interstate 74
    Interstate 80
    Interstate 90
    Interstate 94

  • To select the interstates that intersect both states, one must add the SQL INTERSECT operator between each of the above statements to answer the question of which interstates intersect both states.

    Code:
    SQL> SELECT DISTINCT a.route
    FROM interstates a, states b
    WHERE sde.st_intersects(a.shape,b.shape) = 1
    AND b.state_name = 'Ohio'
    INTERSECT
    SELECT DISTINCT a.route
    FROM interstates a, states b
    WHERE sde.st_intersects(a.shape,b.shape) = 1
    AND b.state_name = 'Indiana'

    ROUTE
    -------------------
    Interstate 70
    Interstate 74
    Interstate 80
    Interstate 90


    The results now correctly represent which interstates intersect both Indiana and Ohio.