List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:October 9 2005 7:32pm
Subject:Re: Distance between Zip codes
View as plain text  
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
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: Hank <heskin@stripped>
> Reply-To: Hank <heskin@stripped>
> Date: Sun, 9 Oct 2005 11:12:10 -0400
> To: "Steffan A. Cline" <steffan@stripped>
> Cc: "mysql@stripped" <mysql@stripped>
> 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/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