How to collect analog sensor data into an SQL database

With the help of sensors we can collect information from our environment. These sensors detect different physical changes (e.g in temperature, light, sound and soil mosture values) and generate an electrical analog signal. There are also digitals sensors which contain the ADC (analog data converter) device.

What is an ADC?

The ADC can convert analog signals to digital data. This step is useful because the data collected by analog sensors can be processed, manipulated, computed, transmitted or stored after the conversion. In this case we will save our collected analog data into an SQL database.

Here you can read about the different types of analog sensors:

1. Temperature sensor

Mostly used thermistor is the NTC (Negative Temperature Coefficient). This type of sensor is based on resistance change.
If the temperature increases the resistance decreases. The increase of resistance value causes a voltage drop.

What you can use it for?

  • Gardening purposes: collect important data of temperature changes which can affect your plants.
  • Heater controlling: e.g 3D printer, indoor temperature

2. Light sensor

A light sensor can be used to detect the current ambient light level, for example how bright or dark it is. There are different types of light sensors, including Photoresistors, Photodiodes and Phototransistors. The most suitable sensor type for common purposes is Photoresistor. It works like the previously mentioned NTC sensor but in this case an increase of light intensity causes resistance decrease.

Why is it useful?

  • Communication: special phototransistors can receive infrared lights
  • Measure light intensity: e.g motion sensor, camera devices
  • Special sensors can identify colours.

3. Soil moisture sensor

A soil moisture sensor is used to measure the moisture content of the soil. The sensor is made of metal probes.
The resistance of this sensor informs you about the moisture levels. You can create a simple setup that will read and monitor the water level value, and tells you whether the soil is wet or dry and needs watering.
If you would like a self-watering system you can add a water pump to the system. With this feature regulating the watering cycles for optimal plant growth is possible.

For example:

  • For making irrigation systems more efficient.
  • Following a recent rainfall event.
  • Prevent over-watering
  • Agricultural and environmental science can use this method for climate research.

4. Sound sensor

A sound detector is a small board that combines a microphone and an amplifier. It provides not only an audio output but an analog representation of its amplitude too. It can be used to detect the sound strength of the environment. The value of output can be adjusted by the potentiometer. This sensor is applicable for detecting changes e.g. if the sound is louder than the set point you have defined.

Examples:

  • You can detect whether a motor is running or not.
  • In the presence of no sound and no motion, you may go into an energy-saving mode and turn off the lights.
  • Turning lights off and on by clapping.
  • Creating light organ using LEDs.

sound sensor
Figure 4 - Sound sensor


Now that we know more about analog sensors and their functionalities, it is time to create your own project! Let’s see what we need for creating such a system.

converting and saving process
Figure 5 - Converting and saving process

Figure 5 shows how an analog data can be converted (using an ADC e.g. Arduino devices) and then saved into an SQL database using Ozeki 10. (Figure 5)

detecting a change in voltage value
Figure 6 - Detecting a change in voltage value

On Figure 6 you can see that if a change in the voltage value occurs then the gathered data is processed by an Arduino board (the ADC) and then the digital form of the data is saved into the SQL database. (Figure 6)

analog to digital
Figure 7 - Analog to digital signal

Figure 7 presents how the form of an analog signal changes when converting to digital signal. (Figure 7)

Now comes the configuration of our tools! Watch the video tutorial and follow our step-by-step guide below in order to learn how you can save your measured data into SQL database.

Step 1 - Connect the devices

First, connect the analog sensor (in this example we use a sound sensor) with the analog sensor cable. (Figure 8)

analog sensor cable
Figure 1 - Analog sensor cable

Then plug the cable into the Arduino device (e.g Arduino Nano). (Figure 9)

connect to arduino device
Figure 9 - Connect to Arduino device

Finally, you have to create a connection between the sensor attached to an Arduino and a PC. Use an USB cable to make it happen. (Figure 10)

connect to pc
Figure 10 - Connect to PC

Step 2 - Set up the Arduino board

Open the software of Ozeki 10 and on the home page click on 'Help' and then 'View Help'. (Figure 11)

click on help
Figure 11 - Click on 'Help'

Then find 'Analog Sensor' in the 'Sensor' menu and choose the type of device you would like to use for converting the gathered analog data. In this case let's select 'Arduino Nano'. (Figure 12)

choose controller device
Figure 12 - Choose controller device

If you scroll down, you can see the source code which needs to be installed on the selected controller device.
Highlight the full code then right click and choose 'Copy'. (Figure 13)

copy source code
Figure 13 - Copy source code

