PostGIS Nearest Neighbor Syntax

It turns out that it is possible to get an indexed n-nearest-neighbor (KNN) search out of PostGIS along with a distance using only one distance calculation and one target literal.

SELECT id, $point <-> geom AS distance
FROM geoms
ORDER BY distance
LIMIT 1

See that?!? Using the column-name syntax for ORDER BY, the <-> operator pulls double duty, both returning the distance to the target list and also forcing an index-assisted KNN ordering.

It's slightly more subtle because our query is finding the nearest object and its distance:
`SELECT id, $point <-> geom
FROM geoms
WHERE foo
ORDER BY 2
LIMIT 1`
It's in the order by clause but we're also returning the distance as a by-product (and taking advantage of the index)

— Joel Haasnoot (@webguy) December 16, 2021

I never considered this possibility until seeing it in this tweet. Before I would have been doing this:

SELECT id, ST_Distance($point, geom) AS distance
FROM geoms
ORDER BY $point <-> geom
LIMIT 1

Two distance calculations (one in the function, one in the operator) and two references to the literal. Yuck!