20.10 SDO_NN
Format
SDO_NN(geometry1, geometry2, param [, number]);
Description
Identifies the nearest neighbors for a geometry.
Keywords and Parameters
| Value | Description |
|---|---|
|
geometry1 |
Specifies a geometry column in a table. A spatial index on this column is required. Data type is SDO_GEOMETRY. |
|
geometry2 |
Specifies either a geometry from a table or a transient instance of a geometry. The nearest neighbor or neighbors to |
|
param |
Determines the behavior of the operator. The available keywords are listed in Table 20-4. If you do not specify this parameter, the operator returns all rows in increasing distance order from |
|
number |
If the SDO_NN_DISTANCE ancillary operator is included in the call to SDO_NN, specifies the same number used in the call to SDO_NN_DISTANCE. Data type is NUMBER. |
Table 20-4 lists the keywords for the param parameter.
Table 20-4 Keywords for the SDO_NN Param Parameter
| Keyword | Description |
|---|---|
|
distance |
Specifies the number of distance units after which to stop searching for nearest neighbors. If you do not also specify the For example: 'distance=10 unit=mile' |
|
ellipsoidal |
Specifies if ellipsoidal distance is always used with geodetic data ( For example: 'ellipsoidal=true' |
|
sdo_batch_size |
Specifies the number of rows to be evaluated at a time when the SDO_NN expression may need to be evaluated multiple times in order to return the desired number of results that satisfy the WHERE clause. Available only when an R-tree index is used. If you specify For example: 'sdo_batch_size=10' |
|
sdo_num_res |
Specifies the number of results (nearest neighbors) to be returned. If neither For example: 'sdo_num_res=5' |
|
unit |
If the For example: 'unit=KM' |
Returns
This operator returns the sdo_num_res number of objects from geometry1 that are nearest to geometry2 in the query. 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
Note:
SDO_NNoperator is not supported for 3D data in Oracle Autonomous Database Serverless deployments.
The operator is disabled if the table does not have a spatial index or if the number of dimensions for the query window does not match the number of dimensions specified when the index was created.
The operator must always be used in a WHERE clause, and the condition that includes the operator should be an expression of the form SDO_NN(arg1, arg2, '<some_parameter>') = 'TRUE'. (The expression must not equate to any value other than 'TRUE'.)
The operator can be used in the following ways:
-
If all geometries in the layer are candidates, use the
sdo_num_reskeyword to specify the number of geometries returned.The
sdo_num_reskeyword is especially useful when you are concerned only with proximity (for example, the three closest banks, regardless of bank name). -
If any geometries in the table might be nearer than the geometries specified in the WHERE clause, use the
sdo_batch_sizekeyword and use the WHERE clause (including the ROWNUM pseudocolumn) to limit the number of geometries returned.The
sdo_batch_sizekeyword is especially useful when you need to consider one or more columns from the same table as the nearest neighbor search column in the WHERE clause (for example, the three closest banks whose name contains MegaBank). -
You can also specify both the
sdo_num_resandsdo_batch_sizekeywords, as explained later in these Usage Notes.
As an example of the sdo_batch_size keyword, assume that a RESTAURANTS table contains different types of restaurants, and you want to find the two nearest Italian restaurants to your hotel but only if they are within two miles. The query might look like the following:
SELECT r.name FROM restaurants r WHERE
SDO_NN(r.geometry, :my_hotel,
'sdo_batch_size=10 distance=2 unit=mile') = 'TRUE'
AND r.cuisine = 'Italian' AND ROWNUM <=2;
In this example, the ROWNUM <=2 clause is necessary to limit the number of results returned to no more than 2 where CUISINE is Italian. However, if the sdo_batch_size keyword is not specified in this example, and if sdo_num_res=2 is specified instead of ROWNUM <=2, only the two nearest restaurants within two miles are considered, regardless of their CUISINE value; and if the CUISINE value of these two rows is not Italian, the query may return no rows.
The sdo_batch_size value can affect the performance of nearest neighbor queries. A good general guideline is to specify the number of candidate rows likely to satisfy the WHERE clause. Using the preceding example of a query for Italian restaurants, if approximately 20 percent of the restaurants nearest to the hotel are Italian and if you want 2 restaurants, an sdo_batch_size value of 10 will probably result in the best performance. On the other hand, if only approximately 5 percent of the restaurants nearest to the hotel are Italian and if you want 2 restaurants, an sdo_batch_size value of 40 would be better.
You can specify sdo_batch_size=0, which causes Spatial to calculate a batch size that is suitable for the result set size. However, the calculated batch size may not be optimal, and the calculation incurs some processing overhead; if you can determine a good sdo_batch_size value for a query, the performance will probably be better than if you specify sdo_batch_size=0.
Specify the number parameter only if you are using the SDO_NN_DISTANCE ancillary operator in the call to SDO_NN. See the information about the SDO_NN_DISTANCE operator in this chapter.
If two or more objects from geometry1 are an equal distance from geometry2, any of the objects can be returned on any call to the function. For example, if item_a, item_b, and item_c are nearest to and equally distant from geometry2, and if sdo_num_res=2, two of those three objects are returned, but they can be any two of the three.
If the SDO_NN operator uses a partitioned spatial index (see Using Partitioned Spatial Indexes), the requested number of geometries is returned for each partition that contains candidate rows based on the query criteria. For example, if you request the 5 nearest restaurants to a point and the spatial index has 4 partitions, the operator returns up to 20 (5*4) geometries. In this case, you must use the ROWNUM pseudocolumn (here, WHERE ROWNUM <=5) to return the 5 nearest restaurants.
If geometry1 and geometry2 are based on different coordinate systems, geometry2 is temporarily transformed to the coordinate system of geometry1 for the operation to be performed, as described in Different Coordinate Systems for Geometries with Operators and Functions.
SDO_NN is not supported for spatial joins.
In some situations the SDO_NN operator will not use the spatial index unless an optimizer hint forces the index to be used. This can occur when a query involves a join; and if the optimizer hint is not used in such situations, an internal error occurs. To prevent such errors, you should always specify an optimizer hint to use the spatial index with the SDO_NN operator, regardless of how simple or complex the query is. For example, the following excerpt from a query specifies to use the COLA_SPATIAL_IDX index that is defined on the COLA_MARKETS table:
SELECT /*+ INDEX(c cola_spatial_idx) */ c.mkt_id, c.name, ... FROM cola_markets c, ...;
However, if the column predicate in the WHERE clause specifies any nonspatial column in the table for geometry1 that has an associated index, be sure that this index is not used by specifying the NO_INDEX hint for that index. For example, if there was an index named COLA_NAME_IDX defined on the NAME column, you would need to specify the hints in the preceding example as follows:
SELECT /*+ INDEX(c cola_spatial_idx) NO_INDEX(c cola_name_idx) */ c.mkt_id, c.name, ... FROM cola_markets c, ...;
(Note, however, that there is no index named COLA_NAME_IDX in the example in Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)
If you join two or more tables with the SDO_NN operator and the sdo_num_res keyword, specify the LEADING hint for the outer table, USE_NL hint to have a nested loops join, and the INDEX hint for the inner table (the table with the spatial index). For example:
SELECT /*+ LEADING(b) USE_NL(b a) INDEX(a cola_spatial_idx) */ a.gid
FROM cola_qry b, cola_markets a
WHERE SDO_NN(a.shape, b.shape, 'sdo_num_res=1')='TRUE';
However, if you join two or more tables with the SDO_NN operator, the sdo_batch_size keyword, and the ROWNUM clause, the best way to implement the logic is to use a PL/SQL block. For example:
BEGIN
FOR item IN ( SELECT b.shape FROM cola_qry b)
LOOP
SELECT /*+ INDEX(a cola_spatial_idx) */ a.gid INTO local_gid
FROM cola_markets a
WHERE SDO_NN(a.shape, item.shape, 'sdo_batch_size=10')='TRUE'
and a.name like 'cola%' and ROWNUM <2;
END LOOP;
END;
For detailed information about using optimizer hints, see Oracle Database SQL Tuning Guide.
For information about 3D support with spatial operators (which operators do and do not consider all three dimensions in their computations), see Three-Dimensional Spatial Objects.
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). (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 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') = 'TRUE';
MKT_ID NAME
---------- --------------------------------
2 cola_b
4 cola_d
The following example uses the sdo_batch_size keyword to find the two objects (ROWNUM <=2), with a NAME value less than 'cola_d', from the SHAPE column in the COLA_MARKETS table that are nearest to a specified point (10,7). The value of 3 for sdo_batch_size represents a best guess at the number of nearest geometries that need to be evaluated before the WHERE clause condition is satisfied. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)
SELECT /*+ INDEX(c cola_spatial_idx) */ c.mkt_id, c.name
FROM cola_markets c
WHERE SDO_NN(c.shape, sdo_geometry(2001, NULL,
sdo_point_type(10,7,NULL), NULL, NULL),
'sdo_batch_size=3') = 'TRUE'
AND c.name < 'cola_d' AND ROWNUM <= 2;
MKT_ID NAME
---------- --------------------------------
2 cola_b
3 cola_c
See also the more complex SDO_NN examples in SDO_NN Examples.
Related Topics
Parent topic: Spatial Operators