LoRa/LoRaWAN tutorial 27: Retrieve and Store Sensor Data From The Things Network

If you like this video and want to support me, go this page for my donation Paypal or crypto addresses: https://www.youtube.com/c/mobilefish/about This is part 27 of the LoRa/LoRaWAN tutorial. In this video series different topics will be explained which will help you to understand LoRa/LoRaWAN. It is recommended to watch each video sequentially as I may refer to certain LoRa/LoRaWAN topics explained earlier. In this tutorial I will demonstrate: - how to retrieve sensor data from The Things Network, - how to store it in a database, - how to retrieve this data from the database and display it in a browser, - and how to create a downlink, using a NodeJS script, sending data from my computer to my LoRa end node. All code used in this tutorial can be found in the following Github repository: https://github.com/robertlie/ttn_app_server The Things Network community developers created several Software Development Kits (SDK) to receive activations and messages from IoT devices via The Things Network to your server. It also allows you to send messages back to the IoT devices from your server. The SDK’s are available in Go, Java, Python and Node.JS Go: https://github.com/TheThingsNetwork/go-app-sdk Java: https://github.com/TheThingsNetwork/java-app-sdk Python: https://github.com/TheThingsNetwork/python-app-sdk Node.JS: https://github.com/TheThingsNetwork/node-app-sdk In this tutorial I will use the Node.JS SDK. This tutorial assumes you have installed the following software packages and know how these packages works. Node.JS (JavaScript server environment) and npm (node package manager) https://nodejs.org/en/download/package-manager/ MySQL (Relational Database Management System)In this tutorial MySQL Community Server is used. https://www.mysql.com/downloads/ phpMyAdmin (Web based administration tool for MySQL) https://www.phpmyadmin.net/ Goto The Things Network console. Goto the applications page and select the application which receives the sensor data. In this demo the application ID is "youtube_demo_app2". To retrieve sensor data from The Things Network to your server, you need: The application ID Example: youtube_demo_app2 Access key Example: ttn-account-v2.uicwOOArAqESHCfa8LGdftBSM6IZWjCdv4Art14iKtc Download the Git repository: https://github.com/robertlie/ttn_app_server Goto folder ttn_app_server Install the node modules, type: npm install Modify file config.js Modify file read_table.php Run the script retrieve.js, type: node retrieve.js This script only retrieves sensor data from TTN and displays it in the terminal. It is possible to create a downlink by sending data to the end node using script send.js. Modify file send.js: client.send("youtube_demo_device", Buffer.alloc(1, 0x00, ‘binary')); Depending on the hex value send, the yellow and green leds can be On or Off. Run the script, type: node send.js The retrieved sensor data from TTN can be stored in a MySQL database. A database and corresponding table needs to be created. First create the database ttn_demo_db, type: node create_db.js Next create the table sensor_data, type: node create_table.js Use the web application phpMyAdmin, to check if the database and table are created. http://localhost/~username/phpmyadmin/index.php The column "payload_raw" has data type tinyblob to store binary data. The payload_fields data is not stored because the payload_raw data can be used to recreate the payload_fields data. The Arduino sketch transmits the humidity and temperature data as four bytes. If the button switch is pressed a single byte is transmitted. To retrieve sensor data from TTN and store it in a MySQL database, type: node store_records.js Use the web application phpMyAdmin, to check if sensor data are stored. The sensor data is stored in the table sensor_data. To display all records from table sensor_data in a terminal, type: node read_table.js The sensor data is stored in the table sensor_data. To display all records from table sensor_data in a browser: First deploy file read_table.php in a web server (for example Apache supporting PHP and MySQL). Open a browser and open the PHP file. To completely delete the database ttn_demo_db, type: node drop_db.js BE CAREFUL, ONCE DELETED ALL DATA IS LOST. Check out all my other LoRa/LoRaWAN tutorial videos: https://www.youtube.com/playlist?list=PLmL13yqb6OxdeOi97EvI8QeO8o-PqeQ0g Subscribe to my YouTube channel: https://www.youtube.com/channel/UCG5_CT_KjexxjbgNE4lVGkg?sub_confirmation=1 The presentation used in this video tutorial can be found at: https://www.mobilefish.com/developer/lorawan/lorawan_quickguide_tutorial.html #mobilefish #lora #lorawan