Vous êtes ici Articles > Calcul de la Distance entre deux coordonnées GPS avec MySQL

 

Calcul de la Distance entre deux coordonnées GPS avec MySQL

Du coté PHP
 
Forty
Vu 38651 fois
Enregistré le 19 Oct 2009
  • Digg cet article sur digg.com
  • Bookmark cet article sur del.icio.us
  • Bookmark cet article sur Google
  • Bookmark cet article sur Yahoo
  • Ajoute Calcul de la Distance entre deux coordonnées GPS avec MySQL
  • Partage cet article sur Facebook
 
 
 
 

Calcul de la Distance entre deux coordonnées GPS avec MySQL


Beaucoup de développeurs utilisent des services de localisation comme Google Map pour rechercher des lieux et donner la distance pour s'y rendre a partir d'un point précis. A l'heure actuelle il est simple de construire soi même ses propres outils.

Une des solutions est d'utiliser ce snippet PHP puis d'extraire toutes les données pour calculer la distance, de trier par distance croissante et de stocker le nombre d'enregistrements à traiter. Cette solution est fort coûteuse en ressource système puisqu'elle nécessite d'extraire toutes les données. La base de données est vite saturée. En y ajoutant le traitement PHP pour trier et filtrer cela pose de sérieux problème de transfert entre le moteur php et les caches MYSQL vite surchargée.

Création de la Fonction MySQL pour calculer la distance en mètres entre 2 points

La solution idéale est d'utiliser la puissance des fonctions MySQL pour réaliser ce traitement complexe et d'extraire en une seule requête les données souhaitées sans risquer de surcharger le serveur.

Plusieurs parties sont à voir, dans un premier temps il faut trier et extraire un nombre limité d'enregistrements et MySQL fait cela très bien avec les options SORT BY et LIMIT. Ensuite il faut déterminer la distance entre deux points sur Terre.
Mathématiquement il s'agit de l'orthodromie qui est le chemin le plus court entre deux points d'une sphère. Dans un langage simple, nous dirions qu'il faut mesurer la longueur de l'arc de grand cercle passant par deux points.

Afin de calculer la distance entre 2 points il faut créer une fonction MySQL (nous parlons ici de procédure stockée) qui prend en paramètre la latitude et la longitude de 2 points (exprimées en degrés).

Pour créer cette fonction il faut soit passer par la console MySQL ou par phpMyAdmin.
Le plus simple est d’utiliser la console MySQL car l'opération se fait en 1 étape mais elle n'est pas toujours accessible (notamment sur des serveurs mutualisés).
C'est pour cela que nous vous proposons les 2 méthodes.

Première méthode depuis la console MySQL

A partir de la console MySQL (avec Wampserver 2.0, c’est dans le menu MySQL), voici les lignes qu’il faut entrer :
.01
.02
.03
.04
.05
.06
.07
.08
.09
.10
.11
.12
.13
.14
.15
.16
.17
.18
.19
.20
.21
.22
.23
 
DELIMITER |
DROP FUNCTION IF EXISTS get_distance_metres|
CREATE FUNCTION get_distance_metres (lat1 DOUBLE, lng1 DOUBLE, lat2 DOUBLE, lng2 DOUBLE) RETURNS DOUBLE
BEGIN
    DECLARE rlo1 DOUBLE;
    DECLARE rla1 DOUBLE;
    DECLARE rlo2 DOUBLE;
    DECLARE rla2 DOUBLE;
    DECLARE dlo DOUBLE;
    DECLARE dla DOUBLE;
    DECLARE a DOUBLE;
    
    SET rlo1 = RADIANS(lng1);
    SET rla1 = RADIANS(lat1);
    SET rlo2 = RADIANS(lng2);
    SET rla2 = RADIANS(lat2);
    SET dlo = (rlo2 - rlo1) / 2;
    SET dla = (rla2 - rla1) / 2;
    SET a = SIN(dla) * SIN(dla) + COS(rla1) * COS(rla2) * SIN(dlo) * SIN(dlo);
    RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a)));
