RE: [PHP] Postal / Zip Code Proximity Search

> -----Original Message----- > From: Peter J. Schoenster [mailto:peter@schoenster.com] > Sent: Tuesday, July 09, 2002 1:26 PM > To: php-general@lists.php.net > Subject: Re: [PHP] Postal / Zip Code Proximity Search > > > I don't know what you mean by straight line. AFAIK all of > this will be as "the crow flies". > > The following came from Jann Linder of cgi-list fame and it > worked for me. Odd, I used it in PostgreSQL not knowing that > there was something homegrown. > > SELECT /*+FIRST_ROWS */ > o.zip, > (3956 * (2 * ASIN(SQRT( > POWER(SIN(((z.latitude-o.latitude)*0.017453293)/2),2) + > COS(z.latitude*0.017453293) * > COS(o.latitude*0.017453293) * > POWER(SIN(((z.longitude-o.longitude)*0.017453293)/2),2) > )))) dist > FROM zipcodes z, > zipcodes o > WHERE z.zip=94112 > AND (3956 * (2 * ASIN(SQRT( > POWER(SIN(((z.latitude-o.latitude)*0.017453293)/2),2) + > COS(z.latitude*0.017453293) * > COS(o.latitude*0.017453293) * > POWER(SIN(((z.longitude-o.longitude)*0.017453293)/2),2) > )))) < 5 order by dist; > > > CREATE TABLE zipcodes ( > recordid int(11) unsigned NOT NULL auto_increment, > zip varchar(5) NOT NULL default '', > state char(2) NOT NULL default '', > city varchar(50) NOT NULL default '', > longitude double NOT NULL default '0', > latitude double NOT NULL default '0', > sure tinyint(3) unsigned NOT NULL default '0', > PRIMARY KEY (recordid), > KEY idx_zip(zip), > KEY idx_state(state), > KEY idx_city(city), > KEY idx_latitude(latitude), > KEY idx_longitude(longitude), > KEY idx_sure(sure) > ) TYPE=MyISAM; > > > > More stuff about this here: > > http://mathforum.org/library/drmath/view/51711.html > http://www.movable-type.co.uk/scripts/LatLong.html > http://earth.uni-muenster.de/~eicksch/GMT-Help/msg00147.html I found the points file here: ftp://ftp.census.gov/pub/tiger/pts/geoex.zip