Calculate distance in miles between two coordinates

Other SQL Server Topics
21

This gives a result of 0.541910656329256 miles:

     ,2*3961
     *asin(sqrt(power(sin(radians(po.lat-ps.lat)/2),2)
               +cos(radians(ps.lat))
               *cos(radians(po.lat))
               *power(sin(radians(po.long-ps.long)/2),2)
               )
          )
1 Like
22

@bitsmed, that worked great (I did have to cast the lats and longs as floats for subtraction).

Thank you so much for your help.

23

I also thought I'd try and use your query as well as the calculation I used previously and that also works, with almost the same results (minor differences that I am happy to overlook, probably due to the 3961 vs 3963.1676)

3963.1676 * (2 * atn2(sqrt((sin(radians(cast(po.Lat as float) - cast(ps.Lat as float))/2) * sin(radians(cast(po.Lat as float) - cast(ps.Lat as float))/2) + cos(radians(ps.Lat)) 
      * cos(radians(po.Lat)) * sin(radians(cast(po.Long as float) - cast(ps.Long as float))/2) * sin(radians(cast(po.Long as float) - cast(ps.Long as float))/2))), sqrt(1 - (sin(radians(cast(po.Lat as float) - cast(ps.Lat as float))/2) * sin(radians(cast(po.Lat as float) - cast(ps.Lat as float))/2) + cos(radians(ps.Lat)) 
      * cos(radians(po.Lat)) * sin(radians(cast(po.Long as float) - cast(ps.Long as float))/2) * sin(radians(cast(po.Long as float) - cast(ps.Long as float))/2)))))

So if anyone else is interested, either one works pretty much.

Thanks to all who helped.