## MySQL Great Circle Distance (Haversine formula)

find nearest location using latitude, longitude sql server

mysql latitude/longitude radius query

mysql latitude/longitude distance query

distant in mysql

great circle distance calculator

st_distance_sphere mysql

mysql select closest lat/long to specific location

I've got a working PHP script that gets Longitude and Latitude values and then inputs them into a MySQL query. I'd like to make it solely MySQL. Here's my current PHP Code:

if ($distance != "Any" && $customer_zip != "") { //get the great circle distance //get the origin zip code info $zip_sql = "SELECT * FROM zip_code WHERE zip_code = '$customer_zip'"; $result = mysql_query($zip_sql); $row = mysql_fetch_array($result); $origin_lat = $row['lat']; $origin_lon = $row['lon']; //get the range $lat_range = $distance/69.172; $lon_range = abs($distance/(cos($details[0]) * 69.172)); $min_lat = number_format($origin_lat - $lat_range, "4", ".", ""); $max_lat = number_format($origin_lat + $lat_range, "4", ".", ""); $min_lon = number_format($origin_lon - $lon_range, "4", ".", ""); $max_lon = number_format($origin_lon + $lon_range, "4", ".", ""); $sql .= "lat BETWEEN '$min_lat' AND '$max_lat' AND lon BETWEEN '$min_lon' AND '$max_lon' AND "; }

Does anyone know how to make this entirely MySQL? I've browsed the Internet a bit but most of the literature on it is pretty confusing.

From Google Code FAQ - Creating a Store Locator with PHP, MySQL & Google Maps:

Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

**Haversine Distance Computation with MySQL Stored Functions,** Stored MySQL functions for computing Great Circle distances A good MySQL Stored Function for the so-called Haversine formula is this: ? For example, you can use this stored haversine function to look in a zip code table and find the fifteen closest points to a particular point. SELECT zip, primary_city, latitude, longitude, 111.045*haversine(latitude,longitude,latpoint, longpoint) AS distance_in_km FROM zip JOIN ( SELECT 42.81 AS latpoint, -70.81 AS longpoint ) AS p ORDER BY distance_in_km LIMIT 15

`$greatCircleDistance = acos( cos($latitude0) * cos($latitude1) * cos($longitude0 - $longitude1) + sin($latitude0) * sin($latitude1));`

with latitude and longitude in radian.

so

SELECT acos( cos(radians( $latitude0 )) * cos(radians( $latitude1 )) * cos(radians( $longitude0 ) - radians( $longitude1 )) + sin(radians( $latitude0 )) * sin(radians( $latitude1 )) ) AS greatCircleDistance FROM yourTable;

is your SQL query

to get your results in Km or miles, multiply the result with the mean radius of Earth (`3959`

miles,`6371`

Km or `3440`

nautical miles)

The thing you are calculating in your example is a bounding box. If you put your coordinate data in a spatial enabled MySQL column, you can use MySQL's build in functionality to query the data.

SELECT id FROM spatialEnabledTable WHERE MBRWithin(ogc_point, GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'))

**MySQL, PostgreSQL, SQL Server,** Of course, finding distances on the surface of the earth means using Great Circle distances, worked out with the Haversine formula, also called The Vincenty great-circle distance formula. by on February 23, 2014 in Geography, MySQL, Reference • 5 Comments. This Vincenty formula is a more numerically stable version of the spherical cosine law formula (commonly and wrongly known as the Haversine formula) for computing great circle distances.

If you add helper fields to the coordinates table, you can improve response time of the query.

Like this:

CREATE TABLE `Coordinates` ( `id` INT(10) UNSIGNED NOT NULL COMMENT 'id for the object', `type` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'type', `sin_lat` FLOAT NOT NULL COMMENT 'sin(lat) in radians', `cos_cos` FLOAT NOT NULL COMMENT 'cos(lat)*cos(lon) in radians', `cos_sin` FLOAT NOT NULL COMMENT 'cos(lat)*sin(lon) in radians', `lat` FLOAT NOT NULL COMMENT 'latitude in degrees', `lon` FLOAT NOT NULL COMMENT 'longitude in degrees', INDEX `lat_lon_idx` (`lat`, `lon`) )

If you're using TokuDB, you'll get even better performance if you add clustering indexes on either of the predicates, for example, like this:

alter table Coordinates add clustering index c_lat(lat); alter table Coordinates add clustering index c_lon(lon);

You'll need the basic lat and lon in degrees as well as sin(lat) in radians, cos(lat)*cos(lon) in radians and cos(lat)*sin(lon) in radians for each point. Then you create a mysql function, smth like this:

CREATE FUNCTION `geodistance`(`sin_lat1` FLOAT, `cos_cos1` FLOAT, `cos_sin1` FLOAT, `sin_lat2` FLOAT, `cos_cos2` FLOAT, `cos_sin2` FLOAT) RETURNS float LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER BEGIN RETURN acos(sin_lat1*sin_lat2 + cos_cos1*cos_cos2 + cos_sin1*cos_sin2); END

This gives you the distance.

Don't forget to add an index on lat/lon so the bounding boxing can help the search instead of slowing it down (the index is already added in the CREATE TABLE query above).

INDEX `lat_lon_idx` (`lat`, `lon`)

Given an old table with only lat/lon coordinates, you can set up a script to update it like this: (php using meekrodb)

$users = DB::query('SELECT id,lat,lon FROM Old_Coordinates'); foreach ($users as $user) { $lat_rad = deg2rad($user['lat']); $lon_rad = deg2rad($user['lon']); DB::replace('Coordinates', array( 'object_id' => $user['id'], 'object_type' => 0, 'sin_lat' => sin($lat_rad), 'cos_cos' => cos($lat_rad)*cos($lon_rad), 'cos_sin' => cos($lat_rad)*sin($lon_rad), 'lat' => $user['lat'], 'lon' => $user['lon'] )); }

Then you optimize the actual query to only do the distance calculation when really needed, for example by bounding the circle (well, oval) from inside and outside. For that, you'll need to precalculate several metrics for the query itself:

// assuming the search center coordinates are $lat and $lon in degrees // and radius in km is given in $distance $lat_rad = deg2rad($lat); $lon_rad = deg2rad($lon); $R = 6371; // earth's radius, km $distance_rad = $distance/$R; $distance_rad_plus = $distance_rad * 1.06; // ovality error for outer bounding box $dist_deg_lat = rad2deg($distance_rad_plus); //outer bounding box $dist_deg_lon = rad2deg($distance_rad_plus/cos(deg2rad($lat))); $dist_deg_lat_small = rad2deg($distance_rad/sqrt(2)); //inner bounding box $dist_deg_lon_small = rad2deg($distance_rad/cos(deg2rad($lat))/sqrt(2));

Given those preparations, the query goes something like this (php):

$neighbors = DB::query("SELECT id, type, lat, lon, geodistance(sin_lat,cos_cos,cos_sin,%d,%d,%d) as distance FROM Coordinates WHERE lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d HAVING (lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d) OR distance <= %d", // center radian values: sin_lat, cos_cos, cos_sin sin($lat_rad),cos($lat_rad)*cos($lon_rad),cos($lat_rad)*sin($lon_rad), // min_lat, max_lat, min_lon, max_lon for the outside box $lat-$dist_deg_lat,$lat+$dist_deg_lat, $lon-$dist_deg_lon,$lon+$dist_deg_lon, // min_lat, max_lat, min_lon, max_lon for the inside box $lat-$dist_deg_lat_small,$lat+$dist_deg_lat_small, $lon-$dist_deg_lon_small,$lon+$dist_deg_lon_small, // distance in radians $distance_rad);

EXPLAIN on the above query might say that it's not using index unless there's enough results to trigger such. The index will be used when there's enough data in the coordinates table. You can add FORCE INDEX (lat_lon_idx) to the SELECT to make it use the index with no regards to the table size, so you can verify with EXPLAIN that it is working correctly.

With the above code samples you should have a working and scalable implementation of object search by distance with minimal error.

**The best way to locate, in MySQL 8 - Maatwebsite,** The Haversine formula determines the great-circle distance between two points on a sphere given their longitudes and latitudes. Important in navigation, it is a The Great Circle Distance Formula. What’s the distance along the surface of the (spherical) earth between two arbitrary points, in degrees, given by their latitude and longitude in degrees? That’s determined by the Spherical Cosine Law, or the Haversine Formula, which is this in MySQL syntax.

I have had to work this out in some detail, so I'll share my result. This uses a `zip`

table with `latitude`

and `longitude`

tables. It doesn't depend on Google Maps; rather you can adapt it to any table containing lat/long.

SELECT zip, primary_city, latitude, longitude, distance_in_mi FROM ( SELECT zip, primary_city, latitude, longitude,r, (3963.17 * ACOS(COS(RADIANS(latpoint)) * COS(RADIANS(latitude)) * COS(RADIANS(longpoint) - RADIANS(longitude)) + SIN(RADIANS(latpoint)) * SIN(RADIANS(latitude)))) AS distance_in_mi FROM zip JOIN ( SELECT 42.81 AS latpoint, -70.81 AS longpoint, 50.0 AS r ) AS p WHERE latitude BETWEEN latpoint - (r / 69) AND latpoint + (r / 69) AND longitude BETWEEN longpoint - (r / (69 * COS(RADIANS(latpoint)))) AND longpoint + (r / (69 * COS(RADIANS(latpoint)))) ) d WHERE distance_in_mi <= r ORDER BY distance_in_mi LIMIT 30

Look at this line in the middle of that query:

SELECT 42.81 AS latpoint, -70.81 AS longpoint, 50.0 AS r

This searches for the 30 nearest entries in the `zip`

table within 50.0 miles of the lat/long point 42.81/-70.81 . When you build this into an app, that's where you put your own point and search radius.

If you want to work in kilometers rather than miles, change `69`

to `111.045`

and change `3963.17`

to `6378.10`

in the query.

Here's a detailed writeup. I hope it helps somebody. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

**MySQL: Calculating Distance Based on Latitude and Longitude,** MySQL: Calculating Distance Based on Latitude and Longitude Applying the Haversine Formula to MySQL circumference of the earth along the equator is roughly 24,901.92 miles and there are 360 degrees in a circle. MySQL & GIS & The Haversine formula and distance between two points. MySQL is not the database that comes to mind first when people think of GIS. The databases listed below are:

I have written a procedure that can calculate the same, but you have to enter the latitude and longitude in the respective table.

drop procedure if exists select_lattitude_longitude; delimiter // create procedure select_lattitude_longitude(In CityName1 varchar(20) , In CityName2 varchar(20)) begin declare origin_lat float(10,2); declare origin_long float(10,2); declare dest_lat float(10,2); declare dest_long float(10,2); if CityName1 Not In (select Name from City_lat_lon) OR CityName2 Not In (select Name from City_lat_lon) then select 'The Name Not Exist or Not Valid Please Check the Names given by you' as Message; else select lattitude into origin_lat from City_lat_lon where Name=CityName1; select longitude into origin_long from City_lat_lon where Name=CityName1; select lattitude into dest_lat from City_lat_lon where Name=CityName2; select longitude into dest_long from City_lat_lon where Name=CityName2; select origin_lat as CityName1_lattitude, origin_long as CityName1_longitude, dest_lat as CityName2_lattitude, dest_long as CityName2_longitude; SELECT 3956 * 2 * ASIN(SQRT( POWER(SIN((origin_lat - dest_lat) * pi()/180 / 2), 2) + COS(origin_lat * pi()/180) * COS(dest_lat * pi()/180) * POWER(SIN((origin_long-dest_long) * pi()/180 / 2), 2) )) * 1.609344 as Distance_In_Kms ; end if; end ; // delimiter ;

**MySQL Great Circle Distance (Haversine formula),** MySQL Great Circle Distance (Haversine formula). php mysql · avatar. Milan Dhameliya. 41 10 months ago. I've got a working PHP script that gets Longitude The haversine formula determines the great-circle distance between two points on a sphere given their longitudes and latitudes. Important in navigation, it is a special case of a more general formula in spherical trigonometry, the law of haversines, that relates the sides and angles of spherical triangles.

**Haversine formula,** Implement a great-circle distance function, or use a library function, to show the 56 МК-61/52; 57 MySQL; 58 Nim; 59 Oberon-2; 60 Objeck; 61 Objective-C The haversine formula is an equation important in navigation, giving great-circle distances between two points on a sphere from their longitudes and latitudes. It is a special case of a more general formula in spherical trigonometry, the law of haversines, relating the sides and angles of spherical "triangles".

**MySQL Great Circle Distance (Haversine formula),** MySQL Great Circle Distance (Haversine formula). Hello All, I've got a PHP script that gets Longitude and Latitude valuesthen inputs them SQL query of Haversine formula in SQL server. MySQL: Distance between two people in a same area Is there a name for the great circle where latitude and

**Calculating distance using MySQL,** Previously, you may have had to manually implement something like the haversine formula manually to get this simple measurement. However, For a function that calculates the great circle distance on Earth between two points, Haversine is an odd name. It's analogous to calling the function for finding the distance between two Cartesian coordinates Pythagorean instead of Distance. Take care to use consistent capitalization (you slipped up in @POINT_b). The implementation looks fine.

##### Comments

- Based on all the excellent answers below, here is working sample of the Haversine formula in action
- Thanks for sharing that Michael.M
- stackoverflow.com/a/40272394/1281385 Has an example of how to make sure index are hit
- the sql statement is really good. but where can i pass my coordinates into this statement? i cant see anywhere coordinates have passed
- Replace 37 and -122 with your coordinates.
- I wonder about the performance implications of this if there are millions of places (+thousands of visitors)...
- You can narrow the query for better performance as explained in this doc: tr.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL
- @FosAvance Yes, this query would work if you have
`markers`

table with id, lan and lng fields. - If the positions are identical, it
*shouldn't*come out NULL, but as zero (as`ACOS(1)`

is 0). You*might*see rounding issues with the xaxis * xaxis + yaxis * yaxis + zaxis * zaxis going out of range for ACOS but you don't appear to be guarding against that? - The last (radians(lat) must be sin(radians(lat))