Libelium - Development

RSS

Storing 802.15.4/ZigBee Sensor Data on a Database

This code example has been tested and improved to be executed in Meshlium with the data coming from a Wireless Sensor Network created with Waspmote.

Frame structure

This example will read, from Meshlium's Xbee module, frames from Waspmote with acceloremter sensor data. Valid frames will be stored on a database.

For this example the frame will have this structure:

<mote_id>#ACCX#Sensor_value#ACCY#Sensor_value#ACCZ#Sensor_value

A valid frame can be:

W01#ACCX#1#ACCY#1#ACCZ#1026

Programming Waspmote

Waspmote will initialize components, read accelerometer sensor values and send it to meshlium using it's Xbee 802.15.4.

File:
"Xbee_accelerometer_humidity.pde"
Note:
Meshlium Xbee MAC is hardcoded into code. Update with your Xbee MAC.


/*
 *  ------Waspmote XBee 802.15.4 Sending & Receiving Example------
 *
 *  Explanation: This example shows how to send and receive packets
 *  using Waspmote XBee 802.15.4 API
 *
 *  This code sends a packet to another node and waits for an answer from
 *  it. When the answer is received it is shown.
 *
 *  Copyright (C) 2009 Libelium Comunicaciones Distribuidas S.L.
 *  http://www.libelium.com
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *
 *  Version:                0.1
 *  Design:                 David Gascón
 *  Implementation:    Alberto Bielsa
 */

 packetXBee* paq_sent;
 int8_t state=0;
 long previous=0;
 int g=0;
 int i=0;
 char X[10];
 float value=0;
 char data[100];

#define  SENS_PIR       SENS_SOCKET7

void setup()
{
  // Inits the XBee 802.15.4 library
  xbee802.init(XBEE_802_15_4,FREQ2_4G,NORMAL);

  // Powers XBee
  xbee802.ON();

  ACC.begin();    // opens I2C bus
  ACC.setMode(ACC_ON); // starts accelerometer
}

void loop()
{
  sendData();
  delay(1000);
}

// sends a message changing it depending on the input option
void sendData()
{
   paq_sent=(packetXBee*) calloc(1,sizeof(packetXBee));
   paq_sent->mode=UNICAST;
   paq_sent->MY_known=0;
   paq_sent->packetID=0x52;
   paq_sent->opt=0;
   xbee802.hops=0;
   xbee802.setOriginParams(paq_sent, "SENDER", NI_TYPE);

   sprintf(data,"W01#ACCX#%d#ACCY#%d#ACCZ#%d%c%c",ACC.getX(), ACC.getY(), ACC.getZ(), '\r', '\n');

   xbee802.setDestinationParams(paq_sent, "0013A20040312A9F", data, MAC_TYPE, DATA_ABSOLUTE);

   state=xbee802.sendXBee(paq_sent);
   if(!state)
   {
     XBee.println("OK");
   }
   free(paq_sent);
   paq_sent=NULL;
}

Creating Database

The database to store data will store the following values: ID of the mote, sensor value recived, and the time of the received frame will be stored.

First we need to create the database for the example.

CREATE SCHEMA IF NOT EXISTS `cursoWasp` DEFAULT CHARACTER SET utf8 ;
USE `cursoWasp`;

GRANT ALL PRIVILEGES ON cursoWasp.* TO 'cursoUser'@'localhost' IDENTIFIED BY 'cursoPass' WITH GRANT OPTION;
flush privileges;

DROP TABLE IF EXISTS `mote_data`;

CREATE TABLE IF NOT EXISTS `mote_data` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `date` datetime NOT NULL,
  `mote_id` varchar(100) NOT NULL,
  `s1_id` varchar(100) NOT NULL,
  `s1_val` varchar(100) NOT NULL,
  `s2_id` varchar(100) NOT NULL,
  `s2_val` varchar(100) NOT NULL,
  `s3_id` varchar(100) NOT NULL,
  `s3_val` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Sniffer code

This program reads frames received through XBee module and store it on a MySQL database.

File:
"sniffer_ddbb.c"

Compilation on Meshlium:
gcc -o sniffer_ddbb -I/usr/include/mysql sniffer_ddbb.c -L/usr/lib/mysql -lmysqlclient -Wall

Usage:
sniffer_ddbb S0 localhost

Note:
This code works at 19200 bauds, you can change it on code. Just look for string B19200 and replace 19200 with the speed you need.

Code


