PHP INITIÉ - Calcul de la Distance entre deux coordonnées GPS avec MySQL

PHP INITIÉ - 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 code 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.

PHP INITIÉ - Calcul de la Distance entre deux coordonnées GPS avec MySQL

A partir de la console MySQL (avec Wampserver 2.0, c'est dans le menu MySQL), voici les lignes qu'il faut entrer :

Première méthode depuis la console MySQL

.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.

FAQ

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.

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.

A quoi correspond proximite ?
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).

Comment peut-on 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...

Réponse : 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