Skip to content

Latest commit

 

History

History
264 lines (216 loc) · 9.46 KB

README.md

File metadata and controls

264 lines (216 loc) · 9.46 KB
Raw

master License

MySQL sun/moon astronomy functions

This repository contains the source code for a MySQL loadable function library (previously called UDF - User Defined Functions), which provides an additonal SQL astrononmy function to get astro sun and moon information for a given geographical location and time.

The calculations are based on an adaptation of the C++ Astronomy class from ESP32-Astronomie.

If you like lib_mysqludf_astro give it a star or fork it:

GitHub stars GitHub forks

Build instructions for GNU Make

Build and install

From the base directory run:

make clean
make
sudo make install

This will build and install the library file.

Localized binary

To create a country-specific version, call make command with the parameter LANG=-DLANG_xx, where xx is the country code: DE=German, ES=Spanish, FR=French, IT=Italian, NL=Netherlands, EN=English (default).

Example: Create and install a Dutch binary use

make clean
make LANG=-DLANG_NL
sudo make install

Finally we activate the loadable function in MySQL Server (replace username by a local MySQL user which has the permission to create functions, e. g. root)

mysql -u username -p < install.sql

Uninstall

To uninstall first deactive the loadable function within your MySQL server using the SQL queries:

DROP FUNCTION IF EXISTS astro_info;
DROP FUNCTION IF EXISTS astro;

then uninstall the library using command line:

sudo make uninstall

Usage

astro(date, latitude, longitude, timezone)

Returns astro info for given date, geolocation and timezone as JSON string.

Parameter

date

A given valid date in 'YYYY-MM-DD hh:mm:ss' format. Invalid dates results in a NULL value.

latitude

North–south position of a point in degrees format

longitude

East-West position of a point in degrees format

timezone

Time zone offset from UTC in hours

Return

The function returns the astro info as JSON string with the following keys:

JSON Key Description Format/Unit
$.Time Date for the given result 'YYYY-MM-DDThh:mm:ss'
$.Zone Time zone offset from UTC in hours hour
$.Latitude Geographic latitude degrees
$.Longitude Geographic longitude degrees
$.deltaT deltaT decimal
$.JulianDate Julian Date decimal
$.GMST Greenwich sidereal time 'hh:mm:ss'
$.LMST Local sidereal time 'hh:mm:ss'
$.Sun.Distance.Earth Distance to the sun (earth's center) km
$.Sun.Distance.Observer Distance to the sun (from the observer) km
$.Sun.Ecliptic Ecliptic length of the sun degrees
$.Sun.Declination Declination of the sun degrees
$.Sun.Azimuth Azimuth of the sun degrees
$.Sun.Height Height of the sun above the horizon degrees
$.Sun.Diameter Diameter of the sun arc seconds
$.Sun.Rise.Astronomical Astronomical dawn 'hh:mm:ss'
$.Sun.Rise.Nautical Nautical dawn 'hh:mm:ss'
$.Sun.Rise.Civil Civil dawn 'hh:mm:ss'
$.Sun.Rise.Sunrise Sunrise 'hh:mm:ss'
$.Sun.Culmination Culmination of the sun 'hh:mm:ss'
$.Sun.Set.Sunset Sunset 'hh:mm:ss'
$.Sun.Set.Civil Civil dusk 'hh:mm:ss'
$.Sun.Set.Nautical Nautical dusk 'hh:mm:ss'
$.Sun.Set.Astronomical Astronomical dusk 'hh:mm:ss'
$.Sun.Ascension Right ascension of the sun 'hh:mm:ss'
$.Sun.Zodiac Zodiac Aries, Taurus, Gemini, Cancer, Leo, Virgo, Libra, Scorpio,Sagittarius, Capricorn, Aquarius, Pisces
$.Moon.Distance.Earth Distance to the moon (earth's center) km
$.Moon.Distance.Observer Distance to the moon (from the observer) km
$.Moon.Ecliptic.Latitude Ecliptic latitude of the moon degrees
$.Moon.Ecliptic.Longitude Ecliptic longitude of the moon degrees
$.Moon.Declination Declination of the moon degrees
$.Moon.Azimuth Azimuth of the moon degrees
$.Moon.Height Height of the moon above the horizon degrees
$.Moon.Diameter Diameter of the moon arc seconds
$.Moon.Rise Moonrise 'hh:mm:ss'
$.Moon.Culmination Culmination of the moon 'hh:mm:ss'
$.Moon.Set Moonset 'hh:mm:ss'
$.Moon.Ascension Right ascension of the moon 'hh:mm:ss'
$.Moon.Phase.Name Moon phase name New Moon, Waxing crescent, First quarter, Waxing gibbous, Full Moon, Waning gibbous, Third quarter, Waning crescent
$.Moon.Phase.Value Moon phase as index 0..7
$.Moon.Phase.Number Age of moon in since New Moon (0) - Full Moon (1) decimal
$.Moon.Age Age of moon in radians 0..359
$.Moon.Sign Moon sign Aries, Taurus, Gemini, Cancer, Leo, Virgo, Libra, Scorpio,Sagittarius, Capricorn, Aquarius, Pisces

Example:

{
  "Time": "2023-01-18T09:00:00",
  "Zone": 1,
  "Latitude": 53.182153,
  "Longitude": 4.854429,
  "deltaT": 65,
  "JulianDate": 2459962.83333,
  "GMST": "15:49:54",
  "LMST": "16:09:19",
  "Sun": {
    "Distance": {
      "Earth": 147182898.6,
      "Observer": 147182769.4
    },
    "Ecliptic": 297.968,
    "Declination": -20.565,
    "Azimuth": 127.65,
    "Height": 1.5,
    "Diameter": 32.51,
    "Rise": {
      "Astronomical": "06:39:38",
      "Nautical": "07:21:00",
      "Civil": "08:04:22",
      "Sunrise": "08:44:23"
    },
    "Culmination": "12:50:58",
    "Set": {
      "Sunset": "16:58:02",
      "Civil": "17:38:04",
      "Nautical": "18:21:28",
      "Astronomical": "19:02:52"
    },
    "Ascension": "20:00:14",
    "Zodiac": "Capricorn"
  },
  "Moon": {
    "Distance": {
      "Earth": 370620.2,
      "Observer": 369274.6
    },
    "Ecliptic": {
      "Latitude": -2.528,
      "Longitude": 248.606
    },
    "Declination": -25.195,
    "Azimuth": 176.16,
    "Height": 12,
    "Diameter": 32.24,
    "Rise": "05:33:24",
    "Culmination": "09:17:17",
    "Set": "12:52:19",
    "Ascension": "16:25:58",
    "Phase": {
      "Name": "Waning crescent",
      "Value": 7,
      "Number": 0.174
    },
    "Age": 310.737,
    "Sign": "Sagittarius"
  }
}

Examples

Get sun rise/set

SET @ts = NOW();
SET @latitude = 53.182153;
SET @longitude = 4.854429;
SET @timezone = TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), NOW());

SELECT
    JSON_VALUE(astro(@ts, @latitude, @longitude, @timezone), '$.Time') AS `Time`,
    JSON_VALUE(astro(@ts, @latitude, @longitude, @timezone), '$.Sun.Rise.Sunrise') AS `Sunrise`,
    JSON_VALUE(astro(@ts, @latitude, @longitude, @timezone), '$.Sun.Set.Sunset') AS `Sunset`;

returns

+---------------------+----------+----------+
| Time                | Sunrise  | Sunset   |
+---------------------+----------+----------+
| 2023-01-18T09:00:00 | 08:44:23 | 16:58:02 |
+---------------------+----------+----------+

Get some moon info

SET @ts = NOW();
SET @latitude = 53.182153;
SET @longitude = 4.854429;
SET @timezone = TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), NOW());

SELECT 
    JSON_VALUE(astro(@ts, @latitude, @longitude, @timezone), '$.Time') AS `Time`,
    JSON_VALUE(astro(@ts, @latitude, @longitude, @timezone), '$.Moon.Rise') AS `Moonrise`,
    JSON_VALUE(astro(@ts, @latitude, @longitude, @timezone), '$.Moon.Set') AS `Moonset`,
    JSON_VALUE(astro(@ts, @latitude, @longitude, @timezone), '$.Moon.Phase.Name') AS `Phase`,
    ROUND(CAST(JSON_VALUE(astro(@ts, @latitude, @longitude, @timezone), '$.Moon.Age') AS FLOAT) / 3.6, 0) AS `% Age`;

returns

+---------------------+----------+----------+-----------------+-------+
| Time                | Moonrise | Moonset  | Phase           | % Age |
+---------------------+----------+----------+-----------------+-------+
| 2023-01-18T09:00:00 | 05:33:24 | 12:52:19 | Waning crescent |    86 |
+---------------------+----------+----------+-----------------+-------+

astro_info()

Returns library info as JSON string

Examples:

> SELECT 
    JSON_UNQUOTE(JSON_VALUE(astro_info(),'$.Name')) AS Name,
    JSON_UNQUOTE(JSON_VALUE(astro_info(),'$."Version"')) AS Version,
    JSON_UNQUOTE(JSON_VALUE(astro_info(),'$."Build"')) AS Build;
+--------------------+---------+----------------------+
| Name               | Version | Build                |
+--------------------+---------+----------------------+
| lib_mysqludf_astro | 1.0.0   | Jan 18 2023 09:00:00 |
+--------------------+---------+----------------------+