PHP latitude and longitude radius

Question

I make a service that give the nearby location of driver to passenger.

Is this query working? Is it actually search nearby location radius of passenger?

I just put the latitude and longitude a number.

Can you guys verify or explain to me?

SELECT  b.id, b.name, b.latitude, b.longitude
FROM    passenger a
JOIN    driver b
ON      ACOS(COS(RADIANS(a.latitude)) * COS(RADIANS(b.latitude)) * COS(RADIANS(b.longitude) - RADIANS(a.longitude)) + SIN(RADIANS(a.latitude)) * SIN(RADIANS(b.latitude))) <= 10 / 6371.0
WHERE   a.latitude = 3.2046532 AND a.longitude = 101.7808791

<?php
error_reporting(E_ERROR | E_PARSE);
error_reporting(E_ALL);
ini_set('display_errors', '0');
include ("conn.php");

$latitude1 = 3.2046532;
$longitude1 = 101.7808791;

$stmt = $dbi->prepare("SELECT b.id, b.name, b.latitude, b.longitude
                FROM passenger a
                JOIN driver b
                ON ACOS(COS(RADIANS(a.latitude)) * COS(RADIANS(b.latitude)) * COS(RADIANS(b.longitude) - RADIANS(a.longitude)) + SIN(RADIANS(a.latitude)) * SIN(RADIANS(b.latitude))) <= 10 / 6371.0
                WHERE   a.latitude = ? AND a.longitude = ?");
$stmt->bind_param('ss', $latitude1,$longitude1); 
mysqli_stmt_execute($stmt) or die (mysqli_error());
mysqli_stmt_store_result($stmt) or die (mysqli_error());
mysqli_stmt_num_rows($stmt);
$stmt->bind_result($newID, $newName, $latitude2, $longitude2);
$stmt->fetch();
$stmt->close();

echo $newName.'<br>';
echo $latitude2.'<br>';
echo $longitude2.'<br>';

?>

You have to verify your codes yourself, we can mostly help if you get into any error — jaysingkar, Aug 08 '16 at 16:40
That's what it looks like. But I'd have to run it to be sure. Gimme a minute. — durbnpoisn, Aug 08 '16 at 16:40
Welcome to Stack Overflow! Can you please have a better title and more detailed information in the content with your effort to solve the problem? — Enamul Hassan, Aug 08 '16 at 17:03
@manetsus okay. my problem is, in this query it is exactly find the nearby location of driver that passenger requested. so i want to know it is this query correct and actually working perfectly? — Ikram Syakir, Aug 08 '16 at 17:18
@manetsus As I understand, you are retrieving all the drivers along the 10 km distance from the user ? — jaysingkar, Aug 08 '16 at 17:52
if this is the case, your query is good, but I couldn't understand why you are using join — jaysingkar, Aug 08 '16 at 18:02
could you please explain, why you are using join ? I don't think it is needed, and your distance won't be the key to be used in `on` clause. — jaysingkar, Aug 08 '16 at 18:03
I think your distance's formula is not correct. please recheck — jaysingkar, Aug 09 '16 at 06:45
@jaysingkar check my new query. i use another option from http://stackoverflow.com/questions/6919661/select-within-20-kilometers-based-on-latitude-longitude — Ikram Syakir, Aug 09 '16 at 06:48
there would be a tick mark against my answer, where you upvote or downvote — jaysingkar, Aug 09 '16 at 07:11

jaysingkar · Accepted Answer · 2016-08-09T06:49:37.217

Your sql query would look something like this:

$stmt = $dbi->prepare("SELECT id, name, latitude, longitude,
                (ACOS(COS(RADIANS(?)) * COS(RADIANS(b.latitude)) * COS(RADIANS(b.longitude) - RADIANS(?)) + SIN(RADIANS(?)) * SIN(RADIANS(b.latitude)))) as distance 
                FROM driver
                WHERE   distance <= 10/6371.0");
$stmt->bind_param('sss', $latitude1,$longitude1,$latitude1);

Explanation:
In the above query I'm calculating the distance of driver from the passenger as distance and then selecting those drivers who are in the 10 km area.
In distance <= 10/6371 statement, 6371 is used as it is the value of radius of earth in kms. 3956 can be used in place of 6371 if you want to calculate the distance in miles, as 3956 is the earth's radius in miles.
More information regarding this can be found here.
If you want to retrieve the passengers details as well, use different query instead of join, as you must be having the passenger's primary key since he's the one requesting for driver's lists.

Update:

$stmt = $dbi->prepare("SELECT id, name, latitude, longitude,
            6371 * (ACOS(COS(RADIANS(?)) * COS(RADIANS(b.latitude)) * COS(RADIANS(b.longitude) - RADIANS(?)) + SIN(RADIANS(?)) * SIN(RADIANS(b.latitude)))) AS distance
            FROM     driver
            HAVING distance <= 10
            ORDER BY distance ASC");
    $stmt->bind_param('sss', $latitude1,$longitude1,$latitude1);

in bind result, you can also bind the distance so as to provide user with the info regarding how far each driver is — jaysingkar, Aug 08 '16 at 18:19
joins can be used in this scenario, http://stackoverflow.com/questions/38676500/get-by-latitude-and-longitude-in-laravel-5-with-other-joins/38801699#38801699 — jaysingkar, Aug 08 '16 at 18:21

PHP latitude and longitude radius

1 Answers1

Update: