mysql_sphere 0.1 Based on pgSphere 1.1.1
Prev		Next

6. Functions

The functions described below are implemented in pgSphere without having an operator. In mysql_sphere they are equally used, but their names just differ by a prepending 's' to avoid conflict with already existing functions. If you are missing some functions, see Section 5 and use the according operator functions.

6.1. Area function

The area function returns the area of a spherical object in square radians. Supported data types are: scircle, spolygon, and sbox.

Example 36. Area of a spherical circle as a multiple of π

mysql> SELECT sarea( scircle( '<(0d,90d),60d>' ) ) / pi() AS area;
+--------------------+
| area               |
+--------------------+
| 0.9999999999999997 |
+--------------------+
1 row in set (0.07 sec)

6.2. spoint functions

6.2.1. Longitude and latitude

The functions

spoint_long(spoint p);

spoint_lat(spoint p);

return the longitude or latitude value of a spherical position p in radians.

Example 37. Get the longitude and latitude of a spherical point in degrees

mysql> SELECT spoint_long( spoint( '(10d,20d)' ) ) * 180.0 / pi() AS longitude;
+-----------+
| longitude |
+-----------+
|        10 |
+-----------+
1 row in set (0.04 sec)

mysql> SELECT spoint_lat( spoint( '(10d,20d)' ) ) * 180.0 / pi() AS latitude;
+----------+
| latitude |
+----------+
|       20 |
+----------+
1 row in set (0.00 sec)

6.2.2. Cartesian coordinates

The functions

spoint_x(spoint p);

spoint_y(spoint p);

spoint_z(spoint p);

return the cartesian x, y or z value of a spherical position p. The returned values are always between -1.0 and +1.0.

Example 38. Get the cartesian z-value of a spherical point

mysql> SELECT spoint_z ( spoint( '(10d,-90d)' ) ) AS z;
+------+
| z    |
+------+
|   -1 |
+------+
1 row in set (0.01 sec)

The pgSphere function xyz(spoint p) cannot be ported to MySQL since a UDF function can only return one value and not a rowset as in PostgreSQL. Therefore the equivalent mysql_sphere function does not exist.

6.3. strans functions

6.3.1. Converting to ZXZ

Using the function strans_zxz(strans), you can convert a Euler transformation to ZXZ-axes transformation.

Example 40. Change the transformation axes to ZXZ

Convert the transformation strans '20d, -270d, 70.5d, XZY' to a ZXZ-transformation.

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

mysql> SELECT sstr( strans_zxz( strans( '20d, -270d, 70.5d, XZY' ) ) );
+----------------------------------------------------------+
| sstr( strans_zxz( strans( '20d, -270d, 70.5d, XZY' ) ) ) |
+----------------------------------------------------------+
| 0d, 90.5d, 90d, ZXZ                                      |
+----------------------------------------------------------+
1 row in set (0.03 sec)

6.3.2. Angles and axes

It is possible to get the components of an Euler transformation with following functions:

Table 4. Getting Euler transformation attributes

function	description
`strans_phi`	first angle of a transformation
`strans_theta`	second angle of a transformation
`strans_psi`	third angle of a transformation
`strans_axes`	transformation axes as a three letter code

The angles will always be returned as a double value in radians. The axes are returned as a three letter code.

Example 41. Get the second axis and its rotation angle

mysql> SELECT strans_theta( strans( '20d,30d,40d,XZY' ) ) * 180 / pi() AS theta;
+--------------------+
| theta              |
+--------------------+
| 29.999999999999996 |
+--------------------+
1 row in set (0.01 sec)

mysql> SELECT SUBSTRING( strans_axes( strans( '20d,30d,40d,XZY' ) ) FROM 2 FOR 1 ) AS axis;
+------+
| axis |
+------+
| Z    |
+------+
1 row in set (0.19 sec)

6.5. sellipse functions

mysql_sphere provides 4 functions to get the parameters of a spherical ellipse:

Table 5. Getting spherical ellipse attributes

function	description
`sellipse_lrad`	the major radius of the ellipse
`sellipse_srad`	the minor radius of the ellipse
`scenter`	the center of the ellipse
`sellipse_inc`	the inclination of the ellipse

