[postgis-users] Nearest Operator

[David Blasby]

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