R Studio

R is a powerful tool to analyse data. Attached the step by step description how to access the data base with rStudio and create some plots

We have RStudio pre installed on the managed server and its ready to use. We also provide a REST API within R to get metadata and historical data.

Get Data from anywhere via RestAPI

Attached the step by step description to access the data base with rStudio. The data base can be accessed via an REST API from every where. The machine where this code runs can be anywhere. You need the following code and the helper function. You can directly download the helper function here. The helper function will handle the REST communication. Copy the following code to the RStudio workspace and run it.

## Install packages if you do not have it already
install.packages("ggplot2")
install.packages("httr")
install.packages("jsonlite")

# Load library
library(ggplot2)
library(httr)
library(jsonlite)

rm(list=ls())

## Call reading Data from NEST, has to be in the same project folder
source("helper_functions_nestData.R")

## Your personal username and password
user = "YourUsername@nest.local"
password = "YourPassword"

allSensorMetadata <- getAllSensorMetadata(user,password)

## Individual sensor data with "numericId" ,"start date" ,"end date"
dat.single <- getSingleSensorData(user, password, "42190139", "2019-01-01", "2019-03-31")


## Data from multiple sensors with "several numericIds separated with comma" ,"start date" ,"end date"
dat.multi <- getMultiSensorData(user, password, c("3200008","100067"), "2019-01-01", "2019-03-31")


## Plot
ggplot(dat.multi)+
  geom_line(aes(x=time, y=value, color=numericId))+
  facet_wrap(.~numericId, ncol=1, scales="free_y")+
  theme_minimal()+
  scale_x_datetime(breaks = "weeks")+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

Example Query with stored procedure from managed server

The following example shows how you can use the stored procedure within the NEST TEST Database to return time series data. This only works if you are using RStudio from the Managed Server Copy the following code to the RStudio workspace and run it. You can directly download it here.

Example Code

#Load Librarys
library(ggplot2)
library(odbc)

#Connect to Database
con <- dbConnect(odbc(),Driver = "SQL Server",Server = "NEST-SQL-HIST",Database = "NestTest")

#Execute sql stored procedure
result2 <-  dbGetQuery (con, "set nocount on\n EXEC [usp].[uspSingleLine36] 
   @listOfSystemId='MTM23,MTM24,MTM34,MTM64,MTM65,MTM66,MTM67,EXP03,U30M1,U30M2,MTM31,U25M1'
  ,@listOfDeviceId='P890'
  ,@listOfNumericId='401180211,401180243,401180371,401180627,401180659,401180691,401180723,40120039,42110289,42110300,42150477,42160122'
  ,@theStartDate='2019-01-01'
  ,@theEndDate='2019-02-01'
  ,@WithData=1
  ,@WithPivot=0
  ,@Language=1")

#Execute sql Query
result <- dbGetQuery(con, "SELECT TOP (1000) * FROM [dbo].[vKepMET51] where _NUMERICID=3200000 order by _TIMESTAMP desc")

result2$`401180211`

plot(x= result2$`_TIMESTAMP`,
     y= result2$`401180211`,
     
     ylab='Aussentemperatur °C',
     xlab = 'Date and Time',
     main = 'Zeitgraf',
     col  = (rainbow(n = 4,start = 0,end = 1)))

sp3<-ggplot(result$`_TIMESTAMP`, aes(x=wt, y=mpg, color=mpg)) + geom_point()
sp3
# Gradient between n colors
sp3+scale_color_gradientn(colours = rainbow(5))