Home / Questions / 30195
Accepted
sioesi
Asked: 2020-10-28 07:43:20 +0800 CST 2020-10-28 07:43:20 +0800 CST

Calculate distance between two points

  • 772

In Sql server 2008 it was very simple for me to obtain the distance between two latitude and longitude points, but now I am using Sql server 2005 and the data type geographydoes not seem to exist... I did this:

DECLARE @source geography = 'POINT(0 51.5)'
DECLARE @target geography = 'POINT(-3 56)'

SELECT @source.STDistance(@target)

Unfortunately this doesn't work for me on Sql server 2005

Any idea or type of data to help me with this, if it doesn't exist, will I have to do the calculation, let's say "manual"?

sql-server

1 Answers

  1. Best Answer

    In SQL 2005, there is no geography data type . If you take into account the actual shape of the earth, this becomes tricky (plus there are many reference systems you may want to interpret coordinates in.

    Simplifying everything to the fact that the earth was a sphere (which it is not, and therefore the result if you implement this answer will only be an approximation of reality), you could do something like this, since in the end the calculation is nothing more than a bit of trigonometry:

    create function deg2rad(@deg float) returns float
as
begin
  return @deg * 3.1415926536 / 180;
end
go


create function DistanceFromLatLonInKm (@lon1 float, @lat1 float, @lon2 float, @lat2 float) returns float 
as 
begin
  declare @R float = 6371; --radio de una _tierra_ esférica en Km.
  declare @dLat float = dbo.deg2rad(@lat2 - @lat1);
  declare @dLon float = dbo.deg2rad(@lon2 - @lon1);

  declare @a float;
  set @a = sin(@dLat / 2) * sin(@dLat / 2)
         + cos(dbo.deg2rad(@lat2)) * cos(dbo.deg2rad(@lat2)) * sin(@dlon / 2) * sin(@dlon / 2);

  declare @c float = 2 * atn2(sqrt(@a), sqrt(1-@a));
  declare @d float = @R * @c;
  return @d;
end
go

    With these functions defined, you would get this result:

    select dbo.DistanceFromLatLonInKm(0, 51.5, -3, 56);

534.04509287162
    • 4