Find distance between two latitude and longitude in SQL query

Question

I've a database table named "contractors" and the table is having 5 fields

Table

Now I've an another lat and long against which I've to select these records and they are :

Latitude: 19.2494730 , Longitude : 72.8612790

My SQL query is :

SELECT *, round( (3959 * acos( cos( radians(19.2494730) ) * cos( radians( tbl.latitude ) ) * cos( radians( tbl.longitude ) - radians(72.8612790) ) + sin( radians(19.2494730) ) * sin( radians( tbl.latitude ) ) ) ),2) AS distance
FROM `contractors` AS tbl

and gives the following result :

enter image description here

But the distance (it is in Miles I think) is not correct because when I run the following JavaScript code, It gives me some accurate result.

function distance(lat1, lon1, lat2, lon2, unit) {
    var radlat1 = Math.PI * lat1/180
    var radlat2 = Math.PI * lat2/180
    var radlon1 = Math.PI * lon1/180
    var radlon2 = Math.PI * lon2/180
    var theta = lon1-lon2
    var radtheta = Math.PI * theta/180
    var dist = Math.sin(radlat1) * Math.sin(radlat2) + Math.cos(radlat1) * Math.cos(radlat2) * Math.cos(radtheta);
    dist = Math.acos(dist)
    dist = dist * 180/Math.PI
    dist = dist * 60 * 1.1515
    if (unit=="K") { dist = dist * 1.609344 }
    if (unit=="N") { dist = dist * 0.8684 }
    alert(dist)
}            
distance(19.2494730, 72.8612790, 19.281085, 72.855994, 'K');

I also have a PHP snippet which gives me more accurate result

function distance($lat1, $lon1, $lat2, $lon2, $unit) {

  $theta = $lon1 - $lon2;
  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
  $dist = acos($dist);
  $dist = rad2deg($dist);
  $miles = $dist * 60 * 1.1515;
  $unit = strtoupper($unit);

  if ($unit == "K") {
    return ($miles * 1.609344);
  } else if ($unit == "N") {
      return ($miles * 0.8684);
    } else {
        return $miles;
      }
}

echo distance(19.2494730, 72.8612790,19.281085, 72.855994, "M") . " Miles<br>";

Could anybody help to make the above SQL query correct so that I can give me the accurate straight distance between all the areas and a particular Lat, Long.

check this http://stackoverflow.com/questions/13026675/calculating-distance-between-two-points-latitude-longitude — Felipe Pereira, Nov 25 '14 at 12:01

score 0 · Answer 1 · answered Nov 25 '14 at 12:11

Haversine Formula?

CREATE function [dbo].[GetDistance](
    @lat1  decimal(18,10),
    @lon1  decimal(18,10),
    @lat2  decimal(18,10),
    @lon2  decimal(18,10)
    )
    returns decimal(18,4)
    as
    begin
        declare @radius decimal(18,10)

        declare @a decimal(18,10)
        declare @distance decimal(18,4)

        -- Sets average radius of Earth in Kilometers
        set @radius = 6371.0E

        -- Convert degrees to radians
        set @lon1 = radians( @lon1)
        set @lon2 = radians( @lon2)
        set @lat1 = radians( @lat1 )
        set @lat2 = radians( @lat2 )

        set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) + 
            (cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )

        set @distance =
            @radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))

        return @distance
    end

How to use it in my query. Will it not make the query so slow?? — Manish Jangir, Nov 25 '14 at 12:12
select *,dbo.GetDistance(lat1,long1,lat2,long2) as distance from Table...and about if it will slow down your query? Try it and let me know. — j4rey, Nov 25 '14 at 12:18

Find distance between two latitude and longitude in SQL query

1 Answers1