How To Find A Doctor Near Your Home in SQL Server

There are many web sites with similar functionality:  find a doctor near your home, find the closest store, search for apartments.  I didn't realize until I needed to make such a site that finding a doctor near your home is fairly difficult.

Suppose I have a database with 10,000 doctors located all over the country.  Each one has an address, including postal code.  For each postal code I have an associated latitude and longitude. 

Image1.jpg 

When a user enters his own address, including postal code, I can get the latitude and longitude from the same table.
 
Now I have a problem. Here is a SQL Server function to calculate the distance in miles between two points for which I have latitude and longitude:
  1. CREATE FUNCTION [dbo].[CalculateDistance]  
  2. (  
  3.     @lat1Degrees decimal(10,6),  
  4.     @lon1Degrees decimal(10,6),  
  5.    
  6.     @lat2Degrees decimal(10,6),  
  7.     @lon2Degrees decimal(10,6)  
  8. )  
  9. RETURNS decimal(9,4)  
  10. AS  
  11. BEGIN  
  12.    
  13.     DECLARE @earthSphereRadiusKilometers as decimal(10,6)  
  14.     DECLARE @kilometerConversionToMilesFactor as decimal(7,6)  
  15.     SELECT @earthSphereRadiusKilometers = 6366.707019  
  16.     SELECT @kilometerConversionToMilesFactor = .621371  
  17.    
  18.     -- convert degrees to radians  
  19.     DECLARE @lat1Radians decimal(10,6)  
  20.     DECLARE @lon1Radians decimal(10,6)  
  21.     DECLARE @lat2Radians decimal(10,6)  
  22.     DECLARE @lon2Radians decimal(10,6)  
  23.     SELECT @lat1Radians = (@lat1Degrees / 180) * PI()  
  24.     SELECT @lon1Radians = (@lon1Degrees / 180) * PI()  
  25.     SELECT @lat2Radians = (@lat2Degrees / 180) * PI()  
  26.     SELECT @lon2Radians = (@lon2Degrees / 180) * PI()  
  27.    
  28.     -- formula for distance from [lat1,lon1] to [lat2,lon2]  
  29.     RETURN ROUND(2 * ASIN(SQRT(POWER(SIN((@lat1Radians - @lat2Radians) / 2) ,2)  
  30.         + COS(@lat1Radians) * COS(@lat2Radians) * POWER(SIN((@lon1Radians - @lon2Radians) / 2), 2)))  
  31.         * (@earthSphereRadiusKilometers * @kilometerConversionToMilesFactor), 4)  
  32.    
  33. END  
That's a lot of math for the database to do.  If I have 10,000 doctors and I want to find the ones that are near my user's address, I could run all 10,000 addresses through this function and select the ones that are nearby.  That's simply unworkable.  It would be far too slow.  If I had several users searching for doctors at the same time it would overwhelm the server.  I need a way to only calculate the distance for doctors I already know are close.

A Little About Latitude And Longitude

 

 

Image2.jpg 
Degrees of latitude are about 69 miles apart.  That is, a latitude of 40.000 is about 69 miles away from latitude 39.000.  So if I want to find doctors within 30 miles of a given point, I can rule out any address whose latitude is more than .5 degrees north or south of my location.
 
Longitude is somewhat more complicated.  Near the poles, lines of longitude are very close.  At the equator they are much further apart.  In the continental United States, lines of longitude are roughly 53 miles apart.  So if I want to find doctors within 30 miles then I should look for ones whose longitude is no more than .6 degrees east or west of my location.
 
Now I don't have to calculate the distance for every doctor in my database.  I can look for addresses whose latitude and longitude are close to the user's location.
 
