将整数更改为浮点数并添加小数点 [英] Changing integer to floating point and adding decimal point

问题描述

我正在 Microsoft SQL Server Management Studio 2008 上使用 SQL.

I'm working with SQL on Microsoft SQL Server Management Studio 2008.

简而言之，我接收的坐标数据为 115949833，我需要将其输出为 115.949833 因为我需要能够计算两者之间的里程纬度和经度坐标.经度和纬度值都保存为没有小数位的整数.我试图将其更改为字符串，以便可以使用子字符串连接并将其转换为浮点数.我已经找出了 lat 和 long 值之间的里程公式，但由于小数点问题，它不准确.

In a nut shell, I'm receiving data on coordinates as 115949833 and I need it to output as 115.949833 because I need to be able to calculate the mileage between the latitude and longitude coordinates. Both the longitude and latitude values are saved as integers with no decimal places. I was trying to change it to a string so I could use a substring to concatenate and convert it into a floating point. I've already figured out the formula for the mileage between lat and long values but its not accurate because of the issue with the decimal point.

这是我用于经度的代码:

Here's the code I used for the longitude:

Cast(substring(str(longitude,1,not null),1,3)+'.'+substring(str(longitude,4,not null),4,9) as float) as longitude

它一直告诉我我在 CAST 附近有一个不正确的语法.

It keeps telling me I have an incorrect syntax near CAST.

我进行了大量研究，但仍然找不到执行此任务的确切方法.如果有人可以提供任何反馈、提示、帮助等，将不胜感激.谢谢.

I've been doing a lot of research and still couldn't find an exact way of performing this task. If anyone could provide any feedback, tips, help, etc. It would be greatly appreciated. Thanks.

***我需要将名为经度"的整个列转换为浮点数或以某种方式在第三个字符后显示小数，并将名为纬度"的整个列在第二个字符后显示小数.

*** I need the entire column named "Longitude" to be converted into a floating point or to somehow display a decimal after the 3rd character and the entire column named "Latitude" to display a decimal after the 2nd character.

例如现在我的列显示:

纬度 经度
36158500 115949833
36340000 115914667
36153488 115944875

我需要它看起来像这样:

纬度 经度
36.158500 115.949833
36.340000 115.914667
36.153488 115.944875

Latitude Longitude
36158500 115949833
36340000 115914667
36153488 115944875

and I need it to look like this:

Latitude Longitude
36.158500 115.949833
36.340000 115.914667
36.153488 115.944875

*编辑*因此，使用 Tim Lehner 的答案，我使用了我的代码并实现了他的答案，但现在我需要弄清楚如何使用新的纬度和经度列来显示里程......我创建了一些临时表来存储我想要的信息基于特定的 radio_name，但我无法使用 Tim 的答案从这些临时表中提取信息.

*EDIT* So using Tim Lehner's Answer, I've used my code and implemented his answer, but now I need to figure out how to get it to show the mileage using the new Latitude and Longitude columns... I created a few temp table's to store the information I wanted in based on specific radio_name's but I can't pull the info from those temp tables using Tim's Answer.

这是我正在使用的代码:
使用[系统]
去
以 CTE 作为
(从 AVL 中选择 *
WHERE(01/30/2013 00:00:00"和
之间的 DATE_TIME'01/30/2013 23:59:59') AND radio_name = 'MAS7')
CTE2 作为
(select *,row_number() over(partition by Vehicle_ID order by Date_Time) 作为RN来自 CTE)

Here's the code I'm using:
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 AVL

实际代码本身确实可以正常工作，但里程计算使用的是原始的整个纬度/经度数字，而不是带有小数点的更新数字.

The actual code itself does work and function properly but the mileage calculation is using the original whole latitude/longitude numbers and not the updated numbers that have the decimal point.

我尝试在最后一条语句中插入 CTE2 而不是 AVL，它说这是一个无效的对象名称.任何指示或提示?...

I tried plugging in CTE2 in the last statement instead of AVL and it says it's an invalid object name. Any pointers or tips?...

推荐答案

我喜欢使用数据类型的优势:

I like to use datatypes to my advantage:

select 115949833 / 1000000.0
-- returns 115.949833000

然后您可以舍入/截断到您的规范.

You can then round/truncate to your spec.

将小数点添加到除数将根据 数据类型优先级.

Adding the decimal point to the divisor will promote the output of this operation to numeric per the rules of datatype precedence.

更新

根据您的测试数据，您可以使用如下查询:

Per your test data, you might use a query like this:

select cast(Latitude / 1000000.0 as decimal(10, 6)) as Latitude
  , cast(Longitude / 1000000.0 as decimal(10, 6)) as Longitude
from MyTable

/*
Returns:
Latitude    Longitude
36.158500   115.949833
36.340000   115.914667
36.153488   115.944875
*/

这篇关于将整数更改为浮点数并添加小数点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！