5. Operator functions

MySQL does not allow the definition of custom operators for specific types like PostgresSQL does. Therefore operators are implemented as function calls. Operator functions will return 0 if they are evaluated as false and 1 if true.

5.1. Casting

mysql_sphere provides different casting mechanisms than possible with pgSphere. These casting methods have been described in the section before, however will be summarised here again. So, you can transform an object to another data type. A cast in mysql_sphere can only be done using a typename(x) construct.

Table 2. Castings

casting argumenttype targetreturns
spoint scircle circle with center position spoint and radius 0.0
spoint sellipse an ellipse at position spoint and radius 0.0
spoint sline a line with length 0.0 at position spoint
scircle sellipse the scircle as sellipse
sline strans the Euler transformation of sline
sellipse scircle the bounding circle of sellipse
sellipse strans the Euler transformation of sellipse
all available types sstr a string representation of the according mysql_sphere type

Example 23. Cast a spherical point as a circle


mysql> SELECT sstr( scircle ( spoint( '(10d,20d)' ) ) );
+-------------------------------------------+
| sstr( scircle ( spoint( '(10d,20d)' ) ) ) |
+-------------------------------------------+
| <(10d , 20d) , 0d>                        |
+-------------------------------------------+
1 row in set (0.01 sec)

5.2. Equality

All data types of mysql_sphere have the sequal( x, y ) equality function. The corresponding negation function is snotequal( x, y ).

Example 24. Equality of two spherical points


mysql> SELECT sequal( spoint( '(10d,20d)' ), spoint( '(370d,20d)' ) );
+---------------------------------------------------------+
| sequal( spoint( '(10d,20d)' ), spoint( '(370d,20d)' ) ) |
+---------------------------------------------------------+
|                                                       1 |
+---------------------------------------------------------+
1 row in set (0.01 sec)

5.3. Contain and overlap

On spheres, an equality relationship is rarely used. There are frequently questions like Is object a contained by object b? or Does object a overlap object b? mysql_sphere supports such queries using special functions returning 1 for true or 0 for false:

Table 3. Contain and overlap operators

mysql_sphere operatorpgSphere equivalentoperator returns true, if
scontainsl @ the left object is contained by right object
slcontainsr ˜ the left object contains right object
srnotcontainsl !@ the left object is not contained by right object
slnotcontainsr the left object does not contain right object
soverlaps && the objects overlap each other
snotoverlaps !&& the objects do not overlap each other

An overlap or contain operator does not exist for all combinations of data types. For instance, srcontainsl( scircle, spoint ) is useless because a spherical point can never contain a spherical circle.

Example 25. Is the left circle contained by the right circle?


mysql> SELECT srcontainsl( scircle( '<(0d,20d),2d>' ), scircle( '<(355d,20d),10d>' ) ) AS test ;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.02 sec)
              

Example 26. Are the circles overlapping?


mysql> SELECT soverlaps( scircle( '<(0d,20d),2d>' ), scircle( '<(199d,-10d),10d>' ) ) AS test ;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.01 sec)
              

5.4. Crossing of lines

Another binary relationship is crossing. mysql_sphere supports only crossing of lines. Therefor the correlative function is named sline_crosses.

Example 27. Are the lines crossed?


mysql> SELECT sline_crosses( sline( '(0d,0d,0d),10d' ), sline( '(90d,5d,5d,XYZ),10d' ) ) AS test ;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.01 sec)
              

5.5. Distance

The distance function sdist is a non-boolean function returning the distance between two objects in radians. Currently, mysql_sphere supports only distances between points, circles, and between point and circle. If the objects are overlapping, the distance operator returns zero (0.0).

Example 28. Distance between two circles


mysql> SELECT 180 * ( sdist( scircle( '<(0d,20d),2d>' ), scircle( '<(0d,40d),2d>' ) ) ) / pi() AS dist ;
+--------------------+
| dist               |
+--------------------+
| 15.999999999999993 |
+--------------------+
1 row in set (0.05 sec)

5.6. Length and circumference

The length/circumference function scircum or slength is a non-boolean unary operator returning the cirumference or length of an object. In the current implementation, mysql_sphere supports only circumferences of circles, polygons, and boxes. It supports lengths of lines and paths too.

Example 29. Circumference of a circle


mysql> SELECT 180 * ( scircum( scircle( '<(0d,20d),30d>' ) ) )/ pi() AS circ ;
+--------------------+
| circ               |
+--------------------+
| 179.99999999999997 |
+--------------------+
1 row in set (0.03 sec)

Example 30. Length of a line


mysql> SELECT 180 * ( slength( sline( '(0d,0d,0d),30d' ) ) )/ pi() AS length ;
+--------------------+
| length             |
+--------------------+
| 29.999999999999996 |
+--------------------+
1 row in set (0.01 sec)
              

5.7. Center

The center function scenter is a non-boolean unary function returning the center of an object. In the current implementation of mysql_sphere, only centers of circles and ellipses are supported.

Example 31. Center of a circle


mysql> SET sphere_outmode="DEG";
Query OK, 0 rows affected (0.02 sec)


mysql> SELECT sstr( scenter( scircle( '<(0d,20d),30d>' ) ) );
 +------------------------------------------------+
