[postgis-users] Nearest Operator

[Roy Irwan]

I prefer to use the first way, even there is a performance issue here. Does
distance function use spatial index?

The search starting point for my app could be anywhere, and the radius may
vary depending on 'other' conditions in the query and the data it self, so I
can't use the 'magic number'.

As I mention before, the NN opr in Oracle can stop the searching if the
number of the result that we want meet.  I hope (and I'm sure) someday
PostGIS also have something similiar even now it's still far away from the
top list of priority. :)

Thank's
Roy

----- Original Message -----
From: "David Blasby" <dblasby at ref...>
Sent: Tuesday, May 13, 2003 1:49 AM
> >
> >
> 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