Set up internal dashboard to run based on open data

[radumas]

In order for the community to fork and improve the code, they should be able to run the dashboard based on the data from open data
I would recommend trying to build off of the internal_dash_open_data branch, which has fewer features, but also means that the data transformations are fewer to make
https://github.com/CityofToronto/bdit_king_pilot_dashboard/tree/internal_dash_open_data

[radumas]

Relevant SQL for views which needs to be transformed to be based on OpenData

• 30 minute travel time aggregation, from 5-min aggregate data which gets fed into dash daily
• dash_daily which gets pulled by the app

The below table definition should match the detailed bluetooth data.

CREATE TABLE bluetooth_data (
result_id TEXT,     
        datetime_bin timestamp without time zone,
        day_type text, 
        category text,
        period_name text,
        tt integer,
        obs integer

)

[Fullchee]

Here's my incomplete instructions to try to get python app.py working

Setup

Database Setup

1. Setup PostgreSQL and have an instance running.
2. In the project root directory, create a file called db.cfg with the following content

[DBSETTINGS]
database={{user name}}
user={{user name}}

The user name should your PostgreSQL username
3. In the data_pipeline branch, in the bluetooth/sql folder, it has all of the SQL commands to create the tables, functions and views that you need.
4. ????

Python Setup

1. pip install pipenv
2. pipenv install

[radumas]

To clarify the aggregations that are happening from the 5-min data:

1. Travel times are averaged per 30-minute bin for each bluetooth segment.
2. Travel times are averaged per time period, date, and bluetooth segment
3. Travel times are summed by corridor. At this point the data is in dash_daily, which gets pulled by the Dash app
4. Baseline Only: Travel times are averaged by day type, and time period over the baseline period.

In order to aggregate by street and direction, need to join 5-min data with the bluetooth segments GIS layer. I'm working on converting this GIS layer to a csv so it will be easier to import into PostgreSQL

[radumas]

I've attached a zipped csv of the segments.

CREATE TABLE bluetooth_gis
(
    segment_name text,
    street text ,
    direction text ,
    from_intersection text ,
    to_intersection text ,
    length bigint
)

bluetooth_geom.zip