浮点 - SQL 使用长/纬度计算距离 &将整数转换为十进制

Question

原始帖子：将整数更改为浮点数并添加小数点

所以这是背景故事：我正在使用一个从车辆接收GPS坐标的数据库，我需要根据经度和纬度值计算出这些车辆的里程。但是，在数据库中，经度/纬度两列都是"整数/非空"，并且没有小数位。所以它们看起来像这样：

Latitude Longitude 
36158500 115949833
36340000 115914667
36153488 115944875

我需要它看起来像这样：

Latitude Longitude 
36.158500 115.949833  
36.340000 115.914667 
36.153488 115.944875

在Tim Lehner的帮助下，我们想出了如何获得小数点，但我不能使用我已经创建的公式来计算里程数。我必须能够根据radio_name搜索长/纬度值，以便我可以单独查看每辆车的里程，这就是为什么我创建了一个名为 CTE 和 CTE2 的临时表。但是，如果我调用临时表，代码将不起作用，因为它说它是一个无效的对象。现在，如果我插入"AVL"（原始表的名称）而不是在下面的最后一条语句中使用 CTE2，代码可以正常工作。但是，里程不是使用新的经度/经度值计算的。

所以回顾一下：列是纬度、经度。我们需要转换这些列，以便在纬度 2 个字符之后放置一个小数点，在经度 3 个字符之后放置一个小数点。然后我们需要一个名为 Miles 的列，用于计算给定的纬度和经度值之间的里程数。

任何提示，帮助，指针等都非常感谢！谢谢！

USE [system]
GO
With CTE as 
(SELECT * FROM AVL 
WHERE (DATE_TIME between '01/30/2013 00:00:00' AND 
'01/30/2013 23:59:59') AND radio_name = 'MAS7'),
CTE2 as 
(select *,row_number() over(partition by Vehicle_ID order by Date_Time) as RN
FROM CTE)
SELECT *, sqrt((69.1*(previous.Latitude - next.Latitude))*
     (69.1*(previous.Latitude-next.Latitude)) +
     (69.1*(previous.Longitude-next.Longitude)) *
     cos(next.Latitude/57.3) * (69.1*(previous.longitude-next.Longitude)) *
     cos(next.Latitude/57.3)) as Miles
From CTE2 as Previous
Join CTE2 as Next
On previous.Vehicle_ID = Next.Vehicle_ID
AND Previous.RN = 
Next.RN - 1
select cast(Latitude / 1000000.0 as decimal(10, 6)) as Latitude
  , cast(Longitude / 1000000.0 as decimal(10, 6)) as Longitude
from cte2

****编辑****
期望的结果将如下所示：

Latitude Longitude  Mileage Radio_Name
36.158500 115.949833 3.444  MAS7
36.340000 115.914667 3.443  MAS7
36.153488 115.944875 4.544  MAS7

（这不是正确的里程，但这只是我正在寻找的结果的一个例子。

Answer 1

请注意夯实表、表变量和 CTE 之间的区别。

您当前使用的是公用表表达式或 CTE，它们更类似于派生表，因为它们只能用于紧跟在它们后面的一个选择（或插入、更新等）语句（尽管您可以像以前一样为语句声明多个 CTE）。

因此，当您

进入第二个选择语句时，您的 CTE 已超出范围，您无法再引用它们（或者您必须再次声明它们）。 在这种情况下，您可能希望使用表变量或临时表。

您可以通过转换 CTE 定义轻松创建临时表，如下所示：

-- Make a temp table
SELECT *
INTO #TempTableName -- Create a new temp table
FROM AVL 
WHERE DATE_TIME between '01/30/2013 00:00:00' AND '01/30/2013 23:59:59'
    AND radio_name = 'MAS7'
-- Check out our new temp table
SELECT * FROM #TempTableName
-- Clean up our temp table, though this will automatically happen once the connection is dropped
DROP TABLE #TempTableName

此外，在使用临时表时，我经常会检查它们并将其放在进程的开头和结尾，如下所示：

if object_id('tempdb..#TempTableName') is not null begin
    drop table #TempTableName
end

在您的代码中使用这些内容，如果您在执行实际距离计算时遇到困难，请告诉我们。