Modular Farm Blog: Post 8 - Database

Hi vertical farmers,

In the previous post we presented our vision system capable of detecting plant illness, disorders and development stages.

In this post we talk about the database we will use to store measurements, production figures and support decision.

Database Requirements

The database should keep track of:

• Measurements, sensors and boards that originated them.
• Actuators commands history.
• Multi-crop records comprising history, productivity, species and location (allotment).
• Events generated automatically by the system or inserted by system operators (triggers).

Database Diagram

Figure 1 presents the database entity–relationship model. It defines allotments as areas in the vertical farm and deals with the different sensors configurations and measurements. Includes trigger relations for reporting and alarm purposes too.

Using this model we also pretend to build our knowledge base to connect to our artificial intelligence virtual observer to fine tune the actuator goal variables.

Figure 1. Database entity–relationship model. Right click on the image and select open in new tab/window to see in larger size.

Database Deployment and Connection

We designed the database with the help of MySQL Workbench and deployed it in a MySQL server.

To test the database, we coded software with Python, Node.js, Socket.IO and Google Chart API to try the insertion of sensor measures into the database and their graphic visualization in real time. The result can be seen on the video below.

We defined a Python node (left bottom on video) to generate random sensor measurements at 2 Hz and insert them into the MySQL database.

We used Node.js for the server-side application and Socket.IO to maintain a bidirectional connection between the client and the server. On the server side, a connection to the database is maintained and, at a rate of 2 Hz (not related to sensor output), we check for any updates on the measured values on a given sensor. We store the last records timestamp and query the database to efficiently retrieve the last updates (upper left on video). Finally we use HTML and JavaScript (+ Google Chart API) for the frontend webpage and, for each event on the Socket.IO instance on the client side, we change the data table of the chart, providing a real time visualization (right side on video).

You don't have permission to edit metadata of this video.

Edit media

Dimensions x Small Medium Large Custom

Subject (required) Brief Description Tags (separated by comma) Video visibility in search results Visible Hidden

Parent content

Modular Farm Blog: Post 8 - Database Design Challenge Summary, Week Ending September 27th

Poster

Upload Preview

Ok

Cancel

And that's it for now. Following is a glimpse of the next post.

As always if you have any questions or comments please feel free to comment this post.

Thanks for following and keep connected!