Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
Have you created a spatial index? –  Kirk Kuykendall Jul 7 '11 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 '11 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. –  Kirk Kuykendall Jul 8 '11 at 19:30
add comment

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
add comment

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);
share|improve this answer
    
What is p? sorry i am a new bie thats why asking –  Shax Aug 25 '12 at 8:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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