Alex Colson - Using ZIP Codes in Web Applications

alexcolson.com

Using ZIP Codes in Web Applications
Home > Tech Talk > Article

Modified: Saturday, March 8, 2008 6:09:29 PM by Alex Colson

[Edit Tags]

Tags: MySQL, SQL, Web Development, ZIP Codes

Using ZIP Codes in a web application allows users to do many things, including:

Finding all cities and states within a certain range of a specific location
Locating the businesses and stores closest to their location
Calculating the distance between two locations

The easiest way to accomplish these tasks is by using Longitude and Latitude to find distances between ZIP Codes and cities. To do this, you need to acquire a database of ZIP Codes, Longitudes, and Latitudes. There are databases for sale from various companies that are updated often or you can download a free ZIP Code database (Zipped CSV, 668kb) from alexcolson.com.

Assemble the database as follows:

zip, char(5), ex: 10108
latitude, varchar(12), ex: +40.780751
longitude, varchar(12), ex: -073.977182
city, varchar(255), ex: New York
state, char(2), ex: NY
county, varchar(255), ex: New York
type, varchar(50), ex: Standard

Once you have a database full of ZIP Codes and other data, we can start working with SQL queries to find cities, locations, distances, and more.

REMEMBER:
When allowing users to input data, such as a City or ZIP Code, you should use MySQL Prep to prevent SQL Injection.

Finding the first ZIP Code based on a City and State being entered:

select zip from zipcodes where city like '%$city%' and state = '$state' limit 1;

Where $city is input as a city name and $state is input as state initials.

Finding Longitude and Latitude of a ZIP Code:

select longitude, latitude from zipcodes where zip = '$zip';

Where $zip is input as a ZIP Code.

Finding cities within a range of a Longitude and Latitude:

select city, state, (((acos(sin(($lat*pi()/180)) * sin((latitude*pi()/180)) + cos(($lat*pi()/180)) * cos((latitude*pi()/180)) * cos((($lng - longitude)*pi()/180))))*180/pi())*60*1.1515) as distance from zipcodes where  distance <= $range order by distance;

Where $lat is a specific latitude, $lng is a specific longitude, and $range is a range in miles that you want to search.

Finding distance to a ZIP Codes:

select (((acos(sin(($lat*pi()/180)) * sin((latitude*pi()/180)) + cos(($lat*pi()/180)) * cos((latitude*pi()/180)) * cos((($lng - longitude)*pi()/180))))*180/pi())*60*1.1515) as distance from zipcodes where zip = '$zip';

Where $lat is the originating latitude, $lng is the originating longitude, and $zip is the destination ZIP Code.

Sunday, September 5, 2010 10:21:03 AM EST (0.0146 s)