[PHP] Postal / Zip Code Proximity Search
Hello, 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. What I have is a database of establishments and their corresponding latitudes and longitudes, and I need to have users enter their own postal / zip code, and have the system calculate which establishments fall into a set range ($range) from their location. Not only does it need to find the establishments, it needs to calculate the distances (straight line, of course). Now, I have dug through the PHP archives, but there is no single clear explanation of how to do this, from beginning to end. I will start by putting down what I need, and then maybe other people can contribute to this and in the end we will have the solution, nice and tidy, in one place. *** NOTE: I know doing this in PHP will be SLOW, but I do not want to write a MySQL user-defined function and recompile it and blah, blah, blah... (unless someone wants to cover that aspect!!! :D) Step 1: Take the postal / zip code that the user entered, and search the national postal / zip code database (which I already have) and return its corresponding latitude and longitude ($userLat and $userLong). Step 2: ??? Step 3: Return the establishments information to the user, complete with the distances to them, ordered by smallest distance to largest. If you can't help me out, maybe you can direct me to some online tutorials on this topic! Thanks in Advance! -- -- Brandon Pearcy Internet Technician Bowes Online -- phone: 1-780-532-1110 ext. 265 fax: 1-780-532-2120 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Postal / Zip Code Proximity Search
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. I would really suggest using postgres for your calculations. You can pass the lat and long and have postgres give you the intersection of these and reference a table that has the zip codes B i g D o g - Original Message - From: Brandon Pearcy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 04, 2002 9:33 AM Subject: [PHP] Postal / Zip Code Proximity Search Hello, 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. What I have is a database of establishments and their corresponding latitudes and longitudes, and I need to have users enter their own postal / zip code, and have the system calculate which establishments fall into a set range ($range) from their location. Not only does it need to find the establishments, it needs to calculate the distances (straight line, of course). Now, I have dug through the PHP archives, but there is no single clear explanation of how to do this, from beginning to end. I will start by putting down what I need, and then maybe other people can contribute to this and in the end we will have the solution, nice and tidy, in one place. *** NOTE: I know doing this in PHP will be SLOW, but I do not want to write a MySQL user-defined function and recompile it and blah, blah, blah... (unless someone wants to cover that aspect!!! :D) Step 1: Take the postal / zip code that the user entered, and search the national postal / zip code database (which I already have) and return its corresponding latitude and longitude ($userLat and $userLong). Step 2: ??? Step 3: Return the establishments information to the user, complete with the distances to them, ordered by smallest distance to largest. If you can't help me out, maybe you can direct me to some online tutorials on this topic! Thanks in Advance! -- -- Brandon Pearcy Internet Technician Bowes Online -- phone: 1-780-532-1110 ext. 265 fax: 1-780-532-2120 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Postal / Zip Code Proximity Search
http://www.totalscripts.com/pages/Perl_Scripts/Calculator/ GeoZip... only for US postcodes tho.. but is ur US fine ;) -Original Message- From: Brandon Pearcy [mailto:[EMAIL PROTECTED]] Sent: 04 July 2002 4:34 PM To: [EMAIL PROTECTED] Subject: [PHP] Postal / Zip Code Proximity Search Hello, 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. What I have is a database of establishments and their corresponding latitudes and longitudes, and I need to have users enter their own postal / zip code, and have the system calculate which establishments fall into a set range ($range) from their location. Not only does it need to find the establishments, it needs to calculate the distances (straight line, of course). Now, I have dug through the PHP archives, but there is no single clear explanation of how to do this, from beginning to end. I will start by putting down what I need, and then maybe other people can contribute to this and in the end we will have the solution, nice and tidy, in one place. *** NOTE: I know doing this in PHP will be SLOW, but I do not want to write a MySQL user-defined function and recompile it and blah, blah, blah... (unless someone wants to cover that aspect!!! :D) Step 1: Take the postal / zip code that the user entered, and search the national postal / zip code database (which I already have) and return its corresponding latitude and longitude ($userLat and $userLong). Step 2: ??? Step 3: Return the establishments information to the user, complete with the distances to them, ordered by smallest distance to largest. If you can't help me out, maybe you can direct me to some online tutorials on this topic! Thanks in Advance! -- -- Brandon Pearcy Internet Technician Bowes Online -- phone: 1-780-532-1110 ext. 265 fax: 1-780-532-2120 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Postal / Zip Code Proximity Search
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 WHEREz.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
RE: [PHP] Postal / Zip Code Proximity Search
-Original Message-
From: Peter J. Schoenster [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 09, 2002 1:26 PM
To: [EMAIL PROTECTED]
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
WHEREz.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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Postal / Zip Code Proximity Search
On Tue, 9 Jul 2002, Peter J. Schoenster wrote: From: Brandon Pearcy [EMAIL PROTECTED] 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. I assume by straight line he means disregarding the curvature of the earth, which at such small distances would be insignificant but add a lot of calculation. miguel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Postal / Zip Code Proximity Search
I found the points file here: ftp://ftp.census.gov/pub/tiger/pts/geoex.zip Sorry, I was wrong here. Those aren't the right files, and it's hard to find the correct ones. Should I ever do that, I'll post a correct link. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
