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" <[EMAIL PROTECTED]> 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
> [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]
>
