Sort order by distance

[phpnow]

I am stuck on a creating something...here it goes.

I have feature that allows people to search for people that are online on my website. It queries a MYSQL db. The two tables involved are LOCATIONS(this hold the longitude, latitude and zip code)
the second table is the MEMBERS table that also holds a zip code.

Once the information is all grabbed by the script it run thru some math to calculate the distances between each user.

The problem that having is that the DISTANCE is not a product of either TABLES. It is produced out side the query by all the MATH functions. So when I try to sort by the distances it does not work

Is there a way to load all this information into a new array and then sort it by the DISTANCE. If so please point me in the right direction....I need help with this bad.

Thanks
Jay😕

[YappyDog]

Sounds like you'd want to use either asort or arsort.........

http://us4.php.net/manual/en/function.asort.php
http://us4.php.net/manual/en/function.arsort.php

[kevinsequeira]

post your existing code with comments so that we can take a look at it and try to help you out

reg
kevin

[phpnow]

//grab first zip info here

$resultzip2 = mysql_db_query($database, "SELECT * FROM locations WHERE zip_codes='$zipCode' ");
$dbloc2 = mysql_fetch_array($resultzip2);

$result2 = mysql_db_query($database, "SELECT * FROM locations ");

 	while ($dbloc = mysql_fetch_array($result2)) {

#######################################################
##--->>> SHOWS LONGITUDE AND LATITUDE FOR ALL--PERSONS ONLINE AND PERSON SEARCHING <<<<<---
#######################################################

	$lon1 = "$dbloc2[LONGITUDE]";
    $lat1 = "$dbloc2[LATITUDE]";
    $lon2 = "$dbloc[LONGITUDE]";
    $lat2 = "$dbloc[LATITUDE]";

	global $lon1;
	global $lat1;
	global $lon2;
	global $lat2;

######################################################################################
##--->>> OK...LETS DO THE MATH FOR DISTANCE MILES CALCULATIONS <<<<<---
######################################################################################

$pi = 3.1415926;
$rad = doubleval($pi/180.0);

$lon1 = doubleval($lon1)*$rad; $lat1 = doubleval($lat1)*$rad;
$lon2 = doubleval($lon2)*$rad; $lat2 = doubleval($lat2)*$rad;

$theta = $lon2 - $lon1;
$dist = acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($theta));
if ($dist < 0) { $dist += $pi; }
$dist = $dist * 6371.2;
$miles = doubleval($dist * 0.621);
$inches = doubleval($miles*63360);
$dist = sprintf("%.2f",$dist);
$miles = sprintf("%.2f",$miles);
$inches = sprintf("%.2f",$inches);
$miles=round($miles);

##########################################
now the question is how to sort by the DISTANCE variable called $miles. This was not from the Database....it was created from the math. Do you know how to load it into an new array hold all the info so it can then be sorted by MILES

Thanks
Jay

[BeastRider]

Do you have a true distance calculator, that is, are you accurately converting lat/long to mileage? One degree of longitude at the equator is 60 nautical miles or about or 69 statute miles, but one degree of longitude at 45 degrees is about half that.

If so, then this will not work because that means you are using an external function to compute distance. But, if you are simply using lat/long as x and y values into the pythagoream thereom, then you do the calculcations directly in the select statement as in the example below. Note that you have to do the calculations again in the ORDER BY statement and cannot use the generated column reference.

You can see the output of this test script at:

http://www.herbhost.com/phptest/calctest.php

<?php

// build a query with a calculation in it
$query = "SELECT filename, annotation, pageno, thumbid,
            sqrt(pow(thumbid,2) + pow(pageno,2)) AS dist
            FROM thumbs
            ORDER BY (sqrt(pow(thumbid,2) + pow(pageno,2)))
            LIMIT 100";



// connect to the database
if(!($dbh=mysql_connect ("localhost",
                         "$user",
                         "$pass")))
  die("Cannot connect to the database");

// open the connected database
if (!mysql_select_db("$db",$dbh))
  die("Cannot open the database");

// do the query

if (!($result = @ mysql_query ($query, $dbh)))
  die("Cannot execute query against the database");

// if we got some rows back then output a pag of test data
$numrows = mysql_num_rows($result);
if ($numrows > 0)
  {
  while ($row = @ mysql_fetch_array($result))
    {
    $fname = $row["filename"];
    $dist = $row["dist"];
    $thumb = $row["thumbid"];
    $page = $row["pageno"];
    echo "Filename - $fname   Page - $page  Thumb - $thumb  Distance - $dist<br>";
    }
  }
?>

[phpnow]

Thanks again for your help, but I am little lost with your MYSQL query you showed me. Is there anyway you can show me what my MYSQL query would need to look like from the code that I posted in the previous thread?

Thanks
Jay

[phpnow]

When you ask if i am convert latitude and longitude to miles what do you mean? when a member joins they are asked to complete a registration form. one of the things this registration form does is retrieve their latitude and longitude from their zip code. it then stores that information in the DB with their user name. at this point nothing has been converted.

then when a search is requested it goes and gets their latitude, longitude and user names along with zip codes and then does the math to calculate the distances between everyone in the db.

i guess what i am asking is do i need to redo my DB structure so that i am converting latitude and longitude to miles prior to storing any information what so ever? or does the query itself do the math on the fly whenever a search is requested?

like i have said...i know you are most like busy but i really appriciate any pointers and the help you are giving.

thanks
jay

[BeastRider]

Answer one question while I think about this:

Are you computing one user point against many zipcode points or do you have multiple users and zipcode points for each query?

This is trivial if you are computing distance from one known point to many other points, much less trivial if you have multiple points to multiple points - for example, 10 rows from $resultzip2 and 10 rows from $result2 yields 100 distance calculations. This most definitely cannot be done in the result set of either query.

[phpnow]

i will try and explain my goals as best as i can. i have a members DB and members can search other members that are either currently online or not. When it shows the result of the search I am trying to get it to show the results listed by the distance. so that when someone searches it shows all the members that meet the search in the order of closest to furthest away.

does that help at all...the way it works is by grabing the member who is searching user information their zip code, longitude and latitude is in this info. then it runs thru the DB and grabs all the other users info and does the all the distance calculations. I just want to be able to sort the query by the distance.

I hope this helps.
thanks
jay

[phpnow]

ok to answer you question with more detail

say i am user1 and do a search.

the query of my search would first gather all my information
zip code, longitude, latitude

then the second query grabs all the info from the other members that i am searching. for example...latitude, longitude, zip code.

then into a function to do the math and determine the distance.
the problem that i have had so far is that the distance variable is created outside the mysql query and therefor i cannot sort by it.

i guess that if i could some how wrap that entire Trig function into a query like you did then it may work...i dont know.

hope that is more of help. if you need more info let me know. i dont know if db structure would help or not...let me know.

thanks
Jay

[BeastRider]

As I see it, the IF statement in the conversion function puts the kibosh on using the entire calculation in an order by statement. It has been a while since I've fooled around with spherical trig, but if the fellow that wrote this routine thinks he can get a distance less than 0, so be it.

So, there are two ways to approach. First, we ignore the IF statement and simply convert all the steps in the calculation to one long expression that fits in an order by statement.

Let us assume that $lat1 and $lon1 are the values of the user making the query. Let us also assume that the values are already doubles since that is how they are defined in the mysql database. Then, we could write a routine like so:

// build a query with a calculation in it 
$query = "SELECT filename, annotation, pageno, thumbid, 
            6371.2 * (acos(sin($lat1*$rad) * sin($lat2*$rad) + cos($lat1*$rad) * cos($lat2*$rad) * cos(($lon2-$lon1)*$rad))) 
 AS dist 
            FROM thumbs 
            ORDER BY 6371.2 * (acos(sin($lat1*$rad) * sin($lat2*$rad) + cos($lat1*$rad) * cos($lat2*$rad) * cos(($lon2-$lon1)*$rad)))"; 

So... all I have done here is combine multiple statements into one.

The other way is to sort the query result array. To do this, you create a "fake" calculation so that you have a field to fill in later with a real distance calculation.

// build a query with a fake calculation in it 
$query = "SELECT filename, annotation, pageno, thumbid, 
            $lat2 * 5  AS dist 
            FROM thumbs 
            ORDER BY  $lat2 * 5  AS dist ;

Now you have a result with a meaningless dist field in it. Use the mathematical functions you described to compute a real value for the dist field. You can cycle through this array with the mysql array commands.

Finally, after you have created a true distance in the dist column, you sort the array with a routine that would look something like this.

function array_distsort($thearray, $field) 
{  

foreach ($thearray as $row) {
   $sortarr[] = $row[$field];
 }
 array_multisort($sortarr, $thearray);
 return $thearray;
}

I've tested none of this. But, methinks one of the two methods will work.

[phpnow]

do i need to have a column in the DB named DISTANCE or is the DISTANCE column something that is created on the fly to be stored in the MYSQL array result. My DB does not have a true column named distance

let me ask this...lets say i have mysql query that grabs all the information for the user that is searching that looks like this:

//member query (this grabs the members info that is searching)

$resultzip2 = mysql_db_query($database, "SELECT * FROM locations WHERE zip_codes='$membersZipcode' ");
$dbloc2 = mysql_fetch_array($resultzip2);

then I have a second query that runs thru the table name locations and grabs all the information like latitude and longitude and zip codes that looks like this:

//grab all info from the LOCATIONS table here
$result2 = mysql_db_query($database, "SELECT * FROM locations ");

while ($dbloc = mysql_fetch_array($result2)) {

//do all the math calculations inside the while loop like the code i posted in the second thread...see above

MY QUESTION: is there a way to add the result from the math to the query. i know you can add a value to an array but can you do it in this case and simple resort the array by the value added. so that the results were listed by the new value DISTANCE or am i way off base with this idea?

}
Thanks
Jay

[BeastRider]

OK... I am going to suggest a method based upon the code you have posted and what I know about mysql results and arrays. I have made similar code work
on a test database, and I think this will do what you want.

First, I conclude that the calculation to determine distance is too complex to realistically include in the SELECT statement. Moreover, if you want both a
calculated field called 'dist' and you want to ORDER BY dist, you must specify the entire calculation again in the ORDER BY clause, you cannot simply specify
the calculated field 'dist' in the ORDER BY clause. Since there are plenty of sine and cosine calculations, doubling this up doesn't do much for speed.

So, I conclude that the only realistic way to do this is by sorting a result array outside of the query. The code below shows how to do it , plus my assumptions.

// set the constants
$pi = 3.1415926; 
$rad = doubleval($pi/180.0); 

// lets get the first lat long to which all others are measured and
// convert to radians as required

$resultzip2 = mysql_db_query($database, "SELECT * FROM locations WHERE zip_codes='$zipCode' "); 
$dbloc2 = mysql_fetch_array($resultzip2); 
$lon1 = "$dbloc2[LONGITUDE]" * $rad; 
$lat1 = "$dbloc2[LATITUDE]" * $rad; 

// next, we get all the zip codes against which the first lat long
// pair will be measured.  we create a simple calculated output
// field in the result of this query so that we have a place holder
// to properly compute distance after we get the records in.  

// I enumerate all the fields (you make them whatever they should
// be) so that I can add a calculated field to work with later.

$result2 = mysql_db_query($database, "SELECT latitude longitude zipcode 
                  2 * latitude AS dist
                  FROM locations "); 

// next, we take the results of this query and load it into an external
// array.  the reason I do this is that I cannot get the result set of a
// query to work in an array sort.  If someone knows how to make 
// the result set look like a multi dimensional array to the sort routines, 
// then you can eliminate this step.

while ($dbloc = mysql_fetch_array($result2))
  {
  $myarray[] = $dbloc;
  }

// note that this array contains all the returned query fields
// including the calculated field dist.  So, we now go through this array
// and make real distance computations.

foreach ($myarray as $row)
  {
  $lon2 = "$row[LONGITUDE]" * $rad; // convert to radians
  $lat2 = "$row[LATITUDE]" * $rad; 
  $theta = $lon2 - $lon1; 
  $dist = acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($theta)); 
  if ($dist < 0) { $dist += $pi; } 
  $dist = $dist * 6371.2; 
// $miles is the magical number we wish to sort on so put it into the array
// into the calulated field we reserved for this purpose
  $miles = doubleval($dist * 0.621); 
  $row["dist"] = $miles;
  }