/*
 *  Copyright (C) 2008 Libelium Comunicaciones Distribuidas S.L.
 *  http://www.libelium.com
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *
 *  Version 0.1
 *  Author: Octavio Benedi Sanchez
 */

#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <fcntl.h>
#include <errno.h>
#include <stdlib.h>
#include <termios.h> /* Terminal control library (POSIX) */
#include <ctype.h>
#include <mysql.h>


#define MAX 500
#define MAX_SQL 700

int main(int argc, char *argv[]) {
    int sd = 3;
    char *serialPort = "";
    char *serialPort0 = "/dev/ttyS0";
    char *serialPort1 = "/dev/ttyS1";
    char *USBserialPort0 = "/dev/ttyUSB0";
    char *USBserialPort1 = "/dev/ttyUSB1";
    char valor[MAX] = "";
    char c;
    struct termios opciones;
    char *s0 = "S0";
    char *s1 = "S1";
    char *u0 = "USB0";
    char *u1 = "USB1";

    /**************************************
     * Defines para crear la insercion SQL
     *************************************/
    char sql[MAX_SQL] = "";
    MYSQL mysql;


    /* STARTING MAIN */
    if (argc != 3) {
        fprintf(stderr, "Usage: %s port bbdd_ip\n", argv[0]);
        exit(0);
    }


    if (!strcmp(argv[1], s0)) {
        serialPort = serialPort0;
    }
    if (!strcmp(argv[1], s1)) {
        serialPort = serialPort1;
    }
    if (!strcmp(argv[1], u0)) {
        serialPort = USBserialPort0;
    }
    if (!strcmp(argv[1], u1)) {
        serialPort = USBserialPort1;
    }
    if (!strcmp(serialPort, "")) {
        fprintf(stderr, "Choose a valid port (S0, S1, USB0, USB1)\n");
        exit(0);
    }

    if ((sd = open(serialPort, O_RDWR | O_NOCTTY | O_NDELAY)) == -1) {
        fprintf(stderr, "Unable to open the serial port %s - \n", serialPort);
        exit(-1);
    } else {
        if (!sd) {
            sd = open(serialPort, O_RDWR | O_NOCTTY | O_NDELAY);
        }
        fcntl(sd, F_SETFL, 0);
    }
    tcgetattr(sd, &opciones);
    cfsetispeed(&opciones, B38400);
    cfsetospeed(&opciones, B38400);
    opciones.c_cflag |= (CLOCAL | CREAD);
    /*No parity*/
    opciones.c_cflag &= ~PARENB;
    opciones.c_cflag &= ~CSTOPB;
    opciones.c_cflag &= ~CSIZE;
    opciones.c_cflag |= CS8;
    /*raw input:
     * making the applycation ready to receive*/
    opciones.c_lflag &= ~(ICANON | ECHO | ECHOE | ISIG);
    /*Ignore parity errors*/
    opciones.c_iflag |= ~(INPCK | ISTRIP | PARMRK);
    opciones.c_iflag |= IGNPAR;
    opciones.c_iflag &= ~(IXON | IXOFF | IXANY | IGNCR | IGNBRK);
    opciones.c_iflag |= BRKINT;
    /*raw output
     * making the applycation ready to transmit*/
    opciones.c_oflag &= ~OPOST;
    /*aply*/
    tcsetattr(sd, TCSANOW, &opciones);

    mysql_init(&mysql);
    mysql_options(&mysql, MYSQL_READ_DEFAULT_GROUP, "your_prog_name");
    if (!mysql_real_connect(&mysql, argv[2], "cursoUser", "cursoPass", "cursoWasp", 0, NULL, 0)) {
        fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql));
        exit(-1);
    } else {
        fprintf(stderr, "Connected to database: return: %s\n", mysql_error(&mysql));
    }


    int j = 0;
    int ret;
    char h1[50];
    char h2[50];
    char mote_id[50];
    char s1_id[50];
    int s1_val;
    char s2_id[50];
    int s2_val;
    char s3_id[50];
    int s3_val;

    while (1) {

        read(sd, &c, 1);
        valor[j] = c;
        j++;
        if ((c == '\n') || (j == (MAX - 1))) {
            valor[j] = '\0';
            ret = sscanf(valor, "%[^#]#%[^#]#%[^#]#%[^#]#%d#%[^#]#%d#%[^#]#%d", &h1[0], &h2[0], &mote_id[0], &s1_id[0], &s1_val, &s2_id[0], &s2_val, &s3_id[0], &s3_val);
            if (ret == 9) {
                fprintf(stderr, "\nWasp id: %s\n%s:%d\n%s:%d\n%s:%d\n%s:%d\n\n", mote_id, s1_id, s1_val, s2_id, s2_val, s3_id, s3_val, s4_id, s4_val);
                // Create SQL insertion string.
                sprintf(sql, "insert into mote_data values(NULL,now(),'%s','%s','%d','%s','%d','%s','%d');", mote_id, s1_id, s1_val, s2_id, s2_val, s3_id, s3_val);
                if (mysql_query(&mysql, sql) != 0) {
                    fprintf(stderr, "BBDDFailed:%s\n", sql);
                } else {
                    fprintf(stdout, "BBDDSucceeded:%s\n", sql);
                }

            } else {
                fprintf(stderr, "\ncadena: %s\n", valor);
                fprintf(stderr, "\nret:%d\nWasp id: %s\n%s:%d\n%s:%d\n%s:%d\n\n", ret, mote_id, s1_id, s1_val, s2_id, s2_val, s3_id, s3_val);
                fprintf(stderr, "\nBad frame received\n");
            }
            j = 0;
            valor[j] = '\0';
        }
    }
    mysql_close(&mysql);
    close(sd);
    exit(0);
}

