Find closest lat long to an input lat long (SQL Server 2008)

Question

I have a point cloud in my database (SQL Server 2008 spatial). That is about 6 million records. There are 3 columns: id, value, geom. What is the optimal way of getting the 'value' at input lat long??

I am new to spatial queries in SQL Server 2008. Can someone post a simple example of finding the point in geom column, matching or closest from the input lat long?

Answer 1

What you are looking for is Nearest Neighbor Query. Look at the following links, I think you will find what you are looking for.

Nearest Neighbor Query

Nearest Neighbors

The nearest neighbor optimization in SQL Server Denali

Answer 2

This uses Geography not Geometry (if data is Lat/Lng you data should be Geography Type not Geometry)

"The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates."

To Select the Top 5 Nearest Records from a lat/lng (-122.0 37.0) point you can use.

SELECT   TOP 5
         geography::STGeomFromText('POINT(-122.0 37.0)', 4326).STDistance(p) 
FROM     markers
WHERE    geography::STGeomFromText('POINT(-122.0 37.0)', 4326).STDistance(p) < 25
ORDER BY geography::STGeomFromText('POINT(-122.0 37.0)', 4326).STDistance(p);