Breitengrad / Längengrad Finden Sie den nächsten Breiten- / Längengrad – komplexe sql oder komplexe Berechnung

Ich habe Breiten- und Längengrad und möchte den Datensatz aus der database ziehen, die den nächsten Breiten- und Längengrad hat, wenn dieser Abstand länger als der angegebene ist, und ihn dann nicht abrufen.

Tabellenstruktur:

id latitude longitude place name city country state zip sealevel 

Was Sie brauchen, ist die Distanz in Längen- und Breitengrade zu übersetzen, basierend auf denen zu filtern, um die Einträge zu begrenzen, die ungefähr in der Bounding Box sind, und dann einen präziseren Entfernungsfilter zu machen. Hier ist ein großartiges Papier, das erklärt, wie man das alles macht:

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

 SELECT latitude, longitude, SQRT( POW(69.1 * (latitude - [startlat]), 2) + POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance FROM TableName HAVING distance < 25 ORDER BY distance; 

wo [starlat] und [startlng] ist die Position, an der man mit dem Messen der Entfernung beginnt.

Google-Lösung:

Erstellen der Tabelle

Wenn Sie die MySQL-Tabelle erstellen, sollten Sie besonders auf die Attribute lat und lng achten. Mit den aktuellen Zoom-functionen von Google Maps sollten Sie nur 6 Nachkommastellen benötigen. Um den für Ihre Tabelle erforderlichen Speicherplatz auf ein Minimum zu beschränken, können Sie angeben, dass die Attribute lat und lng Floats der Größe (10,6) sind. Dadurch können die Felder 6 Nachkommastellen plus bis zu 4 Nachkommastellen speichern, z. B. -123,456789 Grad. Ihre Tabelle sollte auch ein ID-Attribut haben, das als Primärschlüssel dient.

 CREATE TABLE `markers` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 60 ) NOT NULL , `address` VARCHAR( 80 ) NOT NULL , `lat` FLOAT( 10, 6 ) NOT NULL , `lng` FLOAT( 10, 6 ) NOT NULL ) ENGINE = MYISAM ; 

Füllen der Tabelle

Nach dem Erstellen der Tabelle ist es an der Zeit, sie mit Daten zu füllen. Die unten angegebenen Beispieldaten sind für ungefähr 180 Pizzarias, die in den Vereinigten Staaten verstreut sind. In phpMyAdmin können Sie die Registerkarte IMPORT verwenden, um verschiedene Dateiformate einschließlich CSV (durch Kommas getrennte Werte) zu importieren. Microsoft Excel und Google Spreadsheets exportieren beide in das CSV-Format, sodass Sie Daten einfach aus Tabellenkalkulationen in MySQL-Tabellen übertragen können, indem Sie CSV-Dateien exportieren / importieren.

 INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Frankie Johnnie & Luigo Too','939 W El Camino Real, Mountain View, CA','37.386339','-122.085823'); INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Amici\'s East Coast Pizzeria','790 Castro St, Mountain View, CA','37.38714','-122.083235'); INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Kapp\'s Pizza Bar & Grill','191 Castro St, Mountain View, CA','37.393885','-122.078916'); INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Round Table Pizza: Mountain View','570 N Shoreline Blvd, Mountain View, CA','37.402653','-122.079354'); INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Tony & Alba\'s Pizza & Pasta','619 Escuela Ave, Mountain View, CA','37.394011','-122.095528'); INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Oregano\'s Wood-Fired Pizza','4546 El Camino Real, Los Altos, CA','37.401724','-122.114646'); 

Suchen nach Standorten mit MySQL

Um Orte in Ihrer Markentabelle zu finden, die sich in einem bestimmten Radius von einer gegebenen Breite / Länge befinden, können Sie eine SELECT-statement basierend auf der Haversine-Formel verwenden. Die Haversine-Formel wird allgemein zum Berechnen von Großkreisabständen zwischen zwei Koordinatenpaaren auf einer Kugel verwendet. Eine eingehende mathematische Erklärung wird von Wikipedia gegeben, und eine gute Diskussion der Formel in Bezug auf die Programmierung ist auf der Site von Movable Type.

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; 

https://developers.google.com/maps/articles/phpsqlsearch_v3#creating-the-map

Hier ist meine vollständige Lösung in PHP implementiert.

Diese Lösung verwendet die Haversine-Formel wie in http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL dargestellt .

Es sollte beachtet werden, dass die Haversine-Formel an den Polen Schwächen aufweist. Diese Antwort zeigt, wie man die vincenty Great Circle Distance-Formel implementiert, um das zu umgehen , aber ich habe mich dafür entschieden, Haversine einfach zu benutzen, weil es für meine Zwecke gut genug ist.

Ich speichere den Breitengrad als DECIMAL (10,8) und den Längengrad als DECIMAL (11,8). Hoffentlich hilft das!

