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
|
#!/usr/bin/Rscript --vanilla
require(RPostgreSQL)
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=1000, height=800)
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'",
" 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
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'",
" ORDER BY timestamp desc", sep=""))
d$timestamp <- as.POSIXct(round(d$timestamp, "mins"))
dm <- aggregate(d$value, list(timestamp=d$timestamp), mean)
names(dm) <- c("time", sensorName)
dm
}
## multi arg version of merge()
mymerge <- function (a,...,all.x=TRUE) {
if ( length(list(...)) > 1) {
merge(a,merge(...,all.x=all.x),all.x=all.x)
} else {
merge(a,list(...)[1],all.x=all.x)
}
}
warmwasser <- readNumericSensorDay("heizkreisregler9.solarDaten.tempWarmwasserSolar")
kessel <- readNumericSensorDay("heizkreisregler10.betriebsdatenRegler1.kesselTemperatur")
kollektor <- readNumericSensorDay("heizkreisregler9.solarDaten.tempKollektor")
names_kollektor <- names(kollektor)
kollektor <- supsmu(kollektor$time,kollektor$heizkreisregler9.solarDaten.tempKollektor)
names(kollektor) <- names_kollektor
aussenTemperatur <- readNumericSensorDay("feuerungsautomat1.betriebsdatenRegler1.aussenTemperatur")
# -- TODO ausreißer als punkte darstellen
data <- mymerge(kollektor, kessel, warmwasser)
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)
lines(data$time, data$heizkreisregler10.betriebsdatenRegler1.kesselTemperatur, col="red")
lines(data$time, data$heizkreisregler9.solarDaten.tempWarmwasserSolar, col="orange")
lines(data$time, data$heizkreisregler9.solarDaten.tempKollektor, col="blue")
lines(aussenTemperatur$time, aussenTemperatur$heizkreisregler1.datumZeit.aussenTemperatur, col="green")
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)
}
r <- as.POSIXct(range(data$time))
s <- round.POSIXt(seq(r[1], r[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=c('Kessel', 'Warmwasser', 'Kollektor'), fill=c('red', 'orange', 'blue'))
title("Temperaturen")
dev.off()
dbDisconnect(con)
|
