Ride Sharing App - finding surrounding origins and destinations

Question

I looked at several other SO questions that seem somewhat related, but not quite what i need (or i'm just not smart enough to connect the dots).

Working on an app for a client. Their database holds the origin and destination of people that are traveling, limited (i believe) to just places in US and Canada, and a date when the trip will take place. The records are updated regularly. Call these "trips."

Users come to the site, and enter an origin and destination city, and a radius for each, indicating how far away from their desired origin/destination cities they are willing to travel in order to make their trip.

The job of the app is to find any/all trips that are already in the database, that are closest to the origin and destination that the user needs to travel.

My original thought was to find all origin cities in the database that are within the radius of the user's desired origin, then use that recordset to search the destination cities in the database for any/all cities within the radius of the user's desired destination.

I also need a decent (preferably free... low budget project here) API that can help look up the city geographic location and perform the actual radius calculation... I think.

Is what I'm looking to do even close to the best options? It looks like the hardest part will be finding all the existing cities in the database that are within the radius of the user's desired cities - which is a bit of a twist on a more simple query of just "find all cities in the radius of X city".

So, this is KINDA like an Uber situation, except the Uber driver is deciding what the trip parameters are, and the user just needs to know which Uber drivers are going from/to the places nearest those of the user (on the specified date, to boot).

Right now, users are just looking things up at a state level - BC to NY, and reading down rows of data looking at rides to find the ones that seem closest to what they need.

Thanks in advance, for any clever insights you smart folks might have!

Answer 1

Declare @DriverLat float = 41.744068    
Declare @DriverLng float = -71.315024
Declare @Within   int         = 20


Select *
 From (
        Select Distinct
               A.ZipCode
              ,A.CityName
              ,A.StateCode
              ,Miles = [dbo].[udf-Geo-Calc-Miles] (@DriverLat,@DriverLng,A.Lat,A.Lng)
         From  [dbo].[ZipCodes] A
         Where CityType = 'D'
           and ZipType  = 'S'
      ) A
 Where Miles <= @Within
 Order By Miles

Returns

The UDF

CREATE Function [dbo].[udf-geo-Calc-Miles] (@Lat1 float,@Lng1 float,@Lat2 Float,@Lng2 float)  
Returns Float as  
Begin 
   Declare @Miles Float = (Sin(Radians(@Lat1)) * Sin(Radians(@Lat2))) + (Cos(Radians(@Lat1)) * Cos(Radians(@Lat2)) * Cos(Radians(@Lng2) - Radians(@Lng1)))
   Return Case When @Miles is null then 0 else abs((3958.75 * Atan(Sqrt(1 - power(@Miles, 2)) / @Miles))) end
End