ST_NearestPoints function

The ST_NearestPoints function takes a curve or multicurve and a point as input parameters and returns the point on the curve or multicurve that is closest to the point that was specified as input.

If two or more such points are equidistant from the point that was specified as input, they are all returned.

If the curve or multicurve that was specified as input has Z or M coordinates, the Z or M coordinate of the resulting points are computed by interpolation on the specified curve or multicurve.

If the curve or point is empty, then an empty point is returned. If the curve or point that was specified as input is null , then null is returned.

This function can also be called as a method.

Syntax

Parameters

curve: A value of type ST_Curve, ST_MultiCurve, or one of their subtypes that represents the curve or multicurve to which the point closest to the point is returned.
point: A value of type ST_Point that represents the point from which the nearest point on the curve is to be found.

Return type

db2gse.ST_MultiPoint

Examples

The following examples illustrate the use of the ST_NearestPoint function. The following statements show how to set up the function path to use Spatial Extender functions, create a table, and use the ST_LineString function in the INSERT statement to create the linestring that is used in the examples.

SET CURRENT FUNCTION PATH=CURRENT FUNCTION PATH, db2gse
CREATE TABLE sample_lines (id INTEGER, line ST_LineString)

INSERT INTO sample_lines (id, line)
  VALUES (1, ST_LineString('linestring (0 10, 0 0, 10 0, 10 10)' , 0) )

Example 1

This example finds the nearest point on the linestring to the point with coordinates (12,12). In this case, the nearest point is the end point of the linestring. The ST_AsText function is used to convert the returned value (a multipoint) to its well-known text representation.

SELECT 
   VARCHAR(ST_AsText(ST_NearestPoints(line,ST_Point(12,12,0))), 50) NEAREST
  FROM sample_lines

The following text shows the results from the query:

NEAREST
-----------------
MULTIPOINT(10 10)

The following figure shows the nearest point on the linestring to the point with coordinates (12,12):

Diagram of the nearest point on the linestring to the point with coordinates (12,12)/ — Figure 1. Nearest point on the linestring to the point with coordinates (12,12).

Example 2

This example finds the nearest points on the linestring to the point with coordinates (10,0). In this case, the nearest point is at the middle of the linestring. The ST_NearestPoint function always returns an ST_MultiPoint geometry even when there is only a single point. An application often knows that only a single point is returned or is only interested in the first point. The ST_Point value can be extracted by using the ST_PointN function to get the first point.

SELECT 
  VARCHAR(ST_AsText(
    ST_PointN(ST_NearestPoints(line,ST_Point(10,0)),1)
  ), 50) NEAREST
 FROM sample_lines

The following text shows the results from the query:

NEAREST
----------
POINT(5 5)

The following figure shows the nearest point on the linestring to the point with coordinates (10,0):

Diagram of the nearest point on the linestring to the point with coordinates (10,0). — Figure 2. Nearest point on the linestring to the point with coordinates (10,0).