showClosest.php

 < ?PHP /** * Use the Haversine Formula to display the 100 closest matches to $origLat, $origLon * Only search the MySQL table $tableName for matches within a 10 mile ($dist) radius. */ include("./assets/db/db.php"); // Include database connection function $db = new database(); // Initiate a new MySQL connection $tableName = "db.table"; $origLat = 42.1365; $origLon = -71.7559; $dist = 10; // This is the maximum distance (in miles) away from $origLat, $origLon in which to search $query = "SELECT name, latitude, longitude, 3956 * 2 * ASIN(SQRT( POWER(SIN(($origLat - latitude)*pi()/180/2),2) +COS($origLat*pi()/180 )*COS(latitude*pi()/180) *POWER(SIN(($origLon-longitude)*pi()/180/2),2))) as distance FROM $tableName WHERE longitude between ($origLon-$dist/cos(radians($origLat))*69) and ($origLon+$dist/cos(radians($origLat))*69) and latitude between ($origLat-($dist/69)) and ($origLat+($dist/69)) having distance < $dist ORDER BY distance limit 100"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { echo $row['name']." > ".$row['distance']."
"; } mysql_close($db); ?>

./assets/db/db.php

 < ?PHP /** * Class to initiate a new MySQL connection based on $dbInfo settings found in dbSettings.php * * @example $db = new database(); // Initiate a new database connection * @example mysql_close($db); // close the connection */ class database{ protected $databaseLink; function __construct(){ include "dbSettings.php"; $this->database = $dbInfo['host']; $this->mysql_user = $dbInfo['user']; $this->mysql_pass = $dbInfo['pass']; $this->openConnection(); return $this->get_link(); } function openConnection(){ $this->databaseLink = mysql_connect($this->database, $this->mysql_user, $this->mysql_pass); } function get_link(){ return $this->databaseLink; } } ?> 

./assets/db/dbSettings.php

 < ?php $dbInfo = array( 'host' => "localhost", 'user' => "root", 'pass' => "password" ); ?> 

Es kann möglich sein, die performance zu erhöhen, indem Sie eine gespeicherte MySQL-Prozedur verwenden, wie im obigen Artikel “Geo-Distance-Search-with-MySQL” vorgeschlagen.

Ich habe eine database von ~ 17.000 Orten und die Abfrage Ausführungszeit ist 0.054 Sekunden.

Nur für den Fall, dass Sie faul sind wie ich, hier ist eine Lösung, die sich aus dieser und anderen Antworten auf SO zusammensetzt.

 set @orig_lat=37.46; set @orig_long=-122.25; set @bounding_distance=1; SELECT * ,((ACOS(SIN(@orig_lat * PI() / 180) * SIN(`lat` * PI() / 180) + COS(@orig_lat * PI() / 180) * COS(`lat` * PI() / 180) * COS((@orig_long - `long`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `cities` WHERE ( `lat` BETWEEN (@orig_lat - @bounding_distance) AND (@orig_lat + @bounding_distance) AND `long` BETWEEN (@orig_long - @bounding_distance) AND (@orig_long + @bounding_distance) ) ORDER BY `distance` ASC limit 25; 

Einfaches 😉

 SELECT * FROM `WAYPOINTS` W ORDER BY ABS(ABS(W.`LATITUDE`-53.63) + ABS(W.`LONGITUDE`-9.9)) ASC LIMIT 30; 

Ersetzen Sie einfach die Koordinaten durch Ihre benötigten. Die Werte müssen doppelt gespeichert werden. Dies ist ein funktionierendes MySQL 5.x Beispiel.

Prost

Sie suchen nach Dingen wie der hairsine Formel . Siehe auch hier .

Es gibt andere, aber das wird am häufigsten zitiert.

Wenn Sie nach etwas noch Robusterem suchen, sollten Sie sich die GIS-functionen Ihrer database ansehen. Sie sind in der Lage, einige coole Dinge wie sagen Ihnen, ob ein Punkt (Stadt) innerhalb eines bestimmten Polygons (Region, Land, Kontinent) erscheint.

Überprüfen Sie diesen Code anhand des Artikels Geo-Distance-Search-with-MySQL :

Beispiel: Finden Sie die 10 nächstgelegenen Hotels zu meinem aktuellen Standort in einem Umkreis von 10 Meilen:

 #Please notice that (lat,lng) values mustn't be negatives to perform all calculations set @my_lat=34.6087674878572; set @my_lng=58.3783670308302; set @dist=10; #10 miles radius SELECT dest.id, dest.lat, dest.lng, 3956 * 2 * ASIN(SQRT(POWER(SIN((@my_lat -abs(dest.lat)) * pi()/180 / 2),2) + COS(@my_lat * pi()/180 ) * COS(abs(dest.lat) * pi()/180) * POWER(SIN((@my_lng - abs(dest.lng)) * pi()/180 / 2), 2)) ) as distance FROM hotel as dest having distance < @dist ORDER BY distance limit 10; #Also notice that distance are expressed in terms of radius. 

Versuchen Sie dies, es zeigt die nächsten Punkte zu den angegebenen Koordinaten (innerhalb von 50 km). Es funktioniert perfekt:

 SELECT m.name, m.lat, m.lon, p.distance_unit * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(m.lat)) * COS(RADIANS(p.longpoint) - RADIANS(m.lon)) + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(m.lat)))) AS distance_in_km FROM  AS m JOIN ( SELECT  AS latpoint,  AS longpoint, 50.0 AS radius, 111.045 AS distance_unit ) AS p ON 1=1 WHERE m.lat BETWEEN p.latpoint - (p.radius / p.distance_unit) AND p.latpoint + (p.radius / p.distance_unit) AND m.lon BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) ORDER BY distance_in_km 

