Calculate distance between two points


in Sql server 2008 it was very simple for me to get the distance between two latitude and longitude points, but now I am occupying Sql server 2005 and the data type geography apparently does not exist ... I do this:

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

SELECT @source.STDistance(@target)

Unfortunately this does not work for me in Sql server 2005

Any idea or type of data that helps me with this, if it does not exist, I'll have to do the calculation, say "manual"?

asked by sioesi 27.10.2016 в 17:43

1 answer


In SQL 2005, the data type geography does not exist. If you take into account the real form of the earth, this becomes complicated (in addition, there are many reference systems in which you may want to interpret the coordinates.

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

create function deg2rad(@deg float) returns float
  return @deg * 3.1415926536 / 180;

create function DistanceFromLatLonInKm (@lon1 float, @lat1 float, @lon2 float, @lat2 float) returns float 
  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([email protected]));
  declare @d float = @R * @c;
  return @d;

With these defined functions, you would get this result:

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

answered by 27.10.2016 / 19:26