The SDO_WITHIN_DISTANCE operator identifies the set of spatial objects that are within some specified distance of a given object. You can indicate that the distance is approximate or exact. If you specify querytype=FILTER, the distance is approximate because only a primary filter operation is performed; otherwise, the distance is exact because both primary and secondary filtering operations are performed.
Example C-1 Finding All Cities Within a Distance of a Highway
Example C-1 finds all cities within 15 miles of the interstate highway I170.
SELECT /*+ ORDERED */ c.city
FROM geod_interstates i, geod_cities c
WHERE i.highway = 'I170'
AND sdo_within_distance (
c.___location, i.geom,
'distance=15 unit=mile') = 'TRUE';
Example C-1 finds all cities within 15 miles ('distance=15 unit=mile') of the specified highway (i.highway = 'I170'), and by default the result is exact (because the querytype parameter was not used to limit the query to a primary filter operation). In the WHERE clause of this example:
i.highway refers to the HIGHWAY column of the INTERSTATES table, and I170 is a value from the HIGHWAY column.
c.___location specifies the search column (geometry1). This is the LOCATION column of the GEOD_CITIES table.
i.geom specifies the query window (aGeom). This is the spatial geometry in the GEOM column of the GEOD_INTERSTATES table, in the row whose HIGHWAY column contains the value I170.
Example C-2 Finding All Highways Within a Distance of a City
Example C-2 finds all interstate highways within 15 miles of the city of Tampa.
SELECT /*+ ORDERED */ i.highway
FROM geod_cities c, geod_interstates i
WHERE c.city = 'Tampa'
AND sdo_within_distance (
i.geom, c.___location,
'distance=15 unit=mile') = 'TRUE';
Example C-2 finds all highways within 15 miles ('distance=15 unit=mile') of the specified city (c.city = 'Tampa'), and by default the result is exact (because the querytype parameter was not used to limit the query to a primary filter operation). In the WHERE clause of this example:
c.city refers to the CITY column of the GEOD_CITIES table, and Tampa is a value from the CITY column.
i.geom specifies the search column (geometry1). This is the GEOM column of the GEOD_INTERSTATES table.
c.___location specifies the query window (aGeom). This is the spatial geometry in the LOCATION column of the GEOD_CITIES table, in the row whose CITY column contains the value Tampa.