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.