数据库经纬度转换

来源：互联网发布：centos 7 snmp配置编辑：程序博客网时间：2024/05/21 20:28

  USE NSMC53  GO  SELECT T1.UnitID, T1.UnitCode, T1.UnitName, CAST(CAST(ROUND((1.0*T2.Longitude)/100000,0,1) AS INT) AS NVARCHAR(5))+'°'+ CAST(CAST(ROUND(((1.0*T2.Longitude)/100000-ROUND((1.0*T2.Longitude)/100000,0,1))*60,0,1) AS INT) AS NVARCHAR(5))+'’'+ CAST(CAST(ROUND((((1.0*T2.Longitude)/100000-ROUND((1.0*T2.Longitude)/100000,0,1))*60-ROUND(((1.0*T2.Longitude)/100000-ROUND((1.0*T2.Longitude)/100000,0,1))*60,0,1))*60,0,1) AS INT) AS NVARCHAR(5))+'”E' AS '经度', CAST(CAST(ROUND((1.0*T2.Latitude)/100000,0,1) AS INT) AS NVARCHAR(5))+'°'+ CAST(CAST(ROUND(((1.0*T2.Latitude)/100000-ROUND((1.0*T2.Latitude)/100000,0))*60,0,1) AS INT) AS NVARCHAR(5))+'’'+ CAST(CAST(ROUND((((1.0*T2.Latitude)/100000-ROUND((1.0*T2.Latitude)/100000,0,1))*60-ROUND(((1.0*T2.Latitude)/100000-ROUND((1.0*T2.Latitude)/100000,0,1))*60,0,1))*60,0,1) AS INT) AS NVARCHAR(5))+'”N' AS '纬度'   FROM nsmc_TB_UNIT T1  INNER JOIN [dbo].[NSMC_re_UnitLocation] T2  ON T1.UnitID = T2.UnitID

测试数据如下

35aaa120607382403173120°36’26”E24°1’54”N13福州1113564382234521113°33’51”E22°20’42”N14福州硬件133366112234528133°21’57”E22°20’43”N15山东淄博ss113564122234727113°33’50”E22°20’50”N16河南郑州s113567152234535113°34’1”E22°20’43”N2测试2113564802234656113°33’53”E22°20’47”N3硬件测试113563472234639113°33’48”E22°20’47”N4广西自动更新网吧113565832234336113°33’56”E22°20’36”N32广西指纹测试113567072234437113°34’1”E22°20’39”N18云南测试113561552234728113°33’41”E22°20’50”N

数据经纬度计算公式：

编写一个场所表查询脚本（需关联场所坐标表NSMC_re_UnitLocation），没有标记位置的网吧，经纬度数据为空即可�?/P>

经度与纬度数据，需以页面格式一致，�?9.15服务器的的aaa场所，经度：120°36�?6”E�?纬度�?4°1�?4”N

经纬度转换规则：

�?经度/100000, 只取整数

�?(经度/100000-�?*60, 只取整数

�?((经度/100000-�?*60-�?*60, 只取整数