END|
DELIMITER ;
Explications:
La première ligne supprime la fonction stockée si elle existe. La seconde ligne permet de changer le délimiteur séparant les instructions (il est obligatoire de remplacer le délimiteur ";"  par défaut pour qu'il ne soit pas confondu avec les points virgule en fin d'instruction de la fonction (dans le bloc BEGIN/END). L'instruction CREATE est donc terminée par le nouveau délimiteur "|" (après END).

Seconde méthode depuis phpMyAdmin

A partir de phpMyAdmin l'opération se fait à partir de l'onglet SQL en saisissant les lignes suivantes :
.01
.02
.03
.04
.05
.06
.07
.08
.09
.10
.11
.12
.13
.14
.15
.16
.17
.18
.19
.20
.21
 
DROP FUNCTION IF EXISTS get_distance_metres|
CREATE FUNCTION get_distance_metres (lat1 DOUBLE, lng1 DOUBLE, lat2 DOUBLE, lng2 DOUBLE) RETURNS DOUBLE
BEGIN
    DECLARE rlo1 DOUBLE;
    DECLARE rla1 DOUBLE;
    DECLARE rlo2 DOUBLE;
    DECLARE rla2 DOUBLE;
    DECLARE dlo DOUBLE;
    DECLARE dla DOUBLE;
    DECLARE a DOUBLE;
    
    SET rlo1 = RADIANS(lng1);
    SET rla1 = RADIANS(lat1);
    SET rlo2 = RADIANS(lng2);
    SET rla2 = RADIANS(lat2);
    SET dlo = (rlo2 - rlo1) / 2;
    SET dla = (rla2 - rla1) / 2;
    SET a = SIN(dla) * SIN(dla) + COS(rla1) * COS(rla2) * SIN(dlo) * SIN(dlo);
    RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a)));
