Average data from database

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

In my previous post I created a chart showing the hourly average temperature. The average was calculated from measurements taken every 5 seconds. These measurements are stored in a mySQL database together with a timestamp.

The question was how to extract the hourly average from this table to feed to the pChart scripting or to whatever code you like. I found the SQL solution below to make it happen.

    SELECT
      AVG(`value`) AS `Average`,
      YEAR(`timestamp`) AS `Year`,
      MONTH(`timestamp`) AS `Month`,
      Week(`timestamp`) AS `Week`,
      DAY(`timestamp`) AS `Day`,
      HOUR(`timestamp`) AS `Hour`,
    FROM `temperature`
    GROUP BY 'Year',`Month`,`Week`,`Day`,'Hour'`;
Extract average per hour
    SELECT
      AVG(`value`) AS `Average`,
      YEAR(`timestamp`) AS `Year`,
      MONTH(`timestamp`) AS `Month`,
      Week(`timestamp`) AS `Week`,
      DAY(`timestamp`) AS `Day`,
      HOUR(`timestamp`) AS `Hour`,
      Minute(`timestamp`) AS `Minute`
    FROM `temperature`
    GROUP BY 'Year',`Month`,`Week`,`Day`,'Hour', `Minute`;
Extract average per minute

It is possible to create a VIEW in your database which can be queried in your code. A VIEW looks like a table but it is the result of a SQL statement is given a name and can be used as a table in queries.

 

 

Data – (p)Chart – information

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

These days I’m investigating the use of pChart for the graphical representation of all measurements to be collected by my domotica solution. If we collect sensor data the database will be filled with thousands of records. Records in a database are of no value if we don’t convert them to a nice representation. This can be done by generating charts, so we can see e.g. how a temperature changes over time.

Searching the internet I found pChart as a PHP solution for creation of wonderful charts of all kind. Their site is shows numerous examples of line, pie, bar, etc. charts.

It is easy to make a professional chart using database logging together with the pChart classes. The style can be customized and their are abilities to add image maps to get dynamic feedback.

In the picture below you see an example of a chart with the average temperature per hour.
The x-axis shows the week number (of the year) – day number (of the week) – hour (of the day).

We can see the temperature go down a bit in the night and go up again in the morning.

Over time we can learn a lot about e.g. temperature, humidity, pressure, … when enough data is collected. Of course we can use several representations of the data depending of the period that has been logged and the type of data. When electricity consumption is collected it is of course possible to create a chart with the cost per period.

 

 

 

How to communicate using URLConnection in Java

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

Reading data with serial communication between Arduino and PC is one step. After the PC has received the data I want it to send the data (raw or after some calculations) to a website. The website will store the data and show the data in some nice graphs and tables.
The other way around I want to send some commands from the website to the PC where the Java app has to send it to the Arduino which on its turn will perform some actions. I will write another post on this subject some other time.

I found a nice page at Oracle describing how to use the Java URLConnection class.

Depending on the functionality of your website script/app you could start very simple with the code below.


import java.io.*;
import java.net.*;

public class Reverse {
    public static void main(String[] args) throws Exception {

	URL url = new URL("http://www.your-domain.nl/store_value.php?type=KAKU&value=1234567");
	URLConnection connection = url.openConnection();
	connection.setDoOutput(true);

	OutputStreamWriter out = new OutputStreamWriter(
                              connection.getOutputStream());
	out.close();

	BufferedReader in = new BufferedReader(
				new InputStreamReader(
				connection.getInputStream()));
	in.close();
    }
}

All we do here is opening an URL to a PHP page and provide it with two variables. These variables can be extracted from the URL by the PHP script. The snippet below shows a piece of PHP code to extract the variables from the URL. After you got the variable values they can be stored in a database, written to a file, etc..

<?php

$type = $_GET['type'];
$value = $_GET['value'];

// store it in a database or write it to a file or ...

print "TYPE = ".$type." -- VALUE =".$value."</BR>"; 

?>

I know it is very basic and not secure in the dangerous open network world (internet), but as a starting point for private network environments it might be helpful.

 

 

Serial communication between PC and Arduino

Using sensors together with the Arduino or JeeNode is cool. Using the serial monitor of the Arduino IDE lets you see the values. The data is sent from the Arduino over (in my case) a USB cable to my PC. With the serial monitor of the IDE I can receive and also send data.

To make a domotica solution I need something more than the serial monitor of the IDE. I want to be able to send and receive data from my own application and website.

I’m want to use Java to develop the application that’s going to send and receive data. Although I might also try C/C++ in a while, but for now I’ll focus on Java. The reason I want to use Java is that I’m using a Windows computer to develop, but I might want to use a Linux based device to let my application run and do its job. Hopefully it will be easier to switch between the two operating systems with an application written in Java.
(The platform I’m thinking of is the Raspberry Pi; hopefully soon to be ordered)

Searching for a good way to start writing code to communicate with an Arduino I found this page on the Arduino website and also another interesting website. (I did find this site about C and Arduino which might be useful in the future; and this one is I think about Processing and Arduino)

It turns out that by using the RXTX Java library it is possible to use serial communication to send and receive data between a PC and an Arduino. The page shows a code example of a Java app which is able to receive data from a serial port (COM port; in PC terms).

I chose Eclipse as my Java IDE (the SpringSource version to be exact). I had to download the 2.2pre version of the RXTX Library, because my Windows is a 65-bit version. In the 2.2pre package a 64-bit version of the rxtxSerial.dll is included. The 32-bit version will not work on a 64-bit OS, as I found out by trial-and-error. (I didn’t read through the entire page on Arduino.cc otherwise I would have seen the instruction)  Beware that when you use the 2.2pre .dll file you also need to use the 2.2pre RXTXcomm.jar file in your classpath. If you don’t a version mismatch error will be shown.

The steps I took for my basic serial communication test application were:

  1. copy the rxtxSerial.dll to c:\windows\sysWOW64
  2. add the c:\windows\sysWOW64 to your path
  3. added the location of the RXTXcomm.jar file to the classpath in Eclipse
  4. created a new Java project;
  5. added a new file to the \src of the project
  6. named it SerialTest.java
  7. copied the code snippet from the Arduino page (file is attached to this post)
  8. change the COM port to COM5
  9.  changed the bit-rate in the Java code (I needed 57600 for my JeeLink with RF12Demo)
  10. build en run

I saw the expected text from the RF12Demo in the console window of Eclipse.

The first step is done, data is transferred from JeeLink (Arduino) to a ‘self made’ application. The next step is, of course, to send data to an Arduino. I’ll let  you know whta my findings are.

The first of many steps to my own Java Domotica-bridge application.