More Efficient - Calculated Column reuse? 3

[Trevahaha]

So I have a php page(hence the $ variables) that given some zipcode and distance will return all zip codes within the given miles sorted by distance. I want to be able to use column "DIST"

SELECT zip, latitude, longitude, 
DEGREES(acos(sin('$rlat')*sin(RADIANS(latitude))+COS('$rlat')*COS(RADIANS(LATITUDE))*COS('$rlong'-(RADIANS(LONGITUDE))))) as distance
FROM zipcodes WHERE (DEGREES(acos(sin('$rlat')*sin(RADIANS(latitude))+COS('$rlat')*COS(RADIANS(LATITUDE))*COS('$rlong'-(RADIANS(LONGITUDE))))) <= '$degree') ORDER BY dist;

Is there a way I can not make the query calculate this twice?

I tried to do this:

FROM zipcodes WHERE (dist <= '$degree')

however it said it can't find the unknown column 'dist'

Any suggestions?
THANKS!

 

[Trevahaha]

ugh, typo there, distance is supposed to be dist... wanted to make it more readable

 

[cLFlaVA]

A) I don't see any reference to any column 'dist' other than in your ORDER BY clause.

B) If you meant to say ORDER BY distance, well, you can't, because distance is an alias of a column, not an actual column.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[Trevahaha]

Yeah, that was a typo I made.. But is there any way to make this more efficient? Should I assume mySQL will realize these are the same and only calculate it once?

 

[cLFlaVA]

As far as I know, there is no way. I hope someone will tell me otherwise. A SELECT statement is happening once for all records in the database. If you were doing one SELECT at a time, that would be a different story. Since it's only happening once, I'm not aware of any way to store it in a temp variable.

You may want to do some research on

http://www.mysql.com/

regarding: variables and stored procedures (if you're using MySQL 5.0).

Sorry I couldn't be of more help.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[TonyGroves]

One way around that would be to create a temporary table with a field containing the result of the calculation and query that:
[tt]
CREATE TEMPORARY TABLE t AS
SELECT zip,latitude,longitude,DEGREES(...) distance
FROM zipcodes;

SELECT *
FROM t
WHERE distance<=$degree
ORDER BY distance;

DROP TABLE t;
[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[Trevahaha]

Thanks cLFlaVA and TonyGroves for your suggestions. I think I'm going to stick to my original code -- it's doing okay with the 45,000ish zip codes right now, taking just a couple seconds to execute. I'd be afraid that creating a table would take extra time, since I'm only using the information once.

 

[cLFlaVA]

Trev-

I'm looking for some very similar functionality for a website I'm developing. Where did you get your zipcode data from? Did you puchase it?

I know there are many different zipcode references available for purchase (via Googling), but, as I am doing a favor for a friend, would like to keep costs minimal.

Thanks.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[Trevahaha]

I got it from googling a BUNCH - lots of places want to charge you for it -- even the USPS!

It's free from codeproject.com:

http://www.codeproject.com/useritems/zipcodemapping.asp

Here's the direct link to the zip file. It contains some code for mapping (VS/.net) as well as the MS Access database containing the table. It contains Zipcode, Latitude, Longitude, City, State & County.

http://codeproject.com/useritems/zipcodemapping/zipcodemapping_demo.zip

 

[cLFlaVA]

Thanks a ton.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!