Publish a tableau visualisation for ocm-data

[webprofusion-chrisc]

As per https://www.hughsheehy.com/reading/worldwide-ecar-charging-points it would be good to use tableau to make a public visualisation of the most recent data and automatically keep it up to date.

It's useful to be able to dice and slice the data for general statistics and spotting obvious errors.

Originally posted by @hughsheehy in https://github.com/openchargemap/ocm-data/issues/1#issuecomment-685517305

[hughsheehy]

As per https://www.hughsheehy.com/reading/worldwide-ecar-charging-points it would be good to use tableau to make a public visualisation of the most recent data and automatically keep it up to date.

It's useful to be able to dice and slice the data for general statistics and spotting obvious errors.

Originally posted by @hughsheehy in #1 (comment)

Well, for the moment I'm intending to keep it up to date every couple of weeks. But yes, it'd be good to make it autoupdate. I haven't really looked into doing that yet. The charts so far were/are just "quick and dirty" versions.

[webprofusion-chrisc]

@hughsheehy thanks, taking inspiration from your own efforts it would be great for us to have a simple public tableau offering we can embed, that takes pressure off our API for general map browsing and opens up the possibility for people interested in statistics to dive in and remix it. I've previously used Spotfire, Power BI and Tableau (years ago) but hadn't realised Tableau now had a free version.

[webprofusion-chrisc]

Broadly, our users are interested in distribution by Connector Type, Network Operator, Power Level (kw) and AC/DC. It would be ideal if points could hyperlink back to the full details for view/edit.

[webprofusion-chrisc]

@hughsheehy incidentally for your version you should filter POIs on SubmissionStatusTypeID IN 100, 200 as other status types represent data that's either not published or has been delisted but is still present in the raw data set (which we occasionally prune),

[hughsheehy]

@hughsheehy incidentally for your version you should filter POIs on SubmissionStatusTypeID IN 100, 200 as other status types represent data that's either not published or has been delisted but is still present in the raw data set (which we occasionally prune),

Done. Thanks.

[hughsheehy]

Broadly, our users are interested in distribution by Connector Type, Network Operator, Power Level (kw) and AC/DC. It would be ideal if points could hyperlink back to the full details for view/edit.

I've added those filters to my version.

I THINK you could have a link back by having a URL for each charging point in the JSON and including it as a tooltip. I haven't tried that. I'm not sure there's a URL in the data set. Is there?

Also, I THINK that Tableau could connect to an online JSON version of the data, maybe stored on S3 or something similar. I tried just now to connect to the file on GitHub but Tableau and GitHub don't play nice. Not something that I would do for more than a test.

Anyway, you should be able to try that if you like. You should be able to just copy my tableau file and start from there. I'm trying with https://hughsheehyopenchargemap.s3-eu-west-1.amazonaws.com/poi.json but it is SLOOOOOW.

[webprofusion-chrisc]

Thanks yes I've been playing with the file from your version and I can get a hyperlink to work using the Worksheet > Actions https://openchargemap.org/site/poi/details/<ID> - that's good enough to be able to link back and see why something is in the wrong place and edit it.

The json data on github is gzipped and uncompressed its 397MB so linking directly probably isn't required.

[hughsheehy]

Great.

Lemme know how it goes!

[hughsheehy]

Broadly, our users are interested in distribution by Connector Type, Network Operator, Power Level (kw) and AC/DC. It would be ideal if points could hyperlink back to the full details for view/edit.

I've added those filters to my version.

I THINK you could have a link back by having a URL for each charging point in the JSON and including it as a tooltip. I haven't tried that. I'm not sure there's a URL in the data set. Is there?

Also, I THINK that Tableau could connect to an online JSON version of the data, maybe stored on S3 or something similar. I tried just now to connect to the file on GitHub but Tableau and GitHub don't play nice. Not something that I would do for more than a test.

Anyway, you should be able to try that if you like. You should be able to just copy my tableau file and start from there. I'm trying with https://hughsheehyopenchargemap.s3-eu-west-1.amazonaws.com/poi.json but it is SLOOOOOW.

Just to confirm that Tableau didn't connect to a JSON file stored on S3. At least not for me.

So, for the moment, my process is going to be;

1. Download and unzip the poi.json.gz
2. Update the report
3. Save to Tableau Public.

[webprofusion-chrisc]

note that git clone (and subsequently git pull) may or may not be faster than downloading but if you were automating anything then that would be the way to go. I wonder if there are any command line arguments for tableau (or an API) that would let us update the underlying data automatically without launching the UI? I suspect they'd prefer people paid a server license for that sort of thing. I remember when Spotfire cost something like $250K annually!

[hughsheehy]

Fair point re the download.

On the command line utility, there is one but it only works with a licenced version of Tableau (https://help.tableau.com/current/pro/desktop/en-us/extracting_TDE.htm#:~:text=You%20can%20automate%20extract%20refresh,to%20them%20from%20a%20file.)

I think a licence for Tableau Desktop only costs $70/user/month so it's not THAT expensive. I have no need for one.

[webprofusion-chrisc]

Ha yeah, it's not so expensive - except that our budget is zero :) that's ok though if we do use it for a visualisation we can just refresh it periodically.

[hughsheehy]

One other thing, looking at the aggregate data, can it really be true that there's so little installed capacity in Asia? Or even NA? Compared to the EU they both have smaller numbers than I expected.

[webprofusion-chrisc]

Nope, it's absolutely that we don't have the data. We're not especially popular in Asia currently and have virtually no data or contributors.

[webprofusion-chrisc]

For North America we could be slightly under but I don't think we're massively wrong as we take a feed from the AFDC.

[hughsheehy]

Similarly, looking at aggregate data, can the UK be so far ahead of other countries in Europe?

[webprofusion-chrisc]

@hughsheehy it's dangerous to derive statistical facts from incomplete data, and our dataset like every other is most certainly not complete.

Keep in mind that if we don't have a powerkw value against an equipment item, that doesn't mean it has no power, it means the data has not been populated. You could possibly extrapolate approximate power from connection types etc but you also need to multiply available equipment quantity (so a site may have 4 x CCS @ 40kW, but another site may have an unknown number of equipment - we assume 1 but there could be 6 units and we just don't know that). Data quality varies depending on how it is sourced and for imports it depends on both the quality of the source data and the accuracy/ability of the data import to determine power.

[hughsheehy]

Thanks yes I've been playing with the file from your version and I can get a hyperlink to work using the Worksheet > Actions https://openchargemap.org/site/poi/details/<ID> - that's good enough to be able to link back and see why something is in the wrong place and edit it.

The json data on github is gzipped and uncompressed its 397MB so linking directly probably isn't required.

Any progress?

[hughsheehy]

Just an update from here. We used deck.gl and Google Maps to make an alternate visualization. Have a look. https://www.hughsheehy.com/reading/deck-gl-map