[postgis-users] Finding closest point to a given point

[Adrian Holovaty]

Hello,

I have a point (longitude/latitude), and I want to find the closest point to 
it, from my DB table, that matches given criteria. I tried this (assuming the 
point is (-80, 45)) --

SELECT the_point
FROM foo
WHERE some_criteria='t'
ORDER BY distance(the_point, GeomFromText('POINT(-80 45)', -1))
LIMIT 1;

...but it's slow, because the database has to calculate the distance to each 
other point in the table and cannot use the index, as pointed out in the 
docs: http://postgis.refractions.net/docs/ch04.html#id3213331

Following those docs, I added a bounding box to reduce the number of distance 
calculations required.

SELECT the_point
FROM foo
WHERE some_criteria='t'
AND the_point && 'BOX3D(-180 -55, 20 145)'::box3d
ORDER BY distance(the_point, GeomFromText('POINT(-80 45)', -1))
LIMIT 1;

It's faster, but it's still slow because the table has 20,000+ records.

Note that the point (-80 45) is variable. I can't just create an index on a 
custom function that calculates the distance() to that point.

Is there a more efficient way to do this, which doesn't involve putting 
distance() in the ORDER BY clause?

This seems like a FAQ, but I can't find any good solutions in the list 
archives, or on Google. Thanks in advance!

Adrian