latitude/longitude trouver la latitude/longitude la plus proche - sql complexe ou calcul complexe !

Question

J'ai une latitude et une longitude et je veux extraire l'enregistrement de la base de données, qui a la latitude et la longitude la plus proche par la distance, si cette distance est plus longue que celle spécifiée, alors ne le récupère pas.

Table structure:
id
latitude
longitude
place name
city
country
state
zip
sealevel

Answer 1

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;

où [starlat] et [démarrage] est la position où l'on commence à mesurer la distance.

Answer 2

Il faut traduire la distance en degrés de longitude et de latitude, filtrer sur la base de ces degrés pour délimiter les entrées qui se trouvent à peu près dans la boîte englobante, puis effectuer un filtrage plus précis de la distance. Voici un excellent document qui explique comment faire tout cela :

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

Answer 3

Voici ma solution complète mise en œuvre en PHP.

Cette solution utilise la formule de Haversine telle que présentée dans http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL .

Il convient de noter que la formule Haversine présente des faiblesses autour des pôles. Cette réponse montre comment mettre en œuvre le formule de distance du Grand Cercle de vincenty pour contourner ce problème, mais j'ai choisi de n'utiliser que la Haversine car elle est suffisamment bonne pour mes besoins.

Je stocke la latitude en tant que DECIMAL(10,8) et la longitude en tant que DECIMAL(11,8). J'espère que cela vous aidera !

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 - abs(latitude))*pi()/180/2),2)
          +COS($origLat*pi()/180 )*COS(abs(latitude)*pi()/180)
          *POWER(SIN(($origLon-longitude)*pi()/180/2),2))) 
          as distance FROM $tableName WHERE 
          longitude between ($origLon-$dist/abs(cos(radians($origLat))*69)) 
          and ($origLon+$dist/abs(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']."<BR>";
}
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"
);
?>

Il est possible d'améliorer les performances en utilisant une procédure stockée MySQL, comme le suggère l'article "Geo-Distance-Search-with-MySQL" publié ci-dessus.

J'ai une base de données de ~17 000 places et le temps d'exécution des requêtes est de 0,054 seconde.

Answer 4

Juste au cas où vous seriez paresseux comme moi, voici une solution amalgamée à partir de cette réponse et d'autres réponses sur SO.

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;

Answer 5

C'est facile ;)

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

Il suffit de remplacer les coordonnées par celles que vous souhaitez. Les valeurs doivent être stockées sous forme de double. Ceci est un exemple fonctionnel de MySQL 5.x.

Cheers