List: General Discussion « Previous MessageNext Message » From: Eamon Daly Date: April 19 2005 3:20pm Subject: Re: zip code search within x miles View as plain text
```I don't think anyone's replied with an actual great circle
calculation. Here's our zipcode table, populated with data
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)
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

____________________________________________________________
Eamon Daly

----- Original Message -----
From: "Scott Haneda" <lists@stripped>
To: "MySql" <mysql@stripped>
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?

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