Course 9 / Lecture 10:

Use aggregated SQL data to
make robot control decision

When reading sensors values in a loop a very large number of data might be collected. To make decision often aggregated values, such as average, spread, etc should be used. In this lecture you will learn how to use the mathematical functions offered by database servers to get aggregated information in order to make good robot control decisions.

Figure 1 - Take a look how you can make control decisions with aggregated data


Task #1:

Create table for the gyroscope data!

Before creating the SNAP program of this lecture, you need to create a table, where you can store the data coming from the gyroscope sensor. For that just open your database connection in Control Panel and select the SQL tab. Here you have to type the command 'CREATE TABLE gyroscope_data (Rotation_angle INT(10))' as you can see in Figure 2. This will create the table with the name 'gyroscope_data' in your database with one column, which is called 'Rotation_angle' and that column stores numbers of maximum ten digits. You will store angles with SNAP.

Figure 2 - SQL command to CREATE TABLE that stores gyroscope data

Task #2:

Write code to calibrate gyroscope sensor!

After you opened SNAP, first add the 'When green flag clicked' entry point. Next read the correct data from the gyroscope sensor. First you need to do a calibration on it (Figure 3). So please add the 'Recalibrate' block and select the gyroscope sensor connection. After that you have to wait until the sensor is fully calibrated with a 'Wait until' block, and fill it with the condition that the 'Rotation angle' of the gyroscope sensor equals to zero. It means your robot has been positioned to 0°.

Figure 3 - Blocks to calibrate the gyroscope

Task #3:

Improve code insert gyroscope data into the table!

After you calibrated the sensor, you can store the value of rotation angle into the database. For that, first you have to drag the 'Use database' block from the Database menu, and select the database connection that you created in Control Panel. Now you can save the data into the table with the 'Insert into' block (Figure 4), where you have to type the name of the table in the first text field, this is the table where you will insert the data. Then type the name of the column between the brackets, and lastly drag the 'Rotation angle' after the 'values' keyword and select the gyroscope sensor here.

Figure 4 - Rotation angle inserted into the created table

Task #4:

Expand code to move the robot and stop it when the aggregated rotation angle is greater than a critical value!

Step 1: Get to know with 'AVG' and 'ROUND' SQL commands

Before you can continue the writing of the code, please go back to the SQL Console of the database connection. If you collected some data into the 'gyroscope_data' table, you can query the average value of the rotation angle. For that just type 'SELECT AVG(Rotation_angle) FROM gyroscope_data' SQL command. That means if you click 'Execute' the average value of the aggregated values will appear underneath the text field just like in Figure 5.

Figure 5 - Average rotation angle queried with an SQL command

To handle the data more simpler, you can round the average value of the rotation angle. For that you just have to expand the previous SQL command with a SELECT ROUND statement: 'SELECT ROUND (AVG(Rotation_angle)) FROM gyroscope_data' (Figure 6). If you click 'Execute' you can see that now the average value is a whole number with the decimal values cut from the end of it.

Figure 6 - Average rotation angle rounded with 'ROUND' command

Step 2: Start motors, and check the average value

First drag the 'Insert into' block you added in the previous task and drag it to the side of the field, don't delete it, you will need it later. After that, you can add the 'Start motor' block and select the motors that are connected to the robot. Then you need to check the average value continuously with a 'Repeat until' block, but you will fill the head of it later in this step. Now drag into the body of that block the 'Insert into' block that you moved away at the beginning. Finally place the 'Wait 1 secs' block, that stalls the program for a second before inserting the next value (Figure 7).

Figure 7 - The program inserts data into the table every second

The last thing that you have to do, is to set the stopping condition in the head of the 'Repeat until' block. So first drag the '>' operator in there. Next you need to check the absolute value of the angle, so to the left hand side add the 'abs of' block. To the right hand side type the critical value which is in this case: 20. Because the query returns with the value between quotation marks, you have to split the 'Select round(avg(Rotation_angle)) from gyroscope_data' block by the quotes like in Figure 8. With that you created a list. Select the 2nd item of it with the 'Item 2 of' block, where you have to drag the previously created 'split' block. With that finally you can check the average rotation angle of the robot. Finally if the control get out of that 'Repeat until' loop, place the 'Stop motor' block below that to stop the motors of the robot in case of fall over.

Figure 8 - The program averages aggregated data and compares it with 20

Program code

Figure 9 - The program code collects aggregated data and makes control decisions

More information


Thank you for visiting this page