SQL Server - Using spatial data

Question

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.

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.