zipcodes-ss

MySql City, State, Zip, Latitude, Longitude Database

Just recently I needed a mysql database table that contained records for city, state, zip, latitude, and longitude coordinates. I needed a complete list of locations since one of my projects involved an ajax location auto populating script. While browsing on the web to find a free solution, I finally found a complete list of US zip codes from 2006 that contained everything that I wanted. I downloaded the files and realized it was written in Perl by R. Don Henning. I rewrote it in PHP for developers looking for a PHP solution.

Simply Download the files and place the directory wherever you would like. Make sure to edit the zip_insert.php file and change the database connection with yours.

Version 3 Updates:

Replaced file_get_contents with file so we return an array of each line. Replaced for loop with foreach loop. Removed the split function that is deprecated as of PHP 5.3.

Version 2 Updates:

There was an issue with leading zeros not being inserted into the database. I have added zerofill and changed INT(5). This is fixed now!

Download PHP MySql Locations Database Version: 3 Size: (617.3 kB)

8 thoughts on “MySql City, State, Zip, Latitude, Longitude Database

  1. dalitec

    Jesse

    Thank you so much for sharing this. I tested it on a web server and it worked like a charm. Just a note. Becuase the zip code is set up as an integer it deletes all the prefix zeros of the zip code. So zipcode 00871 will only show up as 871 in the zipcode column. I am thinking about setting the zipcode field as char or varchar and change the table creation as needed, modify the primary key and load the data. I have not done that yet. If I get that working, I will share my change here.

    Dal

    1. Jesse Price Post author

      Sorry for not using explode and using split… Also file vs file_get_contents. I wrote this in my early days of php3 programming where the explode function did not exist. Also file returns an array and file_get_contents returns string so I figured that using file_get_contents then using a string manipulating function would suffice… I should update the code since split is now deprecated as of php 5.3…

  2. Sanket Patel

    Hi Jesse,

    Nice work. Keep it up.
    If you are not addicted to PHP (Joking) then you can use this MySQL solution given below (Not joking) to upload data given in zipcode database.
    —————————————————————————–
    CREATE TABLE geoInfo(zipcode INT PRIMARY KEY,latitude VARCHAR(15),longitude VARCHAR(15),stateCode VARCHAR(10),city VARCHAR(20),county VARCHAR(20));

    LOAD DATA LOCAL INFILE ‘/home/sanket/Desktop/zipcodes.txt’ INTO TABLE geoInfo FIELDS TERMINATED BY ‘||’ LINES TERMINATED BY ‘\n’;

    —————————————————————————–

  3. Ron Jones

    Have a look at http://worldcitiesdatabase.info

    They offer a great database of world cities for less than five bucks. Unlike the free databases containing millions of entries, this one only has about 70 thousand entries, which makes it ideal for city selector scripts. Otherwise, it takes forever to populate the selectors.

Comments are closed.