Viewing logged data

[PaddyB]

I've had node red saving lots of signalk into a sqlite3 database for a while now and finally got round to looking at some way to view it - python bokeh library seems very capable! The mouse zoom works well, zoom over the x or y axis to just zoom in one direcrion & click on the legends to show/hide them. 

Data is great! 

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

That's a 6meg file so be warned, and the web space is slow so might take a fair while to load, not too fast on the web either but hopefully gives an idea of what a little python code can do. Much faster on your local drive.

Python code - 

Code:

# import modules
from bokeh.plotting import figure, output_file, show
import sqlite3
import pandas as pd
from bokeh.io import output_notebook
import numpy as np
from bokeh.models import ColumnDataSource, CheckboxGroup, RadioGroup, Toggle
from bokeh.layouts import row


conn = sqlite3.connect('boatdata.db')
df = pd.read_sql_query("select timestamp , eng, alt, exh from engine;", conn, parse_dates=['timestamp']) #load dataframe with sql results - temperature data
df2 = pd.read_sql_query("select timestamp , SOG, COG  from navdata;", conn, parse_dates=['timestamp']) #df2 dataframe is nav data
df3 = pd.read_sql_query("select timestamp , main_batt / 1000  from LX;", conn, parse_dates=['timestamp']) #df3 battery voltage
source = ColumnDataSource(df) # create the sources for p.line
source1 = ColumnDataSource(df2)
source2 = ColumnDataSource(df3)

# output to static HTML file
output_file("lines.html")

# create a new plot with a title and axis labels
p = figure(title="Boat Database", x_axis_label='Time',plot_width=1200,
          plot_height=600, tools="pan, wheel_zoom,  box_zoom,hover, reset", x_axis_type='datetime')

# add a line renderer with legend and line thickness

p.line(x='timestamp', y='SOG', legend="Speed", source=source1, line_width=2, visible = False, color='red')
#p.line(x='timestamp', y='COG', legend="COG", source=source1, line_width=2, visible = False, color='red')
p.line(x='timestamp', y='main_batt / 1000', legend="Battery Voltage", source=source2, line_width=2, color='red')
p.line(x='timestamp', y='exh', legend="Exhaust Temperature", source=source, line_width=2, visible = False, color='green')
p.line(x='timestamp', y='eng', legend="Engine Temperature", source=source, line_width=2, visible = False, color='orange')
p.line(x='timestamp', y='alt', legend="Alt Temperature", source=source, line_width=2, visible = False, color='yellow')
# show the results
p.legend.click_policy="hide"



show(p)