1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
#!/usr/bin/Rscript --vanilla
require(RPostgreSQL)
require(RSvgDevice)
nulltime <- strptime("01.01.1970", "%d.%m.%Y")
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host="10.2.2.22", user="jack", password="jack", dbname="ebus")
## plot 1
#png(file = "Rplots.png", width=1500, height=800)
devSVG(file="Rplots.svg",width=15,height=10)
par(cex=1.25)
cat("Starte Abfragen\n")
brenner <- dbGetQuery(con,
paste("SELECT timestamp as \"time\"
, value_string
FROM value
WHERE sensor_id IN (select id from sensor where name = 'heizkreisregler1.betriebsdatenFeuerungsautomat.betriebszustand')
AND timestamp > now() - interval '1 day'",
" AND value_string IS NOT NULL",
" ORDER BY timestamp asc", sep=""))
## Brennerstatus auf Brennerstaotusänderungen reduzieren
brennerBla <- table(NULL,NULL)
liste <- c()
old_value_string <- "bla"
for (i in seq(1,nrow(brenner))) {
if (brenner$value_string[i] != old_value_string) {
old_value_string <- brenner$value_string[i]
brennerBla <- rbind(brennerBla, data.frame(time=brenner$time[i], value_string=brenner$value_string[i]))
}
}
brenner <- brennerBla
# Solar Pumpe
solarEngine <- dbGetQuery(con,
paste("SELECT timestamp as \"time\",
value_int
FROM value
WHERE sensor_id IN (select id from sensor where name = 'heizkreisregler9.solarDaten.solarPumpe')
AND timestamp > now() - interval '1 day'
AND value_int IS NOT NULL
ORDER BY timestamp asc", sep=""))
# reduzieren
solarEngineNew <- table(NULL,NULL)
emptyList <- c()
old_value_int <- "blub"
for (i in seq(1,nrow(solarEngine))) {
if (solarEngine$value_int[i] != old_value_int) {
old_value_int <- solarEngine$value_int[i]
solarEngineNew <- rbind(solarEngineNew, data.frame(time=solarEngine$time[i], value_int=solarEngine$value_int[i]))
}
}
solarEngine <- solarEngineNew
# Numerische Sensoren Lesen
readNumericSensorDay <- function (sensorName) {
d <- dbGetQuery(con,
paste("SELECT timestamp",
" , COALESCE(value_int,value_float) \"value\"",
" FROM value",
" WHERE sensor_id IN (select id from sensor where name = '",sensorName,"')",
" AND timestamp > now() - interval '1 day'",
# ausreißer ausblenden
" AND COALESCE(value_int,value_float) < 200",
" AND COALESCE(value_int,value_float) > -50",
" ORDER BY timestamp desc", sep=""))
d$timestamp <- as.POSIXct(round(d$timestamp, "mins"))
#d <- supsmu(as.vector(d$timestamp), as.vector(d$value))
names(d) <- c("time", "value")
myMean <- function(values) {
if (mad(values) > 2) {
print(paste("Streiche ",length(values),
" Werte aus",sensorName,
"mad =", mad(values)))
values <- values * NA
}
mean(values)
}
# dm <- aggregate(d$value, list(time=d$time), myMean)
dm <- d
dm$time <- as.POSIXct(dm$time,origin=nulltime)
names(dm) <- c("time", sensorName)
dm
}
warmwasser <- readNumericSensorDay("heizkreisregler9.solarDaten.tempWarmwasserSolar")
kessel <- readNumericSensorDay("heizkreisregler10.betriebsdatenRegler1.kesselTemperatur")
kollektor <- readNumericSensorDay("heizkreisregler9.solarDaten.tempKollektor")
aussenTemperatur <- readNumericSensorDay("feuerungsautomat1.betriebsdatenRegler1.aussenTemperatur")
# -- TODO ausreißer als punkte darstellen
data <- Reduce(function(x,y) { merge(x,y,all.x=TRUE) }, list(kollektor, kessel, warmwasser, aussenTemperatur))
cat("Plotte\n")
plot.new()
plot.window(xlim=c(min(data$time), max(data$time)),
ylim=c(min(data[2:length(data)],na.rm=TRUE)*0.9, max(data[2:length(data)],na.rm=TRUE)*1.1))
par(lwd=2)
for (i in c(2:length(data))) {
lines(subset(data,select=c(1,i)),
col=rainbow(length(data)-1)[i-1])
}
box()
for (i in c(1:nrow(brenner))) {
text(as.numeric(brenner$time[i]), max(data[2:length(data)],na.rm=TRUE)/2, paste("ab hier", brenner$value_string[i]) ,srt=90)
}
# quick and dirty add solarEngine to plot
rect_start <- 0
rect_color <- rgb(255, 255, 0, alpha=40, maxColorValue=255)
for (i in c(1:nrow(solarEngine))) {
if (solarEngine$value_int[i] == 1) {
rect_start <- solarEngine$time[i];
} else if (rect_start != 0) {
end <- solarEngine$time[i];
# plot
rect(rect_start, 0, solarEngine$time[i], 100, col=rect_color,border=F)
beginn <- 0
}
#text(as.numeric(solarEngine$time[i]), max(data[2:length(data)],na.rm=TRUE)/2, paste("Solar Pumpe", solarEngine$value_int[i]), srt=90)
}
rangeX <- as.POSIXct(range(data$time))
s <- round.POSIXt(seq(rangeX[1], rangeX[2], by=60*60),"hour")
axis.POSIXct(1, at=s, format="%H:%M")
r <- range(data[2:length(data)], na.rm=TRUE)
s <- seq(r[1], r[2], by=(r[2]-r[1])/8)
axis(2, at=s, labels=paste(round(s,1),"°C"))
legend('topleft',
legend=names(data)[2:length(data)],
fill=rainbow(length(data)-1))
title(paste("Temperaturen ", rangeX[1], " bis ", rangeX[2]))
dev.off()
dbDisconnect(con)
|
