How do I find the nearest location in latitude and longitude?
This is the structure of the table loc_coordinate
:
Below is the code to fetch the closest places from the database and display the place name stored in the database itself.
<?php
include("config.php");
$lat = "3.107685";
$lon = "101.7624521";
$sql="SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS 'distance' FROM loc_coordinate HAVING 'distance'<='10' ORDER BY 'distance' ASC";
$stmt =$pdo->prepare($sql);
$stmt->execute();
while($row = $stmt->fetch())
{
echo $row['place'];
}
?>
The error shown for this:
Fatal error: in C: \ wamp \ www \ mysite \ by_coor.php on line 8
PDOException: in C: \ wamp \ www \ mysite \ by_coor.php on line 8
echo $sql
shows this:
SELECT ((ACOS (SIN (3.107685 * PI () / 180) * SIN (lat * PI () / 180) + COS (3.107685 * PI () / 180) * COS (lat * PI () / 180) * COS ((101,7624521) â lon) * PI () / 180)) * 180 / PI ()) * 60 * 1.1515) AS 'distance' FROM loc_coordinate HAVING 'distance' <= '10' ORDER BY 'distance' ASC
I am not sure why I am getting this error. This is the site I referenced for the SQL query: http://zcentric.com/2010/03/11/calculate-distance-in-mysql-with-latitude-and-longitude/
source to share
try it
SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(( $lat - LatOnTable) * pi()/180 / 2), 2) +COS( $lat * pi()/180) * COS(LatOnTable * pi()/180) * POWER(SIN(( $long - LongOnTable) * pi()/180 / 2), 2) ))) as distance
from yourTable
having distance <= 10
order by distance
replace LatOnTable with your latitude table column name and longOnTable with your longitude column name in your table.
source to share
This works for me:
SELECT restoran.id,restoran.restoran , (6371 * 2 * ASIN(SQRT( POWER(SIN(( -6.9831375276568055 - restoran.lat) * pi()/180 / 2), 2) +COS( -6.9831375276568055 * pi()/180) * COS(restoran.lat * pi()/180) * POWER(SIN(( 110.40925562381744 - restoran.lng) * pi()/180 / 2), 2) ))) as distance from restoran having distance <= 10 order by distance
6371
numbers to convert to km
source to share