Find the nearest location by latitude and longitude in MySQL using the Haversine formula

If you need to find the nearest location of any point of interest by latitude and longitude in MySQL, then you can use the Haversine formula. Following here is and example that assumes you have a table named "destinations" which contains amongst other things, the latitude and longitude of those 'destinations'. An example of valid destinations in a real-world application would be a table listing hotels and their lat/long coordinates which would enable one to write a query to return the nearest hotels from that table based on your lat/long coordinates. (The distance provided is in miles. To get the distance in kilometers, exchange 3539 with 6371)

SET @my_lat = 35.229205 ; -- the source latitude
SET @my_lon = -114.26811 ;-- the source longitude

SELECT *, ( 3959 * acos( cos( radians(
-- latitude
@my_lat) ) * cos( radians( 
destination.latitude ) ) * cos( radians( 
destination.longitude ) - radians(
-- longitude
@my_lon) ) + sin( radians(
-- latitude
@my_lat) ) * sin( radians( 
destination.latitude ) ) ) ) AS distance 
-- table containing targets to compare distance
FROM destination
ORDER BY distance LIMIT 1 ;

Full example at SQL Fiddle ---> http://sqlfiddle.com/#!2/abba1/4/0 ( PLEASE DONATE )

I recently needed to use this to find the official timezone for a city closest to a person based on their latitude, longitude coordinates in MySQL, so as not to have to depend on look-ups to a web service. For anyone interested, city-timezone data can be downloaded from the geonames.org website and loaded as the 'destinations' table.

Reference:
http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula

Load timezone data by city and country into MySQL

If ever you needed to look-up timezone data in your MySQL database you know that you need to find a nice source for that data and load it into your database.

This is how I did it:

1. Download timezone information by cities from this site: http://citytimezones.info/cms/pending_requests.htm (this example uses the CSV format - download that file)
2. Unzip the file and note the location (full path) of the "cities.txt" file.
3. Use the following SQL to create a table to hold the data:

   CREATE TABLE `timezone_cities` (
     `city` varchar(100) NOT NULL,
     `timezone` varchar(250) NOT NULL,
     `country` varchar(45) NOT NULL,
     `latitude` double NOT NULL,
     `longitude` double NOT NULL,
     `timezone_name` varchar(200) NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

4. Adapt and run the following SQL statement in MySQL to load the data:

   LOAD DATA LOCAL INFILE '/Users/nerd/Downloads/cities_csv/cities.txt'
   INTO TABLE timezone_cities
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   LINES TERMINATED BY '\r\n';
   

Make sure that when you run the LOAD DATA query, that there are no errors or warnings.
Note that I did not optimize the design of the table (field sizes) to match the data ( DIY )