[postgis-users] Closest point problem

[Thurber, Fred]

I have a database full of point data.  When our user stops at a certain lat/lon, how do I find the closest point in our database?

We have at least 10,000 points, maybe a lot more so the search has to be efficient, but I have not found a way.  It seems a little surprising considering how common this query must be.


The obvious way is to scan the entire table and find the closest point.  Let's say our user stops at (40,70), I could do this:

SELECT * FROM foo
ORDER BY distance(point_geom, GeomFromText('POINT(70.0 40.1)', 4326))
LIMIT 1

When I run this through EXPLAIN ANALYZE, it shows a full-table scan.  Is there a way I can use a GIST or is there some other way to speed it up?

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090804/4c1d61ee/attachment.html>