How to find nearest location by latitude and longitude?

Question

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/

it seems you forget the $ in SIN(lat * PI() / 180), check your variables — Yosra Nagati, Jul 26 '15 at 12:07
@YosraNagati, thanks I updated lat with $lat..but still he same error..btw any idea why PI value is not shown above? DO I need to include any library for that? — 112233, Jul 26 '15 at 12:12
It is the supposed minus sign - replace with a definite minus sign - just tried this query on my db and it failed because of it - replaced with a minus sign and it was ok - sort of. The query ran but no idea if it is going to return data because I don't have you data or tables. — Professor Abronsius, Jul 26 '15 at 12:19
@RamRaider, I updated the html in my code with charset=utf-8" and no problem with the minus sign now..Also I updated the table structure in my post above — 112233, Jul 26 '15 at 12:25
possible duplicate of [MySQL Great Circle Distance (Haversine formula)](http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula) — halfer, Jul 26 '15 at 12:35
@u_mulder ... the old minus / hyphen / endash / emdash confusion! Good catch. A known hazard of cutting and pasting code from blog posts. — O. Jones, Jul 26 '15 at 12:51

Yosra Nagati · Accepted Answer · 2019-12-19T13:54:22.547

25

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 .

edited Dec 19 '19 at 13:54

answered Jul 26 '15 at 12:26

Yosra Nagati

780
2
8
26

tried, no error shown but no result returned as well. I changed 10 miles into 100 miles still no result – 112233 Jul 26 '15 at 12:35
1

my suggestion is to try it on your database first, then execute into the php code – Yosra Nagati Jul 26 '15 at 12:37
I changed the <=10 into .>=10 and can see the results – 112233 Jul 26 '15 at 12:39
1

it works in when I tried in database with hardcoded lat and lon values...I shall be able to solve it now.. Thank you – 112233 Jul 26 '15 at 12:46
1

check this if you want to undersatnd the logic behind it check this link http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL – Yosra Nagati Jul 26 '15 at 12:49
Thank you, I'll check it out – 112233 Jul 26 '15 at 12:55
This is amazing, but can anyone walk through the actual formula? No idea why it works :/ – Mirror318 Jul 11 '16 at 22:51
2

Adding `limit 1` on the end will get the "nearest" location, instead of a list of the nearest locations. – Mirror318 Jul 11 '16 at 22:52
i am getting SQL Syntax Error. `SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(( 30.720043 - lat) * pi()/180 / 2), 2) +COS( 30.720043 * pi()/180) * COS(lat * pi()/180) * POWER(SIN(( 76.706276 - long) * pi()/180 / 2), 2) ))) as distance from tbl_events having distance <= 10 order by distance` **Unable to find where it is** – Aman Deep Apr 26 '19 at 13:31

Neha Jaggi · Answer 2 · 2020-06-10T12:00:11.773

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.. :)

score 2 · Answer 3 · edited Oct 31 '17 at 12:01

2

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 is for convert to km

edited Oct 31 '17 at 12:01

Ricardo Rocha

14,612
20
74
130

answered Oct 31 '17 at 10:33

salamflamo

53
5

Update proper description. – KARTHIKEYAN.A Oct 31 '17 at 10:55

How to find nearest location by latitude and longitude?

3 Answers3

Linked