Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using SQL Server 2008 R2 for real-time location aware service. The service mostly receives location data (lat/lon) from a user and returns locations around the user.

The data is being sent from each user once in a second. For every location being reported there is a query that checks other users around that user and returns data about them.

I'm using Lat/Lon as float fields and run a scalar-function to find the closest users.

I guess that approach is not the best. I know there are spatial data constructs in SQL Server, but don't know if they are good. Should I use them? If so, what is the best approach?

What approach should I take to make this operation as fast and scalable as possible?

Maybe NoSQL is better for spatial data?

P.S - I'm talking about thousands and tens of thousands users at any given time.

share|improve this question
    
Yes, you should use them. Without the special spatial indexes, any attempt to run queries for thousands of users and millions of locations through functions will be terribly slow. –  ypercube Dec 11 '11 at 21:32
    
Thanks. but is there any better approach for this? anything that will work even better? –  roman Dec 11 '11 at 22:20
add comment

1 Answer

up vote 2 down vote accepted

Yes you should use spatial indexes, but you should also consider there are limitations that simple sargability concerns can resolve.

A spatial index is good for finding locations in a particular area. It's not good for working out which distance is shortest.

So... when trying to find the closest item, start with a small distance and find items in that area. Something like:

select id
from dbo.locations
where location.STIntersects(@point.STBuffer(@somedistance)) = 1;

...and slowly increase the value of @somedistance until you find what you're looking for. This will allow Seeks to be done, quickly eliminating locations that are not of interest.

share|improve this answer
    
Thanks. Good to know :) –  roman Dec 13 '11 at 8:51
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.