php - Filtering MySql Query for Distance Calculation

900

I'm looking for some help with a query which I'm pretty sure is straightforward but I can't see the wood for the trees, I hope someone can help.

I'm searching my database of users, calculating the distance between users and outputting results as follows:

$result = queryMysql("SELECT user FROM $table WHERE x= 'y'");
$num = mysql_num_rows($result);

for ($j = 0 ; $j < $num ; ++$j)
    {
    $row = mysql_fetch_row($result);
    if ($row[0] == $user) continue;

    $query = "SELECT * FROM $table WHERE user='$row[0]'";
    $res=mysql_query($query);
    $results=mysql_fetch_assoc($res);
    $userlocation=$results['location'];
    //run distance function which outputs $distance
    if($distance <= $maximumdistance)
    {
           //echo user info
    }

What I want to do is run the distance function, identify the users who it returns as true, and get their user ids (unique), either in an array or something else, so i can count the results and then run through them for output.

Does this make sense and/or is there a better way of doing this.

Thanks

848

Answer

Solution:

When you say distance, are you talking about geographical distance? MySQL will be much faster at running the distance calculations than PHP. Personally, here's what I would do. Create another table called user_distances with the fields user_id1, user_id2, and distance. Make the user_id1 and user_id2 together your primary key.

Now, let's populate that table with our distances. I'm going to assume that you already have the latitude and longitude of the users most likely based on their city/zip (if not, get a zip code database). You're going to do something like this:

INSERT INTO user_distances 
    (user_id1, user_id2, distance)
SELECT 
    u1.user_id,
    u2.user_id,
    (69.172 * SQRT( POW(u1.latitude-u2.latitude, 2) + POW(u1.longitude-u2.longitude, 2) ) ) as `distance`
FROM
    users as u1,
    users as u2
WHERE
    u1.user_id != u2.user_id;

Now, you can get a lot more precise with the distance calculation, this is just a very simple example that roughly estimates the distance in miles (advanced algorithms will take into account the shape of the earth). Now that you have this new table user_distances populated with the distances between each user id pair, you can run simple queries like, select where distance < 10.

I hope this helps. I didn't test the SQL, but you should get the point :)

309

Answer

Solution:

Um, just a quick note on the distance latitude1 - Latitude2 * 69.whatever is fine

but latitude and longitude are NOT the same

for longitude a good guess is * *53

The further you go from the equator, the less these work (of course)

Yes, USCG certified Captain, so I do understand longitude and latitude - we do NOT live on a flat earth.....

People are also looking for solutions to the problem: php - Count subcategories with a condition

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question