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
Geometry object.
Geometry object.
Tolerance value (see Tolerance).
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.
Output parameter containing the minimum distance between geom1 and geom2. Specifically, the distance between geoma and geomb.
Output parameter containing the point geometry object on the boundary of geom1 that is closest to the closest point on the boundary of geom2.
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.