mysql_sphere provides spherical data types for storing with MySQL / MariaDB. This is achieved by storing the according data types as base64 encoded binary BLOBs. I.e. in order to store mysql_sphere objects, you need to store them in BLOB columns! Furthermore, there is a data type to do transformations.
If you feel that storing mysql_sphere data types as base64 encoded BLOBs is an unnecessary overhead, you can choose to store them in plain binary by adjusting the sphere_types_base64enc global server variable.
Use cases are:
sites on earth
star positions on the sky sphere
spherical positions on planets
A spherical point (or position) is given by two values: longitude and latitude. Longitude is a floating point value between 0 and 2π. Latitude is a floating point value, too, but between -π/2 and π/2. It is possible to give a spherical position in degrees (DEG) or with a triple value of degrees, minutes and seconds (DMS). Degrees and minutes are integer values. The seconds are represented using a floating point value. A fourth method is specifying a longitude value as a triple value of hours, minutes and seconds (HMS). But, you cannot use it with latitude values.
Example 1. A position specified using longitude and latitude in radians
mysql> SELECT spoint( '(0.1,-0.2)' ); +---------------------------------------+ | spoint( '(0.1,-0.2)' ) | +---------------------------------------+ | PHB0PpqZmZmZmbk/oJmZmZmZyb88dHA+AA== | +---------------------------------------+ 1 row in set (0.02 sec)
The mysql_sphere data type can be converted to a readable string using sstr:
mysql> SELECT sstr(spoint( '(0.1,-0.2)' )); +------------------------------+ | sstr(spoint( '(0.1,-0.2)' )) | +------------------------------+ | (0.1 , -0.2) | +------------------------------+ 1 row in set (0.00 sec)
or
mysql> SELECT sstr('PHB0PpqZmZmZmbk/oJmZmZmZyb88dHA+AA==') +----------------------------------------------+ | sstr('PHB0PpqZmZmZmbk/oJmZmZmZyb88dHA+AA==') | +----------------------------------------------+ | (0.1 , -0.2) | +----------------------------------------------+ 1 row in set (0.00 sec)
Example 2. A position specified using longitude and latitude in degrees
mysql> SELECT spoint( '( 10.1d, -90d)' ); +---------------------------------------+ | spoint( '( 10.1d, -90d)' ) | +---------------------------------------+ | PHB0Pkuz7ixJkMY/GC1EVPsh+b88dHA+AA== | +---------------------------------------+ 1 row in set (0.01 sec)
Example 3. A position specified using longitude and latitude (DMS)
mysql> SELECT spoint( '( 10d 12m 11.3s, -13d 14m)' ); +----------------------------------------+ | spoint( '( 10d 12m 11.3s, -13d 14m)' ) | +----------------------------------------+ | PHB0Pmvvlp5Fy8Y/oOws10SQzb88dHA+AA== | +----------------------------------------+ 1 row in set (0.00 sec)
Example 4. A position specified using longitude in HMS, and latitude in RAD
mysql> SELECT spoint( '( 23h 44m 10s, -1.4321 )' ); +---------------------------------------+ | spoint( '( 23h 44m 10s, -1.4321 )' ) | +---------------------------------------+ | PHB0Pr9QrNw82xhAKKCJsOHp9r88dHA+AA== | +---------------------------------------+ 1 row in set (0.00 sec)
As you can see you can combine the input format for longitude and latitude. The value pairs are always enclosed within braces. Spaces are optional.
Use cases are:
spherical object transformations
spherical coordinates transformations
The input syntax of a Euler transformation is:
angle1, angle2, angle3 [, axes ]
where axes
is an optional 3 letter code
with letters : X, Y, or
Z. Default is ZXZ.
angleN
is any valid angle with the input
format RAD, DEG, or
DMS.
To do a transformation, you have to use a transformation operator (see Section 5.10).
Example 5. Create a transformation object
Create a transformation object to rotate a spherical object counterclockwise, first 20° around the x-axis, second -270° around the z-axis and last 70.5° around the y-axis.
mysql> SELECT strans( '20d, -270d, 70.5d, XZY' ); +-----------------------------------------------------------+ | strans( '20d, -270d, 70.5d, XZY' ) | +-----------------------------------------------------------+ | PGV0Pi0AAAAAAAAAh0TnShhX1j8YLURU+yH5PzEBz87zr/M/PHRlPgA= | +-----------------------------------------------------------+ 1 row in set (0.04 sec)
Example 6. Create a second transformation object
Create a transformation object to rotate a spherical object counterclockwise, first 2° 20' around the z-axis, second 10° around the x-axis, and last 0° around the z-axis.
mysql> SELECT strans( '2d 20m, 10d, 0' ); +-----------------------------------------------------------+ | strans( '2d 20m, 10d, 0' ) | +-----------------------------------------------------------+ | PGV0PjcAAAAAAAAAwwwLaNLZpD+HROdKGFfGPwAAAAAAAAAAPHRlPgA= | +-----------------------------------------------------------+ 1 row in set (0.00 sec)
Use cases are:
sites on earth having a maximum distance from another site
round cluster or nebula on sky sphere
a position with an undirected position error
A circle is specified using a spherical point (spoint) and a radius:
< point , radius >
The radius is given as the "opening angle" of the circle (when viewed from the center of the sphere). Valid radius units are RAD, DEG, and DMS. The circle radius must be less than or equal to 90° and cannot be less than zero.
Example 7. A circle around the North Pole with a radius of 5°.
mysql> SELECT scircle( '< (0d, 90d), 5d >' ); +-----------------------------------------------+ | scircle( '< (0d, 90d), 5d >' ) | +-----------------------------------------------+ | PGNsPgAAAAAAAAAAGC1EVPsh+T+HROdKGFe2PzxsYz4A | +-----------------------------------------------+ 1 row in set (0.00 sec)
Use cases are:
direct connection of two points on a sphere
meteors on the sky sphere
To allow lines with a length larger than 180°, the input i syntax is somewhat complex.
A general located line with a length
length
is defined as a line starting at
position (0d,0d) and ending at position
(length,0d) transformed with a Euler
transformation euler
. The input syntax
is :
( euler ), length
Note:
For a simpler line input, use casting operators (Section 5.1) or constructor functions (Section 4).
If the length is larger than 360°, the line length is truncated to 360°.
The transformation euler
will always be converted to a Euler
transformation using axes Z,
X, and Z.
Example 8. A line input
A line starting at position (200d,+20d) and ending at position (200d,-10d).
mysql> SELECT sline( '( -90d, -20d, 200d, XYZ ), 30d ' ); +-----------------------------------------------------------+ | sline( '( -90d, -20d, 200d, XYZ ), 30d ' ) | +-----------------------------------------------------------+ | PGxpPodE50oYVwZAGC1EVPsh+T9gofLYbIcaQGVzLThSweA/PGlsPgA= | +-----------------------------------------------------------+ 1 row in set (0.07 sec)
If the center of any spherical ellipse is the North Pole, the perpendicular projection into the x-y-plane gives an ellipse as in two-dimensional space.
Use cases are:
cluster or nebula on the sky sphere where the `height' is lower than the `width'
to describe a position error
An ellipse always has:
a major radius rad_1
a minor radius rad_2
a center center
a position angle pos
(inclination)
Hence, the input syntax is:
< { rad_1, rad_2 }, center, pos >
Note:
The radii rad_1
and
rad_2
have to be less than
90°.
If rad_1
is less than
rad_2
, the values will be
swapped.
The position angle pos
is defined within
mysql_sphere as a counterclockwise
rotation around the ellipse center and is zero, if the
ellipse is “parallel to the equator”
Example 9. Input of a spherical ellipse
An ellipse has a center at 20° longitude and 0° latitude. The minor radius is part of the equator and 5°, the major radius is 10°.
mysql> SELECT sellipse( '< { 10d, 5d } , ( 20d, 0d ), 90d >' ); +-----------------------------------------------------------------------+ | sellipse( '< { 10d, 5d } , ( 20d, 0d ), 90d >' ) | +-----------------------------------------------------------------------+ | PGVsPodE50oYV8Y/h0TnShhXtj8YLURU+yH5PwAAAAAAAAAAh0TnShhX1j88bGU+AA== | +-----------------------------------------------------------------------+ 1 row in set (0.03 sec)
rivers on earth
trajectories of planets or comets on the sky plane
Paths within mysql_sphere are simplified lists of positions. The input syntax is :
{pos1,pos2[,pos3[,pos4[,...]]]}
Note:
The distance between 2 sequent positions has to be less than 180° and greater than zero.
At least 2 positions are required.
Example 10. Path input example
A path going from (10d,0d) to (80d,30d) via (45d,15d).
mysql> SELECT spath( '{ (10d,0d),(45d,15d),(80d,30d) } ' ); +--------------------------------------------------------------------------------------------------------+ | spath( '{ (10d,0d),(45d,15d),(80d,30d) } ' ) | +--------------------------------------------------------------------------------------------------------+ | PHBhPkAAAAAAAAAAAwAAAAAAAACHROdKGFfGPwAAAAAAAAAAGC1EVPsh6T9lcy04UsHQP4dE50oY V/Y/ZXMtOFLB4D88YXA+AA== | +--------------------------------------------------------------------------------------------------------+ 1 row in set (0.02 sec)
{pos1,pos2,pos3[,pos4[,... ]]}
Note:
A spherical polygon has the same restrictions as a spherical path (see Section 3.7). Except that a polygon needs at least 3 positions.
The line segments can not be crossed.
The maximum dimension of a polygon must be less than 180°.
Example 11. Input of polygon
A polygon going from (270d,-10d). via (270d,30d) to (290d,10d) back to (270d,-10d)
mysql> SELECT spoly( '{ (270d,-10d), (270d,30d), (290d,10d) } '); +--------------------------------------------------------------------------------------------------------+ | spoly( '{ (270d,-10d), (270d,30d), (290d,10d) } ') | +--------------------------------------------------------------------------------------------------------+ | PHBnPkAAAAAAAAAAAwAAAAAAAADSITN/fNkSQIBE50oYV8a/0iEzf3zZEkBlcy04UsHgPxqW4QPu PhRAh0TnShhXxj88Z3A+AA== | +--------------------------------------------------------------------------------------------------------+
pos_sw
) and the northeast
(pos_ne
) edge of the box. The input
syntax is:
( pos_sw, pos_ne )
or
pos_sw, pos_ne
Note:
If the latitude of the southwest edge is larger than the latitude of the northeast edge, mysql_sphere swaps the edges.
If the longitude of the southwest edge is equal to the longitude of the northeast edge, mysql_sphere assumes a full latitude range, except that the latitudes are equal, too.
Example 12. Input of a full latitude range
A full latitude range between +20° and +23°.
mysql> SELECT sbox( '( (0d,20d), (0d,23d) )' ); +-----------------------------------------------------------+ | sbox( '( (0d,20d), (0d,23d) )' ) | +-----------------------------------------------------------+ | PGJ4PgAAAAAAAAAAh0TnShhX1j8YLURU+yEZQALCVon1sNk/PHhiPgA= | +-----------------------------------------------------------+ 1 row in set (0.01 sec)
Example 13. A simple coordinates range
A coordinate range between -10° and +10° in latitude and 350° and 10° in longitude.
mysql> SELECT sbox( '( (350d,-10d), (10d,+10d) )' ); +-----------------------------------------------------------+ | sbox( '( (350d,-10d), (10d,+10d) )' ) | +-----------------------------------------------------------+ | PGJ4PvTy7JFCbxhAgETnShhXxr+HROdKGFfGP4dE50oYV8Y/PHhiPgA= | +-----------------------------------------------------------+ 1 row in set (0.00 sec)