Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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 |
+---+----------------------------+------------------+-----------+-------------+
<?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']);
}
?>
<?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);
?>
+----+---------------------------+---------------------+
| 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 |
+---+----------------------------+---------------------+
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
+----+---------------------------+-----------------------+
| 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 |
+---+----------------------------+-----------------------+
<?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);
?>
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
@isaacerd

This comment has been minimized.

Copy link

commented Feb 1, 2016

Esto vale oro

@xmon

This comment has been minimized.

Copy link

commented Apr 10, 2016

No tiene precio 👍

@edumagdonal

This comment has been minimized.

Copy link

commented Sep 1, 2016

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

@abrkof

This comment has been minimized.

Copy link

commented Oct 18, 2016

Excelente... probando con mucho exito :3

@iDavidMorales

This comment has been minimized.

Copy link

commented Jan 1, 2018

Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.