Sidequest: Adding latitude and longitude to your dataset (and geocoding it)

Much too late I realized we want to map these schools. So first thing we need to do is convert their addresses to latitude and longitude - a.k.a. geocode them.

Adding columns to your database

First we need t8 be able to save our latitude and longitude. Since the rest of our data lives in the database, we might as well put the lat/lng there, too!

It’ll go in the schools table in the database. The only problem is right now we don’t have columns for latitude and longitude, so neither the database nor peewee would be able to handle this new information.

So let’s do just that: add the latitude and longitude columns to both peewee and our database.

It’s easy enough to add it to the model in models.py. Just two new columns - latitude and longitude are generally stored as decimals, 9 digits long with 6 of those being after the decimal place. This becomes DecimalField(max_digits=9,decimal_places=6) in peewee.

models.py

class School(Model):
  # ...other columns
  latitude = DecimalField(max_digits=9,decimal_places=6)
  longitude = DecimalField(max_digits=9,decimal_places=6)

But what about the database? We need to manually add the columns, which you could do fine by running sqlite3 schools.db but I think is a little more reproducible by putting it in Python code. Peewee can run raw SQL queries with db.execute_sql, so we’re going to do just that.

NOTE: Adding, removing, or changing your database is called a migration.

from models import *

# Run the migration
# Peewee will throw an error if it doesn't work, but we can just use
# try/except to ignore it
try:  
  db.execute_sql("ALTER TABLE schools ADD COLUMN latitude DECIMAL(9,6)")
  db.execute_sql("ALTER TABLE schools ADD COLUMN longitude DECIMAL(9,6)")
except:
  print "Already added columns, skipping that part"

That will add the right kind of column to our database (notice how the SQL name for it - DECIMAL(0,6) - is a little different than the peewee code).

If we ran this twice, our database and peewee would throw an error, since we already added the columns. Can’t add them twice, right? Since we don’t care as long as they made it in the at some point, we use try and except.

NOTE: Code that fails at any point in try immediately jumps to except. Even if you’re ignoring errors, it’s good to print a little message to let the user know what’s going on

Supporting a full address

What are we going to geocode? The full address, of course!

…except we don’t have that as a column! Instead we have a lot of pieces of addresses - the street address, the city, the zip code, all separate. Now we need to do two things:

Something old an boring, in that we make sure that we have all the appropriate columns put in our peewee models.
And something fun and new, where we write a new function for our model to create an entire address.

Luckily for our second point, you can treat a peewee model just like a normal class, and add new functions willy-nilly. Here’s a snippet of what the changes we’ll need:

models.py

class School(Model): # …other columns primary_address_line_1 = CharField() city = CharField() state_code = CharField() zip = CharField() # …other columns

# our function to glue the location together def full_address(self): return “{}, {}, {}, {}”.format(self.primary_address_line_1, self.city, self.state_code, self.zip)

Now calling school.full_address() will give us the location, formatted like "400 IRVING AVENUE, BROOKLYN, NY, 11237". Perfect for geocoding!

While we’re at it, we might as well try it out in add_lat_lng.py just to see if it works.

from models import *

# Run the migration
# Peewee will throw an error if it doesn't work, but we can just use
# try/except to ignore it
try:  
  db.execute_sql("ALTER TABLE schools ADD COLUMN latitude Decimal(9,6)")
  db.execute_sql("ALTER TABLE schools ADD COLUMN longitude Decimal(9,6)")
except:
  print "Already added columns, skipping that part"
  
schools = School.select()

for school in schools:
  print school.full_address()

Adding in the geocoding

Okay, so now we have

The lat/lng columns in the database
The lat/lng columns in peewee
The full address to geocode

Now it’s time to geocode! Since we don’t have that many we can use Google’s geocoding service. I stole the following URL from this Google Refine geocoding tutorial:

http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=the_address_im_looking_for

We’ll need to add the requests library to Python first, so we can actually make the request, and sleep so we can pause betwen them

import requests
from time import sleep

and instead of getting every school, maybe we should just get the ones without a latitude, a.k.a. the ones that haven’t been geocoded yet? You apparently select NULL values in peewee like this:

schools = School.select().where(School.latitude >> None)

And now we have to actually query good for the lat/lng and save it into the database! Here’s your final add_lat_lng.py.

from models import *
from time import sleep
import requests

# Run the migration
# Peewee will throw an error if it doesn't work, but we can just use
# try/except to ignore it
try:  
  db.execute_sql("ALTER TABLE schools ADD COLUMN latitude Decimal(9,6)")
  db.execute_sql("ALTER TABLE schools ADD COLUMN longitude Decimal(9,6)")
except:
  print "Already added columns, skipping that part"

# Let's get all of the schools that haven't been geocoded yet
# This is apparently how you select where latitude is null
schools = School.select().where(School.latitude >> None)

for school in schools:
  # Wait a few seconds between each of these because we'd like to pretend
  # we aren't robots and are polite
  sleep(1)
  # Wrap this in try/except because hey if it fails it fails
  try:
    # Form the URL with the address in it
    url = "http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address={}".format(school.full_address())

    # Request the URL
    response = requests.get(url)

    # Dig deep into the JSON 
    # this will give us something like
    # {u'lat': 40.7135296, u'lng': -73.9856844}
    coords = response.json()['results'][0]['geometry']['location']
    
    # Assign the lat/lng into the school object (the row)
    school.latitude = coords['lat']
    school.longitude = coords['lng']
    
    # And now save it to the database
    school.save()
    print "{} is at {}, {}".format(school.school_name, school.latitude, school.longitude)
  except:
    print "Failed to query/save for {}".format(school.school_name)

And now when you run it, you’ll end up with a wonderful situation like this

Problem(s) solved, geocoding success!

Now let’s get on to adding school maps to our webapp!