sine and cosine function in sqlite – look up table workaround

Besides calculating the sine of an angle outside sqlite (e.g. python, bash or php), you may want to calculate your data inside sqlite. My workaround uses a simple look up table, which can be used for virtually all kinds of limited x-y-allocation.

CREATE TABLE lut(angle FLOAT, sin FLOAT, cos FLOAT);
.mode csv
.separator ","
.import lut trigonometry.csv
.separator "|"
.headers on
.mode columns
.show
select * from lut limit 3;
angle       sin         cos
----------  ----------  ----------
0.0         0.0         1.0
0.1         0.002       1.0
0.2         0.003       1.0
...

The table is limited to all angles between 0.0 to 180.0 in 0.1 steps – you may want adapt this depending on your needs. You can simply look up the sine and/or cosine like this:

sqlite> select *, (sin / cos) AS tan, (cos / sin) AS cotan from lut where angle = 60;
angle       sin         cos         tan         cotan
----------  ----------  ----------  ----------  -----------------
60.0        0.866       0.5         1.732       0.577367205542725

But if you want to do calculation with sine, you need to encapsulate the sine look up. I am calculating the incidence angle on a solar panel according to its tilt and orientation, as well as the elevation and azimuth of the sun (at given location) at a certain timestamp.
The have a sun position table with precalculated entries downloaded from NREL: sun-position-algorithm
the view to calculate the incidence angle is defined in sandia: angle-of-incidence
I applied this formula in sqlite (zenith is exchanged by elevation) in a view with fixed tilt of 30 degrees and south orientation (180 degrees):

CREATE VIEW [incidence] AS
SELECT
datetime(timestamp, 'localtime') as LOCAL,
elevation,
azimuth,
180 AS orientation,
30 AS tilt,
(select sin from lut where angle = round(elevation, 1)) *
(select cos from lut where angle = 30) +
(select cos from lut where angle = round(elevation, 1)) *
(select sin from lut where angle = 30) *
(select cos from lut where angle = round(azimuth - 180, 1))
AS COS_INCIDENT
from sunpos;

Since the sun position table only has entries for each full minute, we need to narrow the timestamp using the substr() and ‘||‘ (concatination) functions like this:

sqlite> select * from incidence where LOCAL = substr(datetime('now', 'localtime'),1,17)||'00' limit 1;

Additionally the table entries and search values should be narrowed to a number of decimals. You can should include this during the creation of your triginometry.csv source file (in excel or libre) by a round() function. the search term in sqlite works like this.

sqlite> select round(3.141592653589793, 2) AS pi;
pi
----------
3.14

The lookup with calculations and big source tables may take its time – especially when working on raspberry pi. You may want to use the limit 1 switch and check your performance with .timer on option.