| sstr( scenter( scircle( '<(0d,20d),30d>' ) ) ) |
+------------------------------------------------+
| (0d , 20d)                                     |
+------------------------------------------------+
1 row in set (0.00 sec)
             

5.8. Change the direction

The unary function sswap changes the direction of sline or spath objects. You can use it with a Euler transformation object in the figurative sense, too (Section 5.10).

Example 32. Swap begin and end of a sline


mysql> SET sphere_outmode="DEG";
Query OK, 0 rows affected (0.02 sec)


mysql> SELECT sstr( sswap( sline ( spoint( '(0d,0d)' ), spoint( '(10d,0d)' ) ) ) );
+----------------------------------------------------------------------+
| sstr( sswap( sline ( spoint( '(0d,0d)' ), spoint( '(10d,0d)' ) ) ) ) |
+----------------------------------------------------------------------+
| ( 350d, 180d, 0d, ZXZ ), 10d                                         |
+----------------------------------------------------------------------+
1 row in set (0.02 sec)
              

5.9. Turn the path of a line

The unary function sline_turn turns the path of sline objects, but preserves begin and end of the spherical line. You could also imagine the resulting line as the complementary of the input line on a great circle. Thus, the length of the returned line will be 360° minus the line length of the operator's argument.

The operator sline_turn returns NULL, if the length of sline argument is 0, because the path of returned sline is undefined. One could also

Example 33. Return length and check if north pole on slines


mysql> SET sphere_outmode="DEG";
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT slength( sline( spoint( '(0d,0d)' ), spoint( '(0d,10d)' ) ) ) * 180.0 / pi() AS length;
+--------------------+
| length             |
+--------------------+
| 10.000000000000012 |
+--------------------+
1 row in set (0.02 sec)

mysql> SELECT srcontainsl( spoint( '(0d,90d)' ), sline( spoint( '(0d,0d)' ), spoint( '(0d,10d)' ) ) ) AS test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> SELECT slength( sline_turn( sline( spoint( '(0d,0d)' ), spoint( '(0d,10d)' ) ) ) ) * 180.0 / pi() AS length;
+--------------------+
| length             |
+--------------------+
| 350.00000000000006 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT srcontainsl( spoint( '(0d,90d)' ), sline_turn( sline( spoint( '(0d,0d)' ), spoint( '(0d,10d)' ) ) ) ) AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

5.10. Transformation

As in a plane, translations and rotations are needed to do object or coordinate transformations. With mysql_sphere, it is done using Euler transformations (strans). On a sphere, there aren't real translations. All movements on a sphere are rotations around axes.

The general syntax for a transformation is always:

functions( object, euler )

where functions are stransform for a usual transformation, sinverse for an inverse transformation. You can transform any object having a mysql_sphere data type, except the data type sbox.

Example 34. Transformation of a point

Rotate a point on a sphere counterclockwise, first 90° around the x-axis, second 90° around the z-axis, and last 40.5° around the x-axis.


mysql> SET sphere_outmode="DEG";
Query OK, 0 rows affected (0.02 sec)

sql> SELECT sstr( stransform( spoint( '(30d,0d)' ), strans( '90d, 90d, 40.5d, XZX' ) ) );
+------------------------------------------------------------------------------+
| sstr( stransform( spoint( '(30d,0d)' ), strans( '90d, 90d, 40.5d, XZX' ) ) ) |
+------------------------------------------------------------------------------+
| (90d , 70.5d)                                                                |
+------------------------------------------------------------------------------+

To obtain the inverse of a transformation, use sinverse instead of stransform:

Example 35.1 An inverse transformation


mysql> SET sphere_outmode="DEG";
Query OK, 0 rows affected (0.02 sec)


mysql> SELECT sstr( sinverse( spoint( '(90d,70.5d)' ), strans( '90d, 90d, 40.5d, XZX' ) ) );
+-------------------------------------------------------------------------------+
| sstr( sinverse( spoint( '(90d,70.5d)' ), strans( '90d, 90d, 40.5d, XZX' ) ) ) |
+-------------------------------------------------------------------------------+
| (30d , -1.130303475e-16d)                                                     |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Alternatively, one can also use the inverse of the Euler transformation with strans_invert, which takes an strans-object as argument, as shown in the following example:

Example 35.2 Inverse transformation by using inverted Euler

Transform the point back to the original point by using strans_invert to invert the Euler-expression:


mysql> SET sphere_outmode="DEG";
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT sstr( stransform( spoint( '(90d,70.5d)' ), strans_invert( strans('90d, 90d, 40.5d, XZX') ) ) );
+------------------------------------------------------------------------------------------------+
| sstr( stransform( spoint( '(90d,70.5d)' ), strans_invert( strans('90d, 90d, 40.5d, XZX') ) ) ) |
+------------------------------------------------------------------------------------------------+
| (30d , -1.130303475e-16d)                                                                      |
+------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

And here is another example, equivalent to Example 35 of the pgSphere documentation:


mysql> SET sphere_outmode="DEG";
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT sstr( strans_invert( strans( '20d, 50d, 80d, XYZ' ) ) ) AS inverted;
+-----------------------+
| inverted              |
+-----------------------+
| 280d, 310d, 340d, ZYX |
+-----------------------+
1 row in set (0.02 sec)