MySQL grande distance du cercle (formule D'Haversine)

$greatCircleDistance = acos( cos($latitude0) * cos($latitude1) * cos($longitude0 - $longitude1) + sin($latitude0) * sin($latitude1));

avec latitude et longitude en radian.

donc

SELECT 
  acos( 
      cos(radians( $latitude0 ))
    * cos(radians( $latitude1 ))
    * cos(radians( $longitude0 ) - radians( $longitude1 ))
    + sin(radians( $latitude0 )) 
    * sin(radians( $latitude1 ))
  ) AS greatCircleDistance 
 FROM yourTable;

est votre requête SQL

pour obtenir vos résultats en Km ou en miles, multipliez le résultat par le rayon moyen de la Terre ( 3959 miles, 6371 Km ou 3440 miles nautiques)

la chose que vous calculez dans votre exemple est une boîte de délimitation. Si vous mettez vos coordonnées dans un spatial " activé MySQL colonne , vous pouvez utiliser MySQL construire dans la fonctionnalité pour interroger les données.

SELECT 
  id
FROM spatialEnabledTable
WHERE 
  MBRWithin(ogc_point, GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'))

si vous ajoutez des champs helper à la table de coordonnées, vous pouvez améliorer le temps de réponse de la requête.

comme ceci:

CREATE TABLE `Coordinates` (
`id` INT(10) UNSIGNED NOT NULL COMMENT 'id for the object',
`type` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'type',
`sin_lat` FLOAT NOT NULL COMMENT 'sin(lat) in radians',
`cos_cos` FLOAT NOT NULL COMMENT 'cos(lat)*cos(lon) in radians',
`cos_sin` FLOAT NOT NULL COMMENT 'cos(lat)*sin(lon) in radians',
`lat` FLOAT NOT NULL COMMENT 'latitude in degrees',
`lon` FLOAT NOT NULL COMMENT 'longitude in degrees',
INDEX `lat_lon_idx` (`lat`, `lon`)
)    

si vous utilisez TokuDB, vous obtiendrez de meilleures performances si vous ajoutez le clustering index sur l'une des prédicats, par exemple, comme ceci:

alter table Coordinates add clustering index c_lat(lat);
alter table Coordinates add clustering index c_lon(lon);

vous aurez besoin du lat et du lon de base en degrés ainsi que du sin(lat) en radians, cos(lat)*cos(lon) en radians et cos (lat) * sin (lon) en radians pour chaque point. Puis vous créez une fonction mysql, smth comme ceci:

CREATE FUNCTION `geodistance`(`sin_lat1` FLOAT,
                              `cos_cos1` FLOAT, `cos_sin1` FLOAT,
                              `sin_lat2` FLOAT,
                              `cos_cos2` FLOAT, `cos_sin2` FLOAT)
    RETURNS float
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY INVOKER
   BEGIN
   RETURN acos(sin_lat1*sin_lat2 + cos_cos1*cos_cos2 + cos_sin1*cos_sin2);
   END

cela vous donne la distance.

n'oubliez pas d'ajouter un index sur lat/lon pour que la boxe limitante puisse aider la recherche au lieu de la ralentir (l'index est déjà ajouté dans la requête de la table CREATE ci-dessus).

INDEX `lat_lon_idx` (`lat`, `lon`)

étant donné une ancienne table avec seulement des coordonnées lat/lon, vous pouvez configurer un script pour la mettre à jour comme ce: (php à l'aide de meekrodb)

$users = DB::query('SELECT id,lat,lon FROM Old_Coordinates');

foreach ($users as $user)
{
  $lat_rad = deg2rad($user['lat']);
  $lon_rad = deg2rad($user['lon']);

  DB::replace('Coordinates', array(
    'object_id' => $user['id'],
    'object_type' => 0,
    'sin_lat' => sin($lat_rad),
    'cos_cos' => cos($lat_rad)*cos($lon_rad),
    'cos_sin' => cos($lat_rad)*sin($lon_rad),
    'lat' => $user['lat'],
    'lon' => $user['lon']
  ));
}

ensuite, vous optimisez la requête actuelle pour ne faire le calcul de la distance que lorsque c'est vraiment nécessaire, par exemple en délimitant le cercle (bien, ovale) de l'intérieur et de l'extérieur. Pour cela, vous devrez précalculer plusieurs paramètres pour la requête elle-même:

// assuming the search center coordinates are $lat and $lon in degrees
// and radius in km is given in $distance
$lat_rad = deg2rad($lat);
$lon_rad = deg2rad($lon);
$R = 6371; // earth's radius, km
$distance_rad = $distance/$R;
$distance_rad_plus = $distance_rad * 1.06; // ovality error for outer bounding box
$dist_deg_lat = rad2deg($distance_rad_plus); //outer bounding box
$dist_deg_lon = rad2deg($distance_rad_plus/cos(deg2rad($lat)));
$dist_deg_lat_small = rad2deg($distance_rad/sqrt(2)); //inner bounding box
$dist_deg_lon_small = rad2deg($distance_rad/cos(deg2rad($lat))/sqrt(2));

étant donné ces préparations, la requête va quelque chose comme ceci (php):

$neighbors = DB::query("SELECT id, type, lat, lon,
       geodistance(sin_lat,cos_cos,cos_sin,%d,%d,%d) as distance
       FROM Coordinates WHERE
       lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d
       HAVING (lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d) OR distance <= %d",
  // center radian values: sin_lat, cos_cos, cos_sin
       sin($lat_rad),cos($lat_rad)*cos($lon_rad),cos($lat_rad)*sin($lon_rad),
  // min_lat, max_lat, min_lon, max_lon for the outside box
       $lat-$dist_deg_lat,$lat+$dist_deg_lat,
       $lon-$dist_deg_lon,$lon+$dist_deg_lon,
  // min_lat, max_lat, min_lon, max_lon for the inside box
       $lat-$dist_deg_lat_small,$lat+$dist_deg_lat_small,
       $lon-$dist_deg_lon_small,$lon+$dist_deg_lon_small,
  // distance in radians
       $distance_rad);

expliquer sur la requête ci-dessus pourrait dire qu'il n'utilise pas index à moins qu'il y ait assez de résultats pour déclencher ça. L'index sera utilisé quand il y aura assez de données dans la table de coordonnées. Vous pouvez ajouter Indice de FORCE (lat_lon_idx) à la SELECT de le faire utiliser l'index sans égard à la taille de la table, de sorte que vous pouvez vérifier avec expliquer que cela fonctionne correctement.

avec les exemples de code ci-dessus, vous devriez avoir une implémentation fonctionnelle et évolutive de la recherche d'objet par distance avec un minimum d'erreur.

j'ai dû régler ça en détail, donc je vais partager mon résultat. Il utilise un tableau zip avec des tableaux latitude et longitude . Il ne dépend pas de Google Maps; vous pouvez plutôt l'adapter à n'importe quelle table contenant lat/long.

SELECT zip, primary_city, 
       latitude, longitude, distance_in_mi
  FROM (
SELECT zip, primary_city, latitude, longitude,r,
       (3963.17 * ACOS(COS(RADIANS(latpoint)) 
                 * COS(RADIANS(latitude)) 
                 * COS(RADIANS(longpoint) - RADIANS(longitude)) 
                 + SIN(RADIANS(latpoint)) 
                 * SIN(RADIANS(latitude)))) AS distance_in_mi
 FROM zip
 JOIN (
        SELECT  42.81  AS latpoint,  -70.81 AS longpoint, 50.0 AS r
   ) AS p 
 WHERE latitude  
  BETWEEN latpoint  - (r / 69) 
      AND latpoint  + (r / 69)
   AND longitude 
  BETWEEN longpoint - (r / (69 * COS(RADIANS(latpoint))))
      AND longpoint + (r / (69 * COS(RADIANS(latpoint))))
  ) d
 WHERE distance_in_mi <= r
 ORDER BY distance_in_mi
 LIMIT 30

Regardez cette ligne au milieu de cette requête:

    SELECT  42.81  AS latpoint,  -70.81 AS longpoint, 50.0 AS r

recherche des 30 entrées les plus proches de la table zip dans un rayon de 50,0 milles du lat / long point 42.81 / -70.81 . Quand vous construisez ceci dans une application, c'est là que vous mettez votre propre point et rayon de recherche.

si vous voulez travailler en kilomètres plutôt qu'en miles, changez 69 en 111.045 et changez 3963.17 en 6378.10 dans la requête.

Voici une liste détaillée. J'espère que cela aide quelqu'un. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

j'ai écrit une procédure qui peut calculer le même, mais vous devez entrer la latitude et la longitude dans le tableau respectif.

drop procedure if exists select_lattitude_longitude;

delimiter //

create procedure select_lattitude_longitude(In CityName1 varchar(20) , In CityName2 varchar(20))

begin

    declare origin_lat float(10,2);
    declare origin_long float(10,2);

    declare dest_lat float(10,2);
    declare dest_long float(10,2);

    if CityName1  Not In (select Name from City_lat_lon) OR CityName2  Not In (select Name from City_lat_lon) then 

        select 'The Name Not Exist or Not Valid Please Check the Names given by you' as Message;

    else

        select lattitude into  origin_lat from City_lat_lon where Name=CityName1;

        select longitude into  origin_long  from City_lat_lon where Name=CityName1;

        select lattitude into  dest_lat from City_lat_lon where Name=CityName2;

        select longitude into  dest_long  from City_lat_lon where Name=CityName2;

        select origin_lat as CityName1_lattitude,
               origin_long as CityName1_longitude,
               dest_lat as CityName2_lattitude,
               dest_long as CityName2_longitude;

        SELECT 3956 * 2 * ASIN(SQRT( POWER(SIN((origin_lat - dest_lat) * pi()/180 / 2), 2) + COS(origin_lat * pi()/180) * COS(dest_lat * pi()/180) * POWER(SIN((origin_long-dest_long) * pi()/180 / 2), 2) )) * 1.609344 as Distance_In_Kms ;

    end if;

end ;

//

delimiter ;

Je ne peux pas commenter la réponse ci-dessus, mais faites attention à la réponse de @Pavel Chuchuva. Cette formule ne retourne pas de résultat si les deux coordonnées sont les mêmes. Dans ce cas, la distance est nulle, et donc que la ligne ne sera pas retourné avec cette formule.

Je ne suis pas un expert MySQL, mais cela semble fonctionner pour moi:

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 OR distance IS NULL ORDER BY distance LIMIT 0 , 20;

 SELECT *, (  
    6371 * acos(cos(radians(search_lat)) * cos(radians(lat) ) *   
cos(radians(lng) - radians(search_lng)) + sin(radians(search_lat)) *         sin(radians(lat)))  
) AS distance  
FROM table  
WHERE lat != search_lat AND lng != search_lng AND distance < 25  
 ORDER BY distance  
FETCH 10 ONLY 

pour une distance de 25 km

j'ai pensé que mon implémentation javascript serait une bonne référence à:

/*
 * Check to see if the second coord is within the precision ( meters )
 * of the first coord and return accordingly
 */
function checkWithinBound(coord_one, coord_two, precision) {
    var distance = 3959000 * Math.acos( 
        Math.cos( degree_to_radian( coord_two.lat ) ) * 
        Math.cos( degree_to_radian( coord_one.lat ) ) * 
        Math.cos( 
            degree_to_radian( coord_one.lng ) - degree_to_radian( coord_two.lng ) 
        ) +
        Math.sin( degree_to_radian( coord_two.lat ) ) * 
        Math.sin( degree_to_radian( coord_one.lat ) ) 
    );
    return distance <= precision;
}

/**
 * Get radian from given degree
 */
function degree_to_radian(degree) {
    return degree * (Math.PI / 180);
}