php - Send the latitude and longitude of Android with php and calculate the distance to the place received from the db and sort by distance

Question

The function I want to implement is to get data from the DB and sort the locations of users and stores in order of distance based on the latitude and longitude received from the user. The part I've worked on so far has been to sort the stores in index order and display them on the screen, and to get the user's coordinates. However, there was a problem in which the coordinates received from the user were sent to php, and the distance was calculated and sorted based on the data received from the db. This is the php code I wrote in the beginning that did not cause any errors, and was successful in sorting the stores in index order and displaying them on the screen. The empty part in $con = mysqli_connect(""); was filled in at that time.

<?php 
    //echo "hello world!";
error_reporting(E_ALL);

ini_set("display_errors", 1);

    $con = mysqli_connect(""); 
        mysqli_set_charset($con,"utf8"); 
    
        $res = mysqli_query($con, "select * from TABLE"); 
    

        $place = array(); 


        while($row = mysqli_fetch_row($res)) { 
            array_push($place, array('name'=>$row[1],'latitude'=>$row[2],'longitude'=>$row[3])); } 
            echo json_encode(array("place"=>$place),JSON_UNESCAPED_UNICODE); 
        
        mysqli_close($con); 
    
?>

This is the java code inside android studio that i wrote to get the coordinates and send them to php. The php address is replaced with an appropriate address each time it is run.

double user_latitude = gpsTracker.getLatitude();
double user_longitude = gpsTracker.getLongitude();
String storeURL = "phpaddress?user_latitude=" + user_latitude + "&user_longitude=" + user_longitude;

And the php code I modified to get this from php and sort it by distance is as follows.

<?php 
    //echo "hello world!";
error_reporting(E_ALL);

ini_set("display_errors", 1); 
    $con = mysqli_connect("“); // 
        mysqli_set_charset($con,"utf8"); 

$user_latitude=$_GET['user_latitude'];
$user_longitude=$_GET['user_longitude'];

$sql = "SELECT (
            (6371*acos(cos(radians(" . $user_latitude . "))*cos(radians(latitude))
            *cos(radians(longitude)-radians(" . $user_logitude . "))
            +sin(radians(" . $user_latitude . "))*sin(radians(latitude))))
        AS distance
        FROM
            TABLE
        ORDER BY
            distance
        DESC";
 

    $res = mysqli_query($con, $sql); 

    $place = array(); 


        while($row = mysqli_fetch_row($res)) { 
            array_push($place, array('name'=>$row[1],'latitude'=>$row[2],'longitude'=>$row[3])); } 

        if( (($_SERVER['REQUEST_METHOD'] == 'POST') && isset($_POST['submit'])) || $android )
{


    echo json_encode(array("place"=>$place),JSON_UNESCAPED_UNICODE); 
        
 
        mysqli_close($con); 
    
?>

I am a beginner in coding, so there are a lot of inexperienced parts in the code that I googled and put together crudely. What I had a question about while applying the code was,

SELECT (
(6371*acos(cos(radians(" . $user_latitude . "))*cos(radians(latitude))
*cos(radians(longitude)-radians(" . $user_logitude . "))
+sin(radians(" . $user_latitude . "))*sin(radians(latitude))))
     AS distance

Here, it was whether the latitude variable was receiving the latitude variable of the db that I set. I was connected to the db, but it seems that the column value of the table I wanted was omitted from there. Also, there is no such variable in the column of my table for the variable called distance, so I was wondering if it is right to put codes like AS distance, order by distance. I'm not trying to teach you this little bit of knowledge, but I'm asking if it has anything to do with not being able to implement it.

标签： phpmysql

Answer 1

MySQL 5.7 引入了 ST_Distance_Sphere，这是一个计算两点之间距离的原生函数（在地球上）。

你应该访问这篇文章

使用 MySQL 计算距离