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.

  • 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. Dec 11, 2011 at 21:32
  • Thanks. but is there any better approach for this? anything that will work even better?
    – roman
    Dec 11, 2011 at 22:20

1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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