HOW TO
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.
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
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
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
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
Article ID: 000010884
Get help from ArcGIS experts
Download the Esri Support App