# [postgis-users] Nearest Operator

David Blasby dblasby at refractions.net
Mon May 12 11:49:35 PDT 2003

```>
>
You can approximate the NN operator using the distance() function.
Here's an inefficient example:

SELECT *, distance(the_geom, <REF GEOM>) as dist FROM <table> ORDER BY
dist LIMIT 1;
OR
SELECT * FROM <table> ORDER BY distance(the_geom, <REF GEOM>) LIMIT 1;

ie.  SELECT * FROM thom_20k  ORDER BY distance(the_geom,
'POINT(1319957.155 656951.6198)'::geometry)  LIMIT 1;

Simply, this calculates all the distances between <REF GEOM> and the
geometries in the table.  The results are ordered so that the nearest
geometries are first in the result.  The LIMIT clause only returns the
1st result.  So, you get the closest geometry.   This works in all cases.

Unfortunately, this requires computing the distance() function for every
geometry in the table - this is expensive.

An efficient alternative is to use a magic number - you need to limit
the search to an area around <REF GEOM>.  If you choose a number (area)
too small, you might not find anything in the search area and this will
return an incorrect result.   But, if you know you data well, you should
be able to choose this correctly:

SELECT * FROM <table>
WHERE expand(<REF GEOM>, <magic number>) && the_geom
ORDER BY distance(the_geom, <REF GEOM>) LIMIT 1;

This will use the index to search for an area *around* <REF GEOM> first.
The geometries found will then be passed through the distance()
function, making this quite quick.  If you choose your magic number too
small, you may get incorrect results because  the correct answer will be
thrown out before its even tested.  If you make the magic number too
large, you'll do extra work.

The clause "expand(<REF GEOM>, <magic number>) " makes a bounding box
centred around <REF GEOM> but bigger by <magic number> units in all
directions.

dave

```