END|
Important:
Il ne faut pas oublier de changer le délimiteur ";" par défaut et mettre "|" (voir copie d'écran). Ce délimiteur permet de séparer plusieurs instructions SQL. Si vous laissez le délimiteur par défaut vous aller avoir une erreur de syntaxe car l'instruction CREATE FUNCTION contient des ";" dans son bloc BEGIN/END.

Voir image:


Utilisation de la fonction avec une requête MySQL

Maintenant que la fonction est créée, nous pouvons l'utiliser. A noter qu'elle s'utilise comme n'importe quelle autre fonction MySQL existante.

Recherchons par exemple les 10 restaurants les plus proches de la Tour Eiffel à Paris (latitude : 48.858205, longitude : 2.294359) et dont la distance est inférieure à 1 kilomètre.
La base de données contient une table "restaurants". Cette table contient, entre autre, les champs "lat" et "lng" correspondant à la latitude et à la longitude du restaurant.

La requête SQL:
.01
.02
.03
.04
.05
.06
 
SELECT *, get_distance_metres('48.858205', '2.294359', lat, lng) 
      AS proximite 
      FROM restaurants 
      WHERE proximite < 1000 ORDER BY proximite ASC 
      LIMIT 10
Et voilà! Vous voyez, c'est pas très difficile de créer ses propres outils de localisation.



Commentaires
 Article vraiment intéressant et qui déporte le calcul à la source SQL à la place des langages de programmation.
Par contre, je ne comprends pas comment cette requête fonctionne : 
SELECT *, 
 get_distance_metres('48.858205', '2.294359', lat, lng) AS proximite   FROM restaurants 
WHERE proximite < 1000 ORDER BY proximite ASC LIMIT 10

 J'obtient l'erreur suivante : 
#1054 - Unknown column 'proximite' in 'where clause'
 
Pas mal et très intéressant, toutefois impossible à appliquer sur mon serveur MySQL 5.0.44 qui me dit :
requête SQL:
CREATE FUNCTION get_distance_metres(
lat1 DOUBLE,

lng1 DOUBLE,

lat2 DOUBLE,

lng2 DOUBLE

) RETURNS DOUBLE BEGIN DECLARE rlo1 DOUBLE;


MySQL a répondu:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Pourtant je rentre bien des ; il les transforme en , mais il n'aime pas la syntaxe... que faire ???
Alex
 
 L'idéal et de se connecter en ligne de commande et copier/coller le code SQL de la section Première méthode depuis la console MySQL afin de garder la déclaration et utilisation d'un délimiteur différent, le |
J'ai rencontré un autre problème ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) résolue sur placeoweb. 
 
Ennorme !
Merci beaucoup, tu me sauve la vie.
ps :
#1054 - Unknown column 'proximite' in 'where clause'
Ca parrais clair comme erreur non ? ajoute un champs 'proximite' dans ta table ...
 
Super
Mais j'ai aussi le même problème
#1054 - Unknown column 'proximite' in 'where clause'
Il ne faut pas ajouter un champ dans la table car ce champ est généré dynamiquement pas la requete avec AS proximite.
Comment contournez vous ce problème merci
 
Bonjour,
Tout d'abord merci pour ce code bien pratique.
Cependant je rencontre exactement le même problème qu'Alex sur 5.0.83.
Aie-je oublié quelque chose ou est-ce simplement pas compatible avec ma version de mysql ?
Merci d'avance.
Hugo
 
Avec phpMyAdmin, il ne faut pas oublier de remplacer le délimiteur ";" par "|" pour créer la fonction stockée. 
Cette solution marche chez OVH mais il se peut que d'autres hébergeurs l'empêchent. Il semble impossible de créer la fonction stockée chez 1&1 par exemple.
 
Cette méthode est très pratique; comment pourrai-je ajouter un test dans la procédure stockée spécifiant:
Si

     lat2 n'est pas null ou vide et lng2 n'est pas null ni vide
alors
     calcul...

Merci encore pour cette excellente source!

 
Tu peux ajouter un test dans le WHERE de ton SELECT :


SELECT *, get_distance_metres('48.858205', '2.294359', lat, lng) 
      AS proximite 
      FROM restaurants 
      WHERE proximite < 1000 
AND   lat <> ''
AND   lng <> ''
ORDER BY proximite ASC
      LIMIT 10
 
Tout simple ;), j'étais parti sur bien plus compliqué, merci pour le retour !
 
J'ai appliqué votre méthode est j'ai bien créer la procédure get_distance_metre





J'ai testé la requête sql donné plus haut par thor76160 ça fonctionne bien il affiche bien la liste des villes dans un rayon de 10km autour de Rouen .



SELECT nom, get_distance_metres('49.433331', '1.08333', latitude, longitude) AS distance

FROM maps_ville

WHERE get_distance_metres('49.433331', '1.08333', latitude, longitude) <=10000

ORDER BY 2




si je voulais afficher dynamiquement une liste de villes par rapport au résultat d'une requête comme la suivante , ou dois je mettre les variables pour les colonnes longitude et latitude.

$colname_villetoto = "-1";

 IF (isset($_GET['id_ville'])) { $colname_villetoto = (get_magic_quotes_gpc()) ? $_GET['id_ville'] : addslashes($_GET['id_ville']);

}

mysql_select_db($database_baseville, $baseville);

