Created
February 27, 2014 14:53
-
-
Save 2803media/9251588 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$sql2="SELECT *, ( 6371 * acos( cos( radians('$var[latitude]') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('$var[longitude]') ) + sin( radians('$var[latitude]') ) * sin( radians( latitude ) ) ) ) AS distance | |
FROM cm_gps HAVING distance > 0.01 AND distance < 25 ORDER BY distance LIMIT 0 , 20;"; | |
Fonction mysql de calcul en live : | |
$sql="SELECT *, get_distance_metres('46', '5.416667', latitude, longitude) | |
AS proximite | |
FROM cm_CAF | |
HAVING proximite < 50000 ORDER BY proximite ASC | |
LIMIT 10"; | |
A partir de phpMyAdmin l'opération se fait à partir de l'onglet SQL en saisissant les lignes suivantes : | |
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. | |
http://www.phpsources.org/calcul-de-la-distance-entre-deux-coordonnees-gps-avec-mysql_101.html |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment