Month: August 2020

Posted on

Geocode into US Census Geographies

Source: https://github.com/ilyankou/geocoder-for-google-sheets

If you have a spreadsheet of addresses, you may want to extrapolate relevant US 2010 Census information.

I had ~475 addresses and was not sure about the best way go about it.

Luckily, I stumbled upon a really helpful website created by Jack Dougherty and Ilya Ilyankou. In it, they talk about how to bulk Geocode your addresses using data from the 2010 US Census. Ilya’s GitHub page also houses this information.

Essentially, they wrote a script for GoogleSheets. The script can geocode your US addresses into latitude, longitude, GeoID, and census tract – so helpful!

If you would like to use this feature, you need to first make a copy of their Google Sheet template. Go to ‘File’ > ‘Make a Copy to your Google Drive.’

Second, you will copy and paste your US addresses into column A.

Third, you will select columns A-H and select the Geocoder menu: US Census 2010 Geographies.

Next, the script may ask for your permission to run. Wait a bit for the script to run; this could take a while depending on how many addresses you have. Then….

Voilà! Now, your spreadsheet has addresses in column A, and GeoID and Census tract in columns G and H.

P.S. The other option is run a batch of addresses through the official US Census Geocoder website. That website can be found here. You do need to clean up your data first. The website usually worked for me, but sometimes did not.

Posted on

Reverse GeoCode Google Sheet

Converting the address to latitude and longitude is known as geocoding. Whereas, converting the latitude and longitude to an address is called reverse geocoding.

Because I am probably not the only person who would like to reverse geocode a dataset, I thought it would be helpful to share my own reverse geocoding process.

Ideally, the dataset would first be in a csv file– with latitude was in one column and longitude in another.

I came across a script that could be used with Google Sheets. It’s on Stack Overflow and called “Get City, State, Country from Latitude and Longitude in Google Sheets. Per the instructions provided by Stack Overflow user Gabriel Rotman, I created a Google Sheet template.

Here is a link to an open, public Google Sheet Template that I created. Feel free to make a copy of it, and then edit it to fit your own needs!

This Google Sheet will provide the address when given the latitude and longitude. The following is the formula: “=reverse_geocode(A1,B1).”

Otherwise, you can copy and paste the script yourself into the ‘<> Script editor’ portion of your Google Sheet (under ‘Tools’). Script is below:

function reverse_geocode(lat,lng) {
Utilities.sleep(1500);

var response = Maps.newGeocoder().reverseGeocode(lat,lng);
for (var i = 0; i < response.results.length; i++) {
var result = response.results[i];
Logger.log('%s: %s, %s', result.formatted_address, result.geometry.location.lat,
result.geometry.location.lng);
return result.formatted_address;
}
}

Again, all credit for the script goes to Stack Overflow user Gabriel Rotman — much thanks to Gabriel!

Cheers!

Japanese Tea
Japanese Tea