From: Eamon Daly (edalynextwavemedia.com)
Date: Tue Apr 19 2005 - 10:39:56 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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" <edalynextwavemedia.com>
To: "Scott Haneda" <listsnewgeo.com>; "MySql" <mysqllists.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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]