SDO_GEOM.SDO_CLOSEST

Format

SDO_GEOM.SDO_CLOSEST_POINTS(     
  geom1          IN SDO_GEOMETRY,      
  geom2          IN SDO_GEOMETRY,      
  tolerance      IN NUMBER,       
  unit           IN VARCHAR2      
  [, ellipsoidal IN VARCHAR2 DEFAULT NULL]
) RETURN SDO_CLOSEST_POINTS_TYPE;

Description

Returns an object containing the computed minimum distance between two geometries and the points (one on each geometry) that are minimum distance apart.

Parameters

geom1

Geometry object.

geom2

Geometry object.

tolerance

Tolerance value (see Tolerance).

unit

Unit of measurement: a quoted string with unit= and an SDO_UNIT value from the MDSYS.SDO_DIST_UNITS table (for example, 'unit=KM'). See Unit of Measurement Support for more information about unit of measurement specification.

If this parameter is not specified, the unit of measurement associated with the data is assumed.

ellipsoidal

Specifies if ellipsoidal distance is always used with geodetic data (true), or if spherical distance is used in some cases (false, the default). See Distance: Spherical versus Ellipsoidal with Geodetic Data.

Usage Notes

This function returns an output object of type SDO_CLOSEST_POINTS_TYPE, that contains the computed minimum distance (DIST) and the output point geometries (GEOMA and GEOMB) associated with the minimum distance. Oracle Spatial defines the object type SDO_CLOSEST_POINTS_TYPE as:

CREATE TYPE sdo_closest_points_type AS OBJECT (
    dist   NUMBER,
    geoma  SDO_GEOMETRY,
    geomb  SDO_GEOMETRY
);

If the distance between the two points is 0 (zero), the output geometries (GEOMA and GEOMB) will be as follows:

For two-dimensional (2D) geometries, if one of the input geometries is a point geometry, each output geometry is that point; otherwise, each output geometry is the first point in the first element of the intersection of the input geometries.
For three-dimensional (3D) geometries, if one of the input geometries is a point geometry, each output geometry is that point; otherwise, the output geometries are null.

An exception is raised if geom1 and geom2 are based on different coordinate systems.

If the input data is three-dimensional and geodetic, a 3D SRID must be used for the geometries; otherwise, the results will be incorrect.

Examples

The following example computes the minimum distance between geometries cola_c and cola_d, as well as the one point on each input geometry associated with the minimum distance.

The resulting SDO_CLOSEST_POINTS_TYPE object, shows the minimum distance of 2.47213595 between the two input geometries along with the two output point geometries. The closest point on cola_c is at (6,5), and the closest point on cola_d is at (7.10557281, 7.21114562). (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

SELECT sdo_geom.sdo_closest_points (c1.shape, c2.shape, 0.5, null) cp
FROM cola_markets c1, cola_markets c2
WHERE c1.name = 'cola_c'
AND c2.name = 'cola_d';

CP(DIST, GEOMA(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES), GEOMB(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES))
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_CLOSEST_POINTS_TYPE(2.47213595, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(6, 5, NULL), NULL, NULL), SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(7.10557281, 7.21114562, NULL), NULL, NULL))