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
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)