List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 10 2005 1:50pm
Subject:Re: Distance between Zip codes
View as plain text  
You might try doing exactly what you ask to speed things up. Create a 
temporary table to hold the results of an intermediate query from 
locations then use the temp table as the source data for the greater 
circle calculation.  By limiting the distance combinations that need to be 
calculated by estimating a "square-ish" range of latitudes and longitudes, 
you should seriously improve performance.

CREATE TEMPORARY TABLE tmpLocations
SELECT  lat, lon, ...other columns ...
FROM locations
WHERE lat BETWEEN ...some range +/- around your target zip...
AND lon BETWEEN ...another range +/- around your target zip...

Then you can use tmpLocations instead of locations in your original 
query.This is definitely one of those situations where performing this 
query in stages makes better sense (speed-wise). 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Steffan A. Cline" <steffan@stripped> wrote on 10/09/2005 03:32:30 PM:

> 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
> > 
> 
> 
> -- 
> 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