$query_villetoto = sprintf("SELECT id_ville, nom_ville, longitude, latitude, get_distance_metres( '49.0364', '3.45222', longitude, latitude )

AS distance FROM communes WHERE get_distance_metres

( '49.0364', '3.45222', longitude, latitude ) <=10000 ORDER BY 2

AND id_ville = %s", GetSQLValueString($colname_villetoto, "int"));

$villetoto = mysql_query($query_villetoto, $baseville) OR die(mysql_error());

$row_villetoto = mysql_fetch_assoc($villetoto);

$totalRows_villetoto = mysql_num_rows($villetoto);


 

merci à vous et bravo pour ce tutorial !

 
 
j'ai trouvé une solution pour mon probleme  posté ci dessus ,

quant les gens tapent dans le formulaire de recherche le nom de la ville ça envoie aussi la longitude et latitude d'un champ hidden, je récupère dans la page de résultat , je met en variable et dans la requête sql je met ces variables. ça marche.
SAUF que j'avais pas fait attention mais ça m'affiche aussi la ville cible.
par exemple pour  Paris  , ça affiche dans la liste des villes dans un rayon de 10 kilomètres la ville de....Paris
soit c'est une petite erreur dans la requête , soit on peut trouver une solution en affichant pas la ville d'où on est censé partir.. peut etre que c'est du à la petite marge d'erreur inerant à ce calcul de distance  de deux points gps
 
Pour solutionner le problème du

#1054 - Unknown column 'proximite' in 'where clause'

il changer

WHERE proximite < 1000 

par

HAVING proximite < 1000 

et le mettre en fin de requête.
 
pour ne pas afficher Paris en cherchant les villes autour de Paris il faut ajouter une condition du style : WHERE ... AND ville <> 'Paris'

 
 
Bonjour,



Si comme moi vous avez ce message d'erreur :

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

ajoutez DETERMINISTIC entre RETURNS DOUBLE et BEGIN ou desactivez les logs binaires (ce qui est impossible pour moi car j'ai une réplication).



Bonne journée et merci pour la fonction :)
 
Bonjour

Quelqu'un saurait me dire si on peut créer cette même fonction sur SQLite3 ?

Ou sinon quelque chose dans le genre.

Merci d'avance
 
Depuis que suis sur MySQL (5.0.51a) la fonction ne fonctionne plus.

Merci d'avance.

Serge
 
Pareil :
#1064 - Erreur de syntaxe près de '|
CREATE FUNCTION get_distance_metres (lat1 DOUBLE, lng1 DOUBLE, lat2 DOUBLE, l' à la ligne 1
 
 Vraiment super script et rapide ! Content de l'avoir trouvé.

Pensez juste à remplacer la clause WHERE par la clause HAVING comme dit précédement et tout roule :-)
 
 A quoi correspond proximite ? Je n'ai pas compris...
 
La fonction get_distance_metres retourne une valeur que tu appelles proximite avec l'opérateur AS. Tu peux ainsi l'utiliser pour ne sélectionner que les entrées dont la "proximite" est inférieur à une distance avec la condition du WHERE (ou HAVING suivant les cas).
 
il semblerais que la procedure stocké pose des probleme quant on passe par PDO , j'ai testé cette procedure avec mysql_conect etc., ça marche impec mais avec un acces à la base via pdo ça plante, si quelqu'un a reussi à faire fonctionner cette procedure avec pdo ça serait sympa de nous montrer coment faire

 

 
 
Je crois que ceci va résoudre tous les problèmes.

Tester avec Mysql 5.5.16 

 

 

DROP FUNCTION IF EXISTS get_distance_metres;

DELIMITER |

 

CREATE FUNCTION get_distance_metres (lat1 DOUBLE, lng1 DOUBLE, lat2 DOUBLE, lng2 DOUBLE) RETURNS DOUBLE

BEGIN

    DECLARE rlo1 DOUBLE;

    DECLARE rla1 DOUBLE;

    DECLARE rlo2 DOUBLE;

    DECLARE rla2 DOUBLE;

    DECLARE dlo DOUBLE;

    DECLARE dla DOUBLE;

    DECLARE a DOUBLE;

    

    SET rlo1 = RADIANS(lng1);

    SET rla1 = RADIANS(lat1);

    SET rlo2 = RADIANS(lng2);

    SET rla2 = RADIANS(lat2);

    SET dlo = (rlo2 - rlo1) / 2;

    SET dla = (rla2 - rla1) / 2;

    SET a = SIN(dla) * SIN(dla) + COS(rla1) * COS(rla2) * SIN(dlo) * SIN(dlo);

    RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a)));

