суббота, 20 декабря 2008 г.

Making Google Map based zip code locator script

At first we need a reliable source of US-postcodes...
After some search I found a bit outdated usps database
provided by U.S. Census Bureau
http://www.census.gov/geo/www/tiger/zip1999.html

Data is provided in dbf format. Now let's convert it into
useable mysql format using this software:

DBF2MySQL converter:
http://server.bo-bo.si/index.php?call=2.1

Although this program is not much user-friendly it does good.
You just need to adjust settings in config.php file and create a directory
with the dbf file inside.

So we have a mysql table with the following format:

CREATE TABLE `us_postcode` (
`Zip_code` varchar(5) NOT NULL default '',
`Latitude` varchar(11) NOT NULL default '',
`Longitude` varchar(11) NOT NULL default '',
`Zip_class` char(1) NOT NULL default '',
`Poname` varchar(28) NOT NULL default '',
`State` char(2) NOT NULL default '',
`County` char(3) NOT NULL default ''
) ENGINE=MyISAM


Now let's work with this program:
http://www.sebflipper.com/googlemaps_demo/

Distributed under the terms of the General Public License (GPL)
It searches locations based on postcode and displays them on google map.
Apparently it can be taught to search our US postcode database.

Now we need to calculate distance between two points using mysql

http://www.douglaskarr.com/2007/09/15/calculate-distance/ shows nice example of such calculation:

$qry = "SELECT *,(((acos(sin((".$latitude."*pi()/180)) * sin((`Latitude`*pi()/180))+cos((".$latitude."*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((".$longitude."- `Longitude`)*pi()/180))))*180/pi())*60*1.1515) as distance FROM `MyTable` WHERE distance <= ".$distance."

For Kilometers:

$qry = "SELECT *,(((acos(sin((".$latitude."*pi()/180)) * sin((`Latitude`*pi()/180))+cos((".$latitude."*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((".$longitude."- `Longitude`)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM `MyTable` WHERE distance <= ".$distance."

Комментариев нет: