函数-计算距离

函数，计算距离

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[GetDistance]
(
	-- Add the parameters for the function here
	@point1Lng DECIMAL(12,6),
	@point1Lat DECIMAL(12,6),
	@point2Lng DECIMAL(12,6),
	@point2Lat DECIMAL(12,6)
)
RETURNS DECIMAL(12,4)
AS
BEGIN
	DECLARE @result DECIMAL(12,4)
	SELECT @result = (6378137.0*ACOS(SIN(@point1Lat/180*PI())*SIN(@point2Lat/180*PI())+COS(@point1Lat/180*PI())*COS(@point2Lat/180*PI())*COS((@point1Lng-@point2Lng)/180*PI())))/1000
	RETURN  Round(@result,2)
END
GO

使用

表结构如下

• pointName  varchar(50),
• pointLng  decimal,
• pointLat decimal,

查询距离某个点最近的top10：

select top 10 pointName,GetDistance(@lng,@lat,pointLng,pointLat) AS distance  
from table order by distance