Sorting data by smallest distance first from Distance Matrix service

[speckytwat]

Hi, I'm using Google's distance matrix service to calculate the distance between a user-submitted postcode and the postcodes of members in a mySQL database. Which works fine except for one thing, it shows the members in a random order, I need them to be displayed in order of closest (smallest distance) first. (Nothing in the API documentation about doing this). Code is below:

if($_POST['find-by-postcode'])
{
    $postcode_input = $_POST['_Postcode'];
    
    $getmembers = $mysqli->query("SELECT * FROM members WHERE Postcode NOT LIKE ''");
        
    while ($row = $getmembers->fetch_assoc()) {  
      $memberid = $row["MemberID"];
  $firstname = $row["FirstName"];
  $surname = $row["Surname"];
  $email = $row["Email"];
  $addressline1 = $row["AddressLine1"];
  $addressline2 = $row["AddressLine2"];
  $town = $row["Town"];
  $dateadded = $row["DateAdded"];
  $formatteddate = date("F j, Y", strtotime($dateadded) );
  $clientele = $row["Clientele"];
  $photo = $row["Photo"];
  $county = $row["County"];
  $postcode_member = $row["Postcode"];
        
    $postcode2 = str_replace(' ', '', $postcode_input);
    $postcode1 = str_replace(' ', '', $postcode_member);
 
$url = "http://maps.googleapis.com/maps/api/distancematrix/json?origins=$postcode2&destinations=$postcode1&mode=driving&language=en-EN&sensor=false";
 
$data = @file_get_contents($url);
 
$result = json_decode($data, true);

?>
<div class="sixteen columns">
<h3><?php echo $firstname.' '.$surname; ?></h3>
<p>Based in <strong><?php echo $town; ?></strong></p>
<p><strong>Email: </strong><?php echo '<a href="mailto:'.$email.'">'.$email.'</a>'; ?></p>
<p><strong>Address: </strong><?php echo $addressline1.','.$addressline2.','.$town.','.$postcode; ?></p>
<p><strong>Clientele: </strong><?php echo $clientele; ?></p>

<?php //This is the bit that outputs the distance between the 2 postcodes
foreach($result['rows'] as $distance) {
    echo '<p><span class="boldgreen" style="color:#090; font-weight:bold;">Distance from you: ' . $distance['elements'][0]['distance']['text'] . ' (' . $distance['elements'][0]['duration']['text'] . ')</span></p>';
}
?>

The problem of course is that the distance data doesn't come from the mySQL database but from Google's API. So I've tried creating a new variable from the foreach loop just above, as follows:

$calculateddistance = $distance['elements'][0]['distance']['text'];

And then put this into the db query, i.e: ORDER BY $calculateddistance ASC - but that didn't work, I created a second while loop that included this (I had to create the first one in order to extract the member's postcode to make the comparison) but no joy. Also tried using fetch_array instead of fetch_assoc but that didn't work.

Anyone know how I can get this to sort by smallest distance first? Thanks!

[requinix]

Are you going to be executing this often? Hitting their API for every single data point every single time you need it could easily get you throttled or blocked. A smarter method would be to store locations with the postcodes (done when the postcode is set or changed). A nice side-effect of doing that is you can calculate distances in your query and thus sort by it at the same time...

[speckytwat]

There are less than 100 members and it won't increase dramatically so hopefully not an issue? How would I calculate distances in the query and sort by it at the same time? bearing in mind that the system doesn't know what postcode the user will be putting in so how can it store it or sort by it?

[mac_gyver]

if you are going to do it this way, by getting the distance between points using an api, you need to cache/store the data in your own source/destination/distance database table and only query the api for data you don't have.

 

you would break the problem down into two steps. 1) for any entered point, determine which pairs of source and destination data you don't have the distance for, query the api for just those values, and store them in your source/destination/distance database table. 2) execute the sql query that gets the data you want (matches the entered point as either the source or destination), joined with the members data, ordered by the distance.

 

however, distance is derived data. you should not store derived data unless you have a good and specific reason for doing so, such as increased accuracy. the amount of code/queries will be less if you just store the latitude and longitude for each postcode being used (not for all possible postcodes) and calculate the distance in the sql query, using the simpler and faster diagonal method that requinix gave in one of your previous threads.

