Populating the Regions, Divisions and States MySQL Tables

Adding the States and Their Data

Adding the states is trickier, because I refuse to hand-write 50 lines of MySQL, I’ll write a PHP script to pull apart a CSV file and insert the data into the database as we designed earlier.

MySQL

## us_states Table
#-----------------------------------------------------------------------
#| id | name | divisionid | population1990 | areaimperial | areametric |
#|    |      |            |                |              |            |
#-----------------------------------------------------------------------

CREATE TABLE `db3420_dev`.`us_states` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL ,
`divisionid` INT UNSIGNED NOT NULL ,
`population1990` INT UNSIGNED NOT NULL ,
`areaimperial` INT UNSIGNED NOT NULL COMMENT 'unit: square miles',
`areametric` INT UNSIGNED NOT NULL COMMENT 'unit: square kilometers'
) ENGINE = MYISAM;

Since we are using the 1990 census population data I included that in the name of the field. When it comes to creating the application we’ll use a variable to decide the year so we can add a population2000 or population1700 if we desire and it will be relatively easy to adjust the scripts as well as perform calculations.

And just like the way I setup the populations fields I set up the area for imperial (square miles) and metric (square kilometers) measurements.

The best two resources I could find for this information were:

• Demographia: Area, Population & Density by US State: 1990
• Infoplease: Population by State 1790 – 2005
• Census Regions and Divisions of the United States
• Environmental Protection Agency: State FIPS Code Listing

They provided me with more data than I had originally planned

• 2000 population data
• 1950 population data
• 1900 population data
• State FIPS code
• State Abbreviations

There was more data but it was not relevant for my needs and I figured this would be more than enough data. So I had to alter my table for these additional fields.

MySQL

ALTER TABLE `us_states`
ADD `abbr` varchar(3) NOT NULL AFTER `name`,
ADD `population2000` INT UNSIGNED NOT NULL AFTER `divisionid`,
ADD `population1950` INT UNSIGNED NOT NULL AFTER `population1990`,
ADD `population1900` INT UNSIGNED NOT NULL AFTER `population1950`,
ADD `fipscode` SMALLINT UNSIGNED NOT NULL;

In addition those pages provided more data that I didn’t need, so before I exported the excel doc I copied the tables into I removed extra data. Ranks can be determined by sorting a query by population and percentage can be calculated in a query too. I don’t need that data cluttering my database.

Though there was data I was missing:

• metric measurements
• divisionid (relationship)

I assumed divisionid I might need to add by hand, so I took the time to add it to the CSV by hand, metric measurements I decided I could calculate in the PHP script then verify at random later.

---

Sample Relationship Queries

Now to query out the states in New England ordered by abbreviation you could do this:

MySQL

SELECT b.name, b.abbr FROM `us_divisions` as a LEFT JOIN `us_states` as b on a.id = b.divisionid WHERE a.id = 1 ORDER BY b.abbr;
## or
SELECT name, abbr  FROM `us_states` WHERE divisionid = 1 ORDER BY abbr;

## Results:
------------------------
| name          | abbr |
------------------------
| Connecticut   | CT   |
| Massachusetts | MA   |
| Maine         | ME   |
| New Hampshire | NH   |
| Rhode Island  | RI   |
| Vermont       | VT   |
------------------------

But to get all the States in the east region you’d have to do a two or three table join.

MySQL

SELECT c.name, c.abbr FROM `us_divisions` as a LEFT JOIN `us_divisions` as b on a.id = b.regionid  LEFT JOIN `us_states` as c on b.id = c.divisionid WHERE a.id = 1 ORDER BY c.abbr;
## or
SELECT c.name, c.abbr  FROM `us_divisions` as b LEFT JOIN `us_states` as c on b.id = c.divisionid WHERE b.regionid = 1 ORDER BY c.abbr;

## Results:
------------------------
| name          | abbr |
------------------------
| Connecticut   | CT   |
| Massachusetts | MA   |
| Maine         | ME   |
| New Hampshire | NH   |
| New Jersey    | NJ   |
| New York      | NY   |
| Pennsylvania  | PA   |
| Rhode Island  | RI   |
| Vermont       | VT   |
------------------------

Ahh, sweet relational databases. I do so <3 them!

Pages: 1 2

Tagged as: data dump, database, division, mysql, query, region, setup, state, usa