Find distance between two points using latitude and longitude in mysql

by

I think your question says you have the city values for the two cities between which you wish to compute the distance.

This query will do the job for you, yielding the distance in km. It uses the spherical cosine law formula.

Notice that you join the table to itself so you can retrieve two coordinate pairs for the computation.

SELECT a.city AS from_city, b.city AS to_city, 
   111.111 *
    DEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.Latitude))
         * COS(RADIANS(b.Latitude))
         * COS(RADIANS(a.Longitude - b.Longitude))
         + SIN(RADIANS(a.Latitude))
         * SIN(RADIANS(b.Latitude))))) AS distance_in_km
  FROM city AS a
  JOIN city AS b ON a.id <> b.id
 WHERE a.city = 3 AND b.city = 7

Notice that the constant 111.1111 is the number of kilometres per degree of latitude, based on the old Napoleonic definition of the metre as one ten-thousandth of the distance from the equator to the pole. That definition is close enough for location-finder work.

If you want statute miles instead of kilometres, use 69.0 instead.

http://sqlfiddle.com/#!9/21e06/412/0

If you’re looking for nearby points you may be tempted to use a clause something like this:

   HAVING distance_in_km < 10.0    /* slow ! */
    ORDER BY distance_in_km DESC

That is (as we say near Boston MA USA) wicked slow.

In that case you need to use a bounding box computation. See this writeup about how to do that. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

The formula contains a LEAST() function. Why? Because the ACOS() function throws an error if its argument is even slightly greater than 1. When the two points in question are very close together, the expression with the COS() and SIN() computations can sometimes yield a value slightly greater than 1 due to floating-point epsilon (inaccuracy). The LEAST(1.0, dirty-great-expression) call copes with that problem.

There’s a better way, a formula by Thaddeus Vincenty. It uses ATAN2() rather than ACOS() so it’s less susceptible to epsilon problems.

Edit 2022 (by Alexio Vay):
As of today the modern solution should be the following short code:

   select ST_Distance_Sphere(
    point(-87.6770458, 41.9631174),
    point(-73.9898293, 40.7628267))

Please check out the answer of Naresh Kumar.

More Related Contents:

  1. What is the ideal data type to use when storing latitude / longitude in a MySQL database?
  2. mySQL longitude and latitude query for other rows within x mile radius
  3. Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)
  4. Calculate distance given 2 points, latitude and longitude [duplicate]
  5. How do you use MYSQL in ColdFusion? [closed]
  6. Using backticks around field names
  7. How do I do a bulk insert in mySQL using node.js
  8. Reorder / reset auto increment primary key
  9. Required to join 2 tables with their FKs in a 3rd table
  10. Mysql query to dynamically convert rows to columns
  11. Copy/duplicate database without using mysqldump
  12. Suppress warning messages using mysql from within Terminal, but password written in bash script
  13. MySQL variable format for a “NOT IN” list of values
  14. Convert partially non-numeric text into number in MySQL query
  15. MYSQL ORDER BY CASE Issue
  16. How to get database structure in MySQL via query
  17. Can’t use a MySQL connection for entity framework 6
  18. MySQL JOIN with LIMIT 1 on joined table
  19. MySQL Select last 7 days
  20. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  21. MySQL view performance
  22. Does MySQL Regexp support Unicode matching
  23. mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists
  24. Remove DEFINER clause from MySQL Dumps
  25. How to retrieve JSON data from MySQL?
  26. Is incrementing a field in MySQL atomic?
  27. Encryption at rest and/or AES_ENCRYPT
  28. MySQL: Compare BETWEEN time
  29. What happens when I exhaust a bigint generated key? How to handle it?
  30. is there a length limit to group_concat or another reason why it would not work on a text field

Leave a Comment