OPS CodeDay: Dash Plotly Map + Graph

from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot
import plotly.graph_objs as go
init_notebook_mode(connected=True)

from psycopg2 import connect
import psycopg2.sql as pgsql
con = connect(database='bigdata')

import pandas
import pandas.io.sql as pandasql
import sqlalchemy

bt_2017 = pandas.read_csv('bt_2017.csv')
bt_2017

bt_2017['observed_time'] = bt_2017['updated'].str.replace('T', ' ')

del bt_2017['updated']

bt_2017.to_csv('bt_2017_fixed.csv', index=False)

weekday_avg_sql = pgsql.SQL('''
SELECT updated::TIME AS "Time", AVG(timeinseconds)
FROM bluetooth.bt_2017
WHERE resultid = {resultid} and EXTRACT('isodow' FROM updated) <6
GROUP BY "Time" 
ORDER BY "Time" ''')

richmond_jan = pandasql.read_sql(weekday_avg_sql.format(resultid=pgsql.Literal('BR2_BR3')), con)

trace = go.Scatter(x=richmond_jan['Time'],
                   y=richmond_jan['avg'],
                   mode='lines')
layout = dict(title = "Average Weekday Travel Times",
              xaxis = dict(title="Time of Day"),
              yaxis = dict(title="Travel Time (s)"))

iplot(dict(data=[trace], layout=layout))

import dash
from dash.dependencies import Input, Output
import dash_core_components as dcc
import dash_html_components as html

app = dash.Dash()

app.layout = html.Div(children=[dcc.Graph(id='travel-time-graph',
                                          figure=dict(data=[trace], layout=layout))])

if __name__ == '__main__':
    app.run_server(debug=True)

geometry_sql = pgsql.SQL('''SELECT resultid, ST_ASGeoJSON(geom) geojson
FROM bluetooth.routes
WHERE resultid = {resultid} ''')

resultid = pgsql.Literal('BR2_BR3')

richmond_df = pandasql.read_sql(geometry_sql.format(resultid=resultid), con)

import json

row = richmond_df.iloc[[0]]

# row['geojson'][0]
geojson = json.loads(row.geojson)
geojson['coordinates'][0]

def get_lat_lon(geojson):
    lons, lats = [], []
    for coord in geojson['coordinates']:
        lons.append(coord[0])
        lats.append(coord[1])
    return lats, lons

segments = []

for row in richmond_df.itertuples():
    geojson = json.loads(row.geojson)
    lats, lons = get_lat_lon(geojson)
    segments.append( dict(type = 'scattergeo',
                          lon = lons, lat = lats,
                          mode = 'lines',
                          line = dict(width = 2)
                         ))

layout = dict(
        title = 'Bluetooth segments',
        showlegend = False,
        geo = dict(
            resolution=50,
            showland = True,
            showlakes = True,
            showcountries = True,
            scope = "North America",
            projection = dict(type = "mercator"),
            lonaxis = dict( range = [-79.39, -79.36]),
            lataxis = dict( range = [43.64, 43.66])
        )
    )
iplot(dict(data=segments,layout=layout))

mapbox_token = 'pk.eyJ1IjoicmVtb3RlZ2VudHJpZnkiLCJhIjoiY2lnanJzMjJpMDA1dnYxbHo5MTZtdGZsYSJ9.gLE8d40zmDAtMSSZyd2h1Q'


data = go.Data([
    go.Scattermapbox(
        lat=lats,
        lon=lons,
        mode='lines',
        hoverinfo='text',
        showlegend=False
    )])
    
layout = go.Layout(
    title='Bluetooth Segments',
    autosize=True,
    hovermode='closest',
    mapbox=dict(
        accesstoken=mapbox_token,
        bearing=0,
        center=dict(
            lat=43.65,
            lon=-79.37
        ),
        pitch=0,
        zoom=12,
        style='light'
    )
)    

iplot(dict(data=data,layout=layout))