quandyfactory/crossing_guard_data.py

## crossing_guard_data.py
#!/usr/bin/env python

"""
This script contains functions that:
* convert tabular data from the City's crossing guards web page [1] into a nested list;
* use the Google Maps API to get geocoding data for each location; and
* convert the whole thing into an SQL insert statement.

[1] http://www.hamilton.ca/CityDepartments/PlanningEcDev/Divisions/ParkingBylawServices/SchoolCrossing/SchoolCrossingGuardLocations.htm

Please note this is a quick-and-dirty script. For now there's no exception handling, proxy handling for the HTTP requests, etc.
"""

__version__ = 0.1
__releasedate__ = '2011-03-20'
__author__ = 'Ryan McGreal <ryan@quandyfactory.com>'
__homepage__ = 'http://quandyfactory.com/projects/2/githubapi/'
__copyright__ = '(C) 2011 by Ryan McGreal. Licenced under GNU GPL 2.0\nhttp://www.gnu.org/licenses/old-licenses/gpl-2.0.html'

import json
import urllib


def prepare_data(string):
    """
    Splits the data string into rows by linebreak and each row into cells by tab.
    Appends ", Hamilton, Ontario" to the first cell so Google Maps API can look up the address.
    """
    data = [] # initialize output list
    for row in string.split('\n'):
        thisrow = [cell.strip() for cell in row.split('\t')]
        thisrow[0] = '%s, Hamilton, Ontario' % (thisrow[0].replace('&', 'and'))
        data.append(thisrow)
    return data


def get_geocodes(data):
    """
    Takes the data list from prepare_data and returns the data list with x and y coordinates added.
    Uses Google Maps Geocoding API.
    """
    output = []
    for row in data:
        url = 'https://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=%s' % (urllib.quote(row[0]))
        response = urllib.urlopen(url) # get response from server
        content = response.read() # read response content
        obj = json.loads(content) # convert JSON response content into native dict
        lat = obj['results'][0]['geometry']['location']['lat'] # get latitude
        lng = obj['results'][0]['geometry']['location']['lng'] # get longitude
        row.append(lat)
        row.append(lng)
        output.append(row)
        print row
    return output


def make_sql(data, tablename='data_crossing_guards', cols=['Location', 'Ward', 'Area', 'Intersection_Type', 'School_Type', 'Latitude', 'Longitude']):
    """
    Takes the data list from prepare_data and returns a MySQL insert statement.
    """
    output = ['insert into %s (' % (tablename)]
    output.append(', '.join(cols))
    output.append(') values ')
    data_rows = []
    for row in data:
        data_rows.append('(\'%s\')' % ('\', \''.join(['%s' % str(field).replace("'", "\'") for field in row])))
    output.append(', '.join(data_rows))
    return '\n'.join(output)


