Page 1 of 2

Distance Equation Failing

Posted: Wed Mar 22, 2006 11:41 am
by Benjamin
I might be missing something really simple here but

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", ".", "");
From the Zip Code Class, converted to...

Code: Select all

$lat_range = $Miles/69.172;
      $lon_range = abs($Miles/(cos($Latitude) * 69.172));
      $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", ".", "");
isn't working. The orginal function accepted miles as $range. This outputs...

Code: Select all

Latitude BETWEEN '-161.8797' AND '-154.6514' AND Longitude BETWEEN '47.4691' AND '66.7227'
I'm pretty tired though so :?

Posted: Wed Mar 22, 2006 12:08 pm
by RobertGonzalez
Are the calculations returning the lat's and lon's that you show in the query? Or are the distances not coming out the way you think they should. I know when it comes to surface radius searches and distances, there's a conversion that needs be made. The distance of one degree (in lats and lons) needs to be converted to X miles. I can look up the formula I have if you want.

Otherwise, correct me if I am not understanding your question.

Posted: Wed Mar 22, 2006 12:12 pm
by hawleyjr
Everah wrote:Are the calculations returning the lat's and lon's that you show in the query? Or are the distances not coming out the way you think they should. I know when it comes to surface radius searches and distances, there's a conversion that needs be made. The distance of one degree (in lats and lons) needs to be converted to X miles. I can look up the formula I have if you want.

Otherwise, correct me if I am not understanding your question.
You are correct. The earth is not flat therefore you will need to calculate for that. There is a distance class here that is pretty slick and will help you with what your doing...

Posted: Wed Mar 22, 2006 1:01 pm
by Benjamin
Well, I would prefer something as accurate as possible. I really don't know why that doesn't work though, it's the same as it is in the zip code distance class. If I comment it out and uncomment this...

Code: Select all

$query .= "(POW((69.1*(Longitude-\"$Longitude\")*cos($Latitude/57.3)),\"2\")+POW((69.1*(Latitude-\"$Latitude\")),\"2\"))<($Miles*$Miles) ";
it works fine, but the problem is that it slows the query down too much, so I would prefer to calculate the min and max values instead, If any of you have anything better I would appreciate it.

Posted: Wed Mar 22, 2006 1:04 pm
by hawleyjr
What does your table look like? If I remember correctly when I did this I was able to run queries super quick.

Posted: Wed Mar 22, 2006 1:07 pm
by Benjamin
The table has hundreds of thousands of records along with around 70 other fields and the query has 5 joins. I can query it in 0.5 seconds by not including the math in the query. With the math equations it takes > 3 seconds and uses quite a bit more memory.

Posted: Wed Mar 22, 2006 1:09 pm
by hawleyjr
agtlewis wrote:The table has hundreds of thousands of records along with around 70 other fields and the query has 5 joins. I can query it in 0.5 seconds by not including the math in the query. With the math equations it takes > 3 seconds and uses quite a bit more memory.
Wow that is wicked long. What are you trying to accomplish?

Posted: Wed Mar 22, 2006 1:13 pm
by Benjamin
World Domination :twisted: Actually it's a members site for a client. Can't really say more yet.

Posted: Wed Mar 22, 2006 1:31 pm
by RobertGonzalez
Seriously, I would consider doing the distance calculation code side. The database can handle the query very fast (mine was scanning a 50K record DB table and returning the result set in like 0.28 seconds) as long as the database wasn't doing the math for each record. The distance calculation I was using was being done in the WHILE loop of the result array assignment and being added to the result array. I was sorting the array by distance by using array_multisort (or a variation of it). Altogether, running the user input through some savage validation, setting lats and lons, querying, calculating, assigning and displaying was taking between 0.9 and 1.2 seconds per search. This, of course, was on a USA Zip Code table that housed about 50K records, but it was still pretty fast.

Posted: Wed Mar 22, 2006 1:37 pm
by Benjamin
The main table contains a latitude and longitude for each record. This isn't poor design and cannot be changed. I would like to calculate the minimum and maximum, latitude and longitude using PHP code. The problem is that the code I have isn't calculating them correctly.

Posted: Wed Mar 22, 2006 1:42 pm
by RobertGonzalez
agtlewis wrote:The main table contains a latitude and longitude for each record. This isn't poor design and cannot be changed.
Gotcha. I agree with you. This is probably the best design for a table to do what you are doing.

What is the calculation doing? How is it wrong?

Re: Distance Equation Failing

Posted: Wed Mar 22, 2006 1:50 pm
by Benjamin
This query is designed to find all records within 250 miles of 99550.

Code: Select all

Latitude BETWEEN '53.4817' AND '60.7101' AND Longitude BETWEEN '-162.4978' AND '-154.0333' ORDER BY POW((69.1*(Longitude-"-158.26557")*cos(57.095912/57.3)),"2")+POW((69.1*(Latitude-"57.095912")),"2") ASC Limit 0,1000
It doesn't return any records at all.

This is an example of a record that should be returned, and is returned with the equation in the query.
Zip Code 99550
Latitude 57.901192
Longitude -153.048717

Looks like the Max Longitude is too low.

Guess I should have said that to begin with.

Posted: Wed Mar 22, 2006 3:51 pm
by RobertGonzalez
It may not matter, but have you tried throwing parenthesis into your query...

Code: Select all

WHERE (Latitude BETWEEN '53.4817' AND '60.7101') 
AND (Longitude BETWEEN '-162.4978' AND '-154.0333') 
ORDER BY POW((69.1*(Longitude-"-158.26557")*cos(57.095912/57.3)),"2")+POW((69.1*(Latitude-"57.095912")),"2") ASC 
Limit 0,1000
This may do nothing at all, but I wanted to throw it out there.

Posted: Wed Mar 22, 2006 8:53 pm
by Benjamin
It shouldn't need the (). I am pretty sure there is a math problem but I don't know enough about math to fix it. Perhaps it's expecting input latitude and longitude as radians?

Posted: Wed Mar 22, 2006 9:52 pm
by Benjamin
feyd can you help me on this one? Anyone good at math?