Distance calculations inside of a mysql query with joins

[dgruetter]

 

Hey,

 

I was wondering if someone can help me with a query I am trying to build. I want to list all nearby cities within a 100 mile radius. I am using a set of tables that are joined and I am trying to calculate the distance inside the query. I am having trouble with the syntax and I am not sure how to reference the fields being calculated. I have something like this:

 

$sql = "SELECT C.Latitude, C.Longitude, C.City, Co.Country, 

(((acos(sin((".$latitude."*pi()/180)) * sin((`Latitude`*pi()/180))+cos((".$latitude."*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((".$longitude."- `Longitude`)*pi()/180))))*180/pi())*60*1.1515) as distance 

FROM Cities C JOIN Countries Co ON Co.CountryId = C.CountryId WHERE distance <= 100"

 

Do I reference the fields by their aliases in the calculation?

 

A thousand bows if someone can help me.

[dgruetter]

I did some research and found

 

"You can't use aliases in WHERE conditions, because WHERE clause evaluation precedes the aliases evaluation. "

 

This makes sense. So I changed the query (took out join because I realized I didn't even need it) to:

 

$sql = "SELECT *

 

FROM `Cities`  WHERE (((acos(sin((".$latitude."*pi()/180)) * sin((`Latitude`*pi()/180))+cos((".$latitude."*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((".$longitude."- `Longitude`)*pi()/180))))*180/pi())*60*1.1515) <= 100";

 

Got a bunch of results. Seems to be working.

[fenway]

You need HAVING -- and don't use great circle distance -- it's just unnecessarily slow.

[dgruetter]

Thanks!

 

I substituted HAVING instead of WHERE and it also worked. Does this save on resources? I am still using the great circle formula but I am trying to find a better one. Any suggestions?

 

$sql = "SELECT Latitude, Longitude, City, (((acos(sin((".$latitude."*pi()/180)) * sin((`Latitude`*pi()/180))+cos((".$latitude."*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((".$longitude."- `Longitude`)*pi()/180))))*180/pi())*60*1.1515) AS distance

FROM `Cities`  HAVING  distance <= 5";

[fenway]

Better == Pythagoras.