CODEWITHSUNDEEP
sql
jnthnjns
jnthnjns

Reputation: 8925

Measuring distance between two Lat/Lng points

I am having issues trying to return the closest location to the user via an SQL statement. To test I am using the exact same coordinates, here is what I have:

SQL:

SELECT  `companies`.`customerName` , 
    (3959 * 
        ACOS(
            COS(
                RADIANS(37.367485) * COS(RADIANS(`locations`.`gps_lat`)) * 
                COS(
                    RADIANS(`locations`.`gps_lng`) - RADIANS(-77.399994) + 
                    SIN(RADIANS(37.367485)) * SIN(RADIANS(`locations`.`gps_lat`))
                )
            )
        )
    )
AS  `distance` 
FROM  `locations` 
JOIN  `companies` ON  `locations`.`co_id` 
HAVING  `distance` > 25
ORDER BY distance
LIMIT 0 , 10

Results: 

| customerName  | distance           |
| SOME COMPANY  | 1914.41747964854   |


locations table values: 

gps_lat   | gps_lng
37.367485 | -77.399994


I used the example from Google, I checked the formula a couple times and I can't seem to come up with where I went wrong. Any help is appreciated.

EDIT:
Since there seems to be some confusion on me knowing what I am doing:

The > was substituted to yield a result, 1914 is obviously greater then 25.

The application of this is passing user coordinates from an Android app to our web server. The Latitude and Longitude will be pulled from the $_GET values and cross-referenced from companies in our web servers MYSQL database.

The syntax above is just me checking my SQL statement in Mysql Workbench. Obviously I am looking for a zero value result.

Upvotes: 5

Views: 5324

Answers (2)

Shams Ud Din
Shams Ud Din

Reputation: 109

Ashok, you can calculate the distance between two cordinates by using the below formula:

enter image description here

where

R  =  radius of the earth (6,371 km)
Δlat =  |lat2- lat1|
Δlong = |long2- long1|

Upvotes: 1

jnthnjns
jnthnjns

Reputation: 8925

I think I have solved this issue by changing from Googles example to the formula provided by "Adventures of an adopted yooper". Using his/her version of the Haversine Formula.
Note: The Google example above is using Haversine as well.

SQL:

SELECT  `companies`.`customerName` , 
(2 * (3959 * ATAN2(
          SQRT(
            POWER(SIN((RADIANS(37.367485 - `locations`.`gps_lat` ) ) / 2 ), 2 ) +
            COS(RADIANS(`locations`.`gps_lat`)) *
            COS(RADIANS(37.367485 )) *
            POWER(SIN((RADIANS(-77.399994 - `locations`.`gps_lng` ) ) / 2 ), 2 )
          ),
          SQRT(1-(
            POWER(SIN((RADIANS(37.367485 - `locations`.`gps_lat` ) ) / 2 ), 2 ) +
            COS(RADIANS(`locations`.`gps_lat`)) *
            COS(RADIANS(37.367485)) *
            POWER(SIN((RADIANS(-77.399994 - `locations`.`gps_lng` ) ) / 2 ), 2 )
          ))
        )
      ))
AS 'distance'
FROM  `locations` 
JOIN  `companies` ON  `locations`.`co_id` 
HAVING  distance < 25
ORDER BY distance
LIMIT 0 , 10


For those curious on my application, Final PHP:

GET Value: ?lat=37.367485&lng=-77.399994

$earth_radius_miles = 3959; // Earth radius in miles
$earth_radius_kilometers = 6371; // Earth radius in kilometers
$result_radius = 10000; // Maximum distance in either miles or kilometers

$get_lat = $_GET["lat"];
$get_lng = $_GET["lng"];

$dbSelect = mysql_query("SELECT  `companies`.`customerName`,
(2 * (".$earth_radius_miles." * ATAN2(
          SQRT(
            POWER(SIN((RADIANS(".$get_lat." - `locations`.`gps_lat` ) ) / 2 ), 2 ) +
            COS(RADIANS(`locations`.`gps_lat`)) *
            COS(RADIANS(".$get_lat.")) *
            POWER(SIN((RADIANS(".$get_lng." - `locations`.`gps_lng` ) ) / 2 ), 2 )
          ),
          SQRT(1-(
            POWER(SIN((RADIANS(".$get_lat." - `locations`.`gps_lat` ) ) / 2 ), 2 ) +
            COS(RADIANS(`locations`.`gps_lat`)) *
            COS(RADIANS(".$get_lat.")) *
            POWER(SIN((RADIANS(".$get_lng." - `locations`.`gps_lng` ) ) / 2 ), 2 )
          ))
        )
      ))
AS 'distance'
FROM  `locations` 
JOIN  `companies` ON  `locations`.`co_id` 
HAVING  distance < ".$result_radius."
ORDER BY distance
LIMIT 0 , 10") 
or die(mysql_error());

Results:

[{"customerName":"SOME COMPANY","distance":"0"}]

I have tested these results with other coordinates and seems to be working perfectly. Haven't tested anything with great distance between the two points but my application doesn't require me to do so.

Upvotes: 5

Related Questions