List:General Discussion« Previous MessageNext Message »
From:Scott Johnson Date:April 19 2005 3:42pm
Subject:RE: zip code search within x miles
View as plain text  
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:edaly@stripped]
> 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" <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?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>

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