1 / 32

Sensor Network Data Collection and Storage

Sensor Network Data Collection and Storage. Chuck Bell, Sr. Software Developer, MySQL Utilities, Oracle Luis Soares, Sr. Software Engineer, MySQL Replication, Oracle July 18, 2012. v1.0.

obelia
Download Presentation

Sensor Network Data Collection and Storage

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Sensor Network Data Collection and Storage Chuck Bell, Sr. Software Developer, MySQL Utilities, Oracle Luis Soares, Sr. Software Engineer, MySQL Replication, Oracle July 18, 2012 v1.0

  2. THE FOLLOWING IS INTENDED TO OUTLINE OUR GENERAL PRODUCT DIRECTION. IT IS INTENDED FOR INFORMATION PURPOSES ONLY, AND MAY NOT BE INCORPORATED INTO ANY CONTRACT. IT IS NOT A COMMITMENT TO DELIVER ANY MATERIAL, CODE, OR FUNCTIONALITY, AND SHOULD NOT BE RELIED UPON IN MAKING PURCHASING DECISION. THE DEVELOPMENT, RELEASE, AND TIMING OF ANY FEATURES OR FUNCTIONALITY DESCRIBED FOR ORACLE'S PRODUCTS REMAINS AT THE SOLE DISCRETION OF ORACLE.

  3. Speakers • Chuck Bell, PhD • Senior Software Developer • MySQL Utilities Team Lead • Luis Soares • Senior Software Engineer • MySQL Replication Team Lead

  4. Agenda • What are Sensor Networks? • Sensor Nodes • Data Nodes • Data Collection Nodes • Data Collection Methods • Introducing the MySQL Connector/Arduino • MySQL Connector/Arduino Examples • Summary

  5. What are Sensor Networks? • An association of sensors to monitor an event or conditions. • Wired • Laboratory monitoring • Manufacturing monitoring • Wireless • Environmental monitoring • Security monitoring • Hybrid • Many types of sensors

  6. Sensor Nodes • Read and propagate values from sensors. • Sensor examples • Temperature • Humidity • Proximity • Voltage • Light • … and many more • Sensor types • Analog • Digital

  7. Data Nodes • More complex than sensor nodes • Require additional circuitry • Require a microprocessor • Store the data • Types of data • Numeric values of integer, float, etc. • Character data • Datetime • Complex : mix of types • Hybrid nodes – sense and store

  8. Data Collector Nodes • Dedicated node(s) to manage and store the data • Collect the data • Parse or cull the data • Store the data • Relieves sensor nodes of the burden thereby reducing size and complexity of sensor nodes • Easier/cheaper to build • Easier to conceal (smaller size) • Enables lower power requirements

  9. Data Collection Methods • Transmit results to a dedicated coordinator or data node • Post results to cloud-based services • Pachube: https://cosm.com/ • Nimbits: http://www.nimbits.com/ • Others: ThinkSpeak, Digi, Sensor Cloud, etc. • Save data locally to files on removable media • Save data locally as part of distributed database • Run a small web server on an Arduino node to store and display data • (new) Write data to a database server directly • MySQL Connector/Arduino

  10. Pachube (COSM)

  11. Pachube (COSM) Code Excerpt #include <SPI.h> #include <Ethernet.h> #define APIKEY "YOUR API KEY GOES HERE" // replace your Cosm api key here #define FEEDID 00000 // replace your feed ID #define USERAGENT "My Project" // user agent is the project name byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED}; IPAddress ip(10,0,1,20); // initialize the library instance: EthernetClient client; char server[] = "api.cosm.com"; // name address for cosm API unsigned long lastConnectionTime = 0; boolean lastConnected = false; const unsigned long postingInterval = 10*1000; ...

  12. Pachube (COSM) Code Excerpt // this method makes a HTTP connection to the server: void sendData(int thisData) { if (client.connect(server, 80)) { Serial.println("connecting..."); // send the HTTP PUT request: client.print("PUT /v2/feeds/"); client.print(FEEDID); client.println(".csv HTTP/1.1"); client.println("Host: api.cosm.com"); client.print("X-ApiKey: "); client.println(APIKEY); client.print("User-Agent: "); client.println(USERAGENT); client.print("Content-Length: "); // calculate the length of the sensor reading in bytes: // 8 bytes for "sensor1," + number of digits of the data: int thisLength = 8 + getLength(thisData); client.println(thisLength);

  13. Pachube (COSM) Code Excerpt // last pieces of the HTTP PUT request: client.println("Content-Type: text/csv"); client.println("Connection: close"); client.println(); // here's the actual content of the PUT request: client.print("sensor1,"); client.println(thisData); } else { // if you couldn't make a connection: Serial.println("connection failed"); Serial.println(); Serial.println("disconnecting."); client.stop(); } // note the time that the connection was made or attempted: lastConnectionTime = millis(); }

  14. New! MySQL Connector/Arduino • Simple to Use • No Dedicated Computer Needed • Provides a minimal client-level access to MySQL • Direct connection to a MySQL Server • Arduino Library Module • Open Source - GPLv2 • Launchpad: https://launchpad.net/mysql-arduino • Ok, But Why?

  15. MySQL Connector/Arduino • Enables sensor networks to realize the Internet of Things • Expands the versatility of sensor networks • Home automation with recorded with history of events • Advanced data analysis of sensor data • Another method for making data visual on the internet • Motivation • Demonstrate the MySQL client protocol applicability to embeded HW • Open doors for the Arduino community for data storage • Provide a database option for sensor network data

  16. Connector/Arduino - Features • Supports DML • SELECT, SHOW, etc. • Supports DDL • CREATE, DROP, INSERT, UPDATE, DELETE • Retrieve Results

  17. Connector/Arduino - Limitations • Multiple result sets not supported. • Data updates and result set rows must fit in memory. • Result set returned via Serial interface is CSV. • Consumes about 16k of program space. • Uses dynamic memory. • Server errors sent to Serial interface. • Requires a modified version of SHA1 code: • http://code.google.com/p/cryptosuite/downloads/list

  18. Connector/Arduino – Getting Started • Download Library • https://code.launchpad.net/~chuck-bell/mysql-arduino/trunk • Download SHA1 code • http://code.google.com/p/cryptosuite/downloads/list • Modify SHA1 code • See Readme.txt and .diff files included with library • In Arduino, open new project and include the library • Set the server’s IP, port. • Go wild!

  19. Connector/Arduino – Getting Results #include <SPI.h> #include <Ethernet.h> #include <sha1.h> #include <avr/pgmspace.h> #include "mysql.h” #include <DHT22.h> byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; IPAddress ip_addr(192, 168, 0, 15); IPAddress server_addr(192, 168, 0, 7); char user[] = "root"; char password[] = ”xxxxxxx"; Connector my_conn; // The Connector/Arduino reference ...

  20. Connector/Arduino – Getting Results void setup() { Ethernet.begin(mac_addr); Serial.begin(115200); delay(1000); Serial.println("Connecting..."); if (my_conn.mysql_connect(server_addr, 3306, user, password)) { delay(1000); my_conn.cmd_query("SELECT * FROM test.temp LIMIT 8"); my_conn.show_results(); } else Serial.println("Connection failed."); }

  21. Connector/Arduino – Getting Results Connecting... Connected to server version 5.1.50-log. id,temp_c,rel_humid 1,24.5,46 2,24.5,45.1 3,24.4,44.8 4,24.4,44.7 5,24.4,44.7 6,24.4,44.7 7,24.4,44.6 8,24.4,44.5 8 rows in result.

  22. Connector/Arduino – Sensor Node #include <SPI.h> #include <Ethernet.h> #include <sha1.h>#include <avr/pgmspace.h> #include "mysql.h” #include <DHT22.h>byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; IPAddress ip_addr(192, 168, 0, 15); IPAddress server_addr(192, 168, 0, 7); char user[] = "root"; char password[] = ”xxxxxxx"; Connector my_conn; // The Connector/Arduino reference ...

  23. Connector/Arduino – Sensor Node void read_data() { DHT22_ERROR_t errorCode; errorCode = myDHT22.readData(); switch(errorCode) { case DHT_ERROR_NONE: char buf[128]; sprintf(buf, "INSERT INTO test.temp VALUES (NULL, %hi.%01hi, %i.%01i)", myDHT22.getTemperatureCInt()/10, abs(myDHT22.getTemperatureCInt()%10), myDHT22.getHumidityInt()/10, myDHT22.getHumidityInt()%10); my_conn.cmd_query(buf); Serial.println("Data read and recorded."); break; ... }

  24. Connector/Arduino – Sensor Node void setup() { Ethernet.begin(mac_addr); Serial.begin(115200); delay(1000); Serial.println("Connecting..."); if (my_conn.mysql_connect(server_addr, 3306, user, password)) delay(500); else Serial.println("Connection failed."); } void loop() { delay(read_delay); read_data(); }

  25. Connector/Arduino – Sensor Node mysql> select * from test.temp limit 8; +----+--------+-----------+ | id | temp_c | rel_humid | +----+--------+-----------+ | 1 | 24.5 | 46 | | 2 | 24.5 | 45.1 | | 3 | 24.4 | 44.8 | | 4 | 24.4 | 44.7 | | 5 | 24.4 | 44.7 | | 6 | 24.4 | 44.7 | | 7 | 24.4 | 44.6 | | 8 | 24.4 | 44.5 | +----+--------+-----------+ 8 rows in set (0.00 sec)

  26. Connector/Arduino – Data Collector • Uses Xbee API mode on Coordinator • Uses Xbee AT mode on Routers and end points • Receives data from sensor nodes • Parses data packet from sensor • Forms SQL INSERT statement • Issues statement to database

  27. Connector/Arduino – Example Network databases Arduino with Connector/Arduino and Xee Coordinator router MySQL Server Xbee Sensor Nodes

  28. Connector/Arduino – Data Collector void loop() { int discard; if (Serial.read() == 0x7e) { // Signal received packet digitalWrite(12, HIGH); delay(500); digitalWrite(12, LOW); delay(500); // Skip to address low for (int i = 0; i < 7; i++) discard = Serial.read(); // Read address low char address[9]; for (int i = 0; i < 4; i++) sprintf(&address[i*2], "%2x", Serial.read());

  29. Connector/Arduino – Data Collector // Skip to sensor data for (int i = 0; i < 7; i++) discard = Serial.read(); // Read sensor data int high_b = Serial.read(); int low_b = Serial.read(); // Check calibrated threshold for analog sensor if (high_b > 2) { char buf[128]; sprintf(buf, "INSERT INTO test.motion VALUES (NULL, '%s')", address); my_conn.cmd_query(buf); } } }

  30. Connector/Arduino – Data Collector mysql> select * from motion; +---------------------+----------+ | event_date | node_id | +---------------------+----------+ | 2012-07-11 15:29:07 | 409029db | | 2012-07-11 15:29:12 | 409029db | | 2012-07-11 15:29:17 | 409029db | +---------------------+----------+ 3 rows in set (0.00 sec)

  31. Summary • There are several ways to collect data in sensor networks • There is a new option for sensor network developers • The MySQL Connector/Arduino • Simplifies sensor networks used to collect data • Eliminates reliance on internet • Eliminates reliance on dedicated computer (for data collection) • Permits greater granularity of storage allowing developer to create a database schema fitting the data • Opens a whole new chapter in sensor networks • Many solutions use MySQL for analysis, presentation, etc. • Another route into the cloud via data ingestion/storage in MySQL

More Related