Calculate distance between two points

Question

Calculate distance between two points

Navigation

#1 by (4 votes)

3

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"?

sql-server sql-server-2005

asked by sioesi 27.10.2016 в 17:43

source

1 answer

Check that there is a word on a div and then change its class Doubt with cstring and string

score 4 · Accepted 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
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 defined functions, you would get this result:

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

534.04509287162