Hi, Just as a speed note! It's faster to calculate a square and find those records and then drop the corners with the circle calculation.
Scott. > -----Original Message----- > From: Eamon Daly [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 19, 2005 11:20 AM > To: Scott Haneda; MySql > Subject: Re: zip code search within x miles > > > I don't think anyone's replied with an actual great circle > calculation. Here's our zipcode table, populated with data > from zipcodedownload.com (and note the index on zipcode, > latitude, and longitude: > > CREATE TABLE `zipcodes` ( > `zipcode` char(5) NOT NULL default '', > `zipcode_type` enum('S','P','U','M') NOT NULL default 'S', > `city` char(45) NOT NULL default '', > `city_type` enum('D','A','N') NOT NULL default 'D', > `state` char(75) NOT NULL default '', > `state_code` char(2) NOT NULL default '', > `area_code` char(3) default NULL, > `latitude` double(12,6) NOT NULL default '0.000000', > `longitude` double(12,6) NOT NULL default '0.000000', > KEY `city_state_code` (`zipcode`,`city`,`state_code`), > KEY `position` (`zipcode`,`latitude`,`longitude`) > ) TYPE=MyISAM > > Here's the calculation to get zipcodes within 5 miles of > 02134: > > SELECT > b.zipcode, b.state, > (3956 * (2 * ASIN(SQRT( > POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + > COS(a.latitude*0.017453293) * > COS(b.latitude*0.017453293) * > POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2) > )))) AS distance > FROM zipcodes a, zipcodes b, zipcodes c > WHERE > a.zipcode = '02134' AND # <-- Your starting zipcode > a.zipcode = c.zipcode AND > (3956 * (2 * ASIN(SQRT( > POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + > COS(a.latitude*0.017453293) * > COS(b.latitude*0.017453293) * > POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2) > )))) <= 5 # <-- Your target radius > GROUP BY distance > > Takes about half a second on our box. Here's the explain: > > *************************** 1. row *************************** > table: a > type: ref > possible_keys: city_state_code,position,default_city > key: position > key_len: 5 > ref: const > rows: 2 > Extra: Using where; Using index; Using temporary; Using filesort > *************************** 2. row *************************** > table: c > type: ref > possible_keys: city_state_code,position,default_city > key: position > key_len: 5 > ref: const > rows: 2 > Extra: Using where; Using index > *************************** 3. row *************************** > table: b > type: ALL > possible_keys: NULL > key: NULL > key_len: NULL > ref: NULL > rows: 70443 > Extra: Using where > > In case you're curious about the number of zipcodes: > > mysql> select count(*), count(distinct zipcode) from zipcodes; > +----------+-------------------------+ > | count(*) | count(distinct zipcode) | > +----------+-------------------------+ > | 70443 | 42471 | > +----------+-------------------------+ > > Can't take credit for the SQL, by the way; I'm pretty sure I > found it on Google. > > ____________________________________________________________ > Eamon Daly > > > > ----- Original Message ----- > From: "Scott Haneda" <[EMAIL PROTECTED]> > To: "MySql" <mysql@lists.mysql.com> > Sent: Friday, April 15, 2005 5:37 PM > Subject: zip code search within x miles > > > > 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? > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
