Distance Equation Failing
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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)";
?>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,
I receive the following result.
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.
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", ".", "");The Latitude of 37.148949 is BETWEEN 33.5348 AND 40.7631MILES: 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 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'Lets start with some basics.agtlewis wrote:Code: Select all
SELECT * FROM `TableName` WHERE Latitude BETWEEN '33.5348' AND '40.7631' AND Longitude BETWEEN '-97.5012' AND '-89.0216'
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'Its a bug in your understanding of math.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?
Between requires that the first number is SMALLER. Bigger negative number = smaller.
Well that is strange because I used this snippet,
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
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.
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'";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'This is driving me nuts. Why does it work this way:
In the Zip code distance class, but when I do the same thing I have to swap min_lon and max_lon?
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'"Oh weird, I thought it was the other way.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.
You may have found an honest to goodness bug in the way it handles negative numbers!