How-to record the activity (motion) data of your BI1

In this how-to we explain the recording and storing of motion data in a SQL database. Afterwards you can plot the data and make them available in the internet.

The standard script of your body interaction (BI) sends motion data with the build-in RFM12b transceiver. The data should activate the vibration motor of other BI boards. Each BI board has a node identification (node id) which is sent together with the motion data. The motion data are called value and are between 0 and 255. We show how-to store the activity (value) for each BI board with its node id in a database. Now you can use the data for further processing – we show you how to plot the data on your computer. In addition we explain how to install a webserver where you can show your data to the community.

What do you need:

• One or more BI boards
• A Jeenode or a similar Arduino compatible board with a RFM12b transceiver.
• Computer with Python and MySQL installation and a Webserver

Arduino script – listening to BIs and sending data using serial port

For this purpose we need a relay station which is connected to your computer. This relay station listen to transmissions from body interaction (BI1) devices. You can use an Arduino compatible development platform with a RFM12b transceiver (like Jeenode, Moteino) or you can connect your Arduino with a RFM12b module. We have used a JeeNodeUSB. It is important that all BI devices and the relay station use the same network.

The following script listen to RF transmissions on a given network. When a message from a BI device is received it is transmitted to your computer using the serial connection. Connect the JeenNodeUSB and your computer with a USB cable. Upload the script to your relay station (JeeNodeUSB) which tries to establish a serial connection. You have to install the Jeelib on your computer (http://jeelabs.net/projects/jeelib/wiki).

#include 
#include 

const int myId=1; //Id number of this node
const int myNetwork=20;
boolean received;

int c2i(char c){
  return (((int)c)+127);
}

void loop() {
  received = rf12_recvDone() && rf12_crc == 0;
  if (received) {
    int diff = c2i(rf12_data[0]);// send value
    Serial.print(diff);
    Serial.print(" ");
    Serial.println(rf12_hdr & 0x1F );//send node id
  }
}

void setup() {
  Serial.begin(115200);
  // Serial.println("255 255"); // data for testing connection to PC
  rf12_initialize(myId, RF12_868MHZ,myNetwork);
}

Open the Arduino console. Now you can see all the RF traffic – the node id and the activity value.

Download and install Python 2.7.9 and the Python “serial” and “mySQL” libraries

In the next step you have to install the Python script language and several libraries.

Install the Python script language and the serial and mysql libraries. We use python 2.7. Keep in mind to download libraries for python 2.7 only. https://www.python.org/downloads/release/python-279/

Download and install pyserial 2.7 – Python Serial Port Extension: https://pypi.python.org/pypi/pyserial

Install Python mySQL connector. How to: http://www.mysqltutorial.org/getting-started-mysql-python-connector/

Download “Windows (Architecture Independent), MSI Installer Python 2.7” from http://dev.mysql.com/downloads/connector/python/2.0.html

Finally get mySQLdb: http://sourceforge.net/projects/mysql-python/

Simple Python Script for testing the serial connection

Now your computer receives the data transmitted over the serial connection and has to put them into a database for further processing (e.g. visualization). But before you should test the serial connection.

Here is a simple Python demo script which listen to the serial connection. You’ll find your serial connection in the Arduino window on the bottom line.

import serial
serx = serial.Serial('COM11', 115200) # use your serial connection and baud rate
while True:
    x=serx.read()
    print (x)
serx.close

Installation of the mySQL database

Now we want to record the data in a database. Download the free MySQL database. We have used the following small installation: mysql-essential-5.1.73-win32.msi  – you can use another version of MySql or use an existing installation of MySql.

http://dev.mysql.com/downloads/mysql/5.1.html#downloads

Create the mySQL database and table

Start the mySQL inline tool and type in the following commands:

CREATE DATABASE biuplink;

USE biuplink;

CREATE TABLE bitable (
time TIMESTAMP,
nodeid INT,
value INT);
</code></pre>
<h3><b>Test the mySQL database</b></h3>
The following commands (a) insert one row in the table and (b) readout the table.
<pre><code>
INSERT INTO bitable 
VALUES (“2008-11-11 13:23:44” ,1,33);

SELECT * FROM bitable;

The result should be:

+---------------------+--------+-------+
| time                | nodeid | value |
+---------------------+--------+-------+
| 2008-11-11 13:23:44 |      1 |    33 |
+---------------------+--------+-------+
1 row in set (0.00 sec)

Python script for inserting data into the mySQL table

We need a python script which listen to the serial port. When data are transmitted over the serial port the script insert the data into the mySQL database. Start the Python shell, go into the file menu and select “New File”. A new window will open. Copy the following script into the window. Go into the file menu, select “Save as” and type in a name like “ardunio_bi_data_upload”. Now go into the “Run” menu and select “Run module”.

#!/usr/bin/python

import MySQLdb
import serial

#establish connection to MySQL. You'll have to change this for your database.
dbConn = MySQLdb.connect('127.0.0.1',"root","password","biuplink") or die ("could not connect to database") 
# root is the username of your MySQL database, password is the corresponding MySQL password. Instead of 127.0.0.1 you can try localhost especially on Linux systems.
#open a cursor to the database
cursor = dbConn.cursor()

device = 'COM11' #this will have to be changed to the serial port you are using
try:
  print ("Trying...",device)
  arduino = serial.Serial(device, 115200)
except:
  print ("Failed to connect on",device)    

while 1==1:
  data = arduino.readline()  #read the data from the arduino
  data=data.strip("\n");     #remove cr
  pieces = data.split(" ")   #split the data by the blank
  #Here we are going to insert the data into the Database
  try:
    cursor.execute("INSERT INTO bitable (value,nodeid) VALUES (%s,%s)", (pieces[0],pieces[1]))
    dbConn.commit() #commit the insert
    print ("item commited");
  except MySQLdb.IntegrityError:
    print ("failed to insert data")
    cursor.close()  #close the cursor

You can start the SQl command inline tool and type in “SELECT * FROM bitable;” to see the inserted activity data.

In the follow up how-to the plotting and publishing of the data is explained.