主页 / dba / 问题 / 284966
Accepted
AngryHacker
Asked: 2021-02-09 11:47:18 +0800 CST

为什么内置的距离功能比我的自定义功能慢很多？

  • 772

我想找到两组坐标之间的距离。首先我使用内置函数：

declare @lat1 real = 33.1, @lon1 real =-117.1, @lat1 real = 39.6, @lon1 real =-98.7
declare @source geography = geography::Point(@lat1, @long1, 4326);
declare @target geography = geography::Point(@lat2, @long2, 4326);

select  @source.STDistance(@target)

现在我使用手动计算来做同样的事情：

 -- Kilometers to Miles: 0.621371, Earth Radius: 6378.137
 select 0.621371 * 6378.137 * ACOS(ROUND(
    (SIN(PI() *  @lat2 /180) * SIN(PI() * @lat1/180))
    + (COS(PI() *  @lat2 /180) * COS(PI() * @lat1/180) * COS(PI() * @lon1/180 - PI() * @lon2 /180)), 12))

在我对 SQL Server 2014 的测试中，手动功能比内置功能快 4 倍左右。这是正常的还是我错过了一些基本的东西？

sql-server sql-server-2014

1 个回答

  1. Best Answer

    SQL Server 2016 对性能有所改进，但基本原因是 STDistance 准确，但如果您了解它们产生的错误，更简单的公式可以更快。

    参见例如Geographic distance can be simple and fast，以及 STDistance 中使用的算法的描述：

    本文概述了 Microsoft SQL Server 中用于以独立于任何制图投影的方式处理地理空间数据的一些几何算法。

    椭球地球模型上的几何算法

    例如

    declare @lat1 real = 33.1 
declare @lon1 real =-117.1
declare @lat2 real = 39.6
declare @lon2 real =-98.7

declare @source geography = geography::Point(@lat1, @lon1, 4326);
declare @target geography = geography::Point(@lat2, @lon2, 4326);

declare @distance float 
declare @i int = 0
declare @iter int = 1000 * 1000
declare @start datetime2 

set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
  set @distance =  @source.STDistance(@target)   
  set @i += 1
end
select concat('STDistance: ',  datediff(ms,@start,sysdatetime()), 'ms' )


set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
 set @distance =  0.621371 * 6378.137 * ACOS(ROUND(
    (SIN(PI() *  @lat2 /180) * SIN(PI() * @lat1/180))
    + (COS(PI() *  @lat2 /180) * COS(PI() * @lat1/180) * COS(PI() * @lon1/180 - PI() * @lon2 /180)), 12))  
  set @i += 1
end
select concat('calculation: ',  datediff(ms,@start,sysdatetime()), 'ms' )

set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
  set @distance =  @source.STDistance(@target)   
  set @i += 1
end
select concat('STDistance: ',  datediff(ms,@start,sysdatetime()), 'ms' )

set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
 set @distance =  0.621371 * 6378.137 * ACOS(ROUND(
    (SIN(PI() *  @lat2 /180) * SIN(PI() * @lat1/180))
    + (COS(PI() *  @lat2 /180) * COS(PI() * @lat1/180) * COS(PI() * @lon1/180 - PI() * @lon2 /180)), 12))  
  set @i += 1
end
select concat('calculation: ',  datediff(ms,@start,sysdatetime()), 'ms' )

    输出

    --------------------------
STDistance: 2367ms


---------------------------
calculation: 1098ms


--------------------------
STDistance: 2347ms


---------------------------
calculation: 1105ms
    • 5

相关问题