Difference between revisions of "Populating Municipality and County Pages Technical Information"

Revision as of 19:04, 7 August 2015

General

This page documents efforts to solve the problem of missing county and municipality information for wells. The general outline of the procedure to be taken is as follows:

Map from latitude and longitude to 15-digit block FIPS code and county name (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) using the API found at https://www.fcc.gov/developers/census-block-conversions-api
Map from 15-digit FIPS code to municipality FIPS code and county FIPS code using the 2010 Census Block Assignment Files (https://www.census.gov/geo/maps-data/data/baf.html).
Map from the combination of municipality FIPS code and state-county FIPS code to municipality name using the municipality table already in our database.
Update wells' municipality names, municipality FIPS codes, state-county FIPS codes, and county names based on the wells' APIs.

This procedure will be performed on a state-by-state basis by the python script scrape_fips.py in ~/installers/wikimanager/.

scrape_fips.py

The execution of this script will update the well table with missing county and municipality information (municipality name, municipality FIPS code, state-county FIPS code, and county name). This script will perform all updates for one specified state.

Usage: python scrape_fips.py fips_state_code where fips_state_code is a 2-digit code such as 08 for Colorado.

fips_codes Table

The table fips_codes was created to map 15-digit block FIPS codes to 5-digit place (municipality) FIPS codes and 3-digit county FIPS codes. The table has the following structure:

BLOCK_ID_FIPS char(15)

COUNTY_FIPS char(3)

PLACE_FIPS char(5)

Index: BLOCK_ID_FIPS

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

Files imported:

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

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.

@@ Line 17: / Line 17: @@
 == fips_codes Table ==
-The table tips_codes was created to map 15-digit block FIPS codes to 5-digt place (municipality) FIPS codes and 3-digit county FIPS codes.
+The table fips_codes was created to map 15-digit block FIPS codes to 5-digit place (municipality) FIPS codes and 3-digit county FIPS codes.  The table has the following structure:
-The data was loaded in five portions, one for each state in the current update:
+BLOCK_ID_FIPS char(15)
+COUNTY_FIPS char(3)
+PLACE_FIPS char(5)
+Index: BLOCK_ID_FIPS
+The data was loaded in five portions, one for each state in the current update.
+''Files imported:''
-Files:
 *block_place_county_co.csv
 *block_place_county_ny.csv
@@ Line 30: / Line 40: @@
 ''Importing:''
 LOAD DATA INFILE 'block_place_county_co.csv'
@@ Line 43: / Line 52: @@
 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),
@@ Line 69: / Line 76: @@
 * 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
 == gaz_place_national Table ==

Track changes made to this page