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.