Ändern einfach . und

Sie können mehr über diese Lösung hier lesen: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

 simpledb.execSQL("CREATE TABLE IF NOT EXISTS " + tablename + "(id INTEGER PRIMARY KEY AUTOINCREMENT,lat double,lng double,address varchar)"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2891001','70.780154','craftbox');"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2901396','70.7782428','kotecha');");//22.2904718 //70.7783906 simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2863155','70.772108','kkv Hall');"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.275993','70.778076','nana mava');"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2667148','70.7609386','Govani boys hostal');"); double curentlat=22.2667258; //22.2677258 double curentlong=70.76096826;//70.76096826 double curentlat1=curentlat+0.0010000; double curentlat2=curentlat-0.0010000; double curentlong1=curentlong+0.0010000; double curentlong2=curentlong-0.0010000; try{ Cursor c=simpledb.rawQuery("select * from '"+tablename+"' where (lat BETWEEN '"+curentlat2+"' and '"+curentlat1+"') or (lng BETWEEN '"+curentlong2+"' and '"+curentlong1+"')",null); Log.d("SQL ", c.toString()); if(c.getCount()>0) { while (c.moveToNext()) { double d=c.getDouble(1); double d1=c.getDouble(2); } } } catch (Exception e) { e.printStackTrace(); } 

Es klingt, als ob Sie eine Suche nach dem nächsten Nachbarn mit einer gewissen Entfernung machen möchten. Soweit ich weiß, unterstützt SQL nichts dergleichen, und Sie müssten eine alternative Datenstruktur wie einen R-Baum oder einen kd-Baum verwenden .

Finden Sie die nächsten Benutzer zu meinem:

Entfernung in Metern

Basiert in Vincentys Formel

Ich habe Benutzer Tabelle:

 +----+-----------------------+---------+--------------+---------------+ | id | email | name | location_lat | location_long | +----+-----------------------+---------+--------------+---------------+ | 13 | xxxxxx@xxxxxxxxxx.com | Isaac | 17.2675625 | -97.6802361 | | 14 | xxxx@xxxxxxx.com.mx | Monse | 19.392702 | -99.172596 | +----+-----------------------+---------+--------------+---------------+ 

sql:

 -- my location: lat 19.391124 -99.165660 SELECT (ATAN( SQRT( POW(COS(RADIANS(users.location_lat)) * SIN(RADIANS(users.location_long) - RADIANS(-99.165660)), 2) + POW(COS(RADIANS(19.391124)) * SIN(RADIANS(users.location_lat)) - SIN(RADIANS(19.391124)) * cos(RADIANS(users.location_lat)) * cos(RADIANS(users.location_long) - RADIANS(-99.165660)), 2) ) , SIN(RADIANS(19.391124)) * SIN(RADIANS(users.location_lat)) + COS(RADIANS(19.391124)) * COS(RADIANS(users.location_lat)) * COS(RADIANS(users.location_long) - RADIANS(-99.165660)) ) * 6371000) as distance, users.id FROM users ORDER BY distance ASC 

Radius der Erde: 6371000 (in Metern)

Klingt, als ob Sie nur PostGIS, SpatialLite, SQLServer2008 oder Oracle Spatial verwenden sollten. Sie können alle diese Frage mit räumlichem SQL beantworten.

Im Extremfall versagt dieser Ansatz, aber für die performance habe ich die Trigonometrie übersprungen und einfach die Diagonale im Quadrat berechnet.

MS SQL Edition hier:

  DECLARE @SLAT AS FLOAT DECLARE @SLON AS FLOAT SET @SLAT = 38.150785 SET @SLON = 27.360249 SELECT TOP 10 [LATITUDE], [LONGITUDE], SQRT( POWER(69.1 * ([LATITUDE] - @SLAT), 2) + POWER(69.1 * (@SLON - [LONGITUDE]) * COS([LATITUDE] / 57.3), 2)) AS distance FROM [TABLE] ORDER BY 3 

Dieses Problem ist nicht sehr schwierig, aber es wird komplizierter, wenn Sie es optimieren müssen.

Was ich meine ist, haben Sie 100 Standorte in Ihrer database oder 100 Millionen? Es macht einen großen Unterschied.

Wenn die Anzahl der Speicherorte klein ist, entfernen Sie sie aus SQL und in Code, indem Sie einfach -> drücken

 Select * from Location 

Sobald Sie sie in Code erhalten haben, berechnen Sie den Abstand zwischen jedem Breitengrad und Ihrem Original mit der Haversine-Formel und sortieren Sie sie.