Storing simple caluclations made by rules into the database jdbc

Hi, what is wrong with the below?

Here’s the scenario:

The KNX weatherstation frequently sends Latitude and Longitude. I want to use it to do some calculations relevant for sun positions and so on…


Number Location_Northern_Latitude “Location Northern Latitude” (All,gSQL) { knx="<(60)1/2/2" }
Number Location_Eastern_Longitude “Location Eastern Longitude” (All,gSQL) { knx="<(60)1/2/1" }
Number Sun_Height “Sonnenhöhe” (All,gSQL)
Number Sun_Sun_Azimut “Azimut Sonne” (All,gSQL)
Switch Sun_Dawn_Solar (All,gSQL)
Switch Sun_Dawn_Civil (All,gSQL)
Switch Sun_Dawn_Nautical (All,gSQL)
Switch Sun_Dawn_Astronomical (All,gSQL)

In the database, all tables except Location_Northern_Latitude and Location_Eastern_Longitude are empty.


 import org.openhab.core.library.types.*
 import java.lang.Math

    // Constants
    var Number K = 0.017453

    // Change this reflecting your destination
    var Number latitude = Location_Northern_Latitude.state as DecimalType
    var Number longitude = Location_Eastern_Longitude.state as Decimal Type 

    rule "Set Sun and Dawn States"
        Time cron "0 0/5 * * * ?"
        var Number tageszahl
        var Number deklination
        var Number zeitgleichung
        var Number stundenwinkel
        var Number x 
        var Number y
        var Number sonnenhoehe
        var Number azimut

        var month  = now.getMonthOfYear
        var day    = now.getDayOfMonth
        var hour   = now.getHourOfDay
        var minute = now.getMinuteOfHour

        // Source:
        tageszahl = (month - 1) * 30 + day + hour / 24 
        deklination = -23.45 * Math::cos((K * 360 * (tageszahl + 10) / 365).doubleValue)
        zeitgleichung = 60.0 * (-0.171 * Math::sin((0.0337*tageszahl+0.465).doubleValue) - 0.1299 * Math::sin((0.01787*tageszahl-0.168).doubleValue))
        stundenwinkel = 15.0 * (hour.doubleValue + (minute.doubleValue/60.0) - (15.0-longitude)/15.0 - 12.0 + zeitgleichung/60.0)
        x = Math::sin((K * latitude).doubleValue()) * Math::sin((K * deklination).doubleValue()) + Math::cos((K * latitude).doubleValue()) * Math::cos((K * deklination).doubleValue()) * Math::cos((K * stundenwinkel).doubleValue())
        y = - (Math::sin((K*latitude).doubleValue) * x - Math::sin((K*deklination).doubleValue)) / (Math::cos((K*latitude).doubleValue) * Math::sin(Math::acos(x.doubleValue)))
        sonnenhoehe = Math::asin(x.doubleValue) / K

        var break = hour.doubleValue + (minute.doubleValue/60.0) <= 12.0 + (15.0-longitude)/15.0 - zeitgleichung/60.0 
        if (break) {
            azimut = Math::acos(y.doubleValue) / K
        } else {
            azimut = 360.0 - Math::acos(y.doubleValue) / K

       logDebug("Sun.rules", "month: " + month)
        logDebug("Sun.rules", "day: " + day)
        logDebug("Sun.rules", "hour: " + hour)
        logDebug("Sun.rules", "minute: " + minute)
        logDebug("Sun.rules", "tageszahl: " + tageszahl)
        logDebug("Sun.rules", "deklination: " + deklination)
        logDebug("Sun.rules", "zeitgleichung: " + zeitgleichung)
        logDebug("Sun.rules", "stundenwinkel: " + stundenwinkel)
        logDebug("Sun.rules", "x: " + x)
        logDebug("Sun.rules", "y: " + y)
        logDebug("Sun.rules", "sonnenhoehe: " + sonnenhoehe)
        logDebug("Sun.rules", "azimut: " + azimut)

        logDebug("Sun.rules", "Calculated new SunHeight angle '" + sonnenhoehe + "°'")
        logDebug("Sun.rules", "Calculated new Azimut angle '" + sonnenhoehe + "°'")

        // Send all calculations to the event bus


        Sun_Dawn_Solar.postUpdate( if (sonnenhoehe < 0) ON else OFF )

        postUpdate(Sun_Dawn_Civil,    if((sonnenhoehe <   0)  && (sonnenhoehe >=  -6) && (hour < 12)) {ON} else {OFF})
        postUpdate(Sun_Dawn_Nautical,     if((sonnenhoehe <  -6)  && (sonnenhoehe >= -12) && (hour < 12)) {ON} else {OFF})
        postUpdate(Sun_Dawn_Astronomical, if((sonnenhoehe < -12)  && (sonnenhoehe >= -18) && (hour < 12)) {ON} else {OFF})

        postUpdate(Sun_Dusk_Civil,    if((sonnenhoehe <   0)  && (sonnenhoehe >=  -6) && (hour > 12)) {ON} else {OFF})
        postUpdate(Sun_Dusk_Nautical,     if((sonnenhoehe <  -6)  && (sonnenhoehe >= -12) && (hour > 12)) {ON} else {OFF})
        postUpdate(Sun_Dusk_Astronomical, if((sonnenhoehe < -12)  && (sonnenhoehe >= -18) && (hour > 12)) {ON} else {OFF})


Now, my logfile tells me the following:

2016-07-18 20:15:00.024 [WARN ] [o.p.j.i.JdbcPersistenceService] - JDBC::store: ignore Item ‘Sun_Dawn_Astronomical’ because it is UnDefType
2016-07-18 20:15:00.111 [WARN ] [o.p.j.i.JdbcPersistenceService] - JDBC::store: ignore Item ‘Sun_Height’ because it is UnDefType
2016-07-18 20:15:00.112 [WARN ] [o.p.j.i.JdbcPersistenceService] - JDBC::store: ignore Item ‘Sun_Dawn_Civil’ because it is UnDefType
2016-07-18 20:15:00.113 [WARN ] [o.p.j.i.JdbcPersistenceService] - JDBC::store: ignore Item ‘Sun_Sun_Azimut’ because it is UnDefType
2016-07-18 20:15:00.236 [WARN ] [o.p.j.i.JdbcPersistenceService] - JDBC::store: ignore Item ‘Pressure_today’ because it is UnDefType
2016-07-18 20:15:00.365 [WARN ] [o.p.j.i.JdbcPersistenceService] - JDBC::store: ignore Item ‘Sun_Dawn_Solar’ because it is UnDefType
2016-07-18 20:15:00.391 [WARN ] [o.p.j.i.JdbcPersistenceService] - JDBC::store: ignore Item ‘Sun_Dawn_Nautical’ because it is UnDefType

What is wrong with my items / rule?

First, the obligatory why not use the Astro binding?

The logs indicate that the Items are not getting set to a value. UnDefType is what Items get initialized to by OH so in this case it looks like those Items are never being set.

Are you certain the rule is triggering? Add some logInfo statements to be sure. If you are not running with DEBUG level logging for OH you will not see your logDebugs. Either up the logging level or change to logInfo.

Are you seeing any other errors? In particular given the syntax error (see below) I wonder if there isn’t an error that talks about a failure to load and parse sun.rules.

I notice this syntax error (see bold text), is this a typo or in your actual code?

var Number longitude = Location_Eastern_Longitude.state as DecimalType

You should load the rules into Designer and see if there are any other more subtle errors that I don’t see with a quick scan.

Well, I was not too much aware on the astro binding, but it sounds like I soon have a new project :wink:
But anyway, I saw even in the astro binding you need the location latitude and the longitude. So my weatherstation is delivering this. I would actually start reading latitude and longitude from the weatherstation, same as with the rule.

If you would put my programmer skills on a scale, than I would assume to be rather on the lower end. Thats why I guess i ask stupid questions for professionals like you :wink: The point is i am in a stage of learning by doing: Taking code from open sources and trying to understand and then fix the issues…

There is two things you maybe can help me with:

  1. what is wrong with “DecimalType”? I see this quite frequent if i search the internet. Is there somewhere a good description that shows the available types and the differences?

  2. How can i get this log debug running? Would it be correct if i set a new variable and ad the log debug like in the below?
    var Number longitude = Location_Eastern_Longitude.state as DecimalType
    logDebug(“Sun.rules”, “Longitude” + longitude)

Actually not in this case. The latitude and longitude for the Astro binding is configured in the openhab.cfg file, not in Items. So once you get your lat/long and manually add it to your config file you are done.

But, a weather station is usually static. So once you get the lat/long once you are basically done. Of course if this is a mobile station on a vehicle or something like that the Astro binding may not be the best choice for you.

You had a space between the “Decimal” and the “Type” in the code. There is nothing wrong with “DecimalType”, but “Decimal Type” is a syntax error.

Either change openHAB to use the logback_debug.xml (note you will see 10-20 times more stuff logged out when in debug mode), Or use logInfo instead of logDebug. The default logging level is INFO so logInfo will appear in the logs with the default configuration.

Assuming you installed using apt-get you can do this by simply making a backup copy of /etc/openhab/logback.xml, copy /etc/openhab/logback_debug.xml to /etc/openhab/logback.xml, and then restart openHAB.