I think it was your formula that I finally got to work as I needed.
Excuse the Lasso in here :
-sql=('SELECT b.zip, b.state,b.storename, b.address1, b.address2, b.city,
b.state, b.zip,
ROUND((3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
COS(a.lat*0.017453293) *
COS(b.lat*0.017453293) *
POWER(SIN(((a.lon-b.lon)*0.017453293)/2),2))))),2) AS distance
FROM zipcodes a, locations b
WHERE
a.zip = "' ($zip) '"
GROUP BY distance
having distance <= ' ($range) ';');
Its fast and works great. Although is there no way for it to lookup the
latitude and longitude for the zip in the "b" table before doing the query?
I was up late trying to get that to work and couldn't so I then looked at
using a trigger to insert it into the "b" (locations) table upon creation
of each new record but I have another thread on that issue. :(
Any suggestions to improve it or a better way of doing it?
Thanks
Steffan
---------------------------------------------------------------
T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net USA
AIM : SteffanC ICQ : 57234309
The Executive's Choice in Lasso driven Internet Applications
Lasso Partner Alliance Member
---------------------------------------------------------------
> From: Hank <[EMAIL PROTECTED]>
> Reply-To: Hank <[EMAIL PROTECTED]>
> Date: Sun, 9 Oct 2005 11:12:10 -0400
> To: "Steffan A. Cline" <[EMAIL PROTECTED]>
> Cc: "mysql@lists.mysql.com" <mysql@lists.mysql.com>
> Subject: Re: Distance between Zip codes
>
> Don't forget that you're not caclucating "driving distance", but
> "great circle" distance, which is roughly a straight line over short
> distances. If you radius is great than, say 50 miles, people might
> complain that the actual driving distance is much greater than the
> straight line distance you provided.
>
> --
>
> -Hank
>
> mysql, query
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
