Sending sensor data from LoRa device to Oracle Cloud via Helium network


INTRO

We are going to configure the integration between Helium and Oracle Cloud. For that purpose we have a LORA device (RAK Developer Kit 2 -Weather Kit-) with temperature, pressure, humidity and light level sensors.

As many other OT networks, Helium provides integrations. For this use case we are gonna use an HTTP integration. For that purpose we are creating an Autonomous Database in Oracle Cloud which provides JSON and REST support easyly. Other integration approaches using micro-services, containers, kubernetes, MQTT, Kafka andd the like that can also be deployed in Oracle Cloud as well.

High level diagram

STEP 1: CONFIGURE DEVICE IN HELIUM CONSOLE

Go to Helium console, devices section. Click add device:

Give a name to the device and fill in the information you put in the device when you programmed it from the Arduino IDE (get help from here or there):

Add a function by clicking on [Addd New Fucntion] under the Functions menu:

Give a name to the function, select Decoder in [Function Type], select Custom Script in [Choose Formart] and put the following code in the STEP2-ENTER CUSTOM SCRIPT texarea:

function Decoder(bytes, port) 
{
  var decoded = {};
  
  if (port== 2)
  {
    var temp  = ((bytes[1]) << 8) | (bytes[2]);
    temp = (temp/100.0);
    decoded.temp = temp;
    
    var hum  = ((bytes[3]) << 8) | (bytes[4]);
    hum = (hum/100.0);
    decoded.hum = hum;
    
    var press = (bytes[5] << 24) | (bytes[6] << 16) | (bytes[7] << 8) | (bytes[8]);
    press = (press / 100.0);
    decoded.press = press;
    
    var light = ((bytes[9]) << 8) | (bytes[10]);
    light = (light / 100.0);
    decoded.light = light;

    return decoded;
  }
}

Enable the function in case it is not:

STEP 2: CREATE AUTONOMOUS DATABASE AND COUPLE OF DATABASE ARTEFACTS IN ORACLE CLOUD

Create an autonomous database in oracle cloud. Then create the following stuff with a database client of your choice:

-- 
-- TABLE
--
CREATE TABLE SIGNALS 
   (	"ID" VARCHAR2(255 BYTE) COLLATE "USING_NLS_COMP", 
	"CREATED_ON" TIMESTAMP (6) DEFAULT sys_extract_utc(SYSTIMESTAMP), 
	"LAST_MODIFIED" TIMESTAMP (6) DEFAULT sys_extract_utc(SYSTIMESTAMP), 
	"VERSION" VARCHAR2(255 BYTE) COLLATE "USING_NLS_COMP", 
	"DATA" BLOB
   )  DEFAULT COLLATION "USING_NLS_COMP" ;

ALTER TABLE SIGNALS MODIFY ("CREATED_ON" NOT NULL ENABLE);

ALTER TABLE SIGNALS MODIFY ("LAST_MODIFIED" NOT NULL ENABLE);

ALTER TABLE SIGNALS MODIFY ("VERSION" NOT NULL ENABLE);

ALTER TABLE SIGNALS ADD CHECK ("DATA" is json format oson (size limit 32m)) ENABLE;

ALTER TABLE SIGNALS ADD PRIMARY KEY ("ID")
  USING INDEX  ENABLE;

ALTER TABLE SIGNALS MODIFY ("ID" NOT NULL ENABLE);

--
-- SEQUENCE
--
CREATE SEQUENCE mysequence
 START WITH     100
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

-- Generated by ORDS REST Data Services 21.4.3.r1300919
-- Schema: ADMIN  Date: Thu May 19 05:51:59 2022 
--

BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'ADMIN',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'admin',
      p_auto_rest_auth      => FALSE);
    
  ORDS.DEFINE_MODULE(
      p_module_name    => 'com.oracle.test.lora',
      p_base_path      => '/api/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'com.oracle.test.lora',
      p_pattern        => 'send',
      p_priority       => 0,
      p_etag_type      => 'NONE',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'com.oracle.test.lora',
      p_pattern        => 'send',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'DECLARE 
   
BEGIN 
   insert into signals (id, created_on, last_modified, version, data) values
(mysequence.nextval,systimestamp, systimestamp, 1, :body_text);
END;');

COMMIT;

END;

Goto Database actions -> REST:

Click on Modules:

Click on com.oracle.test.lora:

Click on Get cURL commands from the ellipsis menu of the send template:

Copy the command:

Execute the command from a terminal window to verify that the REST mechanism is working:

curl --location --request POST \
'https://j...........t.adb.eu-frankfurt-1.oraclecloudapps.com/ords/admin/api/send'

NOTE: Grab the url for later.

Then execute the following SQL comand to veify that a record with null data has been created:

select * from (select * from signals order by created_on desc) where rownum <2
Record with empty data payloadd has been created

Or enable autorest in the signals table…:

Enable autorest
Click [Enable]
Click to obtain a cURL command helper
Copy the cURRL command

…and then execute the command to get a record via RREST:

curl --location 'https://j....t.adb.eu-frankfurt-1.oraclecloudapps.com/ords/admin/signals/348' 
{"id":"348","created_on":"2022-05-19T22:05:52.832Z","last_modified":"2022-05-19T22:05:52.832Z","version":"1","data":null,"links":[...]}

STEP 3: CONFIGURE INTEGRATION IN HELIUM CONSOLE

Goto Helium console->Integrrations. Click [Add New Integratiion]:

Click on HTTP:

Put the URL for the POST method obtained in step 2, then give a name to the integration and click [Add Integration]:

STEP 4: ASSOCIATE INTEGRATION WITH DEVICE

Goto Flows and click on [Devices]:

Drag&Drop a device to the Flows area:

Then click on Functions and Drag&Drop your function to the Flows area:

Click on [Integrations] and Drag&Drop an integration to the Flows area:

Using the mopuse, connect the device with the function and the function with the integration:

STEP 5: VERIFY THE INTEGRATION IS WORKING

Goto devices and select a device with transmission packets:

Goto Event Log, select an event and see the hotspot involved:

Then see the integration messages statuses:

Goto autonomous database and see if new recods are inserted:

Goto Helium Explorer and investigate a little bit about the hotspots (gateways) involved:

That’s all, more to come, hope it helps!!

One Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.