Populating Municipality and County Pages Technical Information

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/.

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 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

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.