hanadb sample Queries for sensor handling

IQA tables :



create column table "sensor.master"
         (SENSORID Integer not null primary key generated by default as IDENTITY,
        SENSORMODEL nvarchar(30),
SENSORSERIAL nvarchar(30),
RESPONSIBLE nvarchar(20)
         
          );


create column table "sensor.transaction"
         (TXNID Integer not null primary key generated by default as IDENTITY,
        SENSORID Integer,
TXNDATE Timestamp,
          SENSORVAL Decimal(15,2),
          FOREIGN KEY (SENSORID) REFERENCES  "<schema>"."sensor.master"
          );

create column table "sensor.transaction.spatials"
         (TXNID Integer not null primary key generated by default as IDENTITY,
        SENSORID Integer,
TXNDATE Timestamp,
          SENSORVAL Decimal(15,2),
LOCATION ST_POINT,
          FOREIGN KEY (SENSORID) REFERENCES  "<schema>"."sensor.master"
          );





___________
Add data

insert into  "<schema>"."sensor.master"  (SENSORMODEL,SENSORSERIAL,RESPONSIBLE)
values('motog','abcxyz','phani');

insert into  "<schema>"."sensor.transaction"  (SENSORID,TXNDATE,SENSORVAL)
values(1,'2015-01-01t00:00:00',1100);

insert into  "sensor.transaction.spatial" (SENSORID,TXNDATE,SENSORVAL,LOCATION) values (24,'2015-03-01T00:23:33',1.2,new ST_POINT(51.5033630, -0.1276250));
______________
get latest record

select * from "sensor.transaction.spatial" where TXNID = (select max(TXNID) from "sensor.transaction.spatial"  where SENSORID=22 );

get latest record with co-ordinates

select *,location.ST_ASGeoJson() from "sensor.transaction.spatial" where TXNID = (select max(TXNID) from "sensor.transaction.spatial"  where SENSORID=22 )


___________

xsodata code:

service namespace "namespace democloud.<schema>.exer.iqa.services"{
"<schema>"."sensor.master" as "sensors";
"<schema>"."sensor.transaction" as "sensordata";
}

annotations{

     enable OData4SAP;

}

___________

url:
https://<tenant>.ap1.hana.ondemand.com/democloud/<schema>/exer/iqa/services/services.xsodata/sensors?$format=json
https://<tenant>.ap1.hana.ondemand.com/democloud/<schema>/exer/iqa/services/services.xsodata/sensordata?$format=json


___________
create sensor
x
get latest values
https://<tenant>.ap1.hana.ondemand.com/democloud/<schema>/exer/iqa/services/services.xsodata/sensordata?$format=json&$orderby=TXNID%20desc&$filter=SENSORID%20eq%2022&$top=1

create spatial values

https://<tenant>.ap1.hana.ondemand.com/democloud/<schema>/exer/iqa/services/operations.xsjs?method=addspatialvalue&SENSORID=22&SENSORVAL=2.434&LAT=1.234&LONG=-2.34

get latest spatial value of sensor
https://<tenant>.ap1.hana.ondemand.com/democloud/<schema>/exer/iqa/services/operations.xsjs?method=getlatestspatialvalue&SENSORID=22


Create FullText Index "tweets" ON "TEAM14"."Tweets"("text") configuration 'EXTRACTION_CORE_VOICEOFCUSTOMER' ASYNC FLUSH EVERY 1 MINUTES LANGUAGE DETECTION ('EN') TEXT ANALYSIS ON;