MySQL-Großkreisabstand (Haversine-Formel)

Ich habe ein funktionierendes PHP-Skript, das Longitude und Latitude Werte erhält und dann in eine MySQL-Abfrage eingibt. Ich möchte es nur MySQL machen. Hier ist mein aktueller 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 "; } 

Kann jemand das vollständig MySQL machen? Ich habe das Internet ein wenig durchsucht, aber die meisten Literatur darüber ist ziemlich verwirrend.

   

Aus Google Code FAQ – Erstellen eines Filialfinders mit PHP, MySQL und Google Maps :

Hier ist die SQL-statement, die die nächsten 20 Orte findet, die sich innerhalb eines Radius von 25 Meilen zur 37, -122-Koordinate befinden. Er berechnet die Entfernung basierend auf dem Breiten- / Längengrad dieser Zeile und der Zielbreite / -länge und fragt dann nur nach Zeilen, bei denen der Abstandswert kleiner als 25 ist, sortiert die gesamte Abfrage nach Entfernung und begrenzt sie auf 20 Ergebnisse. Um nach Kilometern anstelle von Meilen zu suchen, ersetzen Sie 3959 durch 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; 

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

mit Breiten- und Längengrad in Radiant.

damit

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

ist Ihre SQL-Abfrage

Um Ihre Ergebnisse in Km oder Meilen zu erhalten, multiplizieren Sie das Ergebnis mit dem mittleren Radius der Erde ( 3959 Meilen, 6371 Km oder 3440 Seemeilen)

Die Sache, die Sie in Ihrem Beispiel berechnen, ist eine Begrenzungsbox. Wenn Sie Ihre Koordinatendaten in eine spatial-fähige MySQL-Spalte einfügen , können Sie die integrierten functionen von MySQL verwenden, um die Daten abzufragen.

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

Wenn Sie der Koordinaten-Tabelle Hilfsfelder hinzufügen, können Sie die Antwortzeit der Abfrage verbessern.

So was:

 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`) ) 

Wenn Sie TokuDB verwenden, erhalten Sie eine noch bessere performance, wenn Sie Clusterindizes für eines der Prädikate hinzufügen, zum Beispiel wie folgt:

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

Sie benötigen die grundlegenden Längen- und Breitengrade in Grad sowie sin (lat) in Radianten, cos (lat) * cos (lon) in Radianten und cos (lat) * sin (lon) in Radianten für jeden Punkt. Dann erstellen Sie eine mysql-function, etw. So:

 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 

Das gibt dir die Distanz.

Vergessen Sie nicht, einen Index für lat / lon hinzuzufügen, damit das Bounding Boxing die Suche unterstützen kann, anstatt sie zu verlangsamen (der Index wurde bereits in der Abfrage CREATE TABLE oben hinzugefügt).

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

Bei einer alten Tabelle mit nur lat / lon-Koordinaten können Sie ein Skript einrichten, um es so zu aktualisieren: (php mit 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'] )); } 

Dann optimieren Sie die eigentliche Abfrage, um die Abstandsberechnung nur dann durchzuführen, wenn sie wirklich benötigt wird, z. B. indem Sie den Kreis (gut, oval) von innen und außen begrenzen. Dafür müssen Sie mehrere Messwerte für die Abfrage selbst vorberechnen:

 // 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)); 

Mit diesen Vorbereitungen geht die Abfrage in etwa so (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 zu der obigen Abfrage könnte sagen, dass es keinen Index verwendet, es sei denn, es gibt genügend Ergebnisse, um einen solchen auszulösen. Der Index wird verwendet, wenn in der Koordinatentabelle genügend Daten vorhanden sind. Sie können FORCE INDEX (lat_lon_idx) zu SELECT hinzufügen, um den Index ohne Rücksicht auf die Tabellengröße zu verwenden, damit Sie mit EXPLAIN überprüfen können, dass es ordnungsgemäß funktioniert.

Mit den obigen Codebeispielen sollten Sie eine funktionierende und skalierbare Implementierung der Objektsuche nach Entfernung mit minimalem Fehler haben.

Ich musste das etwas genauer ausarbeiten, also teile ich mein Ergebnis. Dies verwendet eine zip Tabelle mit latitude und longitude . Es hängt nicht von Google Maps ab; Sie können es vielmehr an jede Tabelle anpassen, die lat / long enthält.

 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 

Sehen Sie sich diese Zeile in der Mitte dieser Abfrage an:

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

Dies sucht nach den 30 nächsten Einträgen in der zip Tabelle innerhalb von 50 Meilen des Lat / Long-Punktes 42,81 / -70,81. Wenn Sie diese in eine App einbauen, legen Sie dort Ihren eigenen Punkt und Suchradius.

Wenn Sie lieber in Kilometern als in Meilen arbeiten möchten, ändern Sie 69 in 111.045 und ändern Sie 3963.17 in 6378.10 in der Abfrage.

Hier ist eine detaillierte Beschreibung. Ich hoffe, es hilft jemandem. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

Ich habe eine Prozedur geschrieben, die dasselbe berechnen kann, aber Sie müssen den Breiten- und Längengrad in der jeweiligen Tabelle eingeben.

 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 ; 

Ich kann die obige Antwort nicht kommentieren, aber sei vorsichtig mit @ Pavel Chuchuvas Antwort. Diese Formel liefert kein Ergebnis, wenn beide Koordinaten identisch sind. In diesem Fall ist der Abstand null und die Zeile wird daher nicht mit dieser Formel zurückgegeben.

Ich bin kein MySQL-Experte, aber das scheint für mich zu funktionieren:

 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 OR distance IS NULL ORDER BY distance LIMIT 0 , 20; 
  SELECT *, ( 6371 * acos(cos(radians(search_lat)) * cos(radians(lat) ) * cos(radians(lng) - radians(search_lng)) + sin(radians(search_lat)) * sin(radians(lat))) ) AS distance FROM table WHERE lat != search_lat AND lng != search_lng AND distance < 25 ORDER BY distance FETCH 10 ONLY 

für eine Entfernung von 25 km

Ich dachte, meine JavaScript-Implementierung wäre eine gute Referenz zu:

 /* * Check to see if the second coord is within the precision ( meters ) * of the first coord and return accordingly */ function checkWithinBound(coord_one, coord_two, precision) { var distance = 3959000 * Math.acos( Math.cos( degree_to_radian( coord_two.lat ) ) * Math.cos( degree_to_radian( coord_one.lat ) ) * Math.cos( degree_to_radian( coord_one.lng ) - degree_to_radian( coord_two.lng ) ) + Math.sin( degree_to_radian( coord_two.lat ) ) * Math.sin( degree_to_radian( coord_one.lat ) ) ); return distance < = precision; } /** * Get radian from given degree */ function degree_to_radian(degree) { return degree * (Math.PI / 180); }