In [1]:
# create view tt.vw_change_in_journey_time_all as
import pandas as pd
from mylibrary.connections import cursor, conn, engine, Automapped_Base, session
sql = """
drop view if exists tt_gh.vw_change_in_journey_time_london;
create view tt_gh.vw_change_in_journey_time_london as
select o.postcode as postcode,
o.journey_time as original_journey_time, d.journey_time as new_journey_time,
d.journey_time - o.journey_time as change_in_journey_time,
p.geom
from tt_gh.london_journeytime_stpauls as o
left join tt_gh.london_journeytime_camden as d
on o.id=d.id
left join tt_gh.all_postcodes as p
on o.postcode = p.postcode
"""
cursor.execute(sql)
conn.commit()
In [2]:
sql = """
select postcode,original_journey_time,new_journey_time,change_in_journey_time,
ST_X(ST_TRANSFORM(geom, 4326)) as lng,
ST_Y(ST_TRANSFORM(geom,4326)) as lat
from tt_gh.vw_change_in_journey_time_london
where change_in_journey_time is not null
union all
select postcode,original_journey_time,new_journey_time,change_in_journey_time,
ST_X(ST_TRANSFORM(geom, 4326)) as lng,
ST_Y(ST_TRANSFORM(geom,4326)) as lat
from tt_gh.vw_change_in_journey_time_non_london
where change_in_journey_time is not null
"""
df = pd.read_sql(sql,conn)
df.to_csv("website/data/all_staff_journeytime_final.csv", index=False)
df.head()
Out[2]:
In [ ]: