# 23.7 SDO_GEOM.SDO_CLOSEST_POINTS

Format

```SDO_GEOM.SDO_CLOSEST_POINTS(
geom1     IN SDO_GEOMETRY,
geom2     IN SDO_GEOMETRY,
tolerance IN NUMBER,
unit      IN VARCHAR2,
dist      OUT NUMBER,
geoma     OUT SDO_GEOMETRY,
geomb     OUT SDO_GEOMETRY);
```

Description

Computes the minimum distance between two geometries and the points (one on each geometry) that are the 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.

dist

Output parameter containing the minimum distance between `geom1` and `geom2`. Specifically, the distance between `geoma` and `geomb`.

geoma

Output parameter containing the point geometry object on the boundary of `geom1` that is closest to the closest point on the boundary of `geom2`.

geomb

Output parameter containing the point geometry object on the boundary of `geom2` that is closest to the closest point on the boundary of `geom1`.

Usage Notes

This procedure uses output parameters to store the computed minimum distance and the point on each input geometry associated with the minimum distance.

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. It also inserts the two output point geometries into the table and then selects these point geometries. The minimum distance between the two input geometries is 2.47213595499958, 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.)

```DECLARE
cola_c_geom SDO_GEOMETRY;
cola_d_geom SDO_GEOMETRY;
dist NUMBER;
geoma SDO_GEOMETRY;
geomb SDO_GEOMETRY;

BEGIN

-- Populate geometry variables with cola market shapes.
SELECT c.shape into cola_c_geom FROM cola_markets c
WHERE c.name = 'cola_c';
SELECT c.shape into cola_d_geom FROM cola_markets c
WHERE c.name = 'cola_d';

SDO_GEOM.SDO_CLOSEST_POINTS(cola_c_geom, cola_d_geom, 0.005, NULL,
dist, geoma, geomb);

INSERT INTO cola_markets VALUES(9901, 'geoma', geoma);
INSERT INTO cola_markets VALUES(9902, 'geomb', geomb);

DBMS_OUTPUT.PUT_LINE('dist output parameter value = ' || dist);
END;
/
dist output parameter value = 2.47213595499958

PL/SQL procedure successfully completed.

SELECT c.shape FROM cola_markets c WHERE c.name = 'geoma';

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(
6, 5))

SELECT c.shape FROM cola_markets c WHERE c.name = 'geomb';

SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(
7.10557281, 7.21114562))
```

Related Topics

None.