meljameson
commented
3 years ago Need a comprehensive list of data needing to be updated automatically in the Tableau frontends
Open meljameson opened 3 years ago
meljameson
commented
3 years ago Need a comprehensive list of data needing to be updated automatically in the Tableau frontends
meljameson
commented
3 years ago Per Michael:
My viz has a complicated data structure combining two sets of geometries (roadway links, ANC single-member district boundaries) with the crashes data. I believe the roads and boundaries don't change often so really it's only the crashes data including Block Key and SMD_Id that need to be refreshed and available via the web data connector. I think. I might not be able to mix and match data sources. I assume the web data connector can also serve up geometries?
banjtheman
commented
3 years ago Was able to create a connector using february_2021_crashes.csv
I hosted the connector on one of my cloud machines at this URL: http://35.170.77.227:8888/dc_crash_bot.html
With that was able to load the data into Tableau public
Creating a connector was pretty straight forward following this guide https://tableau.github.io/webdataconnector/docs/wdc_tutorial.html
Here is the main code for the connector
HTML: https://github.com/CharlotteJackson/DC_Crash_Bot/blob/tableau_web_connector/tableau/webdataconnector/dc_crash_bot.html JavaScript: https://github.com/CharlotteJackson/DC_Crash_Bot/blob/tableau_web_connector/tableau/webdataconnector/dc_crash_bot.js
banjtheman
commented
3 years ago Follow on tasks/questions,
eichler4dc
commented
3 years ago this is great! The data fields I need in the web data connector include:
Banjo, the data that you made available through the web data portal seems to have fields generated by Charlotte. Is it baed on a CSV? Can we connect it to pull data live from Charlotte's geodatabase? That would be idea!
Let me do a bit more research on the fields i'm actually using and get back to you.
CharlotteJackson
commented
3 years ago Hey guys! So the February_2021_crashes.csv file is still a preliminary table format I'm experimenting with that combines the DC open data crashes dataset and the Pulsepoint data. The dataset that I think is best to feed into Michael's existing dashboard is the table analysis_data.dc_crashes_w_details. I've saved a sample of the top 1000 rows from that table in the Data folder in this repo as analysis_data_dc_crashes_w_details.csv. Michael, can you start by just checking that csv file to see if it has what you need? If yes we can talk about connecting it directly in to the database!
eichler4dc
commented
3 years ago this query populates my viz with all the data I am using right now. Would be great to get a WDC to pull this directly from the dc_crashes_w_details table.
select reportdate, bicycle_fatalities as fatal_bicyclist, vehicle_fatalities as fatal_driver, pedestrian_fatalities as fatal_pedestrian, total_bicyclists as total_bicycles, total_pedestrians, drivers_impaired as driversimipared, drivers_speeding as speeding_involved, blockkey, 0 fatalpassenger, left(smd_id, 2) anc_id, smd_id from analysis_data.dc_crashes_w_details where reportdate between date '2015-01-01' and current_date order by reportdate desc
in the meantime, i've updated the viz to pull from a google spreadsheet. I understand that the Tableau Public server can pull data nightly from a google sheet. Does anyone know how to populate a google sheet from a PostgreSql database?
banjtheman
commented
3 years ago Ok for our next meeting, will work on creating the WDC based on the query
We can prob make a script to upload data from the database to a google sheet, I suggest making a new ticket for that task
eichler4dc
commented
3 years ago Per our chat tonight, I think scripting updates of the google sheet might be the best way to get the data into the free tableau Public data viz platform.
On Sun, Apr 4, 2021 at 7:03 PM Banjo Obayomi @.***> wrote:
Ok for our next meeting, will work on creating the WDC based on the query
We can prob make a script to upload data from the database to a google sheet, I suggest making a new ticket for that task
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/CharlotteJackson/DC_Crash_Bot/issues/56#issuecomment-813112609, or unsubscribe https://github.com/notifications/unsubscribe-auth/ATJORJQ5G3XMLYMRVFAE2G3THDV3ZANCNFSM4Z2NZ6DA .
eichler4dc
commented
3 years ago the WDC was created and working up until recently: http://35.170.77.227:8888/dc_crash_bot.html
I am now getting an error when trying to hit this URL. @banjtheman can you investigate?
banjtheman
commented
3 years ago O I had to shut down my personal instance, We can rehost the app on one of the ec2 instances for this project @CharlotteJackson can you help me to migrate the app
eichler4dc
commented
3 years ago thanks for the update. we can have a conversation about supporting Tableau in our environment. It's super low barrier to entry but does require setting up these data connectors for each data object desired. I'd love to learn how to set up a WDC myself. Banjo, can we work together to document the instructions? I also haven't been able to confirm that they auto-refresh yet. I've been logging in weekly to refresh the dataset. I was thinking maybe it was because I was merging WDC and static data.
banjtheman
commented
3 years ago Yea I can create a guide on creating
For now I reuploaded the server to my free micro instance, pretty easily
What is the Task
The current traffic data frontends are made from static Open DC data in Tableau. We'd like to have a Tableau web connector to feed new data into the Tableau UIs automatically.
Why do we want to do this
Data is currently downloaded and combined by hand which is tedious and time consuming.
How can I get started?
https://public.tableau.com/en-us/s/blog/2017/07/how-create-web-data-connector-beginners-approach Thinking we can pull data on a regular schedule and update dashboards with new data based on timestamp.
Definition of Done
Tableau dashboards should update automatically.