US Zip Code CSV File | Create Table SQL

US Zip Code CSV Database

This database has all the (approx) 40,000 zip codes. 10 of the 11 columns contain NOT NULL data so the database is clean and of high quality.

The 3 Steps | How to setup the zip codes

To setup the US zip codes in a relational database (this example uses MySQL) you must

  1. Download the Zip Codes text file.
  2. Create a database table with a SQL Statement
  3. Import the CSV data using mysqlimport

1. Download the Zip Codes Database

Download the zip codes in CSV text format.

Download the zip codes in Microsoft Excel CSV format.

2. Create Table SQL Statement | Run It

How to create the zip code table in a MySQL database.
Log into the MySQL console and run this create table SQL.

create table bbW_zip_codes(
  zip_code                  VARCHAR(5)         NOT NULL,
  zip_code_type             VARCHAR(16)        NOT NULL,
  zip_code_primary_city     VARCHAR(255)       NOT NULL,
  zip_code_state            VARCHAR(2)         NOT NULL,
  zip_code_county           VARCHAR(64)                ,
  zip_code_lattitude        DOUBLE             NOT NULL,
  zip_code_longitude        DOUBLE             NOT NULL,
  zip_code_world_region     VARCHAR(64)        NOT NULL,
  zip_code_country_code     VARCHAR(2)         NOT NULL,
  zip_code_active_flag      SMALLINT UNSIGNED  NOT NULL,
  zip_code_population       MEDIUMINT UNSIGNED NOT NULL,
  PRIMARY KEY ( zip_code )
);

Remember to check the table has been setup correctly. These commands do that.
SHOW CREATE TABLE bbW_zip_codes;
describe bbW_zip_codes;

3. How to Import the ZipCode Data

After creating the table, import the zipcode data in CSV format using the mysqlimport statement.

mysqlimport --ignore-lines=1 --fields-terminated-by=, --local -u root -p <<database_name>> bbW_zip_codes.csv

The first line is passed over because it holds the column names. The other 42,000 zip codes are loaded into your table ready to go.

Verify Zip Code Data Import

Did the zip codes load correctly? Use these SQL commands to explore your new zip code mini universe.

mysql> select count(*) from bbW_zip_codes;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from bbW_zip_codes;
+----------+
| count(*) |
+----------+
|    42521 |
+----------+
1 row in set (0.04 sec)

mysql> select zip_code_world_region,count(*) from bbW_zip_codes group by zip_code_world_region;

+-----------------------+----------+
| zip_code_world_region | count(*) |
+-----------------------+----------+
| AF                    |        7 |
| AS                    |       81 |
| AU                    |        6 |
| CA                    |        8 |
| EU                    |      146 |
| ME                    |       80 |
| NA                    |    42188 |
| SA                    |        4 |
| WW                    |        1 |
+-----------------------+----------+

Addendum – Create Table With Column Comments

This version of the Create Table command contains comments against each of the 11 columns.

create table bbW_zip_codes(
  zip_code                  VARCHAR(5)         NOT NULL COMMENT 'The 5 digit US zip code',
  zip_code_type             VARCHAR(16)        NOT NULL COMMENT 'ZIP code types are STANDARD MILITARY PO_BOX UNIQUE',
  zip_code_primary_city     VARCHAR(255)       NOT NULL COMMENT 'The biggest city in terms of geography or population',
  zip_code_state            VARCHAR(2)         NOT NULL COMMENT 'The US state 2 character abbreviation',
  zip_code_county           VARCHAR(64)                 COMMENT 'The county within the US state',
  zip_code_lattitude        DOUBLE             NOT NULL COMMENT 'The lattitude running through a geographical zip code',
  zip_code_longitude        DOUBLE             NOT NULL COMMENT 'The longitude running through a geographical zip code',
  zip_code_world_region     VARCHAR(64)        NOT NULL COMMENT 'The region associated with the zip code',
  zip_code_country_code     VARCHAR(2)         NOT NULL COMMENT 'The Alpha 2 country code',
  zip_code_active_flag      SMALLINT UNSIGNED  NOT NULL COMMENT '1 for active and 0 for decommissioned zip codes',
  zip_code_population       MEDIUMINT UNSIGNED NOT NULL COMMENT 'The population estimate for the zip code area',
  PRIMARY KEY ( zip_code )
);

Leave a Reply

Your email address will not be published. Required fields are marked *