Anyone datalogging?

[PaddyB]

Anyone else recording data? For a while now I've been saving various bits to sqlite. Seems to be one of the easiest plus the database is just one file so you can copy it across to another machine to play with. Pus there's a PHP webpage available which makes it easy to  go in and set up or just  have a look 
Little bit of node red if anyone is inerested -

Code:

[
   {
       "id": "ab86fbfb.82f1c8",
       "type": "switch",
       "z": "f1a0cb4.fe86138",
       "name": "Is it eng data?",
       "property": "msg.payload.updates.values.path",
       "propertyType": "jsonata",
       "rules": [
           {
               "t": "cont",
               "v": "propulsion",
               "vt": "str"
           }
       ],
       "checkall": "false",
       "outputs": 1,
       "x": 180,
       "y": 240,
       "wires": [
           [
               "aaed95ff.c274e8"
           ]
       ]
   },
   {
       "id": "aaed95ff.c274e8",
       "type": "change",
       "z": "f1a0cb4.fe86138",
       "name": "create msg.eng etc",
       "rules": [
           {
               "t": "set",
               "p": "eng",
               "pt": "msg",
               "to": "msg.payload.updates.values[path=\"propulsion.engine.temperature\"].$number(value)-273.15",
               "tot": "jsonata"
           },
           {
               "t": "set",
               "p": "alt",
               "pt": "msg",
               "to": "msg.payload.updates.values[path=\"propulsion.alt.temperature\"].$number(value)-273.15",
               "tot": "jsonata"
           },
           {
               "t": "set",
               "p": "exh",
               "pt": "msg",
               "to": "msg.payload.updates.values[path=\"propulsion.exh.temperature\"].$number(value)-273.15",
               "tot": "jsonata"
           }
       ],
       "action": "",
       "property": "",
       "from": "",
       "to": "",
       "reg": false,
       "x": 190,
       "y": 280,
       "wires": [
           [
               "7a2b1aa3.f272f4"
           ]
       ]
   },
   {
       "id": "7a2b1aa3.f272f4",
       "type": "function",
       "z": "f1a0cb4.fe86138",
       "name": "create INSERT topic eng",
       "func": "// set flow variables to signalk data\nif (msg.eng !==undefined) {var eng = flow.set(\"eng\", msg.eng.toFixed(1));\n    //msg.topic= \"INSERT INTO engine  (eng) VALUES (\" + flow.get(\"eng\") + \" );\" ;\n}\nif (msg.alt !==undefined) {var alt = flow.set(\"alt\", msg.alt.toFixed(1));\n    //msg.topic= \"INSERT INTO engine  (alt) VALUES (\" + flow.get(\"alt\") + \" );\" ;\n}\nif (msg.exh !==undefined) {var exh = flow.set(\"exh\", msg.exh.toFixed(1));\n    //msg.topic= \"INSERT INTO engine  (exh) VALUES (\" + flow.get(\"exh\") + \" );\" ;\n}\n\n// create sqlite insert commend\nmsg.topic= \"INSERT INTO engine  (eng, alt, exh) VALUES (\" + flow.get(\"eng\") + \",\" + flow.get(\"alt\") + \",\" +  flow.get(\"exh\") + \" );\" ;\n//msg.topic= \"INSERT INTO engine  (eng) VALUES (\" + flow.get(\"eng\") + \" );\" ;\nreturn msg;\n   ",
       "outputs": "1",
       "noerr": 0,
       "x": 430,
       "y": 260,
       "wires": [
           [
               "c8b0c6b1.e1b078",
               "dc2bd84f.dcb9a8"
           ]
       ]
   },
   {
       "id": "c8b0c6b1.e1b078",
       "type": "delay",
       "z": "f1a0cb4.fe86138",
       "name": "",
       "pauseType": "rate",
       "timeout": "5",
       "timeoutUnits": "seconds",
       "rate": "1",
       "nbRateUnits": "1",
       "rateUnits": "minute",
       "randomFirst": "1",
       "randomLast": "5",
       "randomUnits": "seconds",
       "drop": true,
       "x": 430,
       "y": 300,
       "wires": [
           [
               "8d7a3b1d.824788"
           ]
       ]
   },
   {
       "id": "8d7a3b1d.824788",
       "type": "sqlite",
       "z": "f1a0cb4.fe86138",
       "mydb": "fe279cd9.bbe82",
       "name": "",
       "x": 690,
       "y": 300,
       "wires": [
           []
       ]
   },
   {
       "id": "fe279cd9.bbe82",
       "type": "sqlitedb",
       "z": "",
       "db": "/home/pi/database/boatdata.db"
   }
]

 I tried influxdb with grafana which was really good with lots of plotting options but just too big and hogged too much resources so that had to go.
