SQL SERVER calculates distance function based on map longitude and latitude

A few days ago, a customer made such a request: a mobile food ordering website to query hotels within 5 kilometers of the current location, so that the customer can go to eat.
After getting this request, I didn’t know how to start. I thought about it quietly and added two fields to the hotel’s table to store the longitude and latitude of the hotel. When ordering a meal, the mobile phone is required to get the current customer’s location. The longitude and latitude are passed in, and then calculated with the longitude and latitude of the hotel in the database, it is found.

In order to query the distance between two points in the database, this function needs to be defined in the database.

I searched online for a long time, but I couldn’t find this function. Finally, I solved this problem with the help of a friend on CSDN. I am very grateful to lordbaby for providing me with this function. I put this function here to help more friends who want it.

--计算地球上两个坐标点（经度，纬度）之间距离sql函数  
 
CREATE FUNCTION [dbo].[fnGetDistance](@LatBegin REAL, @LngBegin REAL, @LatEnd REAL, @LngEnd REAL) RETURNS FLOAT 
  AS 
BEGIN 
  --距离(千米)  
  DECLARE @Distance REAL 
  DECLARE @EARTH_RADIUS REAL 
  SET @EARTH_RADIUS = 6378.137    
  DECLARE @RadLatBegin REAL,@RadLatEnd REAL,@RadLatDiff REAL,@RadLngDiff REAL 
  SET @RadLatBegin = @LatBegin *PI()/180.0    
  SET @RadLatEnd = @LatEnd *PI()/180.0    
  SET @RadLatDiff = @RadLatBegin - @RadLatEnd    
  SET @RadLngDiff = @LngBegin *PI()/180.0 - @LngEnd *PI()/180.0     
  SET @Distance = 2 *ASIN(SQRT(POWER(SIN(@RadLatDiff/2), 2)+COS(@RadLatBegin)*COS(@RadLatEnd)*POWER(SIN(@RadLngDiff/2), 2)))  
  SET @Distance = @Distance * @EARTH_RADIUS    
  --SET @Distance = Round(@Distance * 10000) / 10000    
  RETURN @Distance  
END