List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:October 9 2005 4:11am
Subject:Re: Distance between Zip codes
View as plain text  
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
Steffan@stripped                             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" <steffan@stripped>
> Date: Sat, 08 Oct 2005 15:59:35 -0700
> To: "mysql@stripped" <mysql@stripped>
> 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
> Steffan@stripped                             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/mysql?unsub=1
> 

Thread
Distance between Zip codesSteffan A. Cline9 Oct
  • Re: Distance between Zip codesmos9 Oct
  • Re: Distance between Zip codesSteffan A. Cline9 Oct
  • Re: Distance between Zip codesSteffan A. Cline9 Oct
    • Re: Distance between Zip codesHank9 Oct
      • Re: Distance between Zip codesSteffan A. Cline9 Oct
        • Re: Distance between Zip codesSGreen10 Oct
  • Suppress table header when using ODBCGerald Taylor11 Oct
    • Re: Suppress table header when using ODBCGleb Paharenko11 Oct
      • Re: Suppress table header when using ODBCGerald Taylor11 Oct
        • Re: Suppress table header when using ODBCGleb Paharenko11 Oct