If you have a list of addresses that you need to place into census tracts for your Fixed Broadband Subscription filing, or if you need to know the coordinates of addresses for your Fixed Broadband Availability filing, the Census batch geocoder can help.
The Census Bureau has a free address-matching (geocoding) tool that can provide the coordinates as well as the census geographies (state, county, tract and block) associated with addresses. The tool can be used for a single address or up to 10,000 addresses in batch mode, and can be found at http://geocoding.geo.census.gov/geocoder/.
1. Create an Address File
Start with a plain-text address file that’s in good shape – i.e., all the addresses are correct, in USPS format, with the following fields separated by commas: (1) row number (or other unique identifier), (2) street address (no punctuation), (3) city, (4) state, (5) ZIP. The geocoder will parse each row of your file into five parts based on the placement of the commas, so be sure that each row has only four commas total and that they are correctly placed. In a plain text editor, like NotePad, the file should look like this:
2. Geocode the Addresses
Go to http://geocoding.geo.census.gov/geocoder/geographies/addressbatch?form (or go to http://geocoding.geo.census.gov/geocoder/, then from the left menu click Batch Address Processing under Find Geographies, browse for your address file, then set the Benchmark and Vintage parameters as follows:
When you click Get Results, the geocoder will generate a CSV (comma-separated values) file (called GeocodeResults.csv) and open a dialog box that allows you to open or save the file. If you open the file in a plain-text editor like NotePad, it will look like this:
3. Open the Output File in a Spreadsheet Program
Since the output CSV file is not terribly helpful in the format above, the easiest way to deal with it is to open it in Excel or another spreadsheet or database program that will place the comma-separated values into columns and allow you to concatenate the values in the 3 or 4 right-most columns into census tract or block codes using the guidance below. Exercise caution here. If you simply double-click the file to open it in Excel, for instance, Excel will make assumptions about the structure of the file that aren’t always accurate. It’s important to use these steps to import the data to Excel:
- Open Excel with a blank sheet (we’re working in Microsoft® Excel® for Microsoft 365).
- Click Data on the ribbon.
- Click From Text/CSV
- Navigate to the geocoder’s output CSV file on your file system, select it, then click Import
- This will open the Import Wizard, the first step of which will appear like this:
- Click Transform Data. This will pop up the Power Query Editor window as shown below.
- Select Column9, Column10, Column11 & Column12 by first clicking on the word “Column9” then while depressing the shift key click on the words “Column10”, “Column11” and “Column12”. The columns will be shaded green after selected.
- Click Data Type: Whole Number…Text…Replace Current. This will ensure that Excel imports the geographic codes as text rather than numbers. This will make life easier later. Now click Close & Load.
- Click Convert to Range…OK.
- Now you can work with your data in Excel.
4. What can I do with these data?
Here’s what we have:
One thing you can now do is create a census tract code for each address. To generate the complete 11-digit census tract code:
- In cell M1, type in the following: =I1&J1&K1. This will concatenate the 3 columns and give you the complete tract code in column M. After you copy this formula down the rows of the sheet, you should see something like this:
The complete tract code, in column M above, will always be 11 digits, because the state code (column I) will always be 2 digits, the county code (column J) will always be 3 digits and the tract within the county (column K) will always be 6 digits.
Also, column F gives the longitude and latitude coordinates of the address (x,y), respectively, which might be handy if you need to map your customer location points to create your Fixed Broadband Availability data, or to compare the locations you can serve with the locations in the Location Fabric.
- Matching. The quality of the match can take on a few different values…
- Match, exact – the geocoder found an exact match for your address
- Match, non-exact – the geocoder found a match, but it’s not precisely the same as the address you entered. These need a second look to confirm the match.
- Tie – there are multiple possible results and further investigation is necessary
- No Match – check your address data for problems
- Data quality. Success depends heavily on the quality of the address data on your list. The geocoder is actually somewhat smart and can work around some issues, but if street or city names are badly misspelled or if other information is incorrect, the tool may not find a match for your address. Further, the geocoder might even match your data incorrectly. Garbage in, garbage out.
- PO Boxes and other virtual addresses. A PO Box is generally a place to which mail is delivered rather than the location of a home or business. Expect PO Box addresses in your data to be returned unmatched.
- Please see Census’s documentation (https://www.census.gov/programs-surveys/geography/technical-documentation/complete-technical-documentation/census-geocoder.html) and user guide (https://www2.census.gov/geo/pdfs/maps-data/data/Census_Geocoder_User_Guide.pdf) for more information.