Example 43. Get the minor radius of an ellipse

mysql> SELECT sellipse_srad ( sellipse( '< { 10d, 5d }, ( 20d, 0d ), 90d >' ) ) * 180.0/ pi() AS srad ;
+------+
| srad |
+------+
|    5 |
+------+
1 row in set (0.03 sec)

6.6. sline functions

6.6.1. Begin and end

To get the beginning and the end of a line, mysql_sphere provides two functions:

sline_beg(sline line);

sline_end(sline line);

Example 44. Get the beginning of a line

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

mysql> SELECT sstr( sline_beg( sline( '(10d, 90d, 270d, ZXZ ), 20d' ) ) );
+-------------------------------------------------------------+
| sstr( sline_beg( sline( '(10d, 90d, 270d, ZXZ ), 20d' ) ) ) |
+-------------------------------------------------------------+
| (270d , 10d)                                                |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

6.6.2. Create a meridian

You can create a meridian as a line using the function

smeridian(double lng);

The function returns a line starting at a latitude of -90° and ending at a latitude of 90°. The line goes along the given longitude lng in radians.

Example 45. A meridian for longitude 20°

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

mysql> SELECT sstr( smeridian( 20.0 *pi() / 180.0 ) );
+-----------------------------------------+
| sstr( smeridian( 20.0 *pi() / 180.0 ) ) |
+-----------------------------------------+
| ( 270d, 90d, 20d, ZXZ ), 180d           |
+-----------------------------------------+
1 row in set (0.01 sec)

6.7. spath functions

6.7.1. Count of points

You can get the count of points of a spherical path (and as well for polygons) using the function:

snpoints(spath path);

Example 46. Count of spath's points

mysql> SELECT snpoints( spath( '{(0,0),(1,0)}' ) );
+--------------------------------------+
| snpoints( spath( '{(0,0),(1,0)}' ) ) |
+--------------------------------------+
|                                    2 |
+--------------------------------------+
1 row in set (0.03 sec)

6.7.2. Positions at a path

mysql_sphere provides two functions to get points at a path.

spoint(spath path, int i);

spoint(spath path, double f);

The first function returns the i-th point of a path. If i is less than 1 or larger than the count of spath points, the function returns NULL. The second function does nearly the same, but does linear interpolation between edge positions.

Example 47. Get the “center” of a one segment spath

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

mysql> SELECT sstr( spoint( spath( '{(0d,0d),(30d,0d)}' ), 1.5 ) );
+------------------------------------------------------+
| sstr( spoint( spath( '{(0d,0d),(30d,0d)}' ), 1.5 ) ) |
+------------------------------------------------------+
| (15d , 0d)                                           |
+------------------------------------------------------+
1 row in set (0.01 sec)

6.8. spoly functions

6.8.1. Count of edges

Similar to an spath (Section 6.7.1), you can get the count of edges of a spherical polygon using the function:

snpoints(spoly polygon);

Example 48. Count of edges of a spherical polygon

mysql> SELECT snpoints( spoly( '{(0,0),(1,0),(1,1)}' ) );
+--------------------------------------------+
| snpoints( spoly( '{(0,0),(1,0),(1,1)}' ) ) |
+--------------------------------------------+
|                                          3 |
+--------------------------------------------+
1 row in set (0.00 sec)

6.9. sbox functions

The functions

sbox_sw(sbox box);

sbox_ne(sbox box);

sbox_se(sbox box);

sbox_nw(sbox box);

return the corresponding southwest, northeast, southeast, or northwest edge. The returned value will be a spherical point.

Example 49. The southwest edge of a box

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

mysql> SELECT sstr( sbox_sw( sbox( '( (0d,0d), (90d,0d) )' ) ) );
+----------------------------------------------------+
| sstr( sbox_sw( sbox( '( (0d,0d), (90d,0d) )' ) ) ) |
+----------------------------------------------------+
| (0d , 0d)                                          |
+----------------------------------------------------+
1 row in set (0.00 sec)

Prev	Home	Next
Operators		Create an index