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
测试数据如下
35 aaa 12060738 2403173 120°36’26”E 24°1’54”N
13 福州1 11356438 2234521 113°33’51”E 22°20’42”N
14 福州硬件 13336611 2234528 133°21’57”E 22°20’43”N
15 山东淄博ss 11356412 2234727 113°33’50”E 22°20’50”N
16 河南郑州s 11356715 2234535 113°34’1”E 22°20’43”N
2 测试2 11356480 2234656 113°33’53”E 22°20’47”N
3 硬件测试 11356347 2234639 113°33’48”E 22°20’47”N
4 广西自动更新网吧 11356583 2234336 113°33’56”E 22°20’36”N
32 广西指纹测试 11356707 2234437 113°34’1”E 22°20’39”N
18 云南测试 11356155 2234728 113°33’41”E 22°20’50”N
编写一个场所表查询脚本(需关联场所坐标表NSMC_re_UnitLocation),没有标记位置的网吧,经纬度数据为空即可�?/P>
经度与纬度数据,需以页面格式一致,�?9.15服务器的的aaa场所,经度:120°36�?6”E�?纬度�?4°1�?4”N
经纬度转换规则:
�?经度/100000, 只取整数
�?(经度/100000-�?*60, 只取整数
�?((经度/100000-�?*60-�?*60, 只取整数