The most efficient way to get points within a radius of a point with sql server space

Question

I am trying to work out the most efficient query for getting points within the radius of a given point. The results do not have to be very accurate, so I would prefer speed over accuracy.

We tried using the where clause to compare the distance of points using STDistance, like this (where @point and v.GeoPoint are types of geography):

WHERE v.GeoPoint.STDistance(@point) <= @radius 

Also using STIntersects similar to this:

 WHERE @point.STBuffer(@radius).STIntersects(v.GeoPoint) = 1 

Are any of these queries preferable or is there another feature that I missed?

Answer 1

If accuracy is not paramount, then using a filter function might be a good idea: http://msdn.microsoft.com/en-us/library/cc627367.aspx

This can be in many cases an order of magnitude faster because it does not check if your match was exact. The index data is stored in a grid template, so how viable this approach probably depends on your spatial index settings.

Also, if you don't need a lot of matches, then first do a filter and then do a full bust.