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.
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 https://github.com/burnash/gspread.git cd gspread python setup.py 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 = gc.open("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 = "http://maps.googleapis.com/maps/api/geocode/json?address="+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"]["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 gdata.docs.service import sys import gspread import urllib2 import urllib import json username = 'your_gmail' password = 'your_password' gc = gspread.login(username, password) wks = gc.open("church").sheet1 rowcount = wks.row_count startindex = 0 if(len(sys.argv) > 1): startindex = sys.argv 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) if(curloc): print "already contains location: skipping" continue url = "http://maps.googleapis.com/maps/api/geocode/json?address="+address+"&sensor=false" print "fetching location for: "+url process = True while(process): try: resp = json.load(urllib2.urlopen(url)) locstring = "" if(len(resp["results"]) > 0): location = resp["results"]["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.