Page 2 of 2
Posted: Wed Mar 22, 2006 10:29 pm
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.
Posted: Thu Mar 23, 2006 12:23 am
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)";
?>
Posted: Thu Mar 23, 2006 4:16 am
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'
Posted: Thu Mar 23, 2006 8:41 am
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'
Posted: Thu Mar 23, 2006 9:32 am
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?
Posted: Thu Mar 23, 2006 10:12 am
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.

Posted: Thu Mar 23, 2006 10:21 am
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.
Posted: Thu Mar 23, 2006 5:15 pm
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?
Posted: Thu Mar 23, 2006 6:37 pm
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!
Posted: Mon Jun 12, 2006 11:36 pm
by Benjamin
Problem was that Latitude and Longitude tables were varchar rather than DECIMAL or FLOAT.