经纬度度分秒转换小数.sql[原创]

--Make data for test

declare @T table(lat varchar(50),lon varchar(50))

insert into @T select '14°′″','14°′″' union all

select '12°′″','15°′″' union all

select '33°′″','34°′″' union all

select '16°′″','94°′″' union all

select '46°′″','74°′″' union all

select '56°′″','16°′″'

 

--Test mark

select lat,Charindex('°',lat) '°',

         Charindex('′',lat) '′',

         Charindex('″',lat) '″',

       lon,Charindex('°',lon) '°',

         Charindex('′',lon) '′',

         Charindex('″',lon) '″'

from @T

 

--make temp data

if object_id('tempdb..#t') is not null

begin

    drop table #t

end

 

select lat,Substring(lat,1,Charindex('°',lat)-1) 'latdegree',

           Substring(lat,Charindex('°',lat)+1,Charindex('′',lat)-1-Charindex('°',lat)) 'latcent',

           Substring(lat,Charindex('′',lat)+1,Charindex('″',lat)-1-Charindex('′',lat)) 'latsecond',

       lon,Substring(lon,1,Charindex('°',lon)-1) 'londegree',

           Substring(lon,Charindex('°',lon)+1,Charindex('′',lon)-1-Charindex('°',lon)) 'loncent',

           Substring(lon,Charindex('′',lon)+1,Charindex('″',lon)-1-Charindex('′',lon)) 'lonsecond'

into #t

from @T

 

--Make result

declare @multiplycent int

declare @multiplysecond int

declare @dividecent int

declare @dividesecond int

declare @divide float

set @multiplycent =1000000

set @multiplysecond = 100000

set @dividecent=6

set @dividesecond=36

set @divide = 10000000.0

 

if object_id('tempdb..#result') is not null

begin

    drop table #result

end

select lat,latdegree,latcent,latsecond,

       latdegree+str(latcent*@multiplycent/@dividecent+latsecond*@multiplysecond/@dividesecond)/@divide as 'decimallat',

       lon,londegree,loncent,lonsecond,

       londegree+str(loncent*@multiplycent/@dividecent+lonsecond*@multiplysecond/@dividesecond)/@divide as 'decimallon'

into #result

from #t

 

--View the result data round decimal six bit

select lat,decimallat,lon,decimallon,Round(decimallat,6) as resuldlat,Round(decimallon,6) as resultlon from #result

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13972900/viewspace-444250/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13972900/viewspace-444250/