Share and visualize motion data on the web

Continued from How-to record your motion data.

In this follow-up tutorial we explain how-to visualize the motion data which was recorded and saved in a database before. In the second part of the tutorial we explain how-to set up a web server for publishing and sharing your data on the web.

For the visualization we use the Python lib matplotlib. Matplotlib needs several libraries to work.
Download matplotlib: http://sourceforge.net/projects/matplotlib/?source=typ_redirect

Download “datautil”: https://pypi.python.org/pypi/python-dateutil

The package “six” is part of the Pythons distribution – check the python27\Lib directory for the directory “six1.9.0”. Otherwise you can download it here: https://pypi.python.org/pypi/six/#downloads
Although six was part of the Python 2.7 distribution it was not installed and compiled.

Next install the packages six and datautil. Here is a very good explanation for windows:
http://www.instructables.com/id/How-to-install-Python-packages-on-Windows-7/

Download pyparsing and execute the “pyparsing-2.0.3-win32-py2.7.exe” file: http://sourceforge.net/projects/pyparsing/files/pyparsing/pyparsing-2.0.3/pyparsing-2.0.3.win32-py2.7.exe/download

Download numpy and execute “numpy-1.9.2-win32-superpack-python2.7.exe”: http://sourceforge.net/projects/numpy/files/NumPy/1.9.2/numpy-1.9.2-win32-superpack-python2.7.exe/download

You can test the matplot installation with the following script take from http://matplotlib.org/1.4.1/users/pyplot_tutorial.html

import matplotlib.pyplot as plt;
plt.plot([1,2,5,4]);
plt.ylabel('some numbers');
plt.show();

Python script for visualization of BI1 data

This script connects to the MySQL database and fetches all activity data for a given node id (each body interaction vibrator development board has a unique identification (id) number, called node id). The plot is generated using matplotlib. It takes some time until a new window opens displaying the activity plot.

import MySQLdb
import sys
import matplotlib.pyplot as plt;

# open a database connection
connection = MySQLdb.connect('127.0.0.1:3306',"root","password","biuplink") or die ("could not connect to database")
cursor = connection.cursor ()

nodeid=11 # select the number of the node

# execute the SQL query using execute() method.
cursor.execute ("select time,nodeid from bitable where value=%s",nodeid)

# fetch all of the rows from the query
data = cursor.fetchall ()

#put the timestamp and value (activity) data in the list x1 and y1
x1=[];
y1=[];
for row in data:
x1.append(row[0]);
y1.append(row[1]);

#plot data
plt.plot_date(x1,y1, fmt="r-");
plt.title('BI1 activity');
plt.savefig("bi activity.png");
plt.show()

cursor.close ()

The plot show the timestamp on the x-Axis and the activity (value ) of the node on the y-Axis and should appear in a new window.

Installation of a web server for displaying BI activity plots in your browser

Install the xampp web server. xampp comes with MySQL, but as we already have installed a MySQL database we only install the Apache web server.
Now copy the following file in the cgi-bin directory. The main part is more or less identical to the Python script above which displays the plot. But this time we need to generate a HTML page for the web server. Therefore you will find some HTML code in addition to the Python code which generates the plot and does the SQL query.
Put the following script in the cgi-bin folder of your xampp installation. Name it “bidataplot_web.py”.

#!c:/python27/python

import cgi, cgitb
import MySQLdb
import sys
import matplotlib.pyplot as plt;

# Create instance of FieldStorage
form = cgi.FieldStorage()

# Get data from fields
date_from = form.getvalue('date_from')
if date_from==None:
    date_from="2015-3-1"
date_to  = form.getvalue('date_to')
if date_to==None:
    date_to="2015-3-13"
nodeid  = form.getvalue('nodeid')
if date_from==None:
    date_from=11

# open a database connection
connection = MySQLdb.connect('127.0.0.1',"root","password","biuplink") or die ("could not connect to database")
cursor = connection.cursor ()

