List:General Discussion« Previous MessageNext Message »
From:Richard Lynch Date:April 25 2005 6:04am
Subject:Re: zip code search within x miles
View as plain text  
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

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