在ms -sql 中如何求两个经纬度 之间的距离？？

[hgc2002]

哪位大侠能够提供代码，万分感谢

[让你望见影子的墙]

declare@Ttable(latvarchar(50),lonvarchar(50))

insertinto@Tselect'14°′″','14°′″'unionall

select'12°′″','15°′″'unionall

select'33°′″','34°′″'unionall

select'16°′″','94°′″'unionall

select'46°′″','74°′″'unionall

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

 

--Test mark

selectlat,Charindex('°',lat)'°',

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

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

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

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

        Charindex('″',lon)'″'

from@T

 

--make temp data

ifobject_id('tempdb..#t')isnotnull

begin

   droptable#t

end

 

selectlat,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@multiplycentint

declare@multiplysecondint

declare@dividecentint

declare@dividesecondint

declare@dividefloat

set@multiplycent=1000000

set@multiplysecond=100000

set@dividecent=6

set@dividesecond=36

set@divide=10000000.0

 

ifobject_id('tempdb..#result')isnotnull

begin

   droptable#result

end

selectlat,latdegree,latcent,latsecond,

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

      lon,londegree,loncent,lonsecond,

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

into#result

from#t

 


--View the result data round decimal six bit

selectlat,decimallat,lon,decimallon,Round(decimallat,6)asresuldlat,Round(decimallon,6)asresultlonfrom#result
看看有用没有