[speckytwat]

Yes, I tried approaching the problem using a database of postcodes with latitude and longitude but that didn't work at all, I couldn't even get as far as the distances calculating at all which is why I've had to try using the Google API.

 

So how would I actually do this using those two steps? At first the source and destination data I don't have distances for will be everything. How do I then change the query to not include distances that have already been calculated?

 

This seems like a horrendous problem, all I need to do is sort the data by smallest distance first, as I'm already able to get the calculated distances from the API...

[requinix]

Thing is you're ignoring one problem and fixating on another.

 

mac_gyver's approach is a good idea: before you do any distance stuff, first check your database for people who have a postcode set and you haven't already determined their latitude and longitude. That means adding latitude and longitude to your table. Do that now. Then find the places in your site where you set the postcode and make sure it also clears out the latitude and longitude value.

 

Now to fill in data. Write a query that finds all members that have a postcode but no latitude or longitude. Use a loop to query the API for the location, then update the table with what you find. This code goes before the stuff you posted.

 

After all that you should have a table with a lot of latitude and longitude values - a pair for every member with a postcode. Go back to your other thread where I told you about how to calculate distance and put that into a query. If you have problems then post what query you came up with.

[speckytwat]

Ok, I tried doing that, set up new Latitude and Longitude fields in my members table and everyone with a full postcode now has a value for both Lat and Long (inputted these manually as there aren't that many at the moment)- after hours of getting it set up unfortunately now all I get is a blank page - even with all error reporting turned on, no errors displayed - just blank.

 

If I remove all this code from the page so it just has other searches and the form, it displays fine- so it's something to do with the below...

 

This is what I set up so far: (field names are lower case in the "postcodelatlng" database which is a db of all UK postcodes, where I get the latitude and longitude of the postcode inputted from the user form)

if($_POST['find-by-postcode'])
{
    $postcode_input = $_POST['_Postcode'];
    
        $getcoordinates = $mysqli->query("SELECT * FROM postcodelatlng WHERE postcode LIKE $postcode_input");
            while ($row = $getcoordinates->fetch_assoc()) {  
                $latitude_user = $row['latitude'];
                $longitude_user = $row['longitude'];
            }    
    $getmembers = $mysqli->query("SELECT * FROM members WHERE Postcode NOT LIKE ''");
        
    while ($row = $getmembers->fetch_assoc()) {  
      $memberid = $row["MemberID"];
  $firstname = $row["FirstName"];
  $surname = $row["Surname"];
  $postcode_member = $row["Postcode"];
    
    //Get coordinates
    $latitude_member = $row['Latitude'];
    $longitude_member = $row['Longitude'];
    }
    
    $calculateddistance = ($latitude_user - $latitude_member) ** 2 + ($longitude_user - $longitude_member) ** 2;
    
//Now recalculate the query to include and sort by distance
    $getmembers2 = $mysqli->query("SELECT * FROM members WHERE Postcode NOT LIKE '' ORDER BY $calculateddistance ASC");
        
    while ($row = $getmembers2->fetch_assoc()) {  
      $memberid = $row["MemberID"];
  $firstname = $row["FirstName"];
  $surname = $row["Surname"];
  $email = $row["Email"];
  $addressline1 = $row["AddressLine1"];
  $addressline2 = $row["AddressLine2"];
  $town = $row["Town"];
  $postcode_member = $row["Postcode"];

?>
<div class="sixteen columns">
<h3><?php echo $firstname.' '.$surname; ?></h3>
<p>Based in <strong><?php echo $town; ?></strong></p>
<p><strong>Email: </strong><?php echo '<a href="mailto:'.$email.'">'.$email.'</a>'; ?></p>
<p><strong>Address: </strong><?php echo $addressline1.','.$addressline2.','.$town.','.$postcode_member; ?></p>
<p>Distance from you: <?php echo $calculateddistance; ?></p>

</div>
<hr>
<?php } ?>

[BigB]

Hi, I maybe way off here, maybe something like this may help???... (asort) Think you can have 2,500 hits to the Google API before it charges you.

 

 

I've NOT tested... 

asort($result['rows']);

foreach($result['rows'] as $distance) {
echo '<p><span class="boldgreen" style="color:#090; font-weight:bold;">Distance from you: ' . $distance['elements'][0]['distance']['text'] . ' (' . $distance['elements'][0]['duration']['text'] . ')</span></p>';
}

[speckytwat]

Thanks, just tried that - no joy unfortunately, results still returned randomly rather than smallest distance first....

[Barand]

You can't calculate a distance then order by that distance. If the distance you calculated was, say, 25, you end up with query that looks like

 

SELECT ... ORDER BY 25

 

which is as much use as a chocolate teapot.

 

You need to calculate the distance for each record within the query to sort by it. Using the method suggested by requinix (which uses a pseudo-distance) then

SELECT firstname
     , username
     , postcode
     , POW(m.lat - p.lat, 2) + POW(m.long - p.long, 2) as distance
FROM members as m
     CROSS JOIN
     (
         SELECT lat
              , long
         FROM members
         WHERE postcode = ?
         LIMIT 1
     ) as p
WHERE m.postcode <> ''
ORDER BY distance

[speckytwat]

Thanks, I've tried setting this up but just get a blank page when I browse to the form (I have error reporting turned on but nothing is being outputted). I did change some of the query as I assumed the second SELECT was for grabbing the user postcode from the main table of all postcodes.

if($_POST['find-by-postcode'])
{
    $postcode_input = $_POST['_Postcode'];
    
        $getcoordinates = $mysqli->query("SELECT * FROM postcodelatlng WHERE postcode LIKE $postcode_input");
            while ($row = $getcoordinates->fetch_assoc()) {  
                $latitude_user = $row['latitude'];
                $longitude_user = $row['longitude'];
            }
    
    $getmembers = $mysqli->query("SELECT * FROM members WHERE Postcode NOT LIKE ''");
        
    while ($row = $getmembers->fetch_assoc()) {  
      $memberid = $row["MemberID"];
  $firstname = $row["FirstName"];
  $surname = $row["Surname"];
  $postcode_member = $row["Postcode"];
    
    //Get coordinates
    $latitude_member = $row['Latitude'];
    $longitude_member = $row['Longitude'];
    }
    
    $calculateddistance = ($latitude_user - $latitude_member) ** 2 + ($longitude_user - $longitude_member) ** 2;
    //Now recalculate the query to include and sort by distance
    $getmembers2 = $mysqli->query("SELECT MemberID, FirstName, Surname, Email, Postcode, POW(m.lat - p.lat, 2) + POW(m.long - p.long, 2) AS Distance FROM members AS m CROSS JOIN (SELECT latitude,longitude FROM postcodelatlng WHERE postcode = ? LIMIT 1) AS p WHERE m.Postcode <> '' ORDER BY Distance ASC");
    
        
    while ($row = $getmembers2->fetch_assoc()) {  
      $memberid = $row["MemberID"];
  $firstname = $row["FirstName"];
  $surname = $row["Surname"];
  $email = $row["Email"];

  $postcode_member = $row["Postcode"];

        
    
?>
<div class="sixteen columns">
<h3><?php echo $firstname.' '.$surname; ?></h3>
<p><strong>Email: </strong><?php echo '<a href="mailto:'.$email.'">'.$email.'</a>'; ?></p>

<p>Distance from you: <?php echo $calculateddistance; ?></p>

</div>
<hr>
<?php
    
} ?>

[speckytwat]

Ok, I worked out that it doesn't like the $calculateddistance statement:

$calculateddistance = ($latitude_user - $latitude_member) ** 2 + ($longitude_user - $longitude_member) ** 2;

If I comment that out (and the echo of that variable in the results), the form loads. Not sure what the issue is with it though?

 

So I now changed the script as follows so it outputs the Distance from the new query:

 

 

if($_POST['findatherapist-postcode'])
{
    $postcode_input = $_POST['_Postcode'];
    
        $getcoordinates = $mysqli->query("SELECT * FROM postcodelatlng WHERE postcode LIKE $postcode_input");
            while ($row = $getcoordinates->fetch_assoc()) {  
                $latitude_user = $row['latitude'];
                $longitude_user = $row['longitude'];
            }
    
    $getmembers = $mysqli->query("SELECT * FROM members WHERE Postcode NOT LIKE ''");
        
    while ($row = $getmembers->fetch_assoc()) {  
      $memberid = $row["MemberID"];
  $firstname = $row["FirstName"];
  $surname = $row["Surname"];
  $postcode_member = $row["Postcode"];
    
    //Get coordinates
    $latitude_member = $row['Latitude'];
    $longitude_member = $row['Longitude'];
    }
    
    //$calculateddistance = ($latitude_user - $latitude_member) ** 2 + ($longitude_user - $longitude_member) ** 2;
    //Now recalculate the query to include and sort by distance
    $getmembers2 = $mysqli->query("SELECT MemberID, FirstName, Surname, Email, Postcode, POW(m.lat - p.lat, 2) + POW(m.long - p.long, 2) AS Distance FROM members AS m CROSS JOIN (SELECT latitude,longitude FROM postcodelatlng WHERE postcode = ? LIMIT 1) AS p WHERE m.Postcode <> '' ORDER BY Distance ASC");
    //$getmembers2 = $mysqli->query("SELECT * FROM members WHERE Postcode NOT LIKE ''");
        
    while ($row = $getmembers2->fetch_assoc()) {  
      $memberid = $row["MemberID"];
  $firstname = $row["FirstName"];
  $surname = $row["Surname"];
  $email = $row["Email"];

  $postcode_member = $row["Postcode"];
        $distance = $row["Distance"];
    
?>
<div class="sixteen columns">
<h3><?php echo $firstname.' '.$surname; ?></h3>
<p><strong>Email: </strong><?php echo '<a href="mailto:'.$email.'">'.$email.'</a>'; ?></p>

<p>Distance from you: <?php echo $distance; ?></p>

</div>
<hr>
<?php
    }
} ?>

 

But this gives a "Fatal error: Call to a member function fetch_assoc() on a non-object" error???

[Barand]

The inner SELECT uses your input postcode (denoted by the ? placeholder) to get the lat/long of the member at that postcode location. These values are required in distance calculation of every record, hence the CROSS JOIN.

 

(Personally, I would input a member id or username rather than a postcode and use that to get the the initial lat/long coordinates.)

 

As you now seem to want to show the distance, instead of just a pseudodistance that you could sort by, I have changed the distance calculation slightly to give an approx distance in Kms.

 

Your initial query is totally redundant.

$postcode_input = $_POST['_Postcode'];

$sql = "SELECT firstname
     , username
     , email
     , postcode
     , 111 * SQRT(POW((m.lat - p.lat)*COS(DEG2RAD(p.lat)), 2) + POW(m.long - p.long, 2)) as distance
FROM members as m
     CROSS JOIN
     (
         SELECT lat
              , long
         FROM members
         WHERE postcode = ?
         LIMIT 1
     ) as p
WHERE m.postcode <> ''
ORDER BY distance";

$stmt = $mysqli->prepare($sql);
$stmt->bind_param('s', $postcode_input);
$stmt->execute();
$stmt->bind_result($firstname,$surname,$email,$postcode,$distance);

// OUTPUT RESULTS
echo "<table border='1'>\n";

while ($stmt->fetch()) {
    echo "<tr>
      <td>$firstname</td>
      <td>$surname</td>
      <td>$email</td>
      </td>$postcode</td>
      <td>$distance</td>
    </tr>\n";
}
echo "</table>\n";

This is all you should need instead of all your above code. You can alter the output format when you have got it working.

[speckytwat]

Thanks - I'll try this and see how I get on.

[speckytwat]

Ok, I edited the query to match the field names in my members table and set it up, but I get an error still: "Fatal error: Call to a member function bind_param() on a non-object" ???

 

 

 

if($_POST['find-by-postcode'])
{
    $postcode_input = $_POST['_Postcode'];

$sql = "SELECT FirstName, Surname, Email, Postcode, 111 * SQRT(POW((m.Latitude - p.Latitude)*COS(DEG2RAD(p.Latitude)), 2) + POW(m.Longitude - p.Longitude, 2)) as distance FROM members as m CROSS JOIN (SELECT Latitude, Longitude, FROM members WHERE Postcode = ? LIMIT 1) as p WHERE m.Postcode <> '' ORDER BY distance";

$stmt = $mysqli->prepare($sql);
$stmt->bind_param('s', $postcode_input);
$stmt->execute();
$stmt->bind_result($firstname,$surname,$email,$postcode,$distance);

// OUTPUT RESULTS
echo "<table border='1'>\n";

while ($stmt->fetch()) {
    echo "<tr>
      <td>$firstname</td>
      <td>$surname</td>
      <td>$email</td>
      </td>$postcode</td>
      <td>$distance</td>
    </tr>\n";
}
echo "</table>\n";
    
?>

[Barand]

Sounds like the query failed. Use the mysql error reporting to tell you why.

 

BTW why do you insist on obfuscating the sql code structure by putting it all on one line? You wouldn't do that with php code.

[Barand]

PS "DEG2RAD" should be "RADIANS"

 

(deg2rad is the php function)

[speckytwat]

Thanks, I already had error reporting turned on: (I also changed DEG2RAD to RADIANS but that didn't do anything). 

 

Tried the mysqli_error:

 

echo mysqli_error($sql);

 

Nothing additional outputted.

 

And these were already set:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

[speckytwat]

Ok- I tried changing it so the cross join was with the postcodelatlng table just to see if that did anything, and it gives some results- although the numbers are in the format 48.272494811035 etc. i.e many decimal places - not sure what unit the distance is in (assuming it's measuring distance) - is it kilometers?

 

Interestingly although it lists the first six results in descending order (great) after a while it starts listing members with a distance of 3558.8094146396 (all this exact number), no idea how it's calculating that?

 

Changed the query to:

$sql = "SELECT FirstName, Surname, Email, Postcode, 111 * SQRT(POW((m.Latitude - p.Latitude)*COS(RADIANS(p.Latitude)), 2) + POW(m.Longitude - p.Longitude, 2)) as distance FROM members as m CROSS JOIN (SELECT latitude, longitude FROM postcodelatlng WHERE postcode = ? LIMIT 1) as p WHERE m.postcode <> '' ORDER BY distance";

[Barand]

Check for an error after the prepare() statement.

 

$stmt = $mysqli->prepare($sql);
if (!$stmt) echo $mysqli->error;        // add this line

[Barand]

... is it kilometres?

That's what I said they were.

[speckytwat]

Thanks, well I added 

 

if (!$stmt) echo $mysqli->error;  

right after

 

$stmt = $mysqli->prepare($sql);

But no error shows.

 

Looking at the results, it seems to be miscalculating some as I tried another postcode search and it gave one member postcode as being about 60km away (which is about right) and then another member postcode (around the same distance) as something like 3560km...???

[Barand]

Do those two members with same postcode, but different disances, have the same lat and long values?

[speckytwat]

Sorry, I didn't explain that very well - I meant that the postcodes are quite similar (same general area / county) not the same.

 

Interestingly I just remade the query with the other method of calculation and got a similar thing happen - the first 7 results are exactly right and then the others show a distance of (this time) 2634.3731906247 km (regardless of the member's postcode)

$sql = "SELECT FirstName, Surname, Email, Postcode, POW(m.Latitude - p.Latitude, 2) + POW(m.Longitude - p.Longitude, 2) AS distance FROM members as m CROSS JOIN (SELECT latitude, longitude FROM postcodelatlng WHERE postcode = ? LIMIT 1) as p WHERE m.postcode <> '' ORDER BY distance ASC";

Might it be something to do with some longitudes / latitudes having a negative value? (i.e value x subtract a negative value y would actually add them together?) I'm not great on maths, just an idea.

 

One example member postcode has a positive latitude and negative longitude and reports a distance from the inputted postcode of over 2000km every time even if I choose a postcode literally streets away.

 

So I then inputted a postcode up in Scotland and something is definitely wrong with the calculation because it reports the nearest member postcode as 36km away (it's a lot more than that).

[Barand]

So what output are you getting? I am not psychic and cannot see your screen from here