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
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Distance Equation Failing

Post 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 :?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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...
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

What does your table look like? If I remember correctly when I did this I was able to run queries super quick.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

World Domination :twisted: Actually it's a members site for a client. Can't really say more yet.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Distance Equation Failing

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

feyd can you help me on this one? Anyone good at math?
Post Reply