Encontrar Lugares cercanos con MySQL y PHP | URL: http://www.michael-pratt.com/blog/7/Encontrar-Lugares-cercanos-con-MySQL-y-PHP/

database.txt

mysql> SELECT * FROM direcciones;
+---+----------------------------+------------------+-----------+-------------+
| id| nombre                     | direccion        | lat       | lng         |
+---+----------------------------+------------------+-----------+-------------+
| 1 | Centro Comercial Andino    | Carrera 11 #82-71| 4.6665578 | -74.0524521 |
| 2 | Centro Comercial el Retiro | Calle 81 #11-94  | 4.6656246 | -74.0543060 |
| 3 | Centro Comercial Atlantis  | Calle 81 #13-05  | 4.6663179 | -74.0553513 |
| 4 | Carulla de la 85           | Calle 85 #15-29  | 4.6701016 | -74.0554581 |
| 5 | El Corral Parque de la 93  | Calle 93A #12-57 | 4.6766758 | -74.0489044 |
| 6 | Centro Comercial AV Chile  | Calle 73 #10-83  | 4.6582017 | -74.0578461 |
+---+----------------------------+------------------+-----------+-------------+

getBoundaries.php

<?php
function getBoundaries($lat, $lng, $distance = 1, $earthRadius = 6371)
{
    $return = array();
     
    // Los angulos para cada dirección
    $cardinalCoords = array('north' => '0',
                            'south' => '180',
                            'east' => '90',
                            'west' => '270');

    $rLat = deg2rad($lat);
    $rLng = deg2rad($lng);
    $rAngDist = $distance/$earthRadius;

    foreach ($cardinalCoords as $name => $angle)
    {
        $rAngle = deg2rad($angle);
        $rLatB = asin(sin($rLat) * cos($rAngDist) + cos($rLat) * sin($rAngDist) * cos($rAngle));
        $rLonB = $rLng + atan2(sin($rAngle) * sin($rAngDist) * cos($rLat), cos($rAngDist) - sin($rLat) * sin($rLatB));

         $return[$name] = array('lat' => (float) rad2deg($rLatB), 
                                'lng' => (float) rad2deg($rLonB));
    }

    return array('min_lat'  => $return['south']['lat'],
                 'max_lat' => $return['north']['lat'],
                 'min_lng' => $return['west']['lng'],
                 'max_lng' => $return['east']['lng']);
}
?>

haversine-boundaries.php

<?php
$lat =  4.6665578;
$lng = -74.0524521;
$distance = 1; // Sitios que se encuentren en un radio de 1KM
$box = getBoundaries($lat, $lng, $distance);

$pdo = new PDO(........)
$stmt = $pdo->query('SELECT *, ( 6371 * ACOS( 
                                             COS( RADIANS(' . $lat . ') ) 
                                             * COS(RADIANS( lat ) ) 
                                             * COS(RADIANS( lng ) 
                                             - RADIANS(' . $lng . ') ) 
                                             + SIN( RADIANS(' . $lat . ') ) 
                                             * SIN(RADIANS( lat ) ) 
                                            )
                               ) AS distance 
                     FROM direcciones 
                     WHERE (lat BETWEEN ' . $box['min_lat']. ' AND ' . $box['max_lat'] . ')
                     AND (lng BETWEEN ' . $box['min_lng']. ' AND ' . $box['max_lng']. ')
                     HAVING distance < ' . $distance . '
                     ORDER BY distance ASC');

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($rows);
?>

haversine-resultados.txt

+----+---------------------------+---------------------+
| id | nombre                    | distance            |
+----+---------------------------+---------------------+
| 1  | Centro Comercial Andino   | 0                   |
| 2  | Centro Comercial el Retiro| 0.23017992459078465 |
| 3  | Centro Comercial Atlantis | 0.32240807139051025 |
| 4  | Carulla de la 85          | 0.5160069599182567  |
+---+----------------------------+---------------------+

haversine.sql

SELECT id, nombre, ( 6371 * ACOS( 
                                 COS( RADIANS(4.6665578) ) 
                                 * COS(RADIANS( lat ) ) 
                                 * COS(RADIANS( lng ) 
                                 - RADIANS(-74.0524521) ) 
                                 + SIN( RADIANS(4.6665578) ) 
                                 * SIN(RADIANS( lat ) ) 
                                )
                   ) AS distance 
FROM direcciones 
HAVING distance < 1 /* 1 KM a la redonda */
ORDER BY distance ASC

resultado-coseno.txt

+----+---------------------------+-----------------------+
| id | nombre                    | distance              |
+----+---------------------------+-----------------------+
| 1  | Centro Comercial Andino   | 0.00009493529796600342|  
| 2  | Centro Comercial el Retiro| 0.23017994416831827   |
| 3  | Centro Comercial Atlantis | 0.3224080853676892    |
| 4  | Carulla de la 85          | 0.5160069599182567    |
+---+----------------------------+-----------------------+

teorema-coseno-boundaries.php

<?php
$lat  =  4.6665578;
$lng =  -74.0524521;
$distance = 1; // Sitios que se encuentren en un radio de 1KM
$box = getBoundaries($lat, $lng, $distance);

$pdo = new PDO(........)
$stmt = $pdo->query('SELECT *, (6371 * ACOS( 
                                            SIN(RADIANS(lat)) 
                                            * SIN(RADIANS(' . $lat . ')) 
                                            + COS(RADIANS(lng - ' . $lng . ')) 
                                            * COS(RADIANS(lat)) 
                                            * COS(RADIANS(' . $lat . '))
                                            )
                               ) AS distance
                     FROM direcciones
                     WHERE (lat BETWEEN ' . $box['min_lat']. ' AND ' . $box['max_lat'] . ')
                     AND (lng BETWEEN ' . $box['min_lng']. ' AND ' . $box['max_lng']. ')
                     HAVING distance  < ' . $distance . '                                       
                     ORDER BY distance ASC ');

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($rows);
?>

Teorema-del-Coseno.sql

SELECT id, nombre, (6371 * ACOS( 
                                SIN(RADIANS(lat)) * SIN(RADIANS(4.6665578)) 
                                + COS(RADIANS(lng - -74.0524521)) * COS(RADIANS(lat)) 
                                * COS(RADIANS(4.6665578))
                                )
                   ) AS distance
FROM direcciones
HAVING distance < 1 /* 1 KM  a la redonda */
ORDER BY distance ASC

Esto vale oro

No tiene precio 👍

Excelente información!! como seria para solo mostrar el lugar mas cerca!!

Excelente... probando con mucho exito :3

Thanks!