In this code we make use of the Messier demo catalogue.
TSV files used: messier.tsv and messier_legend.tsv
To execute these examples you should login as user “root” with:
mysql --local-infile=1 -u root -p
and type the password when requested.
In the following examples we will assume that SID has been properly installed. Remember that comment lines start with — or #, the query ends with a ; and you can split the query on several rows.
Also note that functions can only return 1 value!
Here we use the SID database and login as root, but you can use any, e.g. mpe2018db and login as mpeusr:
Just a check invoking a mysqsl query from the shell:
shell> mysql -u mpeusr -p -e "select count(*) from messier" mpe2018db Enter password: mpe2018pass +----------+ | count(*) | +----------+ | 110 | +----------+ shell>
It looks good.
shell> mysql -u root -p
Enter password:
-- Use the "SID" database, but can use any, e.g. mpe2018db
USE SID;
--
-- Set geographic coordinate and time zone offset (Garching)
--
-- 48.2618996 , 11.6717692
--
SET @Long := 11.7;
SET @Lat := 48.3;
SET @TZ_offset := -1;
--
-- M1 (Crab Nebula) coordinates: RA = 5.575 (Hours)
-- DEC = 22.0167 (Degrees);
--
-- To convert from Hours to Degrees simply multiply by 15.0
--
SET @Ra := 5.575 * 15.0;
SET @Decl := 22.0167;
--
-- This variable will be used to perform circular selection
--
-- Radius in degrees
--
SET @Rad := 10;
--
-- From now on all angles will be in Degrees
--
-- Create the table that will contain the Messier catalog
DROP TABLE IF EXISTS Messier;
CREATE TABLE Messier (
M int NOT NULL,
Type CHAR(2) DEFAULT '**',
Const CHAR(3) DEFAULT '***',
Mag FLOAT,
Ra FLOAT,
Decl FLOAT,
Dist CHAR(20),
App_size CHAR(20) DEFAULT 'unknown',
PRIMARY KEY(M),
INDEX(Type)
) engine=MyISAM;
DESCRIBE Messier;
SELECT * FROM Messier;
-- Load data into the table
LOAD DATA LOCAL INFILE './messier.tsv'
INTO TABLE Messier;
SELECT * FROM Messier; -- note the Ra column values
-- Convert Ra from hours to degrees.
UPDATE Messier SET Ra = Ra * 15.0;
SELECT * FROM Messier;
-- Create the table that will contain the description of each object type
DROP TABLE IF EXISTS TypeDescr;
CREATE TABLE TypeDescr(
Type CHAR(2),
Descr CHAR(20),
PRIMARY KEY(Type)
);
DESCRIBE TypeDescr;
-- Load data into the table
LOAD DATA LOCAL INFILE './messier_legend.tsv'
INTO TABLE TypeDescr;
SELECT * FROM TypeDescr;
-- A simple join to show the long description of each object type
SELECT Messier.*, TypeDescr.Descr
FROM Messier LEFT JOIN TypeDescr
ON Messier.Type = TypeDescr.Type;
From here we assume you have SID installed! See instructions.
--
-- Add a column and an index for the HTM id (depth 6) to the Messier table
--
ALTER TABLE Messier ADD COLUMN htmID_6 SMALLINT UNSIGNED, ADD INDEX(htmID_6);
DESCRIBE Messier;
SHOW INDEX FROM Messier;
-- Populate the new column with htmIDs
UPDATE Messier SET htmID_6 = HTMLookup(6, Ra, Decl);
SELECT * FROM Messier; -- note the htmID_6 column values
-- Use a demo SID function to query on a circular region (and view the result)
select @Ra, @Decl, @Rad; -- check the these local variables are set
CALL SID.SelectCircleHTM ('', '*', 'mpe2018db.Messier', 'htmID_6', 6, 'RA', 'Decl', @Ra, @Decl, @Rad, '');
-- Same query on 100 times larger region but saving the result into a temporary table ('myregion')
CALL SID.SelectCircleHTM ('myregion', '*', 'mpe2018db.Messier', 'htmID_6', 6, 'RA', 'Decl', @Ra, @Decl, @Rad*100, '');
-- The temporary table goes into the SID database
select * from SID.myregion;
-- Make a join with the TypeDescr table to get a description of these objects
SELECT SID.myregion.*, TypeDescr.Descr
FROM SID.myregion LEFT JOIN TypeDescr
ON SID.myregion.Type = TypeDescr.Type;
-- perform more queries at your wish ...
Below more examples of use of the SID provided functions. With some experience you can build your own functions and procedures to make use of them to meet your needs.
Let’s first describe some of them:
HTMLookup(depth INT, Ra_deg DOUBLE, Dec_deg DOUBLE)
This UDF does a lookup of the htmID, at a given depth, of the pixel which contains the point with with coordinates Ra_deg and Dec_deg.
Returns:
the HTM ID of the pixel at the requested depth.
HEALPLookup(is_nested INT, order INT, Ra_deg DOUBLE, Dec_deg DOUBLE)
This UDF does a lookup of the healpID, at a given order and schema, of the pixel which contains the point with with coordinates Ra_deg and Dec_deg.
Returns:
the HEALPix ID of the pixel at the requested order and schema.
CreateCircle_HTM (Ra_deg DOUBLE, Dec_deg DOUBLE, Rad_arcmin DOUBLE)
CreateCircle_HEALP(Ra_deg DOUBLE, Dec_deg DOUBLE, Rad_arcmin DOUBLE)
These SQL procedures (placed in to the database SID !) generate HTM / HEALPix IDs for those pixel falling in or touching a circle centered at Ra_deg and Dec_deg (deg) with a radius of Rad_arcmin (arcmin) writing them into the TEMPORARY table SID.sid.
CreateRect_HTM (Ra_deg DOUBLE, Dec_deg DOUBLE, SideRA_arcmin DOUBLE, SideDec_arcmin DOUBLE)
CreateRect_HEALP(Ra_deg DOUBLE, Dec_deg DOUBLE, SideRA_arcmin DOUBLE, SideDec_arcmin DOUBLE)
Generate HTM / HEALPix IDs for those pixel falling in or touching a pseudo-rectangle centered at Ra_deg and Dec_deg (deg) with sides along RA and Dec of SideRA_arcmin, SideDec_arcmin (arcmin) writing them into the TEMPORARY table SID.sid.
And now let’s use them:
-- Get the HTM depth 8 and HEALPix order 10 (nested schema) IDs for the first 10 objects
-- in the Messier catalogue
SELECT HTMLookup(8, ra, decl) AS HTMid8,
HEALPLookup(1, 10, ra, decl) AS HEALPid10
FROM Messier
WHERE m < 11;
-- Get the distance from M1 of the following M2 to M5 objects
select ra, decl from Messier where M=1;
-- will return 83.625, 22.0167
SELECT sphedist(83.625, 22.0167, RA, Decl) AS sep_arcmin_from_M1 FROM Messier WHERE M > 1 AND M < 6;
-- results are in arcmin. To get them in degrees:
SELECT sphedist(83.625, 22.0167, RA, Decl)/60 AS sep_deg_from_M1 FROM Messier WHERE M > 1 AND M < 6;
-- Create the list of HTM depth 6 trixel IDs (size about 1.1 deg) for a region centered at
-- coords (15, 20) and a radius of 100 arcmin
CALL SID.CreateCircle_HTM(6, 15, 20, 100);
SELECT * FROM SID.sid;
-- you will see that only 2 trixels are fully covered by this region
-- Do the same but using a region of 8 arcmin and using the HEALPix nested schema
-- at order 10 (pixel size about 3.5 arcmin)
CALL SID.CreateCircle_HEALP(10, 15, 20, 8);
SELECT * FROM SID.sid;
-- you will see more pixels, fully and partially covered by the region
-- Now using a pseudo-square region (sides along RA and Dec)
-- Note that we are passing the size of the 2 "sides"
CALL SID.CreateRect_HTM(6, 15, 20, 100, 100);
SELECT * FROM SID.sid;
CALL SID.CreateRect_HEALP(10, 15, 20, 8, 8);
SELECT * FROM SID.sid;
Below some examples of useful SQL functions and procedures:
delimiter //
--
-- Compute distance between two points on a sphere
--
-- Note that SID already has a function to calculate spherical distance: Sphedist
-- This function is just for demonstration.
--
-- a1/b1: long/lat of first point (DEG)
-- a2/b2: long/lat of second point (DEG)
--
-- Returns:
-- distance in Degrees
--
DROP FUNCTION IF EXISTS skydist//
CREATE FUNCTION skydist(a1 DOUBLE, b1 DOUBLE, a2 DOUBLE, b2 DOUBLE) RETURNS DOUBLE
BEGIN
DECLARE deg2rad, a1r, b1r, a2r, b2r, radiff, cosdis, dist DOUBLE;
SET deg2rad = 0.017453292519943;
SET a1r = a1*deg2rad;
SET b1r = b1*deg2rad;
SET a2r = a2*deg2rad;
SET b2r = b2*deg2rad;
SET radiff = ABS(a2r-a1r);
IF (radiff > pi()) THEN
SET radiff = pi() * 2e0 - radiff;
END IF;
SET cosdis = sin(b1r)*sin(b2r) + cos(b1r)*cos(b2r)*cos(radiff);
SET dist = acos(cosdis)/deg2rad;
RETURN dist;
END//
-- From year, month, day, hour to Julian day
DROP FUNCTION IF EXISTS jdcnv//
CREATE FUNCTION jdcnv(yr INT, mn INT, day INT, hr DOUBLE) RETURNS DOUBLE
BEGIN
DECLARE L DOUBLE;
DECLARE j BIGINT;
DECLARE julian DOUBLE;
SET L = (mn-14) DIV 12;
SET j = day - 32075 + 1461*(yr+4800+L) DIV 4 +
367*(mn - 2-L*12) DIV 12 - 3*((yr+4900+L) DIV 100) DIV 4;
SET julian = j + hr/24.0 -0.5;
RETURN julian;
END//
-- From hour angle and declination to Alt, Az coordinates
DROP PROCEDURE IF EXISTS hadec2altaz//
CREATE PROCEDURE hadec2altaz(IN ha DOUBLE, IN decl DOUBLE, IN lat DOUBLE,
OUT alt DOUBLE, OUT az DOUBLE)
BEGIN
DECLARE d2r, sh, ch, sd, cd, sl, cl DOUBLE;
DECLARE x, y, z, r DOUBLE;
SET d2r = pi()/180.0;
SET sh = sin(ha*d2r);
SET ch = cos(ha*d2r);
SET sd = sin(decl*d2r);
SET cd = cos(decl*d2r);
SET sl = sin(lat*d2r);
SET cl = cos(lat*d2r);
SET x = - ch * cd * sl + sd * cl;
SET y = - sh * cd;
SET z = ch * cd * cl + sd * sl;
SET r = sqrt(x*x + y*y);
SET az = atan(y,x) / d2r;
SET alt = atan(z,r) / d2r;
IF (az < 0) THEN
SET az = az + 360;
END IF;
END//
-- From hour angle and declination to Altitude
DROP FUNCTION IF EXISTS hadec2alt//
CREATE FUNCTION hadec2alt(ha DOUBLE, decl DOUBLE, lat DOUBLE) RETURNS DOUBLE
DETERMINISTIC
BEGIN
DECLARE alt, az DOUBLE;
CALL hadec2altaz(ha, decl, lat, alt, az);
RETURN alt;
END//
-- From hour angle and declination to Azimuth
DROP FUNCTION IF EXISTS hadec2az//
CREATE FUNCTION hadec2az(ha DOUBLE, decl DOUBLE, lat DOUBLE) RETURNS DOUBLE
DETERMINISTIC
BEGIN
DECLARE alt, az DOUBLE;
CALL hadec2altaz(ha, decl, lat, alt, az);
RETURN az;
END//
-- From Julian day to local sidereal time
DROP FUNCTION IF EXISTS jd2st//
CREATE FUNCTION jd2st(jd DOUBLE, lng DOUBLE) RETURNS DOUBLE
DETERMINISTIC
BEGIN
DECLARE c0, c1, c2, c3 DOUBLE;
DECLARE jd2000, lst DOUBLE;
DECLARE t, t0, theta DOUBLE;
SET c0 = 280.46061837;
SET c1 = 360.98564736629;
SET c2 = 0.000387933;
SET c3 = 38710000.0;
SET jd2000 = 2451545.0;
SET t0 = jd - jd2000;
SET t = t0/36525.0;
SET theta = c0 + (c1 * t0) + t*t*(c2 - t / c3 );
SET lst = (theta + lng)/15.0;
IF (lst < 0) THEN
SET lst = 24.0 + (lst % 24.0);
END IF;
SET lst = lst % 24.0;
RETURN lst;
END//
-- From local DATETIME and Time zone to Julian day
DROP FUNCTION IF EXISTS Julian//
CREATE FUNCTION Julian(Time DATETIME, TZ_offset INT) RETURNS DOUBLE
DETERMINISTIC
BEGIN
DECLARE Yr, Mn, Dy, Hr, Mi, Se INT;
DECLARE Hr_fr, jd DOUBLE;
SET Yr = year(Time);
SET Mn = month(Time);
SET Dy = day(Time);
SET Hr = hour(Time);
SET Mi = minute(Time);
SET Se = second(Time);
SET Hr_fr =(Hr+TZ_offset) + Mi/60.0 + Se/3600.0;
SET jd = jdcnv(Yr, Mn, Dy, Hr_fr);
RETURN jd;
END//
-- From local DATETIME and Time zone to local sidereal time
DROP FUNCTION IF EXISTS Sidereal//
CREATE FUNCTION Sidereal(Time DATETIME, TZ_offset INT, Lng DOUBLE) RETURNS DOUBLE
DETERMINISTIC
BEGIN
DECLARE jd, st DOUBLE;
SET jd = Julian(Time, TZ_offset);
SET st = jd2st(jd, Lng);
RETURN st;
END//
-- From equatorial Ra, Dec to Altitude
DROP FUNCTION IF EXISTS radec2alt//
CREATE FUNCTION radec2alt(Ra DOUBLE, Decl DOUBLE, Lat DOUBLE, Lng DOUBLE,
TZ_offset INT, Time DATETIME)
RETURNS DOUBLE
BEGIN
DECLARE st, ha, alt DOUBLE;
SET st = Sidereal(Time, TZ_offset, Lng) * 15.0;
SET ha = st - Ra;
SET alt = hadec2alt(ha, Decl, Lat);
RETURN alt;
END//
-- From equatorial Ra, Dec to Azimuth
DROP FUNCTION IF EXISTS radec2az//
CREATE FUNCTION radec2az(Ra DOUBLE, Decl DOUBLE, Lat DOUBLE, Lng DOUBLE,
TZ_offset INT, Time DATETIME)
RETURNS DOUBLE
BEGIN
DECLARE st, ha, az DOUBLE;
SET st = Sidereal(Time, TZ_offset, Lng) * 15.0;
SET ha = st - Ra;
SET az = hadec2az(ha, Decl, Lat);
RETURN az;
END//
-- back to the default demimiter
delimiter ;
-- Use skydist
SELECT skydist(170.2, 32.4, 171, 33) AS dist_deg;
+-------------------+
| dist_deg |
+-------------------+
| 0.901775161467635 |
+-------------------+
SELECT skydist(Ra, Decl, 83, 22)*60 AS dist_arcmin
FROM Messier WHERE M=1;
+-------------------+
| dist_arcmin |
+-------------------+
| 34.78175964005546 |
+-------------------+
--
-- Example of usage of jdcnv and jd2st
--
SELECT @Time:= now() AS Time,
@Yr:=year(@Time) AS Year,
@Mn:=month(@Time) AS Month,
@Dy:=day(@Time) AS Day,
@Hr:=hour(@Time) AS Hour,
@Mi:=minute(@Time) AS Minute,
@Se:=second(@Time) AS Second,
@Hr_fr:=(@Hr+@TZ_offset) + @Mi/60.0 + @Se/3600.0 AS Hr_fr,
@Jd:=jdcnv(@Yr, @Mn, @Dy, @Hr_fr) AS Julian,
@Si:=jd2st(@Jd, @Long) AS Sidereal;
-- Do the same with
SELECT Julian(now(), @TZ_offset), Sidereal(now(), @TZ_offset, @Long);
--
-- Example of usage of hadec2alt, hadec2az
-- (coordinate conversion from Ra/Dec to Alt/Az)
--
-- Compute sidereal time and convert to Degrees
--
SET @Si := Sidereal(now(), @TZ_offset, @Long) * 15.0;
--
-- Compute hour angle
--
SET @Hour_angle := @Si - @Ra;
SELECT @Ra, @Decl, @Hour_angle,
hadec2alt(@Hour_angle, @Decl, @Lat) AS Alt,
hadec2az( @Hour_angle, @Decl, @Lat) AS Az;
-- Do the same with
SELECT @Ra, @Decl, @Hour_angle,
radec2alt(@Ra, @Decl, @Lat, @Long, @TZ_offset, now()) AS Alt,
radec2az( @Ra, @Decl, @Lat, @Long, @TZ_offset, now()) AS Az;
--
-- Note: variables (like @Lat, @Long, @TZ_offset) cannot be used inside
-- views.
--
DROP VIEW IF EXISTS Messier_view;
CREATE VIEW Messier_view AS
SELECT Messier.M,
TypeDescr.Descr AS Descr,
Messier.Const,
Messier.Mag,
Messier.Ra,
Messier.Decl,
radec2alt(Messier.Ra, Messier.Decl, 38, 13, -1, now()) AS Alt,
radec2az( Messier.Ra, Messier.Decl, 38, 13, -1, now()) AS Az,
Messier.Dist,
Messier.App_size
FROM Messier LEFT JOIN TypeDescr
ON Messier.Type = TypeDescr.Type
ORDER BY Descr, M;
DESCRIBE Messier_view;
SELECT * FROM Messier_view;
-- *******************************************************************
--
-- Set up a trigger to automatically update the htmID_6 field (use the name you have)
-- when a new record is inserted
--
show triggers;
-- eventually
drop trigger trig_ins_Messier;
delimiter //
DROP TRIGGER trig_ins_Messier//
CREATE TRIGGER trig_ins_Messier BEFORE INSERT ON Messier FOR EACH ROW
BEGIN
SET NEW.htmID_6 = HTMLookup(6, NEW.Ra, NEW.Decl);
END//
--
-- Do the same for updated records
--
DROP TRIGGER trig_upd_Messier//
CREATE TRIGGER trig_upd_Messier BEFORE UPDATE ON Messier FOR EACH ROW
BEGIN
SET NEW.htmID_6 = HTMLookup(6, NEW.Ra, NEW.Decl);
END//
delimiter ;