Calculating distance between two points Latitude Longitude

Question

I am trying to calculate the distance between two positions on a map  I have stored in my data  Longitude  Latitude  X POS  Y POS   I have been previously using the below snippet   DECLARE  orig lat DECIMAL DECLARE  orig lng DECIMAL SET  orig lat 53 381538 set  orig lng -1 463526 SELECT        3956   2   ASIN            SQRT  POWER SIN   orig lat - abs dest Latitude     pi   180   2   2                   COS  orig lng   pi   180     COS abs dest Latitude    pi   180                    POWER SIN   orig lng - dest Longitude    pi   180   2   2                AS distance --INTO  includeDistances FROM  orig dest   I don t however trust the data coming out of this  it seems to be giving slightly inaccurate results   Some sample data in case you need it   Latitude        Longitude     Distance  53 429108       -2 500953     85 2981833133896   Could anybody help me out with my code  I don t mind if you want to fix what I already have if you have a new way of achieving this that would be great   Please state what unit of measurement your results are in

User · Answer

In addition to the previous answers  here is a way to calculate the distance inside a SELECT   CREATE FUNCTION Get Distance           La1 float    Lo1 float    La2 float   Lo2 float   RETURNS TABLE  AS RETURN      -- Distance in Meters     SELECT GEOGRAPHY  Point  La1   Lo1  4326  STDistance GEOGRAPHY  Point  La2   Lo2  4326       AS Distance GO   Usage   select Distance from Place P1       Place P2  outer apply dbo Get Distance P1 latitude  P1 longitude  P2 latitude  P2 longitude    Scalar functions also work but they are very inefficient when computing large amount of data   I hope this might help someone

User · Answer

The below function gives distance between two geocoordinates in miles  create function  dbo   fnCalcDistanceMiles    Lat1 decimal 8 4    Long1 decimal 8 4    Lat2 decimal 8 4    Long2 decimal 8 4   returns decimal  8 4  as begin declare  d decimal 28 10  -- Convert to radians set  Lat1    Lat1   57 2958 set  Long1    Long1   57 2958 set  Lat2    Lat2   57 2958 set  Long2    Long2   57 2958 -- Calc distance set  d    Sin  Lat1    Sin  Lat2      Cos  Lat1    Cos  Lat2    Cos  Long2 -  Long1   -- Convert to miles if  d  lt  gt  0 begin set  d   3958 75   Atan Sqrt 1 - power  d  2      d   end return  d end    The below function gives distance between two geocoordinates in kilometres  CREATE FUNCTION dbo fnCalcDistanceKM  lat1 FLOAT   lat2 FLOAT   lon1 FLOAT   lon2 FLOAT  RETURNS FLOAT  AS BEGIN      RETURN ACOS SIN PI    lat1 180 0  SIN PI    lat2 180 0  COS PI    lat1 180 0  COS PI    lat2 180 0  COS PI    lon2 180 0-PI    lon1 180 0   6371 END   The below function gives distance between two geocoordinates in kilometres  using Geography data type which was introduced in sql server 2008  DECLARE  g geography  DECLARE  h geography  SET  g   geography  STGeomFromText  LINESTRING -122 360 47 656  -122 343 47 656    4326   SET  h   geography  STGeomFromText  POINT -122 34900 47 65100    4326   SELECT  g STDistance  h     Usage   select  dbo   fnCalcDistanceKM  13 077085 80 262675 13 065701 80 258916    Reference  Ref1 Ref2

User · Answer

As you re using SQL 2008 or later  I d recommend checking out the GEOGRAPHY data type  SQL has built in support for geospatial queries   e g  you d have a column in your table of type GEOGRAPHY which would be populated with a geospatial representation of the coordinates  check out the MSDN reference linked above for examples   This datatype then exposes methods allowing you to perform a whole host of geospatial queries  e g  finding the distance between 2 points

User · Answer

Since you re using SQL Server 2008  you have the geography data type available  which is designed for exactly this kind of data   DECLARE  source geography    POINT 0 51 5   DECLARE  target geography    POINT -3 56    SELECT  source STDistance  target    Gives  ---------------------- 538404 100197555   1 row s  affected    Telling us it is about 538 km from  near  London to  near  Edinburgh   Naturally there will be an amount of learning to do first  but once you know it it s far far easier than implementing your own Haversine calculation  plus you get a LOT of functionality     If you want to retain your existing data structure  you can still use STDistance  by constructing suitable geography instances using the Point method   DECLARE  orig lat DECIMAL 12  9  DECLARE  orig lng DECIMAL 12  9  SET  orig lat 53 381538 set  orig lng -1 463526  DECLARE  orig geography   geography  Point  orig lat   orig lng  4326    SELECT         orig STDistance geography  Point dest Latitude  dest Longitude  4326           AS distance --INTO  includeDistances FROM  orig dest

User · Answer

It looks like Microsoft invaded brains of all other respondents and made them write as complicated solutions as possible  Here is the simplest way without any additional functions declare statements   SELECT geography  Point LATITUDE 1  LONGITUDE 1  4326  STDistance geography  Point LATITUDE 2  LONGITUDE 2  4326     Simply substitute your data instead of LATITUDE 1  LONGITUDE 1  LATITUDE 2  LONGITUDE 2 e g    SELECT geography  Point 53 429108  -2 500953  4326  STDistance geography  Point c Latitude  c Longitude  4326   from coordinates c

User · Answer

Create Function  dbo   DistanceKM             Lat1 Float 18            Lat2 Float 18           Long1 Float 18           Long2 Float 18    Returns Float 18  AS Begin       Declare  R Float 8          Declare  dLat Float 18          Declare  dLon Float 18          Declare  a Float 18          Declare  c Float 18          Declare  d Float 18         Set  R    6367 45             --Miles 3956 55               --Kilometers 6367 45              --Feet 20890584              --Meters 6367450          Set  dLat   Radians  lat2 -  lat1         Set  dLon   Radians  long2 -  long1         Set  a   Sin  dLat   2                       Sin  dLat   2                       Cos Radians  lat1                       Cos Radians  lat2                        Sin  dLon   2                       Sin  dLon   2          Set  c   2   Asin Min Sqrt  a             Set  d    R    c         Return  d    End GO   Usage    select dbo DistanceKM 37 848832506474  37 848732506474  27 83935546875  27 83905546875   Outputs    0 02849639  You can change  R parameter with commented floats

[sql] Calculating distance between two points (Latitude, Longitude)

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to math

Examples related to sql-server-2008-r2