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