if __name__ == '__main__':
    # I just copied the tabular data off the web page and pasted into a multiline string.
    # This method works in Firefox and Chrome. I'm not sure about other browsers.

    string = """Aberdeen Ave & Dundurn St S 	1 	1 	  	B
Aberdeen Ave & Queen St S 	1 	1 	Full Signal 	B
Albert & King St W 	13 	1 	  	P
Albright Ct & Albright Rd 	5 	3 	  	P
Albright Rd & Harrisford Dr 	5 	3 	3 Way Stop 	B
Albright Rd & Mt. Albion Rd (2) 	5 	3 	Full Signal 	B
Albright Rd & Mt. Albion Rd (2) 	5 	3 	Full Signal 	B
Albright Rd & Nicklaus Dr 	5 	3 	3 Way Stop 	P
Albright Rd & Quigley Rd 	5 	3 	Full Signal 	P
Alma St & Sydenham St 	13 	1 	  	B
Amberly & Wilson 	12 	1 	  	P
Amberly Blvd & Concerto Cr 	12 	1 	  	B
Anson & Carson 	6 	2 	3 Way Stop 	B
Anson Ave & Upper Ottawa St 	6 	2 	  	B
Arnold St & Dalewood Ave 	1 	1 	  	P
Atherley Dr & Upper Sherman Ave 	7 	2 	  	B
Barlake & Violet 	5 	3 	3 Way Stop 	P
Barons Ave & Brittania Ave 	4 	3 	  	B
Barton St & Mountainview School 	10 	3 	Mid Block 	P
Barton St & Sanford Ave 	3 	2 	Full Signal 	P
Barton St & Sherman Avenue 	3 	2 	Full Signal 	B
Barton St & Winona Rd 	11 	3 	  	P
Barton St E & Brunswick St 	4 	3 	Mid Block 	P
Barton St E & Gage 	3 	3 	Full Signal 	P
Barton St E & Lottridge St N 	3 	2 	Full Signal 	P
Barton St E & Ruth St 	3 	2 	PPS 	B
Barton St E & Weir St N 	4 	3 	Mid Block 	P
Barton St W & MacNab St N 	2 	1 	4 Way Stop 	S
Bay St S & Bold St 	2 	1 	  	P
Beaconsfield Dr & Currie St 	6 	2 	3 Way Stop 	P
Beechwood Ave & Sherman Ave N 	3 	2 	  	S
Bendamere Ave & Upper Paradise Rd 	8 	1 	4 Way Stop 	P
Berko Ave & Upper Sherman Ave 	7 	2 	Full Signal 	P
Beryl St & Birchview Dr 	6 	2 	3 Way Stop 	P
Bettina Ave & Lawrence Rd 	5 	3 	Mid Block 	B
Blake St & Maplewood Ave 	3 	2 	  	B
Bond & Glen 	1 	1 	  	P
Bond & King 	1 	1 	Full Signal 	P
Braeheid Ave & Guy Brown School 	15 	2 	  	P
Braeheid Ave & Riley St 	15 	2 	3 Way Stop 	P
Brampton Ave & Dunn Ave 	4 	3 	  	P
Brian Blvd & Longyear Dr 	15 	2 	  	P
Bridlewood & Governor's Rd 	13 	1 	Full Signal 	P
Brigade Ave & Emperor Ave 	7 	2 	3 Way Stop 	B
Brigade Dr & Upper Wellington 	7 	2 	  	B
Britannia Ave & Kenilworth St N 	4 	3 	Full Signal 	S
Britannia Ave & Weir St N 	4 	3 	  	P
Brittania Ave & McLaren Ave 	4 	3 	  	B
Broker Dr & Upper Ottawa St 	6 	2 	Full Signal 	B
Brucedale Ave & East 5th St 	7 	1 	4 Way Stop 	B
Brucedale Ave E & Upper Ottawa 	6 	2 	Full Signal 	P
Burlington St N & John St N 	2 	1 	Full Signal 	P
Cameron Ave & York 	13 	1 	  	P
Cannon St & Lottridge St N 	3 	2 	  	P
Cannon St E & Elgin St N 	2 	2 	  	P
Cannon St E & Gage Ave N 	3 	2 	Full Signal 	P
Cannon St E & Hess St 	2 	1 	Full Signal 	B
Cannon St E & Province St N 	4 	3 	  	P
Cannon St E & Robins Ave 	4 	3 	  	B
Cannon St E & Sanford Avenue 	3 	2 	Full Signal 	P
Cannon St E & Smith Ave 	3 	2 	  	B
Cannon St E & Wentworth St N 	3 	2 	Full Signal 	B
Carla Ave & Green Rd 	10 	3 	  	S
Central Ave & Kenilworth Ave S 	4 	3 	Full Signal 	B
Central Ave & Parkdale Ave S 	4 	3 	Full Signal 	B
Charlton Ave & Walnut 	2 	1 	Full Signal 	P
Charlton Ave W & Locke St S 	1 	1 	3 Way Stop 	B
Charlton St W & Kent Ave 	1 	1 	3 Way Stop 	B
Chedmac & Rice Ave 	8 	1 	3 Way Stop 	P
Chester & West 5th 	8 	1 	  	P
Churchill Ave & Upper James 	8 	1 	PPS 	P
Citation & Meadowlands 	12 	1 	  	B
Cochrane Rd & Montrose Ave 	5 	3 	  	P
Colbourne & James 	2 	1 	  	S
Concession St & East 19th 	7 	2 	  	P
Concession St & Viewpoint 	7 	2 	  	S
Courtland Ave & Stonechurch Rd W 	8 	1 	PPS 	B
Cranbrook Dr & Garrow Dr 	8 	1 	3 Way Stop 	B
Creighton Rd & Governors Rd 	13 	1 	Full Signal 	B
Cumberland Ave & Norway Ave 	3 	2 	3 Way Stop 	P
Cumberland Ave & Prospect St S 	3 	2 	3 Way Stop 	B
Delawana Dr & Jerome Cres 	5 	3 	  	P
Delawana Dr & Riverdale Dr 	5 	3 	3 Way Stop 	P
Delaware Ave & Sherman Ave S 	3 	2 	  	B
Delaware Ave & Wentworth 	3 	2 	  	P
Dewitt & Dupont 	10 	3 	  	S
Dewitt Rd & Glenashton 	10 	3 	  	S
Dewitt Rd & MacIntosh 	10 	3 	4 Way Stop 	S
Dundana Ave & Old Ancaster Rd 	13 	1 	  	P
Dundurn & Stanley 	1 	1 	Mid-Block 	B
Dundurn St N & Lamoreaux 	1 	1 	  	P
Dundurn St S & Herkimer St 	1 	1 	Full Signal 	B
Dunham & Wilson 	12 	1 	  	P
Dunsmure Rd & Grosvenor N 	3 	3 	  	P
East 25th & Mohawk Rd E 	7 	2 	  	S
East 25th St & Mohawk Rd E 	7 	2 	  	S
East 26th St & Queensdale Ave E 	5 	2 	4 Way Stop 	P
East 27th St & Franklin Rd 	7 	2 	3 Way Stop 	P
East 38th & Fennell Ave E 	6 	2 	  	S
East 5th & Fennell 	7 	1 	  	B
East Ave N & King St E 	3 	2 	  	S
East Ave S & Main St E 	2 	2 	Full Signal 	S
Elcho St & Inverness E 	7 	2 	  	B
Elgar & Limeridge 	8 	1 	Full Signal 	B
Elmhill & Golflinks 	12 	1 	  	P
Emerson & Sussex 	1 	1 	  	S
Emperor Ave & Upper Wentworth (2) 	7 	2 	Full Signal 	P
Emperor Ave & Upper Wentworth St 	7 	2 	  	P
Fennell Ave & Upper James St 	7 	1 	Full Signal 	B
Fennell Ave E & High St 	6 	2 	PPS 	B
Ferguson Ave N & Wilson St 	2 	2 	Full Signal 	P
Ferrie St E & John St N 	2 	1 	  	P
Fiddlers Green & Jerseyville Rd 	12 	1 	  	S
Franklin Rd & Upper Sherman Ave 	7 	2 	PPS 	P
Gage Ave N & Primrose Ave 	3 	2 	  	B
Garrow Dr & Garth St 	8 	1 	Full Signal 	B
Garth & Stone Church 	8 	1 	Full Signal 	B
Gatestone & Gatestone School 	9 	3 	Full Signal 	B
Gatestone & Highland Rd E 	9 	3 	4 Way Stop 	B
Gatestone Dr & Summerfield Ave 	9 	3 	3 Way Stop 	B
Glen & Longwood 	1 	1 	  	P
Glenhollow Dr & Winterberry 	9 	3 	  	P
Glenholme Ave & King St E 	4 	3 	  	B
Gordon Drummond & Kennard 	9 	3 	  	B
Grays Rd & King St 	9 	3 	Full Signal 	S
Grays Rd & Roxborough 	10 	3 	  	B
Green Forest & King St 	10 	3 	  	S
Green Rd & Hemlock 	10 	3 	3 Way Stop 	B
Greenhill & Mt. Albion 	5 	3 	Full Signal 	B
Greenhill Ave & Glen Vista Dr 	5 	3 	3 Way Stop 	B
Greeningdon Dr & Hester St 	7 	2 	3 Way Stop 	B
Grosvenor N & Main St E (2) 	3 	3 	Mid Block 	P
Grosvenor N & Main St E (2) 	3 	3 	Mid Block 	P
Guildwood Rd & Upper Horning Rd 	8 	1 	3 Way Stop 	B
Haddon Ave & Sterling St 	1 	1 	4 Way Stop 	P
Haddon St & King St W 	1 	1 	  	P
Hadeland & Upper Paradise Rd 	8 	1 	Full Signal 	B
Herkimer St & Locke St 	1 	1 	4 Way Stop 	B
Hess St & York 	2 	1 	Full Signal 	P
Hester St & Upper Wellington St 	7 	2 	Full Signal 	B
Highbury Dr & Whitedeer Rd 	9 	3 	3 Way Stop 	B
Highway 8 & Green Rd 	10 	3 	Full Signal 	S
Highway 8 & St Francis 	10 	3 	Mid Block 	S
Highway 8 & Winona Rd 	11 	3 	Full Signal 	P
Hunter & Locke St 	1 	1 	  	B
Inverness E & Upper Wellington St 	7 	2 	  	P
Isaac Brock & John Murray 	9 	3 	  	B
Isaac Brock & Pedestrian Underpass 	9 	3 	Mid Block 	B
James St N & Picton St E 	2 	1 	PPS 	B
Jay St & Upper Wellington 	7 	2 	  	B
Jerseyville Rd & Lloyminn Ave 	12 	1 	  	B
John St N & Picton St E 	2 	1 	  	B
Juliebeth & Stone Church Rd W 	8 	1 	  	B
Keewaydin St & Parkside Ave 	15 	2 	Full Signal 	P
King & Lake 	9 	3 	Full Signal 	P
King St E & Edgemont St S 	4 	3 	PPS 	B
King St E & Lottridge 	3 	2 	  	P
King St E & Maple Ave 	3 	3 	PPS 	P
King St E & Wexford Ave S 	4 	3 	  	P
King St W & Pearl St N 	1 	1 	  	P
King St W & Strathcona Ave 	1 	1 	Full Signal 	P
Kitty Murray Lane & Bridgeport 	12 	1 	  	B
Lennox St & Limeridge Rd E 	6 	2 	3 Way Stop 	B
Limeridge Rd E & Skylark 	7 	2 	  	P
Limeridge Rd W & St. Jeromes School 	8 	1 	PPS 	B
Locke St & Peter St 	1 	1 	Full Signal 	P
Loconder Dr & Upper Gage 	6 	2 	Full Signal 	P
Lottridge St & Rosemont Ave 	3 	2 	3 Way Stop 	P
Lynbrook Dr & Montcalm 	8 	1 	  	P
Main St & Pearl St 	1 	1 	  	P
Main St E & Sherman Ave S 	3 	2 	Full Signal 	B
Maplewood Ave & Springer Ave 	3 	2 	  	B
Mary St & Picton St E 	2 	1 	3 Way Stop 	B
McNiven Rd & Mohawk Rd W 	12 	1 	Full Signal 	P
Melville & Sydenham 	13 	1 	  	B
Melvin Ave & Osbourne 	4 	3 	  	P
Mohawk @ 245 & Sanatorium 	8 	1 	Mid Block 	P
Mohawk Rd W & Rice Ave 	8 	1 	Full Signal 	B
Mohawk Rd W & Upper Paradise 	8 	1 	Full Signal 	B
Mohawk Rd W & Upper Paradise Rd 	8 	1 	Full Signal 	B
Nancy St & Queensdale Ave 	6 	2 	  	P
Ninth Ave & Upper Gage Ave 	6 	2 	Full Signal 	S
Paramount Dr & Apex 	9 	3 	  	B
Paramount Dr & St. Paul's School 	9 	3 	Mid Block 	B
Parkdale & Queenston St 	9 	3 	Full Signal 	B
Parkdale Ave & Parkdale School 	4 	3 	  	P
Queen St N & York Blvd 	2 	1 	Full Signal 	P
Queen Victoria & Redbury St 	6 	2 	3 Way Stop 	B
Queensbury & Queen Victoria Dr 	6 	2 	3 Way Stop 	B
Queensdale & Upper Ottawa 	6 	2 	  	P
Queenston Rd & Donn Ave 	9 	3 	PPS 	P
Queenston Rd & Irene(1) 	9 	3 	  	P
Queenston Rd & Irene(2) 	9 	3 	  	P
Queenston Rd & Nash Rd 	9 	3 	Full Signal 	P
Quigley Rd & TH&B 	5 	3 	Mid Block 	B
Quinn Ave & Stonechurch Rd E 	6 	2 	PPS 	B
Rice & Sanatorium 	8 	1 	  	P
Rifle Range Rd & Whitney Ave 	1 	1 	4 Way Stop 	P
Rosseau School & Rosseau School 	12 	1 	  	P
Roxborough Ave & Strathearne Ave 	4 	3 	4 Way Stop 	P
Royal Vista Dr & Templemead Dr 	6 	2 	3 Way Stop 	P
Royal Vista Dr & Upper Gage Ave 	6 	2 	Full Signal 	P
Sanatorium Rd & Upper Paradise Rd 	8 	1 	4 Way Stop 	B
Southbend & West 5th 	8 	1 	Full Signal 	B
Southbend Rd & Upper Wellington St 	7 	1 	PPS 	B
Stinson St & Wellington St S 	2 	2 	  	P
Stonechurch Rd E & Upper Ottawa 	6 	2 	Full Signal 	B
Sunninghill Ave & Upper Gage Ave 	6 	1 	  	P
Tenth Ave & Upper Ottawa St 	6 	2 	  	P
Thorley Ave & Upper Gage 	6 	2 	Full Signal 	P
Tyrone Dr & West 5th St 	8 	1 	3 Way Stop 	P
Walnut St & Young St 	2 	2 	4 Way Stop 	B
Winona Rd & Yellowwood 	11 	3 	  	P
Winterberry & Paramount 	9 	3 	Full Signal 	P
Woodward Ave & Woodward School 	9 	3 	Full Signal 	P"""

    data = prepare_data(string)

    data2 = get_geocodes(data)

    sql = make_sql(data2)

    print sql
	#!/usr/bin/env python

	"""
	This script contains functions that:
	* convert tabular data from the City's crossing guards web page [1] into a nested list;
	* use the Google Maps API to get geocoding data for each location; and
	* convert the whole thing into an SQL insert statement.

	[1] http://www.hamilton.ca/CityDepartments/PlanningEcDev/Divisions/ParkingBylawServices/SchoolCrossing/SchoolCrossingGuardLocations.htm

	Please note this is a quick-and-dirty script. For now there's no exception handling, proxy handling for the HTTP requests, etc.
	"""

	__version__ = 0.1
	__releasedate__ = '2011-03-20'
	__author__ = 'Ryan McGreal <ryan@quandyfactory.com>'
	__homepage__ = 'http://quandyfactory.com/projects/2/githubapi/'
	__copyright__ = '(C) 2011 by Ryan McGreal. Licenced under GNU GPL 2.0\nhttp://www.gnu.org/licenses/old-licenses/gpl-2.0.html'

	import json
	import urllib


	def prepare_data(string):
	"""
	Splits the data string into rows by linebreak and each row into cells by tab.
	Appends ", Hamilton, Ontario" to the first cell so Google Maps API can look up the address.
	"""
	data = [] # initialize output list
	for row in string.split('\n'):
	thisrow = [cell.strip() for cell in row.split('\t')]
	thisrow[0] = '%s, Hamilton, Ontario' % (thisrow[0].replace('&', 'and'))
	data.append(thisrow)
	return data


	def get_geocodes(data):
	"""
	Takes the data list from prepare_data and returns the data list with x and y coordinates added.
	Uses Google Maps Geocoding API.
	"""
	output = []
	for row in data:
	url = 'https://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=%s' % (urllib.quote(row[0]))
	response = urllib.urlopen(url) # get response from server
	content = response.read() # read response content
	obj = json.loads(content) # convert JSON response content into native dict
	lat = obj['results'][0]['geometry']['location']['lat'] # get latitude
	lng = obj['results'][0]['geometry']['location']['lng'] # get longitude
	row.append(lat)
	row.append(lng)
	output.append(row)
	print row
	return output


	def make_sql(data, tablename='data_crossing_guards', cols=['Location', 'Ward', 'Area', 'Intersection_Type', 'School_Type', 'Latitude', 'Longitude']):
	"""
	Takes the data list from prepare_data and returns a MySQL insert statement.
	"""
	output = ['insert into %s (' % (tablename)]
	output.append(', '.join(cols))
	output.append(') values ')
	data_rows = []
	for row in data:
	data_rows.append('(\'%s\')' % ('\', \''.join(['%s' % str(field).replace("'", "\'") for field in row])))
	output.append(', '.join(data_rows))
	return '\n'.join(output)


	if __name__ == '__main__':
	# I just copied the tabular data off the web page and pasted into a multiline string.
	# This method works in Firefox and Chrome. I'm not sure about other browsers.

	string = """Aberdeen Ave & Dundurn St S 1 1 B
	Aberdeen Ave & Queen St S 1 1 Full Signal B
	Albert & King St W 13 1 P
	Albright Ct & Albright Rd 5 3 P
	Albright Rd & Harrisford Dr 5 3 3 Way Stop B
	Albright Rd & Mt. Albion Rd (2) 5 3 Full Signal B
	Albright Rd & Mt. Albion Rd (2) 5 3 Full Signal B
	Albright Rd & Nicklaus Dr 5 3 3 Way Stop P
	Albright Rd & Quigley Rd 5 3 Full Signal P
	Alma St & Sydenham St 13 1 B
	Amberly & Wilson 12 1 P
	Amberly Blvd & Concerto Cr 12 1 B
	Anson & Carson 6 2 3 Way Stop B
	Anson Ave & Upper Ottawa St 6 2 B
	Arnold St & Dalewood Ave 1 1 P
	Atherley Dr & Upper Sherman Ave 7 2 B
	Barlake & Violet 5 3 3 Way Stop P
	Barons Ave & Brittania Ave 4 3 B
	Barton St & Mountainview School 10 3 Mid Block P
	Barton St & Sanford Ave 3 2 Full Signal P
	Barton St & Sherman Avenue 3 2 Full Signal B
	Barton St & Winona Rd 11 3 P
	Barton St E & Brunswick St 4 3 Mid Block P
	Barton St E & Gage 3 3 Full Signal P
	Barton St E & Lottridge St N 3 2 Full Signal P
	Barton St E & Ruth St 3 2 PPS B
	Barton St E & Weir St N 4 3 Mid Block P
	Barton St W & MacNab St N 2 1 4 Way Stop S
	Bay St S & Bold St 2 1 P
	Beaconsfield Dr & Currie St 6 2 3 Way Stop P
	Beechwood Ave & Sherman Ave N 3 2 S
	Bendamere Ave & Upper Paradise Rd 8 1 4 Way Stop P
	Berko Ave & Upper Sherman Ave 7 2 Full Signal P
	Beryl St & Birchview Dr 6 2 3 Way Stop P
	Bettina Ave & Lawrence Rd 5 3 Mid Block B
	Blake St & Maplewood Ave 3 2 B
	Bond & Glen 1 1 P
	Bond & King 1 1 Full Signal P
	Braeheid Ave & Guy Brown School 15 2 P
	Braeheid Ave & Riley St 15 2 3 Way Stop P
	Brampton Ave & Dunn Ave 4 3 P
	Brian Blvd & Longyear Dr 15 2 P
	Bridlewood & Governor's Rd 13 1 Full Signal P
	Brigade Ave & Emperor Ave 7 2 3 Way Stop B
	Brigade Dr & Upper Wellington 7 2 B
	Britannia Ave & Kenilworth St N 4 3 Full Signal S
	Britannia Ave & Weir St N 4 3 P
	Brittania Ave & McLaren Ave 4 3 B
	Broker Dr & Upper Ottawa St 6 2 Full Signal B
	Brucedale Ave & East 5th St 7 1 4 Way Stop B
	Brucedale Ave E & Upper Ottawa 6 2 Full Signal P
	Burlington St N & John St N 2 1 Full Signal P
	Cameron Ave & York 13 1 P
	Cannon St & Lottridge St N 3 2 P
	Cannon St E & Elgin St N 2 2 P
	Cannon St E & Gage Ave N 3 2 Full Signal P
	Cannon St E & Hess St 2 1 Full Signal B
	Cannon St E & Province St N 4 3 P
	Cannon St E & Robins Ave 4 3 B
	Cannon St E & Sanford Avenue 3 2 Full Signal P
	Cannon St E & Smith Ave 3 2 B
	Cannon St E & Wentworth St N 3 2 Full Signal B
	Carla Ave & Green Rd 10 3 S
	Central Ave & Kenilworth Ave S 4 3 Full Signal B
	Central Ave & Parkdale Ave S 4 3 Full Signal B
	Charlton Ave & Walnut 2 1 Full Signal P
	Charlton Ave W & Locke St S 1 1 3 Way Stop B
	Charlton St W & Kent Ave 1 1 3 Way Stop B
	Chedmac & Rice Ave 8 1 3 Way Stop P
	Chester & West 5th 8 1 P
	Churchill Ave & Upper James 8 1 PPS P
	Citation & Meadowlands 12 1 B
	Cochrane Rd & Montrose Ave 5 3 P
	Colbourne & James 2 1 S
	Concession St & East 19th 7 2 P
	Concession St & Viewpoint 7 2 S
	Courtland Ave & Stonechurch Rd W 8 1 PPS B
	Cranbrook Dr & Garrow Dr 8 1 3 Way Stop B
	Creighton Rd & Governors Rd 13 1 Full Signal B
	Cumberland Ave & Norway Ave 3 2 3 Way Stop P
	Cumberland Ave & Prospect St S 3 2 3 Way Stop B
	Delawana Dr & Jerome Cres 5 3 P
	Delawana Dr & Riverdale Dr 5 3 3 Way Stop P
	Delaware Ave & Sherman Ave S 3 2 B
	Delaware Ave & Wentworth 3 2 P
	Dewitt & Dupont 10 3 S
	Dewitt Rd & Glenashton 10 3 S
	Dewitt Rd & MacIntosh 10 3 4 Way Stop S
	Dundana Ave & Old Ancaster Rd 13 1 P
	Dundurn & Stanley 1 1 Mid-Block B
	Dundurn St N & Lamoreaux 1 1 P
	Dundurn St S & Herkimer St 1 1 Full Signal B
	Dunham & Wilson 12 1 P
	Dunsmure Rd & Grosvenor N 3 3 P
	East 25th & Mohawk Rd E 7 2 S
	East 25th St & Mohawk Rd E 7 2 S
	East 26th St & Queensdale Ave E 5 2 4 Way Stop P
	East 27th St & Franklin Rd 7 2 3 Way Stop P
	East 38th & Fennell Ave E 6 2 S
	East 5th & Fennell 7 1 B
	East Ave N & King St E 3 2 S
	East Ave S & Main St E 2 2 Full Signal S
	Elcho St & Inverness E 7 2 B
	Elgar & Limeridge 8 1 Full Signal B
	Elmhill & Golflinks 12 1 P
	Emerson & Sussex 1 1 S
	Emperor Ave & Upper Wentworth (2) 7 2 Full Signal P
	Emperor Ave & Upper Wentworth St 7 2 P
	Fennell Ave & Upper James St 7 1 Full Signal B
	Fennell Ave E & High St 6 2 PPS B
	Ferguson Ave N & Wilson St 2 2 Full Signal P
	Ferrie St E & John St N 2 1 P
	Fiddlers Green & Jerseyville Rd 12 1 S
	Franklin Rd & Upper Sherman Ave 7 2 PPS P
	Gage Ave N & Primrose Ave 3 2 B
	Garrow Dr & Garth St 8 1 Full Signal B
	Garth & Stone Church 8 1 Full Signal B
	Gatestone & Gatestone School 9 3 Full Signal B
	Gatestone & Highland Rd E 9 3 4 Way Stop B
	Gatestone Dr & Summerfield Ave 9 3 3 Way Stop B
	Glen & Longwood 1 1 P
	Glenhollow Dr & Winterberry 9 3 P
	Glenholme Ave & King St E 4 3 B
	Gordon Drummond & Kennard 9 3 B
	Grays Rd & King St 9 3 Full Signal S
	Grays Rd & Roxborough 10 3 B
	Green Forest & King St 10 3 S
	Green Rd & Hemlock 10 3 3 Way Stop B
	Greenhill & Mt. Albion 5 3 Full Signal B
	Greenhill Ave & Glen Vista Dr 5 3 3 Way Stop B
	Greeningdon Dr & Hester St 7 2 3 Way Stop B
	Grosvenor N & Main St E (2) 3 3 Mid Block P
	Grosvenor N & Main St E (2) 3 3 Mid Block P
	Guildwood Rd & Upper Horning Rd 8 1 3 Way Stop B
	Haddon Ave & Sterling St 1 1 4 Way Stop P
	Haddon St & King St W 1 1 P
	Hadeland & Upper Paradise Rd 8 1 Full Signal B
	Herkimer St & Locke St 1 1 4 Way Stop B
	Hess St & York 2 1 Full Signal P
	Hester St & Upper Wellington St 7 2 Full Signal B
	Highbury Dr & Whitedeer Rd 9 3 3 Way Stop B
	Highway 8 & Green Rd 10 3 Full Signal S
	Highway 8 & St Francis 10 3 Mid Block S
	Highway 8 & Winona Rd 11 3 Full Signal P
	Hunter & Locke St 1 1 B
	Inverness E & Upper Wellington St 7 2 P
	Isaac Brock & John Murray 9 3 B
	Isaac Brock & Pedestrian Underpass 9 3 Mid Block B
	James St N & Picton St E 2 1 PPS B
	Jay St & Upper Wellington 7 2 B
	Jerseyville Rd & Lloyminn Ave 12 1 B
	John St N & Picton St E 2 1 B
	Juliebeth & Stone Church Rd W 8 1 B
	Keewaydin St & Parkside Ave 15 2 Full Signal P
	King & Lake 9 3 Full Signal P
	King St E & Edgemont St S 4 3 PPS B
	King St E & Lottridge 3 2 P
	King St E & Maple Ave 3 3 PPS P
	King St E & Wexford Ave S 4 3 P
	King St W & Pearl St N 1 1 P
	King St W & Strathcona Ave 1 1 Full Signal P
	Kitty Murray Lane & Bridgeport 12 1 B
	Lennox St & Limeridge Rd E 6 2 3 Way Stop B
	Limeridge Rd E & Skylark 7 2 P
	Limeridge Rd W & St. Jeromes School 8 1 PPS B
	Locke St & Peter St 1 1 Full Signal P
	Loconder Dr & Upper Gage 6 2 Full Signal P
	Lottridge St & Rosemont Ave 3 2 3 Way Stop P
	Lynbrook Dr & Montcalm 8 1 P
	Main St & Pearl St 1 1 P
	Main St E & Sherman Ave S 3 2 Full Signal B
	Maplewood Ave & Springer Ave 3 2 B
	Mary St & Picton St E 2 1 3 Way Stop B
	McNiven Rd & Mohawk Rd W 12 1 Full Signal P
	Melville & Sydenham 13 1 B
	Melvin Ave & Osbourne 4 3 P
	Mohawk @ 245 & Sanatorium 8 1 Mid Block P
	Mohawk Rd W & Rice Ave 8 1 Full Signal B
	Mohawk Rd W & Upper Paradise 8 1 Full Signal B
	Mohawk Rd W & Upper Paradise Rd 8 1 Full Signal B
	Nancy St & Queensdale Ave 6 2 P
	Ninth Ave & Upper Gage Ave 6 2 Full Signal S
	Paramount Dr & Apex 9 3 B
	Paramount Dr & St. Paul's School 9 3 Mid Block B
	Parkdale & Queenston St 9 3 Full Signal B
	Parkdale Ave & Parkdale School 4 3 P
	Queen St N & York Blvd 2 1 Full Signal P
	Queen Victoria & Redbury St 6 2 3 Way Stop B
	Queensbury & Queen Victoria Dr 6 2 3 Way Stop B
	Queensdale & Upper Ottawa 6 2 P
	Queenston Rd & Donn Ave 9 3 PPS P
	Queenston Rd & Irene(1) 9 3 P
	Queenston Rd & Irene(2) 9 3 P
	Queenston Rd & Nash Rd 9 3 Full Signal P
	Quigley Rd & TH&B 5 3 Mid Block B
	Quinn Ave & Stonechurch Rd E 6 2 PPS B
	Rice & Sanatorium 8 1 P
	Rifle Range Rd & Whitney Ave 1 1 4 Way Stop P
	Rosseau School & Rosseau School 12 1 P
	Roxborough Ave & Strathearne Ave 4 3 4 Way Stop P
	Royal Vista Dr & Templemead Dr 6 2 3 Way Stop P
	Royal Vista Dr & Upper Gage Ave 6 2 Full Signal P
	Sanatorium Rd & Upper Paradise Rd 8 1 4 Way Stop B
	Southbend & West 5th 8 1 Full Signal B
	Southbend Rd & Upper Wellington St 7 1 PPS B
	Stinson St & Wellington St S 2 2 P
	Stonechurch Rd E & Upper Ottawa 6 2 Full Signal B
	Sunninghill Ave & Upper Gage Ave 6 1 P
	Tenth Ave & Upper Ottawa St 6 2 P
	Thorley Ave & Upper Gage 6 2 Full Signal P
	Tyrone Dr & West 5th St 8 1 3 Way Stop P
	Walnut St & Young St 2 2 4 Way Stop B
	Winona Rd & Yellowwood 11 3 P
	Winterberry & Paramount 9 3 Full Signal P
	Woodward Ave & Woodward School 9 3 Full Signal P"""

	data = prepare_data(string)

	data2 = get_geocodes(data)

	sql = make_sql(data2)

	print sql