[postgis-users] Closest point problem

[Paul Ramsey]

SELECT * FROM foo
WHERE ST_DWithin(point_geom, GeomFromText('POINT(70.0 40.1)', 4326), $SEMD)
ORDER BY ST_Distance(point_geom, GeomFromText('POINT(70.0 40.1)', 4326))
LIMIT 1

$SEMD = some effective minimum distance that holds at least one result

On Tue, Aug 4, 2009 at 6:34 AM, Thurber, Fred<Fred.Thurber at gdit.com> wrote:
> 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?
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>