Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to find nearest location by latitude and longitude?

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/

like image 842
112233 Avatar asked Jul 26 '15 11:07

112233


People also ask

Can you search Google maps with latitude and longitude?

To search for a place, enter the latitude and longitude GPS coordinates on Google Maps. You can also find the coordinates of the places you previously found. Besides longitude and latitude, you can use plus codes to share a place without an address.

How do I find the closest location using latitude and longitude in mysql?

To find locations in your markers table that are within a certain radius distance of a given latitude/longitude, you can use a SELECT statement based on the Haversine formula. The Haversine formula is used generally for computing great-circle distances between two pairs of coordinates on a sphere.


2 Answers

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 .

like image 159
Yosra Nagati Avatar answered Oct 02 '22 13:10

Yosra Nagati


Here's the SQL statement that finds the closest locations within a radius of 10 miles to the given coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than and equals to 10, orders the whole query by distance. To search by kilometers instead of miles, replace 3959 with 6371.

SELECT
  id, (
    3959 * acos (
      cos ( radians($lat) )
      * cos( radians( tableLatColName ) )
      * cos( radians( tableLogColName ) - radians($long) )
      + sin ( radians($lat) )
      * sin( radians( tableLatColName ) )
    )
  ) AS distance
FROM table_name
HAVING distance <= 10
ORDER BY distance;

This is using the Google Maps API v3 with a MySQL backend-

https://developers.google.com/maps/solutions/store-locator/clothing-store-locator#findnearsql

I was working on same and found this question so Just wanted to share it.. :)

like image 44
Neha Jaggi Avatar answered Oct 02 '22 12:10

Neha Jaggi