END

|

 

DELIMITER ;

 

 

Boris PADONOU(@padbor)
 
ça marche super et je trouve exactement le même résultat qu'en utilisant la fonction de géométrie fournie par Google Maps en Javascript :

https://developers.google.com/maps/documentation/javascript/geometry

( pour utiliser ces fonctions il ne faut pas oublier de modifier le lien du fichier source GoogleMaps pour inclure la librairie de géométrie :

https://maps.googleapis.com/maps/api/js?libraries=geometry&sensor=true_or_false )


Par contre est-ce que quelqu'un sait s'il y a moyen de faire une requête qui fasse
référence à des lignes de résultat différentes, comme par exemple pour calculer la
distance entre le premier et le dernier point d'une table qui en contient plusieurs,
ou pour faire la somme des distances entre chaque point consécutifs ???
Peut-être que ça n'est pas possible avec une requête mysql unique ...
 
Bon j'ai résolu le problème ( mesurer la longueur totale d'une trace GPS enregistrée dans une table pointGPS avec une colonne latitude, une colonne longitude et une colonne trackid, chaque trackid correspondant à une trace GPS différente  ), je me sers d'une nouvelle fonction qui utilise celle présentée ci-dessus :

 CREATE FUNCTION distance_parcourue ( traceid INT ) RETURNS DOUBLE

BEGIN

    DECLARE lat1 DOUBLE DEFAULT 0;

    DECLARE lng1 DOUBLE DEFAULT 0;

    DECLARE lat DOUBLE DEFAULT 0;

    DECLARE lng DOUBLE DEFAULT 0;

    DECLARE dist DOUBLE DEFAULT 0;

    DECLARE fini INT DEFAULT FALSE;

    DECLARE curseur CURSOR FOR SELECT latitude, longitude FROM `pointGPS` WHERE trackid = traceid ;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fini = TRUE;

    OPEN curseur;

    boucle: LOOP

        FETCH curseur INTO lat, lng;

        IF fini THEN

             LEAVE boucle;

        END IF;

        IF lat1 > 0 THEN

            SET dist = dist + get_distance_metres( lat1, lng1, lat, lng ) ;

            

        END IF;

        SET lat1 = lat;

        SET lng1 = lng;

    END LOOP;

    CLOSE curseur;

    RETURN dist;



END |

 

il suffit ensuite d'appeler la fonction comme suit, pour avoir la distance totale de la trace 1234 :

SELECT distance_totale( 1234 );

là encore je trouve exactement le même résultat qu'en utilisant l'API JavaScript Google Map, sauf que là j'ai le résultat avant le chargement de la page dans le navigateur client.

Par ailleurs il semblerait que MySQL ait tout un ensemble de fonction spécialisées dans la gestion de données géographiques, mais ça suppose d'avoir déjà construit tout son projet en tenant compte des spécifités du standard open GIS ...

http://dev.mysql.com/doc/refman/5.0/fr/spatial-extensions.html
 
 Bonjour,
J'ai essayer cette fonction mais je ne comprend pas ma distance est toujours la même, comme si elle n'était calculée qu'une seule fois et copiées dans le reste des résultats... :/
L'un d'entre vous aurez-t-il une idée ?
Merci d'avance
 
Votre article a honteusement été plagié ici:

http://dotclear.placeoweb.com/post/Formule-de-calcul-entre-2-points-wgs84-pour-calculer-la-distance-qui-separe-ces-deux-points
 
Ajouter un commentaire
Code de sécurité

Attention: Les champs marqués d'une étoile * sont obligatoires
 
Quelques articles qui devraient vous intéresser