Hubway Capstone Project-- Database Creation¶

Hubway is a bike-share program collectively owned by the metro Boston cities; Boston, Cambridge, Somerville, and Brookline. It is operated by Motivate, who manages similar initiatives in NYC, Portland, Chicago, Washington DC, and several other metro areas in Ohio, Tennessee, and New Jersey. They are opening up operations in San Francisco during the month of June, 2017. Hubway currently exists as a system of 188 stations with 18,000 bikes.

• For this project, I investigated shared data for the months of January, May, June, July, and October during the years of 2015 and 2016.
• Of concern were the questions of;
  
  • How do riders use the bike-share service?
    
  • Are the bikes used as a conveyance or for recreation?
    
  • What type of customer uses the service?
    

Below is the building of the initial database from the supplied CSV files.
Note: the data can be found at: Bluebikes System Data | Blue Bikes Boston

Library Import

In [1]:

import numpy as np
#import scipy.stats as stats
#import seaborn as sns
#import matplotlib.pyplot as plt
import pandas as pd
import sqlite3
from pandas.io import sql
from sqlalchemy import create_engine

Using SQLAlchemy to create database

In [27]:

#SQL_STRING = '''PRAGMA encoding="UTF-8";'''
engine = create_engine('sqlite:///C:\Users\Owner\Documents\Capstone\database\hubway_full.db', echo=True)
#conn = sqlite3.connect("C:\Users\Owner\Documents\Capstone\database\hubway_full.db")
#c = conn.cursor()
#c.execute('''CREATE TABLE IF NOT EXISTS hubway_db 
             #(tripduration INTEGER, starttime DATETIME, stoptime DATETIME, start_station_id INTEGER, start_station_name VARCHAR, 
             #start_station_latitude FLOAT, start_station_longitude FLOAT, end_station_id INTEGER, end_station_name VARCHAR, 
             #end_station_latitude FLOAT, end_station_longitude FLOAT, bikeid INTEGER, usertype VARCHAR, birth_year INTEGER, gender INTEGER);''')

#SQL_STRING = '''PRAGMA table_info(hubway_db);'''

CSV file read and conversion to DataFrames

In [28]:

janfif = pd.read_csv('./data/201501-hubway-tripdata.csv', encoding = 'utf-8')
janfif_df = pd.DataFrame(janfif)
#janfif_df.to_sql(hubway_db, conn, if_exists='append', index=False)

mayfif = pd.read_csv('./data/201505-hubway-tripdata.csv', encoding = 'utf-8')
mayfif = pd.DataFrame(mayfif)
#mayfif.to_sql(hubway_db, conn, if_exists='append', index=False)

junfif = pd.read_csv('./data/201506-hubway-tripdata.csv', encoding = 'utf-8')
junfif = pd.DataFrame(junfif)
#junfif.to_sql(hubway_db, conn, if_exists='append', index=False)

julfif = pd.read_csv('./data/201507-hubway-tripdata.csv', encoding = 'utf-8')
julfif = pd.DataFrame(julfif)
#julfif.to_sql(hubway_db, conn, if_exists='append', index=False)

octfif = pd.read_csv('./data/201510-hubway-tripdata.csv', encoding = 'utf-8')
octfif = pd.DataFrame(octfif)
#octfif.to_sql(hubway_db, conn, if_exists='append', index=False)

jansix = pd.read_csv('./data/201601-hubway-tripdata.csv', encoding = 'utf-8')
jansix = pd.DataFrame(jansix)
#jansix.to_sql(hubway_db, conn, if_exists='append', index=False)

maysix = pd.read_csv('./data/201605-hubway-tripdata.csv', encoding = 'utf-8')
maysix = pd.DataFrame(maysix)
#maysix.to_sql(hubway_db, conn, if_exists='append', index=False)

junsix = pd.read_csv('./data/201606-hubway-tripdata.csv', encoding = 'utf-8')
junsix = pd.DataFrame(junsix)
#junsix.to_sql(hubway_db, conn, if_exists='append', index=False)

julsix = pd.read_csv('./data/201607-hubway-tripdata.csv', encoding = 'utf-8')
julsix = pd.DataFrame(julsix)
#julsix.to_sql(hubway_db, conn, if_exists='append', index=False)

octsix = pd.read_csv('./data/201610-hubway-tripdata.csv', encoding = 'utf-8')
octsix = pd.DataFrame(octsix)
#octsix.to_sql(hubway_db, conn, if_exists='append', index=False)

Checking conversion

In [29]:

janfif.head()

Out[29]:

	tripduration	starttime	stoptime	start station id	start station name	start station latitude	start station longitude	end station id	end station name	end station latitude	end station longitude	bikeid	usertype	birth year	gender
0	542	2015-01-01 00:21:44	2015-01-01 00:30:47	115	Porter Square Station	42.387995	-71.119084	96	Cambridge Main Library at Broadway / Trowbridg...	42.373379	-71.111075	277	Subscriber	1984	1
1	438	2015-01-01 00:27:03	2015-01-01 00:34:21	80	MIT Stata Center at Vassar St / Main St	42.361962	-71.092053	95	Cambridge St - at Columbia St / Webster Ave	42.372969	-71.094445	648	Subscriber	1985	1
2	254	2015-01-01 00:31:31	2015-01-01 00:35:46	91	One Kendall Square at Hampshire St / Portland St	42.366277	-71.091690	68	Central Square at Mass Ave / Essex St	42.365070	-71.103100	555	Subscriber	1974	1
3	432	2015-01-01 00:53:46	2015-01-01 01:00:58	115	Porter Square Station	42.387995	-71.119084	96	Cambridge Main Library at Broadway / Trowbridg...	42.373379	-71.111075	1307	Subscriber	1987	1
4	735	2015-01-01 01:07:06	2015-01-01 01:19:21	105	Lower Cambridgeport at Magazine St/Riverside Rd	42.356954	-71.113687	88	Inman Square at Vellucci Plaza / Hampshire St	42.374035	-71.101427	177	Customer	1986	2

In [30]:

janfif.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 7840 entries, 0 to 7839
Data columns (total 15 columns):
tripduration               7840 non-null int64
starttime                  7840 non-null object
stoptime                   7840 non-null object
start station id           7840 non-null int64
start station name         7840 non-null object
start station latitude     7840 non-null float64
start station longitude    7840 non-null float64
end station id             7840 non-null int64
end station name           7840 non-null object
end station latitude       7840 non-null float64
end station longitude      7840 non-null float64
bikeid                     7840 non-null int64
usertype                   7840 non-null object
birth year                 7840 non-null object
gender                     7840 non-null int64
dtypes: float64(4), int64(5), object(6)
memory usage: 918.8+ KB

Concatenation of DataFrames and check

In [31]:

list_df= [janfif, mayfif, junfif, julfif, octfif, jansix, maysix, junsix, julsix, octsix]
hubway_df = pd.concat(list_df)

In [32]:

hubway_df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 1214424 entries, 0 to 137864
Data columns (total 15 columns):
tripduration               1214424 non-null int64
starttime                  1214424 non-null object
stoptime                   1214424 non-null object
start station id           1214424 non-null int64
start station name         1214424 non-null object
start station latitude     1214424 non-null float64
start station longitude    1214424 non-null float64
end station id             1214424 non-null int64
end station name           1214424 non-null object
end station latitude       1214424 non-null float64
end station longitude      1214424 non-null float64
bikeid                     1214424 non-null int64
usertype                   1214424 non-null object
birth year                 1214424 non-null object
gender                     1214424 non-null int64
dtypes: float64(4), int64(5), object(6)
memory usage: 148.2+ MB

In [33]:

hubway_df.tail()

Out[33]:

	tripduration	starttime	stoptime	start station id	start station name	start station latitude	start station longitude	end station id	end station name	end station latitude	end station longitude	bikeid	usertype	birth year	gender
137860	330	2016-10-31 21:48:09	2016-10-31 21:53:40	46	Christian Science Plaza	42.343864	-71.085918	12	Ruggles Station / Columbus Ave.	42.335911	-71.088496	802	Subscriber	1996	1
137861	196	2016-10-31 22:07:54	2016-10-31 22:11:10	46	Christian Science Plaza	42.343864	-71.085918	61	Boylston at Fairfield	42.348762	-71.082383	937	Subscriber	1995	1
137862	446	2016-10-31 23:32:41	2016-10-31 23:40:08	46	Christian Science Plaza	42.343864	-71.085918	10	B.U. Central - 725 Comm. Ave.	42.350406	-71.108279	1158	Subscriber	1970	1
137863	241	2016-10-31 18:03:48	2016-10-31 18:07:49	114	Teele Square at 239 Holland St	42.402763	-71.126908	100	Davis Square	42.396969	-71.123024	883	Subscriber	1989	2
137864	1026	2016-10-26 12:21:48	2016-10-26 12:38:55	11	Longwood Ave / Binney St	42.338629	-71.106500	15	Harvard Real Estate - Brighton Mills - 370 Wes...	42.361667	-71.138020	1866	Subscriber	1973	1

In [34]:

hubway_df.head()

Out[34]:

	tripduration	starttime	stoptime	start station id	start station name	start station latitude	start station longitude	end station id	end station name	end station latitude	end station longitude	bikeid	usertype	birth year	gender
0	542	2015-01-01 00:21:44	2015-01-01 00:30:47	115	Porter Square Station	42.387995	-71.119084	96	Cambridge Main Library at Broadway / Trowbridg...	42.373379	-71.111075	277	Subscriber	1984	1
1	438	2015-01-01 00:27:03	2015-01-01 00:34:21	80	MIT Stata Center at Vassar St / Main St	42.361962	-71.092053	95	Cambridge St - at Columbia St / Webster Ave	42.372969	-71.094445	648	Subscriber	1985	1
2	254	2015-01-01 00:31:31	2015-01-01 00:35:46	91	One Kendall Square at Hampshire St / Portland St	42.366277	-71.091690	68	Central Square at Mass Ave / Essex St	42.365070	-71.103100	555	Subscriber	1974	1
3	432	2015-01-01 00:53:46	2015-01-01 01:00:58	115	Porter Square Station	42.387995	-71.119084	96	Cambridge Main Library at Broadway / Trowbridg...	42.373379	-71.111075	1307	Subscriber	1987	1
4	735	2015-01-01 01:07:06	2015-01-01 01:19:21	105	Lower Cambridgeport at Magazine St/Riverside Rd	42.356954	-71.113687	88	Inman Square at Vellucci Plaza / Hampshire St	42.374035	-71.101427	177	Customer	1986	2

DataFrame to SQL table conversion

In [ ]:

hubway_df.to_sql(name = 'hubway_db', con = engine, if_exists = 'replace', index = False)

2017-05-26 05:43:40,036 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-05-26 05:43:40,038 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,042 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2017-05-26 05:43:40,045 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,051 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("hubway_db")
2017-05-26 05:43:40,053 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,069 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("hubway_db")
2017-05-26 05:43:40,072 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,078 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
2017-05-26 05:43:40,084 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,088 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("hubway_db")
2017-05-26 05:43:40,091 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,095 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'hubway_db' AND type = 'table'
2017-05-26 05:43:40,096 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,102 INFO sqlalchemy.engine.base.Engine PRAGMA foreign_key_list("hubway_db")
2017-05-26 05:43:40,105 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,108 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'hubway_db' AND type = 'table'
2017-05-26 05:43:40,109 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,114 INFO sqlalchemy.engine.base.Engine PRAGMA index_list("hubway_db")
2017-05-26 05:43:40,117 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,121 INFO sqlalchemy.engine.base.Engine PRAGMA index_list("hubway_db")
2017-05-26 05:43:40,122 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,125 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'hubway_db' AND type = 'table'
2017-05-26 05:43:40,128 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:43:40,134 INFO sqlalchemy.engine.base.Engine 
DROP TABLE hubway_db
2017-05-26 05:43:40,134 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:45:18,119 INFO sqlalchemy.engine.base.Engine COMMIT
2017-05-26 05:45:18,128 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE hubway_db (
 tripduration BIGINT, 
 starttime TEXT, 
 stoptime TEXT, 
 "start station id" BIGINT, 
 "start station name" TEXT, 
 "start station latitude" FLOAT, 
 "start station longitude" FLOAT, 
 "end station id" BIGINT, 
 "end station name" TEXT, 
 "end station latitude" FLOAT, 
 "end station longitude" FLOAT, 
 bikeid BIGINT, 
 usertype TEXT, 
 "birth year" TEXT, 
 gender BIGINT
)


2017-05-26 05:45:18,128 INFO sqlalchemy.engine.base.Engine ()
2017-05-26 05:45:18,352 INFO sqlalchemy.engine.base.Engine COMMIT
2017-05-26 05:45:19,397 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-05-26 05:45:52,654 INFO sqlalchemy.engine.base.Engine INSERT INTO hubway_db (tripduration, starttime, stoptime, "start station id", "start station name", "start station latitude", "start station longitude", "end station id", "end station name", "end station latitude", "end station longitude", bikeid, usertype, "birth year", gender) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2017-05-26 05:45:52,763 INFO sqlalchemy.engine.base.Engine ((542L, u'2015-01-01 00:21:44', u'2015-01-01 00:30:47', 115L, u'Porter Square Station', 42.387995000000004, -71.119084, 96L, u'Cambridge Main Library at Broadway / Trowbridge St', 42.373379, -71.111075, 277L, u'Subscriber', u'1984', 1L), (438L, u'2015-01-01 00:27:03', u'2015-01-01 00:34:21', 80L, u'MIT Stata Center at Vassar St / Main St', 42.3619622, -71.0920526, 95L, u'Cambridge St - at Columbia St / Webster Ave', 42.372969, -71.094445, 648L, u'Subscriber', u'1985', 1L), (254L, u'2015-01-01 00:31:31', u'2015-01-01 00:35:46', 91L, u'One Kendall Square at Hampshire St / Portland St', 42.366277000000004, -71.09169, 68L, u'Central Square at Mass Ave / Essex St', 42.36507, -71.1031, 555L, u'Subscriber', u'1974', 1L), (432L, u'2015-01-01 00:53:46', u'2015-01-01 01:00:58', 115L, u'Porter Square Station', 42.387995000000004, -71.119084, 96L, u'Cambridge Main Library at Broadway / Trowbridge St', 42.373379, -71.111075, 1307L, u'Subscriber', u'1987', 1L), (735L, u'2015-01-01 01:07:06', u'2015-01-01 01:19:21', 105L, u'Lower Cambridgeport at Magazine St/Riverside Rd', 42.356953999999995, -71.113687, 88L, u'Inman Square at Vellucci Plaza / Hampshire St', 42.374035, -71.101427, 177L, u'Customer', u'1986', 2L), (311L, u'2015-01-01 01:28:27', u'2015-01-01 01:33:38', 88L, u'Inman Square at Vellucci Plaza / Hampshire St', 42.374035, -71.101427, 76L, u'Central Sq Post Office / Cambridge City Hall at Mass Ave / Pleasant St', 42.366426000000004, -71.10549499999999, 685L, u'Subscriber', u'1989', 1L), (1259L, u'2015-01-01 01:34:54', u'2015-01-01 01:55:54', 91L, u'One Kendall Square at Hampshire St / Portland St', 42.366277000000004, -71.09169, 118L, u'Linear Park - Mass. Ave. at Cameron Ave. ', 42.397828000000004, -71.130516, 940L, u'Subscriber', u'1964', 1L), (338L, u'2015-01-01 02:32:35', u'2015-01-01 02:38:13', 68L, u'Central Square at Mass Ave / Essex St', 42.36507, -71.1031, 95L, u'Cambridge St - at Columbia St / Webster Ave', 42.372969, -71.094445, 656L, u'Subscriber', u'1981', 1L)  ... displaying 10 of 1214424 total bound parameter sets ...  (241L, u'2016-10-31 18:03:48', u'2016-10-31 18:07:49', 114L, u'Teele Square at 239 Holland St', 42.402763, -71.126908, 100L, u'Davis Square', 42.396969, -71.123024, 883L, u'Subscriber', u'1989', 2L), (1026L, u'2016-10-26 12:21:48', u'2016-10-26 12:38:55', 11L, u'Longwood Ave / Binney St', 42.338629, -71.1065, 15L, u'Harvard Real Estate - Brighton Mills - 370 Western Ave', 42.361667, -71.13802, 1866L, u'Subscriber', u'1973', 1L))
2017-05-26 05:49:15,352 INFO sqlalchemy.engine.base.Engine COMMIT

Testing table with a sort by the starting station

In [5]:

for start in c.execute('''SELECT DISTINCT "start station id", "start station name" FROM hubway_db ORDER BY "start station id"'''):
        print start

(1, u'18 Dorrance Warehouse')
(3, u'Colleges of the Fenway')
(4, u'Tremont St. at Berkeley St.')
(5, u'Northeastern U / North Parking Lot')
(6, u'Cambridge St. at Joy St.')
(7, u'Fan Pier')
(8, u'Union Square - Brighton Ave. at Cambridge St.')
(9, u'Agganis Arena - 925 Comm Ave.')
(10, u'B.U. Central - 725 Comm. Ave.')
(11, u'Longwood Ave / Binney St')
(12, u'Ruggles Station / Columbus Ave.')
(13, u'Boston Medical Center -  East Concord at Harrison Ave')
(14, u'HMS / HSPH - Ave. Louis Pasteur at Longwood Ave.')
(15, u'Harvard Real Estate - Brighton Mills - 370 Western Ave')
(16, u'Back Bay / South End Station')
(17, u'Harvard University Housing - 111 Western Ave. at Soldiers Field Park ')
(19, u'Buswell St. at Park Dr.')
(20, u'Aquarium Station - 200 Atlantic Ave.')
(21, u'Prudential Center / Belvidere')
(22, u'South Station - 700 Atlantic Ave.')
(23, u'Mayor Martin J Walsh - 28 State St')
(24, u'Seaport Square - Seaport Blvd. at Boston Wharf')
(25, u'Tremont St / W Newton St')
(26, u'Washington St. at Waltham St.')
(27, u'Roxbury Crossing Station')
(29, u'Innovation Lab - 125 Western Ave. at Batten Way')
(30, u'Brigham Cir / Huntington Ave')
(31, u'Seaport Hotel')
(32, u'Landmark Centre')
(33, u'Kenmore Sq / Comm Ave')
(35, u'Franklin St. / Arch St.')
(36, u'Boston Public Library - 700 Boylston St.')
(37, u'New Balance - 20 Guest St.')
(38, u'TD Garden - Causeway at Portal Park #2')
(39, u'Washington St. at Rutland St.')
(40, u'Lewis Wharf - Atlantic Ave.')
(41, u"Packard's Corner - Comm. Ave. at Brighton Ave.")
(42, u'Boylston St. at Arlington St.')
(43, u'Rowes Wharf - Atlantic Ave')
(44, u'Faneuil Hall - Union St. at North St.')
(45, u'Yawkey Way at Boylston St.')
(46, u'Christian Science Plaza')
(47, u'Cross St. at Hanover St.')
(48, u'Post Office Square')
(49, u'Stuart St. at Charles St.')
(50, u'Boylston St / Berkeley St')
(51, u'Washington St. at Lenox St.')
(52, u'Newbury St / Hereford St')
(53, u'Beacon St / Mass Ave')
(54, u'Tremont St / West St')
(55, u'Boylston / Mass Ave')
(56, u'Dudley Square')
(57, u'Columbus Ave. at Mass. Ave.')
(58, u'The Esplanade - Beacon St. at Arlington St.')
(59, u'Chinatown Gate Plaza - Surface Rd. at Beach St.')
(60, u'Charles Circle - Charles St. at Cambridge St.')
(61, u'Boylston at Fairfield')
(63, u'Dorchester Ave. at Gillette Park')
(64, u'Congress / Sleeper')
(65, u'Boston Convention & Exhibition Center')
(66, u'Allston Green District - Commonwealth Ave & Griggs St')
(67, u'MIT at Mass Ave / Amherst St')
(68, u'Central Square at Mass Ave / Essex St')
(69, u'Coolidge Corner - Beacon St @ Centre St')
(70, u'Harvard Kennedy School at Bennett St / Eliot St')
(71, u'Conway Park - Somerville Avenue')
(72, u'One Broadway / Kendall Sq at Main St / 3rd St')
(73, u'Harvard Square at Brattle St / Eliot St')
(74, u'Harvard Square at Mass Ave/ Dunster')
(75, u'Lafayette Square at Mass Ave / Main St / Columbia St')
(76, u'Central Sq Post Office / Cambridge City Hall at Mass Ave / Pleasant St')
(77, u'Somerville City Hall')
(78, u'Union Square - Somerville')
(79, u'Beacon St at Washington / Kirkland')
(80, u'MIT Stata Center at Vassar St / Main St')
(81, u'Boylston St / Washington St')
(82, u'Washington Square at Washington St. / Beacon St.')
(84, u'CambridgeSide Galleria - CambridgeSide PL at Land Blvd')
(85, u'Spaulding Rehabilitation Hospital - Charlestown Navy Yard')
(86, u'Brookline Village - Pearl Street @ MBTA')
(87, u'Harvard University Housing - 115 Putnam Ave at Peabody Terrace')
(88, u'Inman Square at Vellucci Plaza / Hampshire St')
(89, u'Harvard Law School at Mass Ave / Jarvis St')
(90, u'Lechmere Station at Cambridge St / First St')
(91, u'One Kendall Square at Hampshire St / Portland St')
(92, u'UMass Boston Integrated Sciences Complex')
(93, u'JFK / UMASS at MBTA Station')
(94, u'Charlestown - Main St at Austin St')
(95, u'Cambridge St - at Columbia St / Webster Ave')
(96, u'Cambridge Main Library at Broadway / Trowbridge St')
(97, u'Harvard University River Houses at DeWolfe St / Cowperthwaite St')
(98, u'Charlestown - Warren St at Chelsea St')
(99, u'Wilson Square')
(100, u'Davis Square')
(102, u'Powder House Circle - Nathan Tufts Park')
(103, u'JFK Crossing at Harvard St. / Thorndike St.')
(104, u'Harvard University Radcliffe Quadrangle at Shepard St / Garden St')
(105, u'Lower Cambridgeport at Magazine St/Riverside Rd')
(106, u'Mt Pleasant Ave / Dudley Town Common')
(107, u'Ames St at Main St')
(108, u'Harvard University / SEAS Cruft-Pierce Halls at 29 Oxford St')
(109, u'TD Garden - Causeway at Portal Park #1')
(109, u'TD Garden - West End Park')
(110, u'Harvard University Gund Hall at Quincy St / Kirkland S')
(111, u'Packard Ave / Powderhouse Blvd')
(112, u'Somerville Hospital at Highland Ave / Crocker St')
(113, u'Andrew Station - Dorchester Ave at Humboldt Pl')
(114, u'Teele Square at 239 Holland St')
(115, u'Porter Square Station')
(116, u'359 Broadway - Broadway at Fayette Street')
(117, u'Binney St / Sixth St')
(118, u'Linear Park - Mass. Ave. at Cameron Ave. ')
(119, u'South Boston Library - 646 East Broadway')
(120, u'Charles St at Beacon St')
(121, u'West Broadway at Dorchester St')
(122, u'BIDMC - Brookline at Burlington St')
(123, u'JP Center - Centre Street at Myrtle Street')
(124, u'Curtis Hall at South Street')
(125, u'Hyde Square at Barbara St')
(126, u'Egleston Square at Columbus Ave')
(128, u'E. Cottage St at Columbia Rd')
(129, u'Hayes Square at Vine St.')
(130, u"Upham's Corner - Ramsey St at Dudley St")
(130, u"Upham's Corner - Columbia Rd")
(131, u'Jackson Square T at Centre St')
(132, u'Summer St at Cutter St')
(133, u'Green St T')
(134, u'New Balance Store - Boylston at Dartmouth')
(135, u'ID Building East')
(136, u'ID Building West')
(137, u'Magoun Square at Trum Field')
(138, u'Mass Ave at Newmarket Square')
(139, u'Dana Park')
(140, u'Danehy Park')
(141, u'Kendall Street')
(142, u'Alewife Station at Russell Field')
(143, u'EF - North Point Park')
(145, u"Rindge Avenue - O'Neill Library")
(146, u'Day Boulevard')
(149, u'Harvard University Transportation Services - 175 North Harvard St')
(150, u'State Street at Channel Center')
(151, u'John F Fitzgerald - Surface Road at India Street')
(152, u'Ink Block')
(153, u'8D OPS 03')
(158, u'8D OPS 01')
(159, u'Heath St at South Huntington')
(160, u'Wentworth Institute of Technology')
(161, u'West Broadway at D Street')
(162, u'Franklin Park - Seaver Street at Humbolt Ave')
(163, u'Lawn on D')
(167, u'Ryan Playground - Dorchester Avenue Station')
(169, u'Edwards Playground - Main Street & Eden Street')
(170, u'Franklin Park Zoo')
(171, u'Bunker Hill Community College')
(173, u'Savin Hill MBTA Station')
(174, u'Washington St at Brock St')
(175, u'Brighton Center')
(176, u'Lesley University')
(177, u'University Park')
(178, u'MIT Pacific St at Purrington St')
(179, u'MIT Vassar St')
(180, u'Mt Auburn')
(183, u'Alewife MBTA at Steel Place')
(184, u'Sidney Research Campus/ Erie Street at Waverly')
(185, u'Third at Binney')
(186, u'Congress St and Northern Ave')
(189, u'Kendall T')
(190, u'Nashua Street at Red Auerbach Way')
(192, u'Purchase St at Pearl St')
(193, u'Brookline Village - Station Street @ MBTA')
(194, u'Broadway St at Mt Pleasant St')
(195, u'Brian P. Murphy Staircase at Child Street')
(196, u'Roxbury YMCA')
(197, u'MLK Blvd at Washington St')
(199, u"Upham's Corner T Stop")
(200, u'Washington St at Melnea Cass Blvd')
(201, u'Walnut Ave at Crawford St')
(202, u'Grove Hall Library')
(203, u'Columbia Rd at Ceylon St')
(204, u'Walnut Ave at Warren St')
(205, u'Bowdoin St at Quincy St')
(207, u'Market St at Faneuil St')
(208, u'Oak Square YMCA')
(209, u'Chelsea St at Saratoga St')
(210, u'Bennington St at Byron St')
(211, u'Piers Park- Marginal St at East Boston Shipyard')
(212, u'Maverick Sq - Lewis Mall')
(213, u'EBNHC - 20 Maverick Sq')
(214, u'Airport T Stop - Bremen St at Brooks St')
(215, u'The Eddy at New Street')
(216, u'Glendon St at Condor St')
(217, u'Orient Heights T Stop - Bennington St at Saratoga St')
(218, u'Watermark Seaport')

In [ ]:

engine.close()

Relative to a laptop's resources, the database file is ~1.2M records 233 Mb is stored locally, but runs slowly and lags system.

Back to Executive Summary