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:

1. 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
2. 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).
3. Map from the combination of municipality FIPS code and state-county FIPS code to municipality name using the municipality table already in our database.
4. 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/.

Update Counts

CO:

• 1530 fips_state_county values filled
• 6,549 FIPS_MUNICIPALITY values filled
• 5,225 WELL_MUNICIPALITY values filled

WV:

• 7 FIPS_MUNICIPALITY values filled
• 2,383 WELL_MUNICIPALITY values filled

PA:

• 598 FIPS_MUNICIPALITY values filled

NY:

• 13 fips_state_county values filled
• 969 FIPS_MUNICIPALITY values filled
• 12 WELL_COUNTY values filled
• 5 WELL_MUNICIPALITY values filled

OH:

• 38 fips_state_county values filled
• 17,527 FIPS_MUNICIPALITY values filled
• 5702 WELL_COUNTY values filled
• 15,658 WELL_MUNICIPALITY values filled

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.