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?

  • Have you created a spatial index? Jul 7, 2011 at 14:09
  • I tried. But there are 6 million records and creating index fails with different reasons. I am trying now to create a blank table, add a spatial index on geom and then add data. does that work?
    – Shaunak
    Jul 8, 2011 at 19:12
  • What errors are you getting? An index can slow loading down a lot, for that many rows I think adding an index after loading would be a lot faster. Performance will depend greatly on the index, so it is worth getting that right. Jul 8, 2011 at 19:30

2 Answers 2


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


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.

         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);
  • What is p? sorry i am a new bie thats why asking
    – Shax
    Aug 25, 2012 at 8:47
  • DECLARE @p geography Jun 18, 2015 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.