Here's a SQL Server Stored Procedure that uses this technique to select postal codes near a given location.  It uses the CalculateDistance function shown above. 
  1. -- =============================================  
  2. -- Description:   Get all zip codes within ~30 miles north, south, east or west of the input location.  
  3. -- =============================================  
  4. /*  
  5. exec [dbo].[ZipCodesNearLatLong] 37.000495, -94.840850  
  6. */  
  7.    
  8. CREATE PROCEDURE [dbo].[ZipCodesNearLatLong]  
  9.    
  10.             @Latitude DECIMAL(10,6),  
  11.             @Longitude DECIMAL(10,6)  
  12. AS  
  13. BEGIN  
  14.             SET NOCOUNT ON;  
  15.             select distinct  
  16.             PC.ZipCode,  
  17.             PC.Latitude,  
  18.             PC.Longitude,    
  19.             dbo.CalculateDistance(@Latitude, @Longitude, PC.Latitude, PC.Longitude) as Distance            
  20.             from PostalCodes PC  
  21.             where (ABS(@Latitude - PC.Latitude) < .5)  -- Lines of latitude are ~69 miles apart.   
  22.             and (ABS(@Longitude - PC.Longitude) < .6)  -- Lines of longitude in the U.S. are ~53 miles apart.             
  23.             order by Distance     
  24. END  
Now that I can get a list of nearby postal codes, I can get a list of doctors in these postal codes: 
  1. -- =============================================  
  2. -- Description:   Search for a doctor near a specified zip code.   
  3. --                          @LastName is optional  
  4. -- =============================================  
  5.    
  6. /*  
  7.             exec [FindDoctorByNameAndZipCode] 43001, 'Fenster'  
  8. */  
  9.    
  10. CREATE PROCEDURE [dbo].[FindDoctorByNameAndZipCode]  
  11.    
  12.     @ZipCode CHAR(5),   
  13.     @LastName VARCHAR(50) = NULL,   
  14.    
  15. AS  
  16. BEGIN  
  17.    
  18. SET NOCOUNT ON;  
  19.     
  20. IF (@LastName = '')   
  21.     SELECT @LastName = NULL   
  22. ELSE   
  23.     SELECT @LastName = @LastName + '%'  
  24.                          
  25.     -- Get latitude and longitude for the zip code  
  26. DECLARE @Latitude DECIMAL(10,6)  
  27. DECLARE @Longitude DECIMAL(10,6)  
  28. EXECUTE [dbo].[LatitudeAndLongitudeForZipCode] @ZipCode,@Latitude OUTPUT ,@Longitude OUTPUT  
  29.              
  30.     -- Get a table of zip codes  
  31. DECLARE @ZipCodes TABLE  
  32. (  
  33.             ZipCode CHAR(5),  
  34.             Latitude DECIMAL(10,6),  
  35.             Longitude DECIMAL(10,6),  
  36.             Distance DECIMAL(10,1)  
  37. )  
  38. INSERT INTO @ZipCodes EXECUTE [dbo].[ZipCodesNearLatLong] @Latitude, @Longitude  
  39.              
  40.     -- Search  
  41. SELECT DISTINCT TOP 50  
  42. DR.ProviderID,  
  43. DR.Title,  
  44. DR.FirstName,  
  45. DR.LastName,  
  46. DR.Address1,  
  47. DR.City,  
  48. DR.StateAbbreviation,  
  49. DR.ZipCode,  
  50. DR.Phone,  
  51. DR.Gender,  
  52. Z.Distance  
  53.    
  54. FROM Doctors DR  
  55. JOIN @ZipCodes Z  
  56. ON DR.ZipCode = Z.ZipCode  
  57.              
  58. WHERE ((P.LastName LIKE @LastName) OR (@LastName IS NULL))  
  59.    
  60. ORDER BY Z.Distance  
  61. END  
One More Thing
 
My function to calculate distance came from this extremely helpful article:  http://spyriadis.net/2010/07/calculate-distance-between-locations-in-sql-server-2008-using-geography/.  (I modified it slightly.)  In that article, the author goes on to discuss how to use the SQL Server geography data type. 
 
If you're using SQL Server, the geography data type and associated SQL Server functions can be used to calculate distances and search for nearby locations.  Performance can be slow.  Although I have provided SQL Server examples, my goal was to describe a method that is not limited to SQL Server.