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

query.This is definitely one of those situations where performing this
query in stages makes better sense (speed-wise).

Shawn Green
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 :
>
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>
> > 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
>

```