List:General Discussion« Previous MessageNext Message »
From:Richard Lynch Date:April 19 2005 1:59am
Subject:Re: zip code search within x miles
View as plain text  
You can buy up-to-the-minute zips, or snag TIGER (or gazateer?) data
that's a bit old for free.

It's a 1-1 mapping of zip to long/lat.

The tricky bit is this.

There's about 65,000 zips, even in the out-dated list for free.

Let's say you've got, oh, 2000 records to search through.

You're gonna end up doing a JOIN with:
2,000 X 65,000 == 130,000,000 tuples (records/results).

130 MILLION tuples is *way* too many for your basic $20/month site.

So what you do is break the rules.

That's right, it's rule-breaking time.

First, add a longitude and latitude column to your 2000 record table,
default to NULL.

Next, add a trigger (in MySQL 4.1+ I think) so that any time you change
the country or zip, the latitude/longitude gets looked up in the zips
table, and filled in.

If you can't do triggers (in MySQL pre-4.1) you'll have to arrange your
Busicness Logic such that any time you UPDATE country or zip in your 2000
records table, you reset longitude/latitude to NULL.

Next, write a cron job to snag, say, 10 records from your table "WHERE
latitude is NULL" in random order.

Lookup the country and zip in your zips table, and get your $latitude and
$longitude for that country/zip then do:

update EXAMPLE set latitude = $latitude, longitude = $longitude where
country = '$country' and zip = '$zip'

Errr, I kinda slipped into PHP there, but you probably know what I mean. 
$x is a variable holding the values you looked up.

Run that cron job a whole lot at first, by hand, to get mostly all the
2000 records fixed up with the right long/lat.

The point of this is that *NOW* you only have to search through 2000
records for your distance function, which sure beats a join with 130
million records, eh?

Also, if you just want the NEAREST matches, forget all that complicated
crap about Great Circle distance and expensive trigonometric calculations.

You can just get the zip they want to search by, lookup $longitude and
$latitude for that zip, and then use:

select {application_data_here), abs(latitude - $latitude) + abs(longitude
- $longitude) as closest from ... order by closest

If MySQL won't let you use 'closest' in the where clause, just move the
calculations there.

I've done this before for clients who didn't want to muck with all this. 
It's a few hours' work.  Call it $200.

PS I'm working on a GLOBAL POSTAL system which will data-gather OSS
long/lat and country/zip for the entire world, rather than the current
mess of having only the US for free, and maybe Canada and Australia, but
not much else is free, and often not even available.

I guess I'd better post here when I launch that.  No time-line available yet.

On Fri, April 15, 2005 3:37 pm, Scott Haneda said:
> How are sites doing the search by zip and coming up with results within x
> miles?  Is there some OSS zip code download that has been created for
> this?
> --
> -------------------------------------------------------------
> Scott Haneda                                Tel: 415.898.2602
> <http://www.newgeo.com>                     Novato, CA U.S.A.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


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