Toronto Elections data with Neo4j and Python part 2 of 3

In my last post, I took some campaign contribution data and plugged it into Neo4j using a sweet Python plugin called py2neo. Now we’re going to take that same graph and give it some added value, namely flesh out the geospatial aspect of it.

Getting back to the example from last time, if you take a nice close look, you’ll see that there are postal codes. What’s missing?

….that’s right: You have all these postal codes, but you can’t really do that much with them because you don’t have any distances between them. If you want to do any analysis around concentrations of neighborhoods, or if you are a candidate and want to save on travel time, it’s really hard to do the way it is, now. So let’s add some distance data between the postal codes and add it to our graphs.

I decided to use the MapQuest API for this, mainly because the plugin (mapq for Python) is dead easy to get going. Unfortunately, I got some wonky results, probably because I didn’t have complete address data. If I was going to do this “for real” I would probably get better address data or use a plugin that can handle incomplete postal codes better.

The steps are as follows (after you’ve created the graph, natch):

Get your MapQuest API key
Get a unique list of postal codes
Loop through each postal code, getting the province and city data, if available
For each postal code, get the longitude and latitude using the MapQuest API
Using itertools, get all the combinations of coordinates, getting the distance for each
Feed the distances for each combination into Neo4j

Part 3 of this series is going to do some analysis using Cypher, but for now, let’s get the data loaded!

import csv import py2neo from py2neo import neo4j, node, rel #this is a handy add-in for working with Neo4j import mapq import time ## Optional. Add this if you want to create a delay on the API call import itertools mapq.key('xxxxxxxxxxxxxxxxxxxxxxx') source = 'C:\Users\jlalonde\Documents\personal\elections\\tblMayor.csv' #obviously you want to adjust your path to suit what you need codes = [] #this is your list of postal codes coordinates = [] #this will be the postal codes, plus lat-lon distances = [] # this will be the final list of postal codes plus distances from one another fs = ['K','L','M','N'] #these will help to filter on Ontario postal codes x = 0 with open(source, 'rb') as csvfile: #for this loop you just need unique postal codes. Let's do it! s = csv.reader(csvfile, delimiter=';') for row in s: #row[2] is the address, but it is not filled out most of the time #row[3] is the contributor postal code if row[11] == 'Gold, Mitch': #I'm just using Mitch Gold the candidate for this example. codes.append([row[3], row[2]]) codes.sort() # print codes codes_unique = list(codes) codes_unique.sort() for p in codes_unique: if p[0][:1] in fs:#this is to distinguish between Ontario and Quebec prov = ', Ontario, ' else: prov = ', Quebec, ' if len(p[1]) > 1: #this asks if there is address data c = p[1].split(', ') city = ', ' + str(c[-1]) #if there is append the city else: city = '' #otherwise, leave this blank m = str(p[0]) + city + prov + 'Canada' #you are creating the # print m if x < 50: t = mapq.latlng(m) single = [p[0], t.get('lat'), t.get('lng')] coordinates.append(single) x = x + 1 #time.sleep(1) #a one-second delay for pinging mapquest API combinations = list(itertools.combinations(coordinates, 2)) print combinations

Up until this point you now have all the possible combinations of postal codes, along with their longitude and latitude. Next up is to get the distances and then plug them in. FYI a BIG thank you and shout out to Michael0x2a for his code on StackExchange on distances.

from math import sin, cos, sqrt, atan2, radians graph_db = neo4j.GraphDatabaseService() R = 6373.0 for i in combinations: if i[0][0] != i[1][0]: lat1 = radians(i[0][1]) lon1 = radians(i[0][2]) lat2 = radians(i[1][1]) lon2 = radians(i[1][2]) dlon = lon2 - lon1 dlat = lat2 - lat1 a = (sin(dlat/2))**2 + cos(lat1) * cos(lat2) * (sin(dlon/2))**2 c = 2 * atan2(sqrt(a), sqrt(1-a)) distance = R * c #print "Result of ", str(i[0][0]), " to ", str(i[1][0]), " is ", str(distance) # from here you can see some distances are zero, which means bad data. #my guess is the Mapquest API is having a hard time with just postal codes being thrown at it. string2 = 'MATCH (p1 {p_name: "' + i[0][0] +'"}), (p2 {p_name: "' + i[1][0] + '"})' string2 = string2 + ' CREATE UNIQUE p1-[:ADJ {distance: ' + str(distance) + '}]->p2' print string2 query2 = neo4j.CypherQuery(graph_db, string2) go2 = query2.execute()