Proximity Search

Solution:1

If there are enough records for speed to matter, here’s a way to index them ahead of time.

Define a grid of bins about 20 miles on a side. Store the bin number with each store’s record. At search time, compute the numbers of all bins that intersect a 20-mile radius from your search point. Then retrieve all stores in any of those bins, and proceed as before.

Solution:2

We use this to do many thousands of points. It is important if you are performing this in SQL to have an index on the Latitude and Longitude column. We tried doing this in SQL 2008 with spatial indexes but we really didn’t see the performance increase we expected. Though if you want to calculate within a certain distance from a ZIP you need to think about if you are going to use the ZIP centroid or a polygon representation of the ZIP code.

Haversine forumla is a good place to start.

We have not had performance problems calculating the distance on the fly, we do calculate it ahead of time for some applications where we know the points ahead of time and there are going to be millions of records.

SELECT
        [DistanceRadius]=
        69.09 *
        DEGREES(
          ACOS(
            SIN( RADIANS(latitude) )*SIN( RADIANS(@ziplat) ) 
           +
            COS( RADIANS(latitude) )*COS( RADIANS(@ziplat) ) 
           *
            COS( RADIANS(longitude - (@ziplon)) )
          )
        )
        ,*
        FROM
            table

    ) sub
WHERE
    sub.DistanceRadius < @radius