// next we want to sort this array into distance order.  I use a function
// that I found in the php doc to do the job.  the function is listed 
// at the end of this code.  Put it first in your code

$mysortarray = array_csort($myarray,"dist");

// we now have an array sorted in ascending order by distance 
// it is ready output.  For example, based on the fields I used

echo "<br>Zipcodes Sorted By Distance (near to far)<br>";
foreach ($mysortarray as $row)
  {
  $zip = $row["zipcode"];
  $lat = $row["latitude"];
  $long = $row["longitude"];
  $dist = $row["dist"];
  echo "Zipcode $zip at Lat $lat and Long $long is $dist miles from you<br>";
  }

// that ought to do what you want - send me a check 

// finally the sort function

function array_csort($marray, $column)
  {
  foreach ($marray as $row)
    {
    $sortarr[] = $row[$column];
    }
  array_multisort($sortarr, SORT_NUMERIC, SORT_ASC, $marray);
  return $marray;
  }


// all the rest of these are formatting items and should be moved
// to where ever you do data display - I comment them out
//  $inches = doubleval($miles*63360); 
//  $dist = sprintf("%.2f",$dist); 
//  $miles = sprintf("%.2f",$miles); 
//  $inches = sprintf("%.2f",$inches); 
//  $miles=round($miles); 

[BeastRider]

I am curious about one thing. Do you have zip 77081 in your database?

The actual latitude is roughly 29.7 degrees north but I am guessing that the latitude value in your zip database is 60.3 degrees. Am I correct?

It is possible to make the same computation using a second method that does not involve checking to see if both endpoints are in the same quadrant, but I still think just moving the result to an array and sorting it there is still easier.

[phpnow]

no i dont see that zip in my DB....is it new? also i noticed that the code you sent does not work 100 %. if i just take it and plug it in it calculates some number but it is not a mile. it parses them out in order but if you notice where you have the ECHO line to show the output you have the VAR $DIST to be shown as MILES and this in correct, but when I change it the MILES it show only one answer for every zipcode. almost like it isnot doing the WHILE loop or the FOREACH command correctly. if you want me to do something specific for testing i will...please let me know....so close...so excited.

By the way thank you for all you hard work and time on this matter....i really do appriciate this

also i wanted to ask you...you have seen the way i write my php and mysql queries. is it out dated or is it ok? i see so many different ways that they are done and i wonder what is the best way?
Thanks
Jay

[BeastRider]

I calculated a couple of distances by hand and the formula as written does compute mileage. However, if you are not getting the correct answer then you will need to do one more thing and that is subtract the latitude values from 90 degrees as in

$lat1 = 90 - $lat1;
$lat2 = 90 - $lat2;

This should make the values work. That is the reason I asked you to check the zip code.

The formula you are using is the first method for computing a side of an oblique spherical triangle when given the other two sides and the angle between them. However, latitude is measured from the equator and the angle needs to be measured from the pole.

Of course using MILES won't work. It is nowhere in the table. What you keep seeing is the last calculation value from earlier in the script.

As to coding style, you can get 100 answers as to the best way. I have written assembly language, Fortran, C, Cobol, Pascal, Visual Basic, Matlab, Lisp, and a couple of others. To me, the key is code clarity. I like white space. I like indents. I like reasonably short lines.

When a see an ugly mass of single spaced code posted here, with word wrapped lines, no comments, and little or no indents, I just move on. The challenge is figuring out the problem, the challenge is trying to unsnarl ugly code and that is no fun.

Spherical trig ref pages so you know what you are doing.

http://mathworld.wolfram.com/SphericalTrigonometry.html

http://www.angelfire.com/nt/navtrig/B3.html

http://www.jqjacobs.net/astro/arc_form.html (second method for computing distance)

http://www.hartrao.ac.za/nccsdoc/slalib/sun67.htx/node198.html

[phpnow]

thanks for the respose....i think you may have misunderstood me tho. or i misunderstood you...lol. anywaym what i was trying to say is you know how when your code got down the while loop and foreach statements you have the following:

// note that this array contains all the returned query fields
// including the calculated field dist. So, we now go through this array
// and make real distance computations.

foreach ($myarray as $row)
{
$lon2 = "$row[LONGITUDE]" $rad; // convert to radians
$lat2 = "$row[LATITUDE]" $rad;

$theta = $lon2 - $lon1;

$dist = acos(sin($lat1) sin($lat2) + cos($lat1) cos($lat2) cos($theta));

if ($dist < 0) { $dist += $pi; }

$dist = $dist 6371.2;

// $miles is the magical number we wish to sort on so put it into the array
// into the calulated field we reserved for this purpose
$miles = doubleval($dist * 0.621);

$row["dist"] = $miles;
}

AND THEN---->>(this controls showing the results)
// we now have an array sorted in ascending order by distance
// it is ready output. For example, based on the fields I used

echo "<br>Zipcodes Sorted By Distance (near to far)<br>";
foreach ($mysortarray as $row)
{
$zip = $row["zipcode"];
$lat = $row["latitude"];
$long = $row["longitude"];
$dist = $row["dist"];
echo "Zipcode $zip at Lat $lat and Long $long is $dist miles from you<br>";
}

what i was talking about was the VAR $dist spits out answers like 36.4384849(thats an example) and it does so in order from lowest to highest (perfect...thats what i needed) but that number 36.4384849 is not a correct number for miles. now if i change the echo statement to echo $miles and not $dist then it is the correct distance but it does not put it thru the loop and spits out say 50 results but with the miles. I guess what i am asking is how do i take that number 36.4384849 and get it to the correct miles but still keep sorting the way i need? hope i was not to confusing

thanks
jay

[BeastRider]

Post a couple of lat long values from your zip file and tell me where the zip codes are located (city and state).

Also, post some real values (along with zip code and location) that you have gotten from the script... both the $dist and $mile variables.

[BeastRider]

The problem lies in the use of "foreach". It works on a copy of the array, and thus, any computations made into the array don't show up.

As a matter of efficiency, I would also change all the "foreach" statements in the routine to a "for ($i=0...)" statement to avoid array copying.

But, what you need to do to make this work is change this block of code:

foreach ($myarray as $row) 
  { 
  $lon2 = "$row[LONGITUDE]" * $rad; // convert to radians 
  $lat2 = "$row[LATITUDE]" * $rad;  

  $theta = $lon2 - $lon1;  

  $dist = acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($theta));  

  if ($dist < 0) { $dist += $pi; }  

  $dist = $dist * 6371.2;  

// $miles is the magical number we wish to sort on so put it into the array 
// into the calulated field we reserved for this purpose 
  $miles = doubleval($dist * 0.621);  

  $row["dist"] = $miles; 
  } 

to:

$arrcount = count($myarray);
for ($i = 0; $i < $arrcount; $i++)
  { 
  $lon2 = "$myarray[$i][LONGITUDE]" * $rad; // convert to radians 
  $lat2 = "$myarray[$i][LATITUDE]" * $rad;  

  $theta = $lon2 - $lon1;  

  $dist = acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($theta));  

  if ($dist < 0) { $dist += $pi; }  

  $dist = $dist * 6371.2;  

// $miles is the magical number we wish to sort on so put it into the array 
// into the calulated field we reserved for this purpose 
  $miles = doubleval($dist * 0.621);  

  $myarray[$i]["dist"] = $miles; 
  } 

[BeastRider]

And, if you do as above, you can also forget about creating a DIST placeholder in the query as in

$result2 = mysql_db_query($database, "SELECT latitude longitude zipcode 
                  2 * latitude AS dist 
                  FROM locations ");  

The element dist will be added to $myarray automatically as a result of the calculation, so your query becomes

$result2 = mysql_db_query($database, "SELECT * FROM locations ");