SqlService GPS 坐标距离计算

CREATE FUNCTION [dbo].[Rad](
@d float
)
 RETURNS float
BEGIN
  return @d * PI()/ 180.00;
 End

CREATE FUNCTION [dbo].[DISTANCE](
@lat1 float,
@lng1 float,
@lat2 float,
@lng2 float
)
 RETURNS float
BEGIN

  Declare @radLat1 float,@radLat2 float,
		  @radLon1 float,@radLon2 float,
		  @x1 float,@x2 float,
		  @y1 float,@y2 float,
		  @z1 float,@z2 float,
		  @d float,@theta float,@dist float,
		  @EARTH_RADIUS float = 6378.137;

  set @radLat1 = dbo.Rad(@lat1);
  set @radLat2 = dbo.Rad(@lat2);

  set @radLon1=dbo.Rad(@lng1);
  set @radLon2=dbo.Rad(@lng2);

  if @radLat1<0
	set @radLat1 = PI() / 2 + ABS(@radLat1);-- south
  if @radLat1>0
     set @radLat1 = PI() / 2 - ABS(@radLat1);-- north

  if @radLon1<0
     set @radLon1 = PI() * 2 - ABS(@radLon1);-- west
  if @radLat2<0
      set @radLat2 = PI() /2 -ABS(@radLat2);-- south

  if @radLat2>0
      set @radLat2 = PI() / 2 - ABS(@radLat2);-- north
  if @radLon2<0
     set @radLon2 = PI() * 2 - ABS(@radLon2);-- west

	set @x1= @EARTH_RADIUS * COS(@radLon1) * SIN(@radLat1);
	set @y1= @EARTH_RADIUS * SIN(@radLon1) * SIN(@radLat1);
	set @z1= @EARTH_RADIUS * COS(@radLat1);

	set @x2= @EARTH_RADIUS * COS(@radLon2) * SIN(@radLat2);
	set @y2= @EARTH_RADIUS * SIN(@radLon2) * SIN(@radLat2);
	set @z2= @EARTH_RADIUS * COS(@radLat2);

	set @d= SQRT(
				  (@x1 - @x2) * 
				  (@x1 - @x2) + 
				  (@y1 - @y2) * 
				  (@y1 - @y2) + 
				  (@z1 - @z2) * 
				  (@z1 - @z2)
			  );
	--余弦定理求夹角
	set @theta= ACOS((@EARTH_RADIUS * @EARTH_RADIUS + @EARTH_RADIUS * @EARTH_RADIUS - @d * @d) / (2 * @EARTH_RADIUS * @EARTH_RADIUS));
	set @dist= @theta * @EARTH_RADIUS;
  return @dist;
 End

select dbo.DISTANCE(113.52271, 22.263208, 113.520257, 22.245865)