Geocoding and Accessing Google Doc using Python Script

May 16, 2012

Churches In the Philippines

I have a list of around 1000+ churches that contains the name of the church, simple address, the residing priest and some contains mass schedules. I thought it will be nice if we can get the latitude and longitude based on the given address.

The way I do it before is to manually search the address in Google Maps and get the latitude/location by centering the marker and executing the javascript below:


Without any prior knowledge about python scripts, I decided to give it a try and automate the process.

The list of churches is stored in a google spreadsheet so I initially thought the best way to access this is to use the Google Data API client for Python. I tried it and it works perfectly but then I found another library called gspread that provides an easier way to access google spreadsheets. Maybe Google Data API is easy as well but the website lacks enough examples necessary for a python newbie like me.

I already have python (2.7.3) installed on my mac so I only need to install gspread library:

git clone
cd gspread
python install

First thing that you need to do is to login to your google account.

gc = gspread.login(username, password)

and then open up the worksheet by title (which I think you cannot do in Google Data API client)

wks ="church").sheet1

Next, we need to iterate for each row and get the location of the church by using Google Maps API’s geocoding service.

Get the number of rows and use that for our for loop

rowcount = wks.row_count
for i in range(startindex, rowcount):
    val = wks.cell(i+1, 1).value

I use startindex so that we can easily resume from the last row in case of any application error. wks.cell(i+1, 1) will fetch the name of the church that we can use to call the geocoding service.

url = ""+address+"&sensor=false"
resp = json.load(urllib2.urlopen(url))

We use urllib2 (import urllib2) to perform HTTP Get and json library (import json) to parse the json response. After that, getting the location data is just as easy as

location = resp["results"][0]["geometry"]["location"]
locstring = str(location["lat"]) + ", " + str(location["lng"])

and finally update our Google Spreadsheet with the location data

wks.update_cell(i+1, 8, locstring)

I made a few minor changes to the script so that it can accept command line params and also a bit of error handling. You can check out the source below.

import sys
import gspread
import urllib2
import urllib
import json

username = 'your_gmail'
password = 'your_password'

gc = gspread.login(username, password)
wks ="church").sheet1

rowcount = wks.row_count
startindex = 0

if(len(sys.argv) > 1):
    startindex = sys.argv[1]

if(not startindex):
    startindex = 0

print startindex

for i in range(int(startindex), rowcount):
    if(i > 0 ):
        print "processing row " +str(i+1)
        val = wks.cell(i+1,1).value
        curloc = wks.cell(i+1,8).value
        address = urllib.quote(val)

            print "already contains location: skipping"

        url = ""+address+"&sensor=false"
        print "fetching location for: "+url

        process = True
                resp = json.load(urllib2.urlopen(url))
                locstring = ""
                if(len(resp["results"]) > 0):
                    location = resp["results"][0]["geometry"]["location"]
                    print "got location: "+str(location["lat"]) + "," + str(location["lng"])
                    locstring = str(location["lat"]) + ", " + str(location["lng"])

                if(not curloc):
                    print "updating cell with " + locstring
                    wks.update_cell(i+1, 8, locstring)

                process = False
            except Exception as e:
                print e.args
                print e

It was really fun and I think I’m going to invest some time to learn python.

comments powered by Disqus