Ok. I think I have what I need... Almost. Here is the query I settled on: SELECT b.zip, b.state,b.storename, (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))))) AS distance FROM zipcodes a, locations b WHERE a.zip = "85032" GROUP BY distance having distance <= 10;
The problem is that in my locations (stores) table I am required to have a latitude and longitude. Is there anyway to modify this query to look up the lat/lon of the zipcode in the locations (stores) table BEFORE it does the remainder of the query? The goal is to not need those in the locations table. 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: "Steffan A. Cline" <[EMAIL PROTECTED]> > Date: Sat, 08 Oct 2005 15:59:35 -0700 > To: "mysql@lists.mysql.com" <mysql@lists.mysql.com> > Subject: Distance between Zip codes > > I was wondering if anyone might have a canned query I could use for the > following scenario. > > I need to search for a list of locations within a certain distance of a user > given zip code and order them by driving distance calculated from a table of > zip codes containing lon and lat info from zipwise. > > Example: > > 1. I enter my zip of 85050 > 2. enter a range in miles > 3. search a table of establishments within x miles from step 2 > 4. list top ten within range of step 2 ordered by distance. > > For the establishments I have the zip codes and as I said I have the zipwise > tables. > > Any suggestions? This query if completely possible within MySQL is well > above me. > > > > 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 > --------------------------------------------------------------- > > > > -- > 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]