# SQL query:retrieve activity data (time,value) for nodeid and between date_from and date_to
insert_stmt = (
  "select time,value from bitable where nodeid=%s and date(time)&gt;=%s and date(time)&lt;=%s"
)
data= (nodeid,date_from,date_to)
cursor.execute(insert_stmt, data)
data = cursor.fetchall ()

# create list x1 with all activity time entries
# create list y1 with all activity value entries
x1=[];
y1=[];
for row in data:
    x1.append(row[0]);
    y1.append(row[1]);

#plot data
plt.plot_date(x1,y1, fmt="r-");
title='BI1 activity for node '+str(nodeid)+"\nfrom "+date_from+" to "+date_to;
plt.title(title);
plotname= "bi_activity.png";
plt.savefig(plotname);
cursor.close ()

# write HTML code
print "Content-type: text/html\n\n"
print "&lt;html&gt;"
print "&lt;head&gt;"
print "&lt;title&gt;BI activity&lt;/title&gt;"
print "&lt;/head&gt;"
print "&lt;body&gt;"
print "node id = ", nodeid
data_uri = open(plotname, 'rb').read().encode('base64').replace('\n', '')
img_tag = '&lt;img src="data:image/png;base64,{0}"&gt;'.format(data_uri)
print(img_tag)
print "&lt;/body&gt;"
print "&lt;/html&gt;"

Start the MySQL database and your web browser and type in:

http://localhost/cgi-bin/bidataplot_web.py

The browser should now display the activity graph in your browser:

Create a form for retrieving data

In the next step we explain how-to retrieve data for a specific node id and within a time interval. Therefore we have to generate a dynamic Python script.

The following Python script generates a form where you can input the node id and the date “from” and “to”:

#!c:/python27/python
import MySQLdb
import sys  

# open a database connection
connection = MySQLdb.connect('127.0.0.1',"root","password","biuplink") or die ("could not connect to database")
cursor = connection.cursor ()
cursor.execute ("select distinct(nodeid) from bitable order by nodeid desc")
data = cursor.fetchall ()

print "Content-type: text/html\n\n"
print "&lt;html&gt;"
print "&lt;head&gt;"
print "&lt;title&gt;BI activity&lt;/title&gt;"
print "&lt;/head&gt;"
print "&lt;body&gt;"
print "&lt;h1&gt;Plot your body interaction 1 data&lt;/h1&gt;"
print "Nodes in database:"
for row in data:
    print row[0],

print "&lt;form action=""bidataplot_web.py"" method=""get""&gt;"
print "&lt;br&gt;&lt;br&gt;"
print "From: (YYYY/MM/DD)
 &lt;input name="" type="" /&gt;
"
print "&lt;br&gt;"
print "To: (YYYY/MM/DD)
 &lt;input name="" type="" /&gt;
"
print "&lt;br&gt;"
print "Node ID: &lt;br&gt;&lt;input type=""integer"" name=""nodeid"" /&gt;"
print "&lt;br&gt;&lt;br&gt;"
print "&lt;input type=""submit"" value=""Submit"" /&gt;"
print "&lt;/form&gt;"

print "&lt;/body&gt;"
print "&lt;/html&gt;"

Copy this script in the cgi-bin folder of your xampp directory. Use the file name “getbidata2.cgi”. Now return to your internet browser and type in:
http://localhost/xampp/cgi-bin/getbidata2.cgi

You will see the following form in your browser:

Type in the node id of your body interaction node and the “from“ and “to” date, the format is YYYY/MM/DD eg. “2015/01/31”.

After pressing the submit button you will see a graph like that:

That’s all. Now you can store all activity data of your BI board in a SQL database and visualize the data in a graph plot. You can improve the script to print activity data of other body interaction node in the same plot. If you have more sensors (like pulse) you can add a new SQL table and visualize and correlate pulse and activity data.

REFERENCE
http://www.instructables.com/id/Interface-Arduino-to-MySQL-using-Python/?lang=en&ALLSTEPS
https://scriptingmysql.wordpress.com/2011/09/09/retrieving-data-from-mysql-via-python/