20.11 SDO_NN_DISTANCE
Format
SDO_NN_DISTANCE(number);
Description
Returns the distance of an object returned by the SDO_NN operator. Valid only within a call to the SDO_NN operator.
Keywords and Parameters
Value | Description |
---|---|
number |
Specifies a number that must be the same as the last parameter passed to the SDO_NN operator. Data type is NUMBER. |
Returns
This operator returns the distance of an object returned by the SDO_NN operator. In determining how near two geometry objects are, the shortest possible distance between any two points on the surface of each object is used.
Usage Notes
SDO_NN_DISTANCE is an ancillary operator to the SDO_NN operator. It returns the distance between the specified geometry and a nearest neighbor object. This distance is passed as ancillary data to the SDO_NN operator. (For an explanation of how operators can use ancillary data, see the section on ancillary data in Oracle Database Data Cartridge Developer's Guide.)
You can choose any arbitrary number for the number
parameter. The only requirement is that it must match the last parameter in the call to the SDO_NN operator.
Use a bind variable to store and operate on the distance value.
Examples
The following example finds the two objects from the SHAPE column in the COLA_MARKETS table that are nearest to a specified point (10,7), and it finds the distance between each object and the point. (The example uses the definitions and data described and illustrated in Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)
SELECT /*+ INDEX(c cola_spatial_idx) */ c.mkt_id, c.name, SDO_NN_DISTANCE(1) dist FROM cola_markets c WHERE SDO_NN(c.shape, sdo_geometry(2001, NULL, sdo_point_type(10,7,NULL), NULL, NULL), 'sdo_num_res=2', 1) = 'TRUE' ORDER BY dist; MKT_ID NAME DIST ---------- -------------------------------- ---------- 4 cola_d .828427125 2 cola_b 2.23606798
Note the following about this example:
-
1 is used as the
number
parameter for SDO_NN_DISTANCE, and 1 is also specified as the last parameter to SDO_NN (after'sdo_num_res=2'
). -
The column alias
dist
holds the distance between the object and the point. (For geodetic data, the distance unit is meters; for non-geodetic data, the distance unit is the unit associated with the data.)
The following example uses the sdo_batch_size
keyword in selecting the two closest Italian restaurants to your hotel from a YELLOW_PAGES table that contains different types of businesses:
SELECT * FROM (SELECT /*+ FIRST_ROWS */ y.name FROM YELLOW_PAGES y WHERE SDO_NN(y.geometry, :my_hotel, 'sdo_batch_size=100', 1) = 'TRUE' AND y.business = 'Italian Restaurant' ORDER BY SDO_NN_DISTANCE(1)) WHERE ROWNUM <=10;
In the preceding query, the FIRST_ROWS hint enables the optimizer to improve performance by pushing the ORDER BY operation into the spatial index. :my_hotel
can be either a bind variable or a literal value.
The FIRST_ROWS hint is also available to a local partitioned spatial index. In the preceding example, if the YELLOW_PAGES table is partitioned by name, the query will be executed as follows:
-
For each partition, the ORDER BY operation is processed using the spatial index until 10 rows are found.
-
After all partitions are completed, all rows found in the preceding step are sorted, and the top 10 rows are returned.
Related Topics
Parent topic: Spatial Operators