Interestingly enough, I found another great circle routine here:
http://www.meridianworlddata.com/Distance-Calculation.asp
and adapted it for MySQL like so:
SELECT
b.zipcode, b.state,
3963.0 * acos(
sin(a.latitude/57.2958) * sin(b.latitude/57.2958) +
cos(a.latitude/57.2958) * cos(b.latitude/57.2958) * cos(b.longitude/57.2958 - a.longitude/57.2958)
) AS distance
FROM zipcodes a, zipcodes b, zipcodes c
WHERE
a.zipcode = '02134' AND # <-- Your starting zipcode
a.zipcode = c.zipcode AND
3963.0 * acos(
sin(a.latitude/57.2958) * sin(b.latitude/57.2958) +
cos(a.latitude/57.2958) * cos(b.latitude/57.2958) * cos(b.longitude/57.2958 - a.longitude/57.2958)
) <= 5 # <-- Your target radius
GROUP BY distance
They both achieve similar results; anyone have a feel for which is "better"?
____________________________________________________________ Eamon Daly
----- Original Message ----- From: "Eamon Daly" <[EMAIL PROTECTED]>
To: "Scott Haneda" <[EMAIL PROTECTED]>; "MySql" <mysql@lists.mysql.com>
Sent: Tuesday, April 19, 2005 10:20 AM
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.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]