<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Exchange Server">
<!-- converted from rtf -->
<style><!-- .EmailQuote { margin-left: 1pt; padding-left: 4pt; border-left: #800000 2px solid; } --></style>
</head>
<body>
<font face="Arial, sans-serif" size="2">
<div>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? </div>
<div><font face="Times New Roman, serif" size="3"> </font></div>
<div>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.</div>
<div><font face="Times New Roman, serif" size="3"> </font></div>
<div><font face="Times New Roman, serif" size="3"> </font></div>
<div>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:</div>
<div><font face="Times New Roman, serif" size="3"> </font></div>
<div>SELECT * FROM foo </div>
<div>ORDER BY distance(point_geom, GeomFromText('POINT(70.0 40.1)', 4326)) </div>
<div>LIMIT 1</div>
<div><font face="Times New Roman, serif" size="3"> </font></div>
<div>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?</div>
<div><font face="Times New Roman, serif" size="3"> </font></div>
</font>
</body>
</html>