HOW TO

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

Last Published: April 25, 2020

## 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                           IndianaInterstate  65                           IndianaInterstate  69                           IndianaInterstate  70                           IndianaInterstate  70                           OhioInterstate  71                           OhioInterstate  74                           IndianaInterstate  74                           OhioInterstate  75                           OhioInterstate  76                           OhioInterstate  77                           OhioInterstate  80                           IndianaInterstate  80                           OhioInterstate  90                           IndianaInterstate  90                           OhioInterstate  94                           IndianaInterstate 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  70Interstate  71Interstate  74Interstate  75Interstate  76Interstate  77Interstate  80Interstate  90Interstate 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  64Interstate  65Interstate  69Interstate  70Interstate  74Interstate  80Interstate  90Interstate  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  70Interstate  74Interstate  80Interstate  90`

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

Article ID:000010884

Software:
• Legacy Products

Get summarized answers and video solutions from our new AI chatbot.