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

Immutable

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

Data Type GEOMETRY GEOGRAPHY (Perfect Sphere)
Point

Yes

Yes

Multipoint

Yes

Yes

Linestring

Yes

Yes

Multilinestring

Yes

Yes

Polygon

Yes

Yes

Multipolygon

Yes

Yes

GeometryCollection

Yes

No

Example

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)