The chain from reality to pretty graphs

[JourneymanWizard]

I am a hardware sort trying to get some practice going from sensor interactions to logs, reports, and graphs; I wanted to check here and see if there are points that could be simpler or if I am on the right path.

Assume a setup measuring a (binary) state  - whether a fan is on or off.  My end goal is that I can create a log of time on, time off, and a graph of on duration over a period that can be pulled up off of the LAN.

To this end, I think
Fan State ---> optical isolator ---> [ESP8266 module sending event over MQTT] ---> [Linux box / PI pushing MQTT to MySQL db, and querying database to construct reports] ---> Generated webpage on demand with state, last state change, and graph

Most concerning is how to get data (state change, but arbitrary small data sets like ADC values too) to the computer from the micro - is MQTT a good way that does not rely on external cloud services?

[rstofer]

Raspberry Pi comes with a lot of Python IO examples and updating a .db should be trivial.

[nfmax]

If you are using a Raspberry Pi, it can interface with an optocoupler directly via a GPIO pin plus pull-up resistor. Raspbian comes with a (rather old) installation of Node-RED, with which you can easily read the input pins, save to a database, and generate a graphical 'dashboard' including charts, all using a browser-based graphical IDE. it also speaks MQTT, but you may not need that.

[legacy]

graphical 'dashboard' including charts

I don't have difficulties in interfacing sensors to mySQL from a PHP/python/C module, but I might have to think about dashboard and charts: what do you use to generate them dynamically? and on which framework?

[nfmax]

graphical 'dashboard' including charts

I don't have difficulties in interfacing sensors to mySQL from a PHP/python/C module, but I might have to think about dashboard and charts: what do you use to generate them dynamically? and on which framework?

Node-RED is an Express application running on NodeJS. It serves both the IDE and the dashboard over HTTP. The IDE uses a whole bunch of frameworks. The dashboard uses AngularJS under the hood, and I think ChartFX. Generally, you don't need to know as you seldom need to write a single line of JavaScript to get an application working. Drag nodes from the palette onto the canvas, wire them up and fill in their propert dialogs. That will generally get you to 95% of a solution. Give it a try, free to download from nodered.org

[legacy]

supposing I don't want to use nodejs and javascript: alternatives?

[rstofer]

graphical 'dashboard' including charts

I don't have difficulties in interfacing sensors to mySQL from a PHP/python/C module, but I might have to think about dashboard and charts: what do you use to generate them dynamically? and on which framework?

When I did this a long time ago, I used JavaScript.  There was a C program running on a PC that queried a PLC network for power consumption across the campus.  This program posted the data to file or database on the web server.  The web server ran the JavaScript to format the page.  I wanted the appearance of meter dials so I had to use a separate library to implement that.

[Berni]

I have often found graphing a large amount of data to be annoying. Especially if it has to update in real time and be interactive.

So many times i went "Oh il just graph this file in Excel" only to deeply regret my decision an hour later. Some MATLAB-like tools are better at it but still annoying.

So far my favorite graphing tool is the .NET framework Chart control. Its pretty flexible and can make some handsome looking graphs if you tweak it enough, but still becomes slow as molasses on large data sets, so some downsampling is usually needed for when there are millions of points to show.

In your case since you are just logging it over a long period you could just make a program that turns your data into a PNG picture every so often and just host that using your webserver of choice. When the log gets really long you pretty much have to do stuff server side as you don't want to transfer over a few MB of data points to the browser and then have some not very optimized javascript framework comb trough all of that to draw a pretty graph.

[lwatts666]

Have a look at the D3.js library https://d3js.org . There are plenty of examples for visualizing data.

There is a nice example using MQTT and D3.js at https://www.hardill.me.uk/wordpress/2012/11/08/d3-mqtt-topic-tree-visualiser/ that draws a dynamic tree of all MQTT data available from the broker with the ability to collapse branches.

No association with the linked projects, just a user.

[nfmax]

supposing I don't want to use nodejs and javascript: alternatives?

You can generate your graphs on the server in any language you like using the W3C SVG standard, which any modern browser should be able to render. I don't know of any specific libraries to help, though. The problem is this will be static - it won't update as new data arrives, unless the user refreshes the page. Javascript on the browser lets you establish a live connection using Websockets, so that the graph updates automatically to new data. Browser side charting also tends to use the HTML Canvas element, which is generally considerably faster than SVG, and supports interaction like live zooming & panning, and data cursors. You could of course do the same sort of thing in Java or Flash, but they are becoming very rare on browsers now.

For the avoidance of doubt, it should be possible to implement the OP's requirement using Node-RED without writing a single line of Javascript.

[frozenfrogz]

To visualize data I would like to suggest checking out Processing.
It is also Java / Javascript though.

[nfmax]

Most concerning is how to get data (state change, but arbitrary small data sets like ADC values too) to the computer from the micro - is MQTT a good way that does not rely on external cloud services?

Yes: this is one of the design goals for MQTT. It does, however, require a broker, though this can be on the local computer. It also requires a networking stack on the micro, and some way to configure it to access the broker. The ESP8266 family of micros make this much simpler than it used to be, there are several FOSS projects around providing MQTT client implementations.