Updating the Database of Cities, States, and ZIP Codes

When customers from the US purchase services from the online store, the information about their state and city is prefilled automatically, based on the ZIP code they specify.

The information about the correspondence of cities, states, and ZIP codes is kept in a database at the management node. It is updated automatically with CloudBlue Commerce.

If you want to update the database manually, do the following:

1. Download the ZIP archive from http://download.geonames.org/export/zip/US.zip and extract its contents on the management node.

2. Run the following script in the console to make an SQLite database from the archive.

   	cat <<EOF > import.sql
   
   	CREATE TABLE zip_lookup(
   
   	country_code char(2),
   
   	postal_code varchar(20),
   
   	place_name varchar(180),
   
   	admin_name1 varchar(100),
   
   	admin_code1 varchar(20),
   
   	admin_name2 varchar(100),
   
   	admin_code2 varchar(20),
   
   	admin_name3 varchar(100),
   
   	admin_code3 varchar(20),
   
   	latitude varchar(100),
   
   	longitude varchar(100),
   
   	accuracy varchar(100)
   
   	);
   
   	CREATE INDEX zip_lookup_idx0 ON zip_lookup (country_code, postal_code);
   
   	.separator "\t"
   
   	.import US.txt zip_lookup
   
   	EOF

3. Run the following command to import data to the database:

   	sqlite3 zip-codes.db < import.sql

4. Back up the existing database:

   	cp /usr/share/zip-codes/zip-codes.db /usr/share/zip-codes/zip-codes-backup.db

5. Update the database:

   	mv zip-codes.db /usr/share/zip-codes/zip-codes.db