Calculate the distance between two points' longitude and latitude under SQL SERVER

A few days ago, a customer made such a request: a mobile phone ordering website, query for 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 The longitude and latitude of the customer's location are passed over, and then calculated with the longitude and latitude of the hotel in the database to find out.

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 could not 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.

The code is as follows:

 --计算地球上两个坐标点（经度，纬度）之间距离sql函数  
    --作者：lordbaby  
    --整理：www.aspbc.com   
    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

-跟坐标距离小于5公里的数据  
    SELECT * FROM 商家表名 WHERE dbo.fnGetDistance(121.4625,31.220937,longitude,latitude) < 5

The longitude and latitude here are the longitude and latitude fields of the hotel respectively, and 121.4625,31.220937 is the longitude of the current customer obtained by the mobile phone, and the following 5 means 5 kilometers within the range.

【Related recommendations】

1. Free mysql online video tutorial

2. MySQL latest manual tutorial

3. Chuanzhi Podcast Liu Daocheng MySql series video tutorials

The above is the detailed content of Calculate the distance between two points' longitude and latitude under SQL SERVER. For more information, please follow other related articles on the PHP Chinese website!