Description

The <-> operator returns the 2D distance between the centroids of the bounding boxes of two geometries. Useful for doing nearest neighbor approximate distance ordering.

	This operand will make use of 2D GiST indexes that may be available on the geometries. It is different from other operators that use spatial indexes in that the spatial index is only used when the operator is in the ORDER BY clause.

	Index only kicks in if one of the geometries is a constant (not in a subquery/cte). e.g. 'SRID=3005;POINT(1011102 450541)'::geometry instead of a.geom

Refer to OpenGeo workshop: Nearest-Neighbour Searching for real live example.

Availability: 2.0.0 -- KNN only available for PostgreSQL 9.1+

Examples

SELECT ST_Distance(geom, 'SRID=3005;POINT(1011102 450541)'::geometry) as d,edabbr, vaabbr 
FROM va2005 
ORDER BY d limit 10;

        d         | edabbr | vaabbr
------------------+--------+--------
                0 | ALQ    | 128
 5541.57712511724 | ALQ    | 129A
 5579.67450712005 | ALQ    | 001
  6083.4207708641 | ALQ    | 131
  7691.2205404848 | ALQ    | 003
 7900.75451037313 | ALQ    | 122
 8694.20710669982 | ALQ    | 129B
 9564.24289057111 | ALQ    | 130
  12089.665931705 | ALQ    | 127
 18472.5531479404 | ALQ    | 002
(10 rows)

Then the KNN raw answer:

SELECT st_distance(geom, 'SRID=3005;POINT(1011102 450541)'::geometry) as d,edabbr, vaabbr 
FROM va2005 
ORDER BY geom <-> 'SRID=3005;POINT(1011102 450541)'::geometry limit 10;

        d         | edabbr | vaabbr
------------------+--------+--------
                0 | ALQ    | 128
 5579.67450712005 | ALQ    | 001
 5541.57712511724 | ALQ    | 129A
 8694.20710669982 | ALQ    | 129B
 9564.24289057111 | ALQ    | 130
  6083.4207708641 | ALQ    | 131
  12089.665931705 | ALQ    | 127
  24795.264503022 | ALQ    | 124
 24587.6584922302 | ALQ    | 123
 26764.2555463114 | ALQ    | 125
(10 rows)

Note the misordering in the actual distances and the different entries that actually show up in the top 10.

Finally the hybrid:

WITH index_query AS (
  SELECT ST_Distance(geom, 'SRID=3005;POINT(1011102 450541)'::geometry) as d,edabbr, vaabbr
  	FROM va2005
  ORDER BY geom <-> 'SRID=3005;POINT(1011102 450541)'::geometry LIMIT 100) 
  SELECT * 
  	FROM index_query 
  ORDER BY d limit 10;

        d         | edabbr | vaabbr
------------------+--------+--------
                0 | ALQ    | 128
 5541.57712511724 | ALQ    | 129A
 5579.67450712005 | ALQ    | 001
  6083.4207708641 | ALQ    | 131
  7691.2205404848 | ALQ    | 003
 7900.75451037313 | ALQ    | 122
 8694.20710669982 | ALQ    | 129B
 9564.24289057111 | ALQ    | 130
  12089.665931705 | ALQ    | 127
 18472.5531479404 | ALQ    | 002
(10 rows)

 			

See Also

ST_DWithin, ST_Distance, <#>