Performance of lat/long proximity search

Question

Hey,

I have a page where I show businesses that are within a certain number of miles of a user. I store the user and business's latitude and longitude in the database. Currently, I'm using NHibernate to return all businesses in the database, then checking each business to see if it's lat/long is within X miles of the user, then creating a new list of only the businesses within that radius and returning those.

public static List<Business> FindNearbyBusinesses(Coordinates coordinates, int radiusInMiles)
        {//TODO: figure out better way to do this performance-wise!
            //get all businesses.
            var criteria = DetachedCriteria.For<Core.Models.Business>();
            var businesses = FindAll(criteria);
            //find nearby businesses.
            var nearbyBusinesses = new List<Business>();
            foreach (var business in businesses)
            {
                business.MilesToLocation = GeoHelper.GetDistanceBetweenCoordinates(coordinates, business.Coordinates);
                if (business.MilesToLocation <= radiusInMiles)
                {
                    nearbyBusinesses.Add(business);
                }
            }
            //sort.
            nearbyBusinesses.Sort(delegate(Business b1, Business b2)
            {
                return b1.MilesToLocation.CompareTo(b2.MilesToLocation);
            });
            return nearbyBusinesses;
        }

This of course is terribly inefficient and won't scale well once there are tens or hundreds of thousands of businesses in the database. Is there a way I can do this better so that I don't have to return every single business in the database first and run this distance calculation against every single one? One thought I had was maybe there's a way to say, if the radius is a certain amount of miles, then the lat/long should be within this range, then run the calc on only those.

Thanks for any suggestions, Justin

Answer 1

My MSSQL/C# experience is limited, but could you not write a stored procedure to do this?

Apparently you can use C# to build an SP. Saves you doing it all on the server rather than the DB. I could be wrong though ;)

Answer 2

Some googling brought me back to this site: http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula

You should use the sql statement there in a stored procedure. I'm using it myself for a store locator.