CODEWITHSUNDEEP
phpmysqlgoogle-mapsmapslatitude-longitude
Ikram Syakir
Ikram Syakir

Reputation: 37

PHP latitude and longitude radius

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>';

?>

Upvotes: 0

Views: 2280

Answers (1)

jaysingkar
jaysingkar

Reputation: 4435

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);

Upvotes: 3

Related Questions