Fetching Data From A External Database Based On Location

Question

While searching on the internet for a few hours I found that there are several ways of calculating the distance between 2 geo points, and getting the distance in different units. However I didn't really find the explanation for what I'm looking for.

In my androind project I have a LocationService which upon onLocationChanged UPDATE's the users table with a new latitude and longitude within the logged in users row.

But what I need now is to upon the UPDATE to also Query for users which are within a certain distance from my UPDATED Location. Obviously I want to search/query for the users in php, and then return the data to the logged in user. The code which I currently have is listed below.

How can I query for all users within a certain distance (in meters) and return them into a list in Android?

if (!$con) {
  die("Not connected : " . mysql_error());
}

$latitude1 = $_POST["Latitude"];
$longitude1 = $_POST["Longitude"];
$username = $_POST["Username"];

$updatequery = mysqli_prepare($con , "UPDATE users SET Latitude = ?, Longitude = ? WHERE Username = ?");
    mysqli_stmt_bind_param($updatequery ,"dds",$latitude1,$longitude1,$username);
    mysqli_stmt_execute($updatequery);

mysqli_stmt_close($updatequery);
mysqli_close($con);

Well the best way to start is to code a query maybe in phpMyAdmin or similiar tool. You play with it for a while using test data until you get the answer you want. Then you put that query into some PHP code and run it. If it does not work quite as expectd you come and ask for help here. However you do not issues a spec and expect people to provide you with **code for free** — RiggsFolly, Aug 05 '15 at 14:22
I'm not asking for the code for free. I need pointers on how to search for other users with similar Latitude and Longitudes in the Database. Since I do know how to calculate the distance between the users in other units such as miles, kilometers, feet, meter.. I need a way to use that distance to gather other data from users. Example: I'm here, Mark is 500 meters away, his location has changed and has been sent to db, query his data, compare to mine, if it's 500 or less than mine, get his Name. — Ivan Javorovic, Aug 05 '15 at 14:35

score 0 · Accepted Answer · answered Aug 05 '15 at 14:44

So now all you need to do is get your distance calculator code to work out what the Lat and Long is for a box a specified distance away from the user you are interested in. Basically calc a box whos outside is 500 meters from the position you are interested in.

$square = distance_to_latlon($curLat, $curLon, 'metres', 500);

Then you can feed the lats and longs from above into a query like this

$sql = "SELECT * FROM users
        WHERE Latitude BETWEEN {$square['lat1']} AND {$square['lat2']}
          AND Longitude BETWEEN {$square['long1']} AND {$square['long2']}";

Fetching Data From A External Database Based On Location

1 Answers1