Php – How to find nearest location by latitude and longitude

google mapsMySQLPHP

This is the loc_coordinate table structure:

enter image description here

Below is the code, to fetch the nearest places from database and display the place name stored in 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'm unsure why I'm getting this error. This is the site I referred to for the SQL query: http://zcentric.com/2010/03/11/calculate-distance-in-mysql-with-latitude-and-longitude/

Best Answer

try this 

     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

substitute LatOnTable with the latitude table column name , and longOnTable with you longitude column name in your table .

Related Topic