Distance Equation Failing

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

without knowing the specific entries that the math must go against ahead of time, it's a bit difficult to really speed up the math. But you can precalculate a lot of the math and help MySQL by simplifying the math out for it.

Things such as the cosine and squares can be precalculated and simplified, respectively. Those alone may speed up things. I remember POW() generally being a fairly taxing function. Cosine, not so much, but every little bit helps when you're running against thousands.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

agtlewis, this is the query I use to pull all results within a radius. I know this is not exactly what you are after but maybe this could steer you in the right direction?

Code: Select all

<?php
// c is the client table, z is the zipcode data table
$sql = "SELECT c.*, z.state AS zstate 
		FROM client_table c 
		INNER JOIN zipcode_table z 
			ON c.client_zipcode = z.zipcode
		WHERE (
			pow((69.1 * (z.lon_column-$userLongitude)) * cos($userLatitude/57.3),2) + 
			pow((69.1 * (z.lat_column-$userLatitude)),2)
		) < ($radius * $radius)"; 
?>
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Thank you Everah, I have it setup like that currently but it's too slow. What I have below "should" work as everything looks correct, but it doesn't.

Take for example my zip code, 65804.

My Latitude and Longitude are 37.148949 and -93.261385.

Now when I run the following,

Code: Select all

echo "MILES: " . $Miles . "<br />";
      echo "Latitude: " . $Latitude . "<br />";
      echo "Longitude: " . $Longitude . "<br />";
      
      $lat_range = $Miles/69.172;
      $lon_range = abs($Miles/(cos($Latitude) * 69.172));
      
      echo "Latitude Range: " . $lat_range . "<br />";
      echo "Longitude Range: " . $lon_range . "<br />";
      
      $min_lat = number_format($Latitude - $lat_range, "4", ".", "");
      $max_lat = number_format($Latitude + $lat_range, "4", ".", "");
      $min_lon = number_format($Longitude - $lon_range, "4", ".", "");
      $max_lon = number_format($Longitude + $lon_range, "4", ".", "");
I receive the following result.
MILES: 250
Latitude: 37.148949
Longitude: -93.261385
Latitude Range: 3.614179147632
Longitude Range: 4.2398077445168

Latitude BETWEEN '33.5348' AND '40.7631' AND Longitude BETWEEN '-97.5012' AND '-89.0216'
The Latitude of 37.148949 is BETWEEN 33.5348 AND 40.7631
The Longitude of -93.261385 is BETWEEN -97.5012 AND -89.0216 however no records are returned.

Even a very simple query such as the one below returns no rows.

Code: Select all

SELECT * FROM `TableName` WHERE Latitude BETWEEN '33.5348' AND '40.7631' AND Longitude BETWEEN '-97.5012' AND '-89.0216'
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post by Roja »

agtlewis wrote:

Code: Select all

SELECT * FROM `TableName` WHERE Latitude BETWEEN '33.5348' AND '40.7631' AND Longitude BETWEEN '-97.5012' AND '-89.0216'
Lets start with some basics.

First, do an explain on the query, and see what it yields.

My other suspicion is that the negative ranges might be confusing it. Try switching the last between like so:

Code: Select all

SELECT * FROM `TableName` WHERE Latitude BETWEEN '33.5348' AND '40.7631' AND Longitude BETWEEN '-89.0216' AND '-97.5012'
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Interesting, swapping the minimum & maximum longitudes did the trick. Is that a bug in MySQL? I just hope it doesn't have any bad side effects down the road. Is there anyway I can leave it how it is supposed to be a speficy that it is a float in the query?
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post by Roja »

agtlewis wrote:Interesting, swapping the minimum & maximum longitudes did the trick. Is that a bug in MySQL? I just hope it doesn't have any bad side effects down the road. Is there anyway I can leave it how it is supposed to be a speficy that it is a float in the query?
Its a bug in your understanding of math. :)

Between requires that the first number is SMALLER. Bigger negative number = smaller. :)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Well that is strange because I used this snippet,

Code: Select all

$lat_range = $range/69.172;
      $lon_range = abs($range/(cos($details[0]) * 69.172));
      $min_lat = number_format($details[0] - $lat_range, "4", ".", "");
      $max_lat = number_format($details[0] + $lat_range, "4", ".", "");
      $min_lon = number_format($details[1] - $lon_range, "4", ".", "");
      $max_lon = number_format($details[1] + $lon_range, "4", ".", "");

      $return = array();    // declared here for scope

      $sql = "SELECT zip_code, lattitude, longitude FROM zip_code
              WHERE zip_code <> $zip AND lattitude BETWEEN '$min_lat' AND 
             '$max_lat' AND longitude BETWEEN '$min_lon' AND '$max_lon'";
from the Zip Code Distance class. I never tested that class but other people say it works. So if the math is wrong, how does it work?

And I do realize that a (negative) bigger number is smaller, which makes me wonder how reversing them works, because

Code: Select all

Longitude BETWEEN '-89.0216' AND '-97.5012'
is backwards. -89 is greater than -97, and that is what works in the query. When I had it the correct way, with the smaller number first, it would not return any results.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

This is driving me nuts. Why does it work this way:

Code: Select all

$sql = "SELECT zip_code, lattitude, longitude FROM zip_code
              WHERE zip_code <> $zip AND lattitude BETWEEN '$min_lat' AND 
             '$max_lat' AND longitude BETWEEN '$min_lon' AND '$max_lon'"
In the Zip code distance class, but when I do the same thing I have to swap min_lon and max_lon?
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post by Roja »

agtlewis wrote:is backwards. -89 is greater than -97, and that is what works in the query. When I had it the correct way, with the smaller number first, it would not return any results.
Oh weird, I thought it was the other way.

You may have found an honest to goodness bug in the way it handles negative numbers!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Problem was that Latitude and Longitude tables were varchar rather than DECIMAL or FLOAT.
Post Reply