This project was inspired by a thread on the Tableau Forums (Day and night change on map). The version I published there addressed the basic question, using only the “terminator” (the boundary between day and night – sunset and sunrise), based on the astronomical elevation angle of the center of the sun.
In reality, the diameter of the sun and atmospheric refraction makes this transition gradual, a period known as twilight, which this original map ignores. When showing twilight around equinoxes, the sinusoidal pattern shifts to an oval pattern. And, I thought this project relatively useless unless I could also plot locations of points of interest on the same map. Equinoxes are also a mathematical problem: when the declination is zero, the formulas break down. Together, these factors have nagged me, and drove me to develop this new and improved version.
A complete explanation of the definitions of twilight can be found at Twilight - Wikipedia, the free encyclopedia. Briefly, the stages of twilight are determined by the angle of the sun below the horizon. The following descriptions are for sunset, but the same values and condition exist in reverse for sunrise.
- Civil twilight begins (sunrise) and ends (sunset) when the center of the sun is 6° below the horizon. In general, this is the time in the evening that street lights and headlights come on, but it is still bright enough to do most outdoor activities without artificial light.
- Nautical twilight begins (sunrise) and ends (sunset) when the center of the sun is 12° below the horizon. During this time, navigation using the horizon is possible and the brightest stars are visible.
- Astronomical twilight begins (sunrise) and ends (sunset) when the center of the sun is 18° below the horizon. This seems like “night” to most casual observers, but there is still enough light to interfere with astronomical observations.
The core of this whole project is mathematics. Specifically, spherical and celestial trigonometry. The first published version was solely focused on the moment of the terminator (sunrise/sunset), so the problem was to solve for a solar elevation angle of zero degrees. In that situation, much of the formula cancels itself, resulting in a much simpler formula.
However, solving for latitude when elevation angle is variable, instead of zero, suddenly became much more complicated. I struggled with it for weeks. I sought the help of our engineer and mathematician, Ross du Clair, who has a Ph.D. in Satellite Space Science from Georgetown, and has taught at Sacramento City College and a couple of branches of the military.
You can see the details of the mathematics in the attached Word document, but suffice it to say that we solved it with what turned out to be a rather complicated quadratic formula.
Signs and Animation
For testing and development, we used a date roughly a month after the Spring equinox, so we had both sinusoidal patterns for the lower the elevation angles, and oval patterns for the higher elevation angles. This allowed us to see the patterns all at once without constantly changing dates. But resulting values became confusing. At one point in the discussion, Ross made the innocent but brilliant observation, “I wish we could animate this.” I said, “Tableau can do that!” So, by putting the elevation angle into the Page shelf to animate them, and watching the lines change progressively, we were able to see the line suddenly change. What looked like nice curve suddenly transitioned into a curve with a spike at the end.
With square roots and trigonometric functions, the sign (positive and negatives) of the result can get lost. It turned out that those points where the actual elevation angle did not match the desired elevation angle needed the sign reversed. When the sign of the latitude was changed, the desired elevation angle matched the desired elevation angle.
The story "Building Polygons" illustrates this discovery and development process:
Unfortunately, due to the complexity of the mathematics involved, the ideal version stresses Tableau beyond practical limits. In order to smooth the curves in extreme cases, such as equinoxes, I needed to plot a point for each degree of longitude. Having both top and bottom latitude points doubles that. And then we have 19 polygons for the 18 degrees of elevation angle plus night. That makes 13,680 points to calculate. I created a “scaffold” database with the longitudes and degrees of elevation angle. I then created all the calculated fields in Tableau. When completed, it took roughly 18 minutes for my dual 2.7GHz Xeon processors to calculate, and gobbled up all of my 16 GB of memory. Needless to say, this isn’t practical for daily use.
As a result, the version published on Tableau Public, and attached .twbx file, reduces the twilight stages from one step for each of the 18 degrees of solar elevation angle, to only 3 (Civil, Nautical and Astronomical), and only one out of every five the longitude points, sacrificing some smoothness of the curves . Even with this, it was impracticably slow to render. Tableau is strong at presentation, but week with internal calculation. The solution was to pre-calculate (sometimes referred to as “materialize”) the data. Due to the one-million row limit in Tableau Public, I was able to include the 3 steps of twilight plus night, at the top of each hour, for one day per week for a year. One nice side-effect of this version is that you can easily and quickly step through various weeks of the year or time of day and see how the patterns shift. If you download this to Tableau Desktop, you can simply drag one of the filters to the Pages shelf to animate these changes. Of course, the downside is inability to see any specific date and time at will.
To see any particular date and time on demand, I also wrote a stored procedure which accepts the desired date-time as a parameter, performs all the calculations in a temporary table, and returns the result set. That executes in about 2 seconds. Then, in Tableau, the parameter is passed to the stored procedure. This takes about 30 seconds to render.
The workbooks and SQL code are all included with this entry. The .twb files MUST be linked to a live database to function (sorry, an extract will not work in this situation).
Tableau Version: 9.0.1
Original Author: Bill Lyons