Visualization of the data received on a web page

Finally we will make a PHP script that connects to the DDBB and shows last 25 frames received.


<?php

$BBDD_host='localhost';
$BBDD_dbuser='cursoUser';
$BBDD_dbpass='cursoPass';
$BBDD_database='cursoWasp';

function GetMyConnection()
{
	global $g_link;
	global $BBDD_host;
	global $BBDD_dbuser;
	global $BBDD_dbpass;
	global $BBDD_database;
	if( $g_link ){
            return $g_link;
        }
	$g_link = mysql_connect( $BBDD_host, $BBDD_dbuser, $BBDD_dbpass) or die('Could not connect to mysql server.' );
	mysql_select_db($BBDD_database, $g_link) or die('Could not select database.');
	return $g_link;
}
function query($sql)
{
	global $g_link;
	$result = mysql_query($sql, $g_link) or die("Could not execute query $sql");
	return $result;
}

$db_conn=GetMyConnection();
$sql='SELECT * FROM mote_data ORDER BY id desc limit 25 ;';
$query_result=query($sql);
$bbdd='<tr><th>Id</th><th>Date</th><th>Mote Id</th><th>Sensor</th><th>Data</th><th>Sensor</th><th>Data</th><th>Sensor</th><th>Data</th></tr>';
while ($row = mysql_fetch_assoc($query_result))
{
	$bbdd.='<tr><td>'.$row['id'].'</td><td>'.$row['date'].'</td><td>'.$row['mote_id'].'</td><td>'.$row['s1_id'].'</td><td>'.$row['s1_val'].'</td><td>'.$row['s2_id'].'</td><td>'.$row['s2_val'].'</td><td>'.$row['s3_id'].'</td><td>'.$row['s3_val'].'</td></tr>';
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
  <title>WSN data example</title>
</head>
    <body>
        <table style="text-align: left;" border="1" cellpadding="10" cellspacing="0">
            <tbody>
              <?=$bbdd ?>
            </tbody>
        </table>
    </body>
</html>

Generation of a CSV with the data received

Finally we will make a PHP script that connects to the DDBB and generate a CSV file with all the data received from waspmote.


<?php

$BBDD_host='localhost';
$BBDD_dbuser='cursoUser';
$BBDD_dbpass='cursoPass';
$BBDD_database='cursoWasp';

function GetMyConnection()
{
	global $g_link;
	global $BBDD_host;
	global $BBDD_dbuser;
	global $BBDD_dbpass;
	global $BBDD_database;

	if( $g_link ){
            return $g_link;
        }
	$g_link = mysql_connect( $BBDD_host, $BBDD_dbuser, $BBDD_dbpass) or die('Could not connect to mysql server.' );
	mysql_select_db($BBDD_database, $g_link) or die('Could not select database.');
	return $g_link;
}
function query($sql)
{
	global $g_link;
	$result = mysql_query($sql, $g_link) or die("Could not execute query $sql");
	return $result;
}

// /tmp/example_csv_file.csv will be generated but any filename can be used.
$fp=fopen('/tmp/example_csv_file.csv','w');

$db_conn=GetMyConnection();
$sql='SELECT * FROM mote_data ORDER BY id asc;';
$query_result=query($sql);
$last_id='0';
while ($row = mysql_fetch_assoc($query_result))
{
    $last_id=$row['id'];
    $bbdd.=$row['id'].','.$row['date'].','.$row['mote_id'].','.$row['s1_id'].','.$row['s1_val'].','.$row['s2_id'].','.$row['s2_val'].','.$row['s3_id'].','.$row['s3_val']."\n";
    fwrite($fp,$bbdd);
}
fclose($fp);
?>

Download code

You can download the code of this tutorial.

(*)

I have read and I accept the privacy policy

In order to comply with the provisions in Spanish Data Protection Act (L.O. 15/1999 of 13th December) and its implementing Regulation (R.D. 1720/2007, of 21st December), LIBELIUM hereby informs its Customers and websites users that any personal data supplied through the forms contained in its websites shall be recorded in a file named “CUSTOMERS” for which LIBELIUM is responsible, located at LIBELIUM´s registered address and which has been registered with the Spanish General Registry for Data Protection (Registro General de Protección de Datos de la Agencia Española de Protección de Datos).

Except for those fields expressly requesting compulsory filling, answers to the questions contained in any form are voluntary and therefore, the lack of answer does not imply a reduction of the quantity or quality of LIBELIUM´s services. The aim of this data collection are to provide access to LIBELIUM´s online shop, to send information on LIBELIUM´s Product(s) and services as well as to collect Customer’s acceptance to the terms of use of the forums and other sections in LIBELIUM´s websites. By filling these forms Customers consent in receiving promotional offers concerning LIBELIUM´s Product(s) and services. You further consent further that LIBELIUM may make such data available to its services suppliers to the sole purpose of enabling service provision.

Any User providing personal data through any of the forms in LIBELIUM´s websites shall count on a 30 day period to inform LIBELIUM in writing of his/her refusal to the processing of his/her personal data. Unless this communication is made, it shall be deemed that the User consents the processing of his/her personal data according to the terms set forth in these T&C. All personal data collected shall be processed by using the security measures requested by Law to avoid their loss, damage or access by any unauthorised third party. User may nevertheless be aware of the fact that the existing security measures for computer systems on the Internet are not entirely trustworthy. Should you think that your email address has been disclosed to us without your consent, do not hesitate to inform us thereof. Users may at any time exercise their rights of access, rectification, update, cancellation and objection, as well as revoke the consent granted for all of any of the above-mentioned processings, by sending a written statement to LIBELIUM, either through the “Contact” section in our website http://www.libelium.com or by post to the following address: LIBELIUM COMUNICACIONES DISTRIBUIDAS, S.L., c/ María de Luna, 11, nave 5, CEEIARAGON. 50018 Zaragoza (Spain).

To exercise the above-mentioned rights you must indicate your name, ID/passport number, full address, date and signature, including any document supporting your request, in case it is needed; a copy of your ID card/passport must be enclosed, unless you use an electronic signature. User shall be liable for the veracity of data provided and only in case of holders of parental rights concerning children under fourteen and in connection with said children, shall LIBELIUM accept the disclosure of personal data concerning third parties. Users shall assume all liability for direct or consequential damages arising out of or in connection with the provision of false, inaccurate, incomplete or non updated data.

Furthermore, LIBELIUM reserves its right to exclude from any service for which prior registration is required, to any User having provided false data or failing to comply with these T&C, notwithstanding any other legal action to which LIBELIUM may be entitled. LIBELIUM reserves its right to modify its Privacy Policy or these T&C to adapt them to the regulations in force or for any other reason. Provided that the use of LIBELIUM´s websites by Users shall be deemed as User’s acceptance of LIBELIUM´s Privacy Policy and T&C, User is hereby requested to check these T&C and any further amendments from time to time.

Privacy Policy
Users may visit LIBELIUM´s websites without disclosing neither their identity nor any personal data. LIBELIUM´s servers may only collect domain names but not email addresses of their visitors. This kind of information is used to elaborate reports on visit statistics, the time spent in the websites, websites accessed, the general origin of visitors (through “Favourites”, search engines, links from other websites, etc.) to the sole purposes of getting information on how the websites are used and improving their contents and services.

Linked sites
LIBELIUM´s websites may provide links to other sites but LIBELIUM assumes no liability on the privacy policies adopted by the linked sites, directly or indirectly. Links to other sites are provided as a suggestion only and do not imply LIBELIUM´s warranty or liability concerning their quality, accuracy or contents of the information provided therein.

LIBELIUM does not warrant the veracity or accuracy of the information disclosed by its suppliers or third parties whose products or services are offered through LIBELIUM, their origin, ownership or the use or practical implementation made by Customers.

(*) Mandatory fields

| Terms of use