Now paste the selected source code into the Arduino IDE environment and click on the 'Upload' icon indicated as an arrow. (Figure 14)

Figure 14 - Upload source code

After the successful upload you can see some information about the uploaded code. (Figure 15)

Figure 15 - Successful upload

Step 3 - Set up your analog sensor connection

Let's go back to the home page by clicking on the Ozeki logo in the left corner. In the 'Connections' list select 'MyAnalogSensor@localhost'. (Figure 16)

select sensor connection
Figure 16 - Select sensor connection

On the new page you can see details of your analog sensor connection. Now go to 'Test' menu in order to start measuring data. (Figure 17)

go to test menu
Figure 17 - Go to 'Test' menu

If you click on the 'Measure' button you can see that your analog data has been measured and converted to digital signal and its value is shown on the list. (Figure 18)

mesure analog data
Figure 18 - Measure analog data

Now click on 'Configure' menu and in the 'General' tab you can see 'Periodic read' groupbox. Tick the checkbox which enables the option to read data periodically. (Figure 19)

enable polling option
Figure 19 - Enable polling option

Then you can set a time interval for reading the data. (Figure 20)

set a time interval
Figure 20 - Set a time interval

If you are finished with the configuration then click 'Ok.' (Figure 21)

click ok
Figure 21 - Click 'Ok'

If you go back to 'Test' menu then you can see that data have been measured after the time interval you have previously defined passes. (Figure 22)

collect data periodically
Figure 22 - Collect data periodically

Step 4 - Set up a MySQL connection

To the left 'Connect' option can be found. Click on it and you can see a menu bar below it. Select 'Databases' in order to start setting up your database connection. (Figure 23)

select database connection
Figure 23 - Select database connnection

Now you have to click on 'Create new database connection' and to the right a list of database types will appear.
Select 'MySQL' connection. (Figure 24)

create a mysql database
Figure 24 - Create a MySQL database

Go to 'General' tab and in the 'Connection settings' groupbox you can see default parameters of the MySQL connection. (Figure 25)

mysql connection settings
Figure 25 - MySQL connection settings

As a next step provide the name of your created MySQL database, the userID and password (if needed) to set up the connection. If finished, click 'Ok' to save the changes. (Figure 26)

provide custom data
Figure 26 - Provide custom data

Nice job! The MySQL database connection has been successfully configured and has appeared in the 'Database connections' list! If you a see a green circle next to the database then it means it is available. (Figure 27)

available database
Figure 27 - Available database

Step 5 - Create an SQL messaging connection

Now go to the 'Connections' menu and click on 'Create new connection'. (Figure 28)

create new connection
Figure 28 - Create new connection

To the right a window appears which contains the different type of connection methods. Let's select 'Application'.
(Figure 29)

select application connection type
Figure 29 - Select 'Application' connection type

Then choose 'SQL' connection. (Figure 30)

choose sql
Figure 30 - Choose 'SQL'

In the 'Receive' tab a textbox can be seen where we have to enter SQL INSERT statement. In our case it is: INSERT INTO sensor (Value, Time) VALUES ('$messagedata', '$receivedtime'). SQL queries make it possible to define which parameters of the collected data you would like to save into the database. If finished with providing the code then you can click 'Ok'. (Figure 31)

enter sql statement
Figure 31 - Enter SQL INSERT statement

Now it is time to create a route which makes it possible to forward the measured data from Ozeki's software to your created database. Go to 'Routes' menu and then click on 'Create new route'. (Figure 32)

create a new route
Figure 32 - Create new route

In the 'General' tab you can see 'Settings' groupbox where you can specify the route of the data. First, click on the 'From' box and select the analog sensor connection. Then you have to provide a route where you would like to send the gathered data to. Click on 'To' option and choose the SQL messaging connection which you have created in the previous step. Finally, click 'Ok' to save the settings. (Figure 33)

provide router settings
Figure 33 - Provide route settings

Step 6 - Check the database

Now open a web browser and type into the search bar: 'localhost/phpmyadmin/'. Provide your login data and press 'Go' to gain access to your MySQL database. (Figure 34)

login to phpmyadmin
Figure 34 - Login to phpMyAdmin

On the left side a menu can be seen which contains the databases. Find your created database (in our case its name is ozeki) and the table within this database. Click on it and see what data it contains. (Figure 35)

click on database table
Figure 35 - Click on database table

And the values of the measured data can be seen. All saved into the database! (Figure 36)

data is saved into sql
Figure 36 - Data is saved into SQL

More information