Transcription
mobilefish.comLORA / LORAWAN TUTORIAL 27Retrieve and Store Sensor DataFrom The Things NetworkNode.JSnpmMySQLv1.0.1
mobilefish.comINTRO Inthis 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 mycomputer to my LoRa end node.
mobilefish.comTTN APP SERVER GITHUB REPOSITORY Allcode used in this tutorial can be found in the following Github repository:https://github.com/robertlie/ttn app server
mobilefish.comSENDING SENSOR DATA FROM END NODE TO TTN Ihighly recommend that you first watch tutorial 26 if you have not done so.https://youtu.be/EMoZ9taGZRs Intutorial 26 I have demonstrated how sensor data is send to The Things Network.This tutorial (tutorial 27) uses this data.
mobilefish.comSDK RETRIEVING SENSOR DATA FROM TTN The ThingsNetwork community developers created several Software DevelopmentKits (SDK) to receive activations and messages from IoT devices via The ThingsNetwork to your server.It also allows you to send messages back to the IoT devices from your server. TheSDK’s are available in Go, Java, Python and Node.JSGo: : thon: Node.JS: https://github.com/TheThingsNetwork/node-app-sdk Inthis tutorial I will use the Node.JS SDK.
mobilefish.comPREREQUISITES Thistutorial assumes you have installed the following software packages and knowhow these packages works. Node.JS(JavaScript server environment) and npm (node package ager/ 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/
mobilefish.comPREREQUISITES As a demonstration a PHP program “read table.php” is written to display the sensordata in a browser. To make this PHP program work on your computer you need to install a web server(for example Apache) in conjunction with PHP and MySQL. In this tutorial I will not explain how these packages are installed or configured.
mobilefish.comAPPLICATION ID AND ACCESS KEY Goto The ThingsNetwork console. Gotothe applications page and select the application which receives the sensor data.In this demo the application ID is “youtube demo app2”. WARNING:In tutorial 26 the application ID was “youtube demo app”.For tutorial 27, as a test, I deleted the application ID “youtube demo app” in theassumption I could recreate the application ID again but this was a wrong assumption!Once you delete an application ID you can NOT recreate it again.So be aware of this!
mobilefish.comAPPLICATION ID AND ACCESS KEY Toretrieve sensor data from The Things Network to your server, you need: Theapplication IDExample: youtube demo app2 AccesskeyExample: rt14iKtc
mobilefish.comMODIFY FILE CONFIG.JS Downloadthe Git repository:https://github.com/robertlie/ttn app server Gotofolder ttn app server Installthe node modules, type: npm install Modifyfile config.jsuser: 'ENTER MYSQL ACCOUNT NAME HERE'password: 'ENTER MYSQL PASSWORD HERE'appID: ‘ENTER TTN APP ID HERE'accessKey: 'ENTER TTN ACCESSKEY HERE'
mobilefish.comMODIFY FILE READ TABLE.PHP Modifyfile read table.php username "ENTER MYSQL ACCOUNT NAME HERE"; password “ENTER MYSQL PASSWORD HERE";
mobilefish.comEND NODE SENDS SENSOR DATA TO TTN Makesure the end node sends sensor data to TTN, see tutorial 26.
mobilefish.comRETRIEVE.JS Runthe script retrieve.js, type: node retrieve.jsThis script only retrieves sensor data from TTN and displays it in the terminal.
mobilefish.comSEND.JS Itis possible to create a downlink by sending data to the end node using script send.js. Modifyfile send.js:client.send("youtube demo device", Buffer.alloc(1, 0x00, ‘binary')); Depending Runon the hex value send, the yellow and green leds can be On or Off.the script, type:node send.jsHex value00010203Yellow LedOffOnOffOnGreen LedOffOffOnOn
mobilefish.comCREATE DB.JS AND CREATE TABLE.JS Theretrieved sensor data from TTN can be stored in a MySQL database.A database and corresponding table needs to be created. Firstcreate the database ttn demo db, type: node create db.js Next Usecreate the table sensor data, type: node create table.jsthe web application phpMyAdmin, to check if the database and table are created.http://localhost/ username/phpmyadmin/index.php
mobilefish.comCREATE DB.JS AND CREATE TABLE.JS
mobilefish.comTABLE COLUMN PAYLOAD RAW Thecolumn “payload raw” has data type tinyblob to store binary data. Thepayload fields data is not stored because the payload raw data can be used torecreate the payload fields data. Intutorial 26 I have used this Arduino n-otaa-sensors.ino.txt TheDHT11 sensor measured the humidity and temperature.
mobilefish.comTABLE COLUMN PAYLOAD RAW Thesketch transmits the humidity and temperature data as four bytes:byte 0byte 1byte 2byte 308 fc 05 dchumidity4 bytestemperature humidity 0x08fc 2300temperature 0x05dc 1500 humidity 2300 / 100 23.00 % RHtemperature 1500 / 100 15.00 C
mobilefish.comTABLE COLUMN PAYLOAD RAW Ifthe button switch is pressed a single byte is transmitted.byte 0021 byteHex value00010203Yellow LedOffOnOffOnGreen LedOffOffOnOn
mobilefish.comDECODER FUNCTIONfunction Decoder(bytes, port) {if(bytes.length 1) {if(bytes[0] 1) {return {'button': 'activated'}} else {return {'error': 'button action unknown'}}} else if(bytes.length 4) {var humidity (bytes[0] 8) bytes[1];var temperature (bytes[2] 8) bytes[3];return {'humidity': humidity/ 100,'temperature': temperature/100}} else {return {'error': 'payload unknown'}}}code usedfor buttonswitchDecoder functionused in tutorial 26A modified versioncan be found in:read table.jsread table.phpretrieve.jscode usedfor DHT11
mobilefish.comTABLE COLUMN TIME Thecolumn “time” has data type varchar(30) and not datetime.Time example received from TTN: ‘2018-12-27T14:39:12.420921047Z’ The Itime is measured with 9 digits fractional-seconds (420921047).have not used the datetime data type because MySQL has fractional secondssupport for datetime with up to 6 digits precision.
mobilefish.comSTORE RECORDS.JS Toretrieve sensor data from TTN and store it in a MySQL database, type:node store records.js Usethe web application phpMyAdmin, to check if sensor data are stored.http://localhost/ username/phpmyadmin/index.php
mobilefish.comREAD TABLE.JS Thesensor data is stored in the table sensor data. To display all records from tablesensor data in a terminal, type: node read table.js
mobilefish.comREAD TABLE.PHP The Tosensor data is stored in the table sensor data.display all records from table sensor data in a browser: Firstdeploy file read table.php in a web server (for example Apache supportingPHP and MySQL). Opena browser and open the PHP file.
mobilefish.comREAD TABLE.PHP
mobilefish.comDROP DB.JS Tocompletely delete the database ttn demo db, type: node drop db.jsBE CAREFUL, ONCE DELETED ALL DATA IS LOST.
SEND.JS mobilefish.com 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 scr
