php - query - MySQL Great Circle Distance(fórmula Haversine)

mysql query geolocation

Question

Tengo un script PHP en funcionamiento que obtiene los valores de longitud y latitud y luego los ingresa en una consulta MySQL. Me gustaría que sea únicamente MySQL. Aquí está mi Código PHP actual:

if ($distance != "Any" && $customer_zip != "") { //get the great circle distance

    //get the origin zip code info
    $zip_sql = "SELECT * FROM zip_code WHERE zip_code = ''$customer_zip''";
    $result = mysql_query($zip_sql);
    $row = mysql_fetch_array($result);
    $origin_lat = $row[''lat''];
    $origin_lon = $row[''lon''];

    //get the range
    $lat_range = $distance/69.172;
    $lon_range = abs($distance/(cos($details[0]) * 69.172));
    $min_lat = number_format($origin_lat - $lat_range, "4", ".", "");
    $max_lat = number_format($origin_lat + $lat_range, "4", ".", "");
    $min_lon = number_format($origin_lon - $lon_range, "4", ".", "");
    $max_lon = number_format($origin_lon + $lon_range, "4", ".", "");
    $sql .= "lat BETWEEN ''$min_lat'' AND ''$max_lat'' AND lon BETWEEN ''$min_lon'' AND ''$max_lon'' AND ";
    }

¿Alguien sabe cómo hacer esto completamente MySQL? He navegado por Internet un poco, pero la mayoría de la literatura es bastante confusa.

Answer 1

De las preguntas frecuentes de Google Code: creación de un localizador de tiendas con PHP, MySQL y Google Maps :

Aquí está la declaración de SQL que encontrará las 20 ubicaciones más cercanas que se encuentran dentro de un radio de 25 millas con la coordenada 37, -122. Calcula la distancia en función de la latitud / longitud de esa fila y la latitud / longitud objetivo, y luego solicita solo filas donde el valor de la distancia es inferior a 25, ordena toda la consulta por distancia y la limita a 20 resultados. Para buscar por kilómetros en lugar de millas, reemplace 3959 con 6371.

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

Answer 2

He escrito un procedimiento que puede calcular el mismo, pero debes ingresar la latitud y la longitud en la tabla respectiva.

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 ;

Answer 3

He tenido que resolver esto con cierto detalle, así que compartiré mi resultado. Utiliza una tabla zip con tablas de latitude y longitude . No depende de Google Maps; más bien puedes adaptarlo a cualquier tabla que contenga 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

Mire esta línea en medio de esa consulta:

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

Esto busca las 30 entradas más cercanas en la tabla zip dentro de 50.0 millas del punto lat / long 42.81 / -70.81. Cuando construyes esto en una aplicación, ahí es donde colocas tu propio radio de búsqueda y punto.

Si desea trabajar en kilómetros en lugar de millas, cambie 69 a 111.045 y cambie 3963.17 a 6378.10 en la consulta.

Aquí hay una descripción detallada. Espero que ayude a alguien. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

Answer 4

No puedo comentar sobre la respuesta anterior, pero tenga cuidado con la respuesta de @Pavel Chuchuva. Esa fórmula no devolverá un resultado si ambas coordenadas son iguales. En ese caso, la distancia es nula, por lo que esa fila no se devolverá con esa fórmula como está.

No soy un experto en MySQL, pero esto parece estar funcionando para mí:

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;

Answer 5

Pensé que mi implementación de javascript sería una buena referencia para:

/*
 * 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);
}

Answer 6

Si agrega campos auxiliares a la tabla de coordenadas, puede mejorar el tiempo de respuesta de la consulta.

Me gusta esto:

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 está utilizando TokuDB, obtendrá un rendimiento aún mejor si agrega índices de clúster en cualquiera de los predicados, por ejemplo, así:

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

Necesitarás el lat básico y el lon en grados, así como sen (lat) en radianes, cos (lat) * cos (lon) en radianes y cos (lat) * sen (lon) en radianes para cada punto. Luego creas una función mysql, algo así:

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

Esto te da la distancia.

No olvide agregar un índice en lat / lon para que el boxeo delimitador pueda ayudar a la búsqueda en lugar de ralentizarla (el índice ya se ha agregado en la consulta CREATE TABLE anterior).

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

Dada una tabla antigua con solo coordenadas lat / lon, puede configurar una secuencia de comandos para actualizarla así: (php using 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'']
  ));
}

Luego, optimiza la consulta real para hacer solo el cálculo de la distancia cuando realmente se necesita, por ejemplo, delimitando el círculo (bien, ovalado) desde dentro y desde fuera. Para eso, deberá precalcular varias métricas para la consulta en sí:

// 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));

Dados esos preparativos, la consulta es algo como esto (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);

EXPLICAR en la consulta anterior podría decir que no está usando índice a menos que haya suficientes resultados para activarlo. El índice se usará cuando haya suficientes datos en la tabla de coordenadas. Puede agregar FORCE INDEX (lat_lon_idx) al SELECT para hacer que use el índice sin importar el tamaño de la tabla, para que pueda verificar con EXPLAIN que está funcionando correctamente.

Con los ejemplos de código anteriores, debe tener una implementación funcional y escalable de búsqueda de objetos por distancia con un error mínimo.

Answer 7

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

con latitud y longitud en radianes

asi que

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

es tu consulta SQL

para obtener sus resultados en Km o millas, multiplique el resultado con el radio medio de la Tierra ( 3959 millas, 6371 Km o 3440 millas náuticas)

Lo que está calculando en su ejemplo es un cuadro delimitador. Si coloca sus datos de coordenadas en una columna MySQL con habilitación espacial , puede usar la funcionalidad de compilación de MySQL para consultar los datos.

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

Answer 8

 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 

para una distancia de 25 km