In [ ]:

    

# For staff outside London, they 
# Pick a random sample of UK postcodes.
import pandas as pd


    

In [ ]:

    

# get secret key
from mylibrary.secrets import app_id, app_key, orig_name, dest_name, orig_pc, dest_pc
from mylibrary.connections import cursor, conn, engine, Automapped_Base, session


    

In [ ]:

    

sql = """
select  s.postcode, s.geom, g.name from tt_gh.staff_locations as s, tt_gh.gb_and_london as g
where st_contains(g.geom,s.geom)  and g.name='london'
"""
london_staff = pd.read_sql(sql, conn)
london_staff.sample(5)
print(len(london_staff))


    

In [ ]:

    

# RUNNING THIS WILL DELETE ALL YOUR JOURNEYTIME RESULTS!!!

# for name in (orig_name, dest_name):
    
#     sql = """
#     drop table if exists tt_gh.london_journeytime_{name};
    
#     create table tt_gh.london_journeytime_{name} as
#     select row_number() over () as id, s.postcode, s.geom from tt_gh.staff_locations as  s, tt_gh.gb_and_london as g
#     where st_contains(g.geom,s.geom) and g.name = 'london'
#     """.format(name = name)
#     cur = conn.cursor()
#     cur.execute(sql)
#     conn.commit()

#     sql = """
#     ALTER TABLE tt_gh.london_journeytime_{name} 
#     ADD tfl_request text, ADD tfl_response json,
#     ADD querydict json,
#     ADD journey_time int,
#     ADD tfl_message text;
   
#     ALTER TABLE tt_gh.london_journeytime_{name} ADD PRIMARY KEY (id);

#     """.format(name=name)
#     cur = conn.cursor()
#     cur.execute(sql)
#     conn.commit()


    

In [ ]:

    

from sqlalchemy import or_
from mylibrary.tfl_helpers import get_journeyplanner_raw_content, status_of_tfl_response_journey, get_journeyplanner_results

London_orig = Automapped_Base.classes.london_journeytime_stpauls
London_dest = Automapped_Base.classes.london_journeytime_camden


    

In [ ]:

    

# Multithread 
import threading
num_threads = 10

from sqlalchemy import or_


from sqlalchemy import create_engine
engine = create_engine('postgresql://postgres:@localhost:5432/postgres') 

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine, MetaData



London = Automapped_Base.classes.london_journeytime_camden
ids  = session.query(London.id).all()

counter = 1
def new_thread():
    
    param_dict = {
    "to": orig_pc.replace(" ",""),
    "id": app_id,
    "key": app_key,
    "time": "1000", 
    "date": "20160517",
    "nationalsearch": False
    }

    param_dicts = []
    param_dicts.append(param_dict)

    d1 = param_dict.copy()
    d1["time"] = "1500"
    d1["date"] = "20160518"
    param_dicts.append(d1)

    d2 = param_dict.copy()
    d2["time"] = "1600"
    d2["date"] = "20160519"
    param_dicts.append(d2)

    global counter
    engine = create_engine('postgresql://postgres:@localhost:5432/postgres') 
    m = MetaData(schema='tt_gh')
    Automapped_Base = automap_base(bind=engine,metadata=m)
    Automapped_Base.prepare(engine, reflect=True)
    session = Session(engine)

    London = Automapped_Base.classes.london_journeytime_camden
    
    

    while len(ids) > 0:
        id = ids.pop()
        station = session.query(London).filter(London.id == id).one()
        
        for d in param_dicts:
            d["from"] = station.postcode
            if "stpauls" in London.__table__.name:
                d["to"] = orig_pc.replace(" ","")
            if "camden" in London.__table__.name:
                d["to"] = dest_pc.replace(" ","")
                
        journey_object = get_journeyplanner_results(param_dicts)

        station.tfl_request = journey_object["request_url"]
        station.tfl_response = journey_object["tfl_response"]
        station.querydict = journey_object["query_dict"]
        station.tfl_message = journey_object["tfl_message"]

        session.add(station)
        session.commit()
        
        counter +=1
        if counter % 10 == 0:
            print counter
 
my_threads = []

for i in range(num_threads):
    t = threading.Thread(target=new_thread)
    my_threads.append(t)
    t.start() #start this thread

for x in my_threads:
    x.join()


    

In [ ]:

    

# Finally pull out journeytime
def get_duration(station):
    tfl_response_json = json.loads(station.tfl_response)
    return_object = {}
    return_object["duration"] = tfl_response_json["journeys"][0]["duration"]
    return return_object 

import json


for London in (London_orig, London_dest):
    ids = session.query(London.id).filter(London.tfl_message == "ok").all()
    for id in ids:
        try:
            station = session.query(London).filter(London.id == id).one()
            journey = get_duration(station)
            station.journey_time = journey["duration"]
            session.add(station)
            session.commit()
        except:
            pass


    

In [ ]: