How to calculate sunrise, sunset, solar noon and daylight hours for any location at any given day in Tableau
In the past few months (on and off), I have been working on a blog post providing 50+ time formulas in Tableau, similar to the article String Calculations in Tableau published in 2014.
As you can imagine, developing and especially describing 50 Calculated Fields is a lot of work and that’s why this is still in progress. But I am close.
Anyway, during the development I had the idea of providing Calculated Fields for the time of sunrise and sunset in any location at a selected day. It turned out that this is way more complicated than it may sound and I couldn’t get my head around the math. Luckily, I finally stumbled across a brilliant Excel workbook provided by the Earth System Research Laboratory with all necessary calculations and formulas. Transferring the Excel formulas to Tableau Calculated Fields was only a diligent, but routine piece of work.
Today’s short post provides a Tableau Public workbook visualizing sunrise, sunset, solar noon and daylight hours for the ~ 8,800 largest cities in the world at any given day. If you need the Calculated Fields for your own workbook, you can download the workbook from the Tableau Public dashboard published below.
The underlying data source is a simple table of all cities in the world with more than 50,000 inhabitants (8,819 cities). The database includes the name of the city, the country, latitude, longitude, population and finally the time zone respectively the time difference to GMT in hours:
Data source: geonames.org
I won’t go into the details here how the math is working. Mainly because I do not understand it myself. I can see that a lot of trigonometric functions are used in combination with some cryptic constant numbers, but - truth be told - I do not have a clue how all these calculations work in detail. As mentioned in the introduction, my tiny contribution to this is simply the transfer from Excel to Tableau. If you are interested in the details, please refer to the Earth System Research Laboratory website.
The calculation flow uses 15 (!) intermediate formulas to come to the main results. For your and my convenience, I named these intermediate formulas [SC_01_Julian_Day] to [SC_15_HA_Sunrise_(deg)] and the main results (the ones used in the visualizations) [Sunrise], [Sunset], [Solar Noon] and [Daylight Hours]. This makes it easier to separate the intermediate calculations from the more important results.
According to the Earth System Research Laboratory, the calculations are not 100% accurate:
“The calculations in the NOAA Sunrise/Sunset and Solar Position Calculators are based on equations from Astronomical Algorithms, by Jean Meeus. The sunrise and sunset results are theoretically accurate to within a minute for locations between +/- 72° latitude, and within 10 minutes outside of those latitudes. However, due to variations in atmospheric composition, temperature, pressure and conditions, observed values may vary from calculations.”
Furthermore, there is a limitation regarding the selected day:
“Please note that calculations […] are only valid for dates between 1901 and 2099, due to an approximation used in the Julian Day calculation.”
So, the results are not 100% accurate (error range 1 to 10 minutes) and only valid for the 20th and 21st century, but this is good enough in my book.
Sunrise and Sunset in Tableau
Without further ado, here is the small Tableau workbook providing sunrise, solar noon, sunset and daylight hours on a user-selected day for all cities in the world with more than 50,000 inhabitants:
The interactive features of this dashboard:
- pick a date at the top
- if desired, filter the list of cities with the at-least-population slider
- hover over the map to see the details in the tooltips
- click on the map to select a city and filter the table
- click on a data row in the table to highlight the city on the map
As already mentioned in the introduction: if you are interested in the Calculated Fields, please download the workbook from the Tableau Public visualization above and have a look.
Disclaimer: Daylight Saving Times not reflected
Please be advised that the solar calculations do not reflect potential daylight saving times (DST). It goes without saying that it is possible to calculate start and end date of a certain DST rule in a given year with Calculated Fields in Tableau. I will demonstrate how this works in the Time Calculations Collection I mentioned in the introduction.
However, for the data set up used in this article, considering DST is a real challenge for two reasons:
- DST are not everywhere consistent across a country. E.g. in the US, in Australia and in Brazil, DST are only observed in some states/regions. Thus, you can’t deduce the DST rule from the country, but rather need the information if a DST is observed in the specific city for the entire list of cities used above. I did not have this data available.
- Even more complex is the time dimension: since the workbook allows to pick any given date between 1901 and 2099, you would need the information about which country observed DST when. For instance, Netherlands observed DST from 1916 to 1945 and since 1977, whereas Germany observed DST 1916-1918, 1940-1949, and since 1980.
In a nutshell: you would need much more data (and a couple of additional Calculated Fields) to correctly reflect DST. That’s why potential DST are not included in the results of the Tableau Public workbook above.
Finally, I am not sure if there is a use case for solar calculations in Tableau, especially in a corporate environment, but you never know…