On 9 Jul 2002 at 10:39, Ray Hunter wrote:
> One suggestion is to use the geo functionality of Postgres....
>
> We use postgres to calculate city, state, and/or country by using the
> geo functions in postgresql.
> ----- Original Message -----
> From: "Brandon Pearcy" <[EMAIL PROTECTED]>
> > I have a couple of questions with respect to creating a postal /
> > zip code proximity search that is remotely accurate. The system I am
> > using now is OK for small distances, but is terrible at calculating
> > large distances.
> > Not only does it need to find the establishments, it needs to
> > calculate the distances (straight line, of course).
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
Peter
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
