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.

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

School Recess + Adults = ?

I recently presented research on how adults can influence school recess.

This year the North American Society for the Psychology of Sport and Exercise and Physical Activity (NASPSPA) hosted a virtual conference. Instead of the traditional poster presentation, my lab created a short 4 minute video, which was uploaded to YouTube.

Find the YouTube video below:

School Recess + Adults = ?

Often, when we think of recess, we think it’s all fun and games — but, actually, a lot of bullying and teasing can happen during recess too. So, the purpose of our study was to check out problems during school recess and try to figure out why these problems happen.

In order to do this, our lab went to 25 different schools throughout the USA and observed 112 recess periods. We used a guide (called The Great Recess Framework-Observational Tool) to help us observe recess. While we were out on the playground during recess, we also wrote down some notes to help us remember what we were noticing. We jotted down notes about (1) safety, (2) student behaviors, (3) adult behaviors, (4) physical activity, and (5) what happens right before and after the recess bell rings.

After we were all done going to schools, our lab re-read all the notes. We tried to notice any patterns from our notes.

We noticed a few patterns. First, we noticed problems with the schoolyard itself — like holes in a chain link fence, lots of trash, or broken rusty play sets. Problems with the schoolyard seem connected to safety problems; kids could easily run into a busy street or trip over hidden holes in the grass.

Second, we noticed that there were often too many kids in a small schoolyards with not enough things to play with (like jump ropes or balls). When this happened, it seemed like the same kids were usually left out and excluded from playing during recess. We noticed that older boys usually got to play, but others couldn’t. For example, 6th grades boy would play soccer and 5th grade boys would play basketball, but other kids could only just walk around or watch.

We might think that children can handle recess all by themselves, but adults can be a part of recess too. They can help make sure things go well for all the kids. For example, the principal or vice principal can walk around the school yard before recess starts to make sure there are no holes in the fence or rusty, broken play equipment. Teachers or yard monitors can make sure the balls are being shared with everyone — not just the oldest students. If a kid is being bullied or teased, adults can step in and stop this. Even better, adults can play and hang out with kids during recess. For example, teachers can pitch the ball in kickball, be goalie in soccer, or shoot some hoops in basketball.

School Recess + Helpful Nice Adults = Fun Safe Recess for More Kids!

OSU Research Team: Deanna Perez, Janelle Thalken, Alexandra Szarabajko, Laura Neilson, & William V. Massey

Kinesiology Program | School of Biological and Population Health Sciences | College of Public Health and Human Sciences

Small thoughts about research

I am learning the foundations of qualitative and mixed-methods research.

Ethics always guide my decision making. I recognize how research is embedded in culture. I aim for my research to include marginalized groups.

My end goal is not to simply create knowledge for knowledge’s sake. Rather, I hope that the knowledge I can help co-create will be applied in real word settings.

Through my research, I hope to effect positive change — especially for people of color and those from low income neighborhoods.