List:General Discussion« Previous MessageNext Message »
From:<gunmuse Date:April 25 2005 7:51am
Subject:RE: zip code search within x miles
View as plain text  
Http://www.gunmuse.com

Ok I use a storelocator.

First if you have 8000 + records it becomes an issue.  BUT  Lat and long is
in minutes and minutes can be used to estimate miles.  By Breaking down the
lat and long,  Breaking down the Zip to a two digit prefix 88254 becomes 88
for indexing (Because the post offices goes in order folks with some
exceptions) Then with a wide lasso you can rope your results to do your math
check with.  Break your lat and long fields up in hours minutes and seconds
and filtering down becomes very easy to do.

Learning to read a map before determining the key and distance calculation
would help better understand this problem.

Thanks
Donny Lairson
President
29 GunMuse Lane
P.O. box 166
Lakewood NM 88254
http://www.gunmuse.com
469 228 2183


-----Original Message-----
From: Richard Lynch [mailto:ceo@stripped]
Sent: Monday, April 25, 2005 12:05 AM
To: Hank
Cc: MySql
Subject: Re: zip code search within x miles


On Tue, April 19, 2005 8:55 am, Hank said:
> Talk about over complicating things... here's the above query simplifed.
>
> I can not figure out why they were self joining the table three times:
>
> SELECT b.zip_code, b.state,
>        (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.lng-b.lng)*0.017453293)/2),2))))) AS distance
> FROM zips a, zips b
> WHERE
>        a.zip_code = '90210'
> GROUP BY distance
> having distance <= 5;

You'd have to time it, and *MAYBE* with enough indices this will all work
out, but you'd probably be better off doing two queries.

One to look up the long/lat for 90210, and another on just zips to
calculate the distance.

Benchmark on your own hardware and see for yourself.  I could be 100% wrong.

--
Like Music?
http://l-i-e.com/artists.htm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1



Thread
zip code search within x milesScott Haneda16 Apr
  • RE: zip code search within x milesgunmuse16 Apr
  • Re: zip code search within x milesRobert Dunlop16 Apr
    • RE: zip code search within x milesScott Johnson19 Apr
  • Re: zip code search within x milesGreg Donald16 Apr
  • Re: zip code search within x milesScott Gifford16 Apr
    • RE: zip code search within x milesgunmuse16 Apr
      • Re: zip code search within x milesJeff Kolber16 Apr
        • Re: zip code search within x milesScott Gifford17 Apr
          • Re: zip code search within x milesJeff Kolber20 Apr
  • Re: zip code search within x milesRichard Lynch19 Apr
    • Re: zip code search within x milesparis lundis19 Apr
      • Re: zip code search within x milesHank19 Apr
    • Re: zip code search within x milesHank19 Apr
      • Re: zip code search within x milesRichard Lynch25 Apr
  • Re: zip code search within x milesEamon Daly19 Apr
    • RE: zip code search within x milesScott Johnson19 Apr
  • Re: zip code search within x milesEamon Daly19 Apr
    • Re: zip code search within x milesHank19 Apr
      • Re: zip code search within x milesKeith Ivey19 Apr
        • Re: zip code search within x milesHank19 Apr
          • Re: zip code search within x milesSGreen19 Apr
            • Re: zip code search within x milesHank19 Apr
              • Re: zip code search within x milesRichard Lynch25 Apr
            • Re: zip code search within x milesJeremy Cole29 Apr
          • Problem detected this morning.Fredrick Bartlett5 Aug
            • Re: Problem detected this morning.Gleb Paharenko7 Aug
        • Re: zip code search within x milesScott Haneda19 Apr
      • Re: zip code search within x milesRichard Lynch25 Apr
        • RE: zip code search within x milesgunmuse25 Apr