How To Find A Doctor Near Your Home in SQL Server

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.

-- =============================================  
-- Description:   Get all zip codes within ~30 miles north, south, east or west of the input location.  
-- =============================================  
/*  
exec [dbo].[ZipCodesNearLatLong] 37.000495, -94.840850  
*/  
   
CREATE PROCEDURE [dbo].[ZipCodesNearLatLong]  
   
            @Latitude DECIMAL(10,6),  
            @Longitude DECIMAL(10,6)  
AS  
BEGIN  
            SET NOCOUNT ON;  
            select distinct  
            PC.ZipCode,  
            PC.Latitude,  
            PC.Longitude,    
            dbo.CalculateDistance(@Latitude, @Longitude, PC.Latitude, PC.Longitude) as Distance            
            from PostalCodes PC  
            where (ABS(@Latitude - PC.Latitude) < .5)  -- Lines of latitude are ~69 miles apart.   
            and (ABS(@Longitude - PC.Longitude) < .6)  -- Lines of longitude in the U.S. are ~53 miles apart.             
            order by Distance     
END  

Now that I can get a list of nearby postal codes, I can get a list of doctors in these postal codes.

-- =============================================  
-- Description:   Search for a doctor near a specified zip code.   
--                          @LastName is optional  
-- =============================================  
   
/*  
            exec [FindDoctorByNameAndZipCode] 43001, 'Fenster'  
*/  
   
CREATE PROCEDURE [dbo].[FindDoctorByNameAndZipCode]  
   
    @ZipCode CHAR(5),   
    @LastName VARCHAR(50) = NULL,   
   
AS  
BEGIN  
   
SET NOCOUNT ON;  
    
IF (@LastName = '')   
    SELECT @LastName = NULL   
ELSE   
    SELECT @LastName = @LastName + '%'  
                         
    -- Get latitude and longitude for the zip code  
DECLARE @Latitude DECIMAL(10,6)  
DECLARE @Longitude DECIMAL(10,6)  
EXECUTE [dbo].[LatitudeAndLongitudeForZipCode] @ZipCode,@Latitude OUTPUT ,@Longitude OUTPUT  
             
    -- Get a table of zip codes  
DECLARE @ZipCodes TABLE  
(  
            ZipCode CHAR(5),  
            Latitude DECIMAL(10,6),  
            Longitude DECIMAL(10,6),  
            Distance DECIMAL(10,1)  
)  
INSERT INTO @ZipCodes EXECUTE [dbo].[ZipCodesNearLatLong] @Latitude, @Longitude  
             
    -- Search  
SELECT DISTINCT TOP 50  
DR.ProviderID,  
DR.Title,  
DR.FirstName,  
DR.LastName,  
DR.Address1,  
DR.City,  
DR.StateAbbreviation,  
DR.ZipCode,  
DR.Phone,  
DR.Gender,  
Z.Distance  
   
FROM Doctors DR  
JOIN @ZipCodes Z  
ON DR.ZipCode = Z.ZipCode  
             
WHERE ((P.LastName LIKE @LastName) OR (@LastName IS NULL))  
   
ORDER BY Z.Distance  
END  

Conclusion

This article taught us how to find a doctor nar your home in SQL Server. 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.