Populating Municipality and County Pages Technical Information

gaz_place_national Table

The table gaz_place_national was created. This table contains place (municipality) names for place (municipality) FIPS code. The table is currently unused since the table municipality also contains this information, but may contain newer information or additional information to what our database already contains that we will find useful later on. gaz_place_national has the following structure:

STATE_CODE char(2) - United States Postal Service State Abbreviation

FIPS_STATE_CODE char(2)

FIPS_PLACE_CODE char(5)

ANSI_CODE char(8) - American National Standards Insititute code

MUNICIPALITY_NAME varchar(50)

MUNICIPALITY_LSAD varchar(20) - Legal/Statistical area descriptor

MUNICIPALITY_NAME_LONG varchar(150)

LATITUDE_DECIMAL double

LONGITUDE_DECIMAL double

Importing:

LOAD DATA INFILE '2013_Gaz_place_national.csv'

INTO TABLE gaz_place_national

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS

(STATE_CODE, @DUMMY, @DUMMY, @DUMMY, ANSI_CODE, MUNICIPALITY_NAME_LONG, LATITUDE_DECIMAL, LONGITUDE_DECIMAL, MUNICIPALITY_NAME, MUNICIPALITY_LSAD, FIPS_STATE_CODE, FIPS_PLACE_CODE)

29510 rows inserted.

The source of this data was the 2013 U.S. Gazetteer Files, found at http://www.census.gov/geo/maps-data/data/gazetteer2013.html.

fips_codes Table

The table tips_codes was created to map 15-digit block FIPS codes (a concatenation the 2-character state FIPS code, the 3-character county FIPS code, the 6-character census tract code, and the 4-character tabulation block code) to 5-digt place (municipality) FIPS codes and 3-digit county FIPS codes.

The data was loaded in five portions, one for each state in the current update:

Files:

• block_place_county_co.csv
• block_place_county_ny.csv
• block_place_county_oh.csv
• block_place_county_pa.csv
• block_place_county_wv.csv

Importing:

LOAD DATA INFILE 'block_place_county_co.csv'

INTO TABLE fips_codes

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS

(BLOCK_ID_FIPS, @PLACE_FIPS_TMP, @COUNTY_FIPS_TMP)

SET PLACE_FIPS=IF(LENGTH(@PLACE_FIPS_TMP)<2,NULL,@PLACE_FIPS_TMP), COUNTY_FIPS=IF(LENGTH(@COUNTY_FIPS_TMP)<2,NULL,@COUNTY_FIPS_TMP)

CO: 201062 rows inserted.

NY: 350169 rows inserted.

OH: 365344 rows inserted.

PA: 421545 rows inserted.

WV: 135218 rows inserted.

For a total of 1473338 rows inserted.

The source of the data was https://www.census.gov/geo/maps-data/data/baf.html

• provinces of interest were selected from the dropdown menu
• block, place, and county code FIPS data for each state were extracted from the BlockAssign_ST08_*_INCPLACE_CDP.txt and BlockAssign_ST08_*_VTD.txt files and added to a new CSV
• zero padding was added in Excel so that BLOCK_ID_FIPS, PLACE_FIPS, and COUNTY_FIPS had lengths of 15, 5, and 3 respectively

Indexes were added as follows in order to speed up the script's queries:

• FIPS_STATE in well
• BLOCK_ID_FIPS in fips_codes
• the pair (FIPS_STATE_COUNTY, FIPS_MUNICIPALITY) in municipality