STV_NN

Calculates the distance of spatial objects from a reference object and returns (object, distance) pairs in ascending order by distance from the reference object.

Parameters g1 and g2 must be both GEOMETRY objects or both GEOGRAPHY objects.

STV_NN is an analytic function. For more information, see Analytic functions.

Behavior type

Syntax

STV_NN( g, ref_obj, k ) OVER()

Arguments

g

Spatial object, value of type GEOMETRY or GEOGRAPHY

ref_obj

Reference object, type GEOMETRY or GEOGRAPHY

k

Number of rows to return, type INTEGER

Returns

(Object, distance) pairs, in ascending order by distance. If a parameter is EMPTY or NULL, then 0 rows are returned.

Supported data types

Examples

The following example shows how to use STV_NN.

Create a table and insert nine GEOGRAPHY points:

=> CREATE TABLE points (g geography);
CREATE TABLE
=> COPY points (gx filler LONG VARCHAR, g AS ST_GeographyFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> POINT (21.5 18.4)
>> POINT (21.5 19.2)
>> POINT (21.5 20.7)
>> POINT (22.5 16.4)
>> POINT (22.5 17.15)
>> POINT (22.5 18.33)
>> POINT (23.5 13.68)
>> POINT (23.5 15.9)
>> POINT (23.5 18.4)
>> \.

Calculate the distances (in meters) of objects in table points from the GEOGRAPHY point (23.5, 20).

Returns the five objects that are closest to that point:

=> SELECT ST_AsText(nn), dist FROM (SELECT STV_NN(g,
   ST_GeographyFromText('POINT(23.5 20)'),5) OVER() AS (nn,dist) FROM points) AS example;
     ST_AsText      |       dist
--------------------+------------------
 POINT (23.5 18.4)  |  177912.12757541
 POINT (22.5 18.33) | 213339.210738322
 POINT (21.5 20.7)  |  222561.43679943
 POINT (21.5 19.2)  | 227604.371833335
 POINT (21.5 18.4)  | 275239.416790128
(5 rows)