Scalability Location Distance Search Over 100,000 LatLng positions in the USA

Scenario =

1) Delivery offices are distributed across the United States with a maximum delivery radius limit in miles.

2) The geo target address converted to LatLng is the delivery destination.

Target = Return a dataset of delivery offices (1) for which the delivery limit is at this distance to the target address (2)

Attempt =

As a starting point for my problem, I use a great example of a Storm consultant to determine the closest office for a client: Haversine distance between two points

The table "My offices" stores the address of the office together with its Lat and Lng values, as well as their maximum "deliveryLimit" distance.

The SQL for calculating Haversine is blowing my mind and is currently beyond my power!

The stormy SQL is below, but instead of only selecting one row from the direct calculations, I need to select all rows of offices whose maximum delivery limit is less than the distance between office and client.

Question1 = How do I add a maximum distance filter to the SQL query so that it returns offices with a delivery zone that includes the target location?

Question2 = How can I limit the number of offices requested by those that can actually be located in the US target area? For example, if the target location is Boise, Idaho and the Los Angeles offices, California has a delivery distance limit of 300 miles. There is no point in even asking for such offices. However, the offices in Washington; Oregon and Northern Nevada state that Idaho boundaries should be included in the search query, as some may have maximum distance values ​​that reach this Boise, ID example.

SQL for Haversine used by Storm:

SELECT TOP 1 *, ( 3960 * acos( cos( radians( @custLat ) ) *
cos( radians( Lat ) ) * cos( radians(  Lng  ) - radians( @custLng ) ) +
sin( radians( @custLat ) ) * sin( radians(  Lat  ) ) ) ) AS Distance
FROM Offices
ORDER BY Distance ASC

      

        
        
        
      

    

The above SQL example only selects the closest office to the target lat / long (@custLng)

The storm approached the distance calculation from two different directions. The SQL above was the first. The second way was to store the coordinates of the office in a list in memory and create a method with a function to cycle through the list, calculating the distances and finally choosing the closest one, for example:

/// <summary>
/// Returns the distance in miles or kilometers of any two
/// latitude / longitude points.
/// </summary>
/// <param name="pos1">Location 1</param>
/// <param name="pos2">Location 2</param>
/// <param name="unit">Miles or Kilometers</param>
/// <returns>Distance in the requested unit</returns>
public double HaversineDistance(LatLng pos1, LatLng pos2, DistanceUnit unit)
{
    double R = (unit == DistanceUnit.Miles) ? 3960 : 6371;
    var lat = (pos2.Latitude - pos1.Latitude).ToRadians();
    var lng = (pos2.Longitude - pos1.Longitude).ToRadians();
    var h1 = Math.Sin(lat / 2) * Math.Sin(lat / 2) +
              Math.Cos(pos1.Latitude.ToRadians()) * 
              Math.Cos(pos2.Latitude.ToRadians()) *
              Math.Sin(lng / 2) * Math.Sin(lng / 2);
    var h2 = 2 * Math.Asin(Math.Min(1, Math.Sqrt(h1)));
    return R * h2;
}

public enum DistanceUnit { Miles, Kilometers };

      

        
        
        
      

    

and

var Offices = GetMyOfficeList();
for(int i = 0; i< Offices.Count; i++)
{
    Offices[i].Distance = HaversineDistance(
                        coord,
                        new LatLng(Offices[i].Lat, Offices[i].Lng),
                        DistanceUnit.Miles);
}

var closestOffice = Offices.OrderBy(x => x.Distance).Take(1).Single();

      

        
        
        
      

    

Scalability is important as my script can easily get much more than 100,000 offices, so listing office numbers in a layout is unlikely!