What isn't going so well with sqlite is plotting - any one know of any python or PHP that would fit the bill? My programming skills aren't that far ahead of hello world   What would be fantastic would be to plot a days battery voltage over voltage from a week or month before to see if there's much difference and maybe get a bit of notice if they aren't so well. Already just seeing how quick the engine warms up and things like that has been really interesting. 
Such a powerful tool, a little Pi, openplotter and node red - systems into the thousands can't do a load of things which it can. 
Ta

[shark24]

Currently I'm logging data to an online MySQL database because the Pi is hooked up to the internet and I don't want to mess up it's SD with all the writes. I guess I will use SQLite like you when I'll install the Pi on the boat.

I'm with you on Grafana it's just a little bit too heavy for the cute RPi.

If the default plots of the node-red dashboard aren't good enough I would look up some javascript graphing libraries. There must be a way to implement this charts via the template node into the node-red dashboard. It might be helpful to get some insights on AngularJS which is used by the dashboard. If that's to complicated you can just use php to get the data out of the db in form of json and build your own little dashboard with a nice javascript plotting library.
I can't recommend you a specific library but I've used several without any problems. If you google it there are tons of sites with the X best libraries. You just need to try which one suits you best especially with your lets say fundamental programming skills
 
I don't know much about PHP plotting because online everyone would prefer javascript nowadays. Python has some mighty plotting libraries like matplotlib which I have used successfully some times but in the end python and I are not getting along. So if there is an alternative I personally would always use another path

Edit: a link on how to implement third party plotting libraries in node-red http://noderedguide.com/tutorial-node-re...al-charts/

[PaddyB]

Thanks , Shark.   I have some graphs already on node red for day to day use, one page reads from the database - very handy day to day but it would be nice to have something a bit more "heavyweight" to dig deeper.  

I've played around with highcharts in javascript and an exported csv file - very pretty but maybe not so good for very specific plots. Maybe it's lots of focused google time and get to grips better with either python and/or javascript, wouldn't really matter if some of the sql had to be done by hand I suppose. Anyway, graphing is an extremely powerful way to dig deep into whats going on with the boat and spotting trends - if I come up with anything which actually might be useful I'll put it up here.

Cheers

[shark24]

A few weeks ago I've tried to plot some time series data from SQL to node-red and was very unpleased by the results. So I came to the same conclusion as you, that I need some external plotting library but couldn't find the time to explore.
For a better understanding: how do you want to plot the voltage? (I've picked some random images from google I hope that's ok)

With two x-axis...

source:https://i.stack.imgur.com/g0qTA.png

...or with multiple series in one plot...

source:https://peltiertech.com/images/2016-08/PlotMultipleTimeSeries.png

... or do you want to select two specific dates and compare the two graphs?

source:https://www.tutorialspoint.com/r/images/line_chart_2_lines.jpg

I'm very interested in your results maybe I'll find some time to dig a little bit in the deeps of google as well.

[PaddyB]

I just checked and the graph on my main node red page is actually from data read from sqlite, defaults to battery voltage but has a drop down menu for pressure and engine temperature as well, little bit slow but works OK. Starting to get a bit chilly onboard at night now..



Not sure what I really really want, plotting two days over each other for voltage would be interesting to maybe get a heads up if they weren't happy with the overnight voltage drop being different, but then temperature would make a big difference with fridge duty cycle. Often I think with these sorts of things it's good to keep an open mind - spending time on creating a better plotting system to look at all the lovely data almost certainly will produce interesting and useful results, but you might not know what until you get there

Found a highcharts test I uploaded onto the web as well, highcharts is pretty cool

Easy to see when the laptop was switched off every night

http://www.moondogmoving.co.uk/highcharts.html

[tocan]

Hi all, i did very early looking to datalogging and did play from FHEM Base to use at least MySQL for solar and battery. Next step is grafana and influxdb and i got some first steps to run. My suggestion is to include both to openplotter in future. Under normal conditions the most are interest to time now data. such you can put in an array and this way you have both and here come the advantages from my view of point. First thing i like at least to rech next year and i hope for developers ( im sorry to be not one of those to follow the idea even if they do not see the sense in this and have no use for it for the moment).

Special data logging is you can use for sampling data for others. I think at first for the OSM water deapth (damned english hope you know how to write correct) project :-)

Another point i am playing and i was attacked is special with ESP8266.

The sense is in my opinion that we have a server what is the most usefull part in openplotter. But you never walk allone and you can not expect that the boat of yours neighbour have a raspberry pi o board. The step can be that you have in future mobile sensors :-) or pic up Mobile data, which can be discused.

If you travel together the other boat can conect to yours raspberry pi and headles gets own data from signal k. Signal k can also be a Internetserver somewhere and you share data. Mobile sensor needs just be arduino and a cheap ESP.

Maybee i am wrong but i do not support in General Open CPN because of the use of Kap Files and not using the Tiles or MTiles of OSM direct without conversation. 

Anyways to let you know some way. I followed discussion here and other places which display to use and did decide early to use headless.
ow Android 7.x is out and i have splitscreen but screens are still small on a phone but i do not think so...

I did bought recently a 360° 4 K panoramic cam and here things did start because you need to have the 4 K a phone to get the full resolution like Samsung S8 (600 Euros no way or a S7 with broken screen used for 200 Euros) no way I did found a Chinese Mobile for 200 Euros. It have brightness of 500 and a realy big screen of 6.44 inches what match nearly with the Raspberry display. Best is the Battery have the size of a powerbank -5300. So i think that way including split screen i am allmost much more flexible. the 360° will doing panorama fotos but also can be used as simple monitor for the complete boat.

And realy if realy in practize my (phantasy) works correct there is my solution to buy a second one to have at least a 12 inch monitor but also flexible to use. At the moment its in front of christmas and i will try to make some review in my blog and make again such of my stupid videos.

So why i do this point here in this post. My hope is that here are some Android developers that do an easy to use app for the future. I mean support in headless mode some kind of Split Screen for the Headless transfer and support of powerball technology inside openplotter. A powerball on raspberry pi will be great. You tap just one point on the screen and get a lot of predefined Views with just one click on desktop to let popup the view you like to see.

So to understand what i mean I put a touch screen based user improved desktop for fast changing the different applications in fast way for fingerprints just with one click.

Do not understand this video as a pure marketing please. The Mi Max is the absolute first Smartphone i did bought new. Openplotter still is like to use desktop apps and i think to concentrate and to focus to usability in the next generation will be great. Grafana is going in direction of it. And gave me the idea of big buttons by touchscreen what you can make predefined solution for see as much fast as possible the information you realy need. In general with grafana i can do it now. But i think exact this is the way to focus for the next generation of openplotter. Usability improvement. I did not now put an affilate link here but if someone is interesting the way i go can point me a PM. My way is from now data logging with openplotter yes and this with optimised database (influx) and flexible view (grafana) - here i see the best solution to optimize in general touchscreen use and give away the concept a little bit to use old desktop style what is difficult to use when wind and weather give you difficulties. As long you sit at home and testing the virtual boat is not realy moving. But after you go in reality on tour it needs to be optimised.

[PaddyB]

Hi all, i did very early looking to datalogging and did play from FHEM Base to use at least MySQL for solar and battery. Next step is grafana and influxdb and i got some first steps to run. My suggestion is to include both to openplotter in future.

I had both Grafana and influxdb runiing for a while  -  far  too resource hungry for such a critical system as openplotter running as a boat nav system IMHO. Always at the very top of Htop , peaking near 100% during writes and reads. 
Leave it out.
Sqlite is as it says, very light. Remember tocan, we are sailors - it's very different out in the ocean, you need something rock solid, no need for all the bells and whistles.

[tocan]

Maybee you wonder why i make for a phone solution some input. I hav not try for the moment but why to use hungry resources on the raspberry? the database is independant and most use it on the same servers. But in my thinkings than if there is no internet you have no access. The idea i go arrount to make openplotter slimm and put the database and servers just directely to the local android. https://github.com/farble1670/influxdb-android or something like this. Same i am looking to make me a local Openseamap server. The phone have 64 gb ram and i can put a 256 gb sd card inside. This way i have enough storage and reduce the risk for the raspberry... maybe an unusual concept here in openplotter discussion.

[shark24]

@paddy
Lucky you with your boat still in the water. My little lady is already out of her element. In Germany we call it "high and dry"
I've tried highcharts before and it didn't let me down but I've only used it for small projects with very simple graphs. Keep on playing and let the ideas come to you

@tocan
Dear tocan it's so hard to get the meaning of some of your statements because you are jumping from subject to subject. Even when I try to translate it back to German - I really can't wrap my head around your thoughts. My English isn't great either but I'm trying to stick to the topic and write my thoughts down one by one.
Never mind - I'm trying to understand your ideas and show you my perspective.

I think we have contrary demands on the main use of OpenPlotter.
I need OpenPlotter as an bulletproof system that helps me to sail safe. I need it for navigation, collecting all necessary data for it and giving me a pool of information which I can view via a monitor or smartphone/tablet. I want a software that helps me to deal with my N2k network and doesn't cost a fortune. I want opensource, low cost, low power, DIY possibilities and again most important a stable system because I wont use any plotter next to OpenPlotter.

Graphs and all the other shiny stuff are a great gimmick but when they interfere with the main functions of OpenPlotter it's very bad.
Grafana and InfluxDB aren't made for the Pi and they slow down the system heavily.
OpenPlotter isn't FHEM. It's not for home automation, it's for the sea.

[tocan]

Hi all, i did very early looking to datalogging and did play from FHEM Base to use at least MySQL for solar and battery. Next step is grafana and influxdb and i got some first steps to run. My suggestion is to include both to openplotter in future.

Leave it out.
Sqlite is as it says, very light. Remember tocan, we are sailors - it's very different out in the ocean, you need something rock solid, no need for all the bells and whistles.

There are 2 different things: Boat on Tour or boat in harbour. On tour i use the database inside the mobile. Not local. In Harbour the raspi nearly have nothing to do. Like at me at home so it can better use the integrated database. As well i see that the data itself and transfer do not need much resources. I guess i did link to the resources for the case there is a problem with raspberry that data get not lost and there are interupts. I think about to reorganize an old transport protocol i used 15 years ago for another project. Noone care about it.

I told when i did join here that i like not to go here as developer. but i think there is maybe a use for this old stuff...

https://sourceforge.net/p/hxp/wiki/Home/

I am realy out of this stuff since a long time but some solution in the past was for large ammound of medical data special ct scans to use a decentralised database servers... in that old time and i realy do not like to promote such realy old and outdated stuff. @Sailloog did suggest me when i did started here if i like to start a forge at sourceforge and there i am sitting somehow to find the right keypoint.

What i like to say just. I am realy not absolute fixed to the raspberry pi and not be a fanboy. I use it but i am fully aware that after the comes allways something. I see a future but i think that raspberry have not the power now is no reason to leave such concepts like grafana and influx. MySQL and the lite version are ok for stabile data but i think influx as speialised database have less limits for the data logging... special if you whish to log dynamic data. Its also not ideal for storage large ammounts of pictures or videos.