Constructors within mysql_sphere are in no way different to defining mysql_sphere objects, just with other options. They are in a way overloaded construction functions from the previous chapter. In pgSphere a distinction between type definition and construction was made, mainly due to the capabilities of PostgreSQL. In mysql_sphere this is not needed anymore and the following sections describe functions needed to create spherical data types from other data types. mysql_sphere constructor functions are named by returned data type. Constructor functions expecting one parameter only are implemented as casting functions, a distinction necessary in PostgreSQL. These casting functions will only be mentioned here and no example is given.
There are two constructor functions for spherical points.
where lng is the longitude of the spherical
point in radians, lat is the latitude of
the spherical point in radians.
spoint(spath path, int i);
returning the i-th point in a path, where path is a mysql_sphere path binary BLOB and i is the i-th point in the path (start counting with 1).
Example 14. A spherical point from longitude and latitude
Get a spherical position with 270° of longitude and -30° of latitude.
mysql> SELECT spoint( 270.0*pi()/180.0,-30.0*pi()/180.0 ); +---------------------------------------------+ | spoint( 270.0*pi()/180.0,-30.0*pi()/180.0 ) | +---------------------------------------------+ | PHB0PtIhM3982RJAaHMtOFLB4L88dHA+AA== | +---------------------------------------------+ 1 row in set (0.05 sec)
There are two constructor functions for a Euler transformation:
strans(double phi, double theta, double psi);
strans(double phi, double theta, double psi, character axis);
where phi, theta and
psi are the three angles of
Euler transformation. The fourth parameter is the three letter code
of the Euler transformation axes. If that parameter is omitted,
mysql_sphere will assume ZXZ.
For more information about that parameter, see Section 3.3.
Further casting constructors are:
Example 15. Create a Euler 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 ( 20.0*pi()/180.0, -270.0*pi()/180.0, 70.5*pi()/180.0, 'XZY'); +----------------------------------------------------------------------+ | strans ( 20.0*pi()/180.0, -270.0*pi()/180.0, 70.5*pi()/180.0, 'XZY') | +----------------------------------------------------------------------+ | PGV0Pi0AAAAAAAAAh0TnShhX1j8YLURU+yH5PzABz87zr/M/PHRlPgA= | +----------------------------------------------------------------------+ 1 row in set (0.01 sec)
The function
returns a spherical circle with center at
center and a radius
radius in radians. The circle radius has
to be larger than or equal to zero but less or equal to 90°.
Otherwise, this function returns an error.
Further casting constructors are:
Example 16. A circle around the North Pole
Get a spherical circle around the North Pole with a radius of 30° ( sstr casts a given mysql_sphere data type to a readable string).
mysql> SET sphere_outmode="DEG";
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT scircle ( spoint( '(0d,90d)' ), 30.0*pi()/180.0 );
+---------------------------------------------------+
| scircle ( spoint( '(0d,90d)' ), 30.0*pi()/180.0 ) |
+---------------------------------------------------+
| PGNsPgAAAAAAAAAAGC1EVPsh+T9lcy04UsHgPzxsYz4A |
+---------------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT sstr( scircle ( spoint( '(0d,90d)' ), 30.0*pi()/180.0 ) );
+-----------------------------------------------------------+
| sstr( scircle ( spoint( '(0d,90d)' ), 30.0*pi()/180.0 ) ) |
+-----------------------------------------------------------+
| <(0d , 90d) , 30d> |
+-----------------------------------------------------------+
1 row in set (0.02 sec)
The input of spherical lines using Euler transformation and length is quite circumstantial (see Section 3.5). For short lines it is easier to input a line specifying the beginning and the end of the line.
If the distance between begin and
end is 180° (π), this function
returns an error because the location of the line is undefined.
However, if longitudes of begin and
end are equal,
pgSphere assumes a meridian and
returns the corresponding spherical line.
Example 17. A line created using begin and end of line
A line starting at spoint '(270d,10d)' and ending at spoint '(270d,30d)' ( sstr casts a given mysql_sphere data type to a readable string):
mysql> SET sphere_outmode="DEG"; Query OK, 0 rows affected (0.02 sec) mysql> SELECT sline( spoint( '(270d,10d)' ), spoint( '(270d,30d)') ); +-----------------------------------------------------------+ | sline( spoint( '(270d,10d)' ), spoint( '(270d,30d)') ) | +-----------------------------------------------------------+ | PGxpPodE50oYV8Y/GC1EVPsh+T/SITN/fNkSQIVE50oYV9Y/PGlsPgA= | +-----------------------------------------------------------+ 1 row in set (0.01 sec) mysql> SELECT sstr( sline( spoint( '(270d,10d)' ), spoint( '(270d,30d)') ) ); +----------------------------------------------------------------+ | sstr( sline( spoint( '(270d,10d)' ), spoint( '(270d,30d)') ) ) | +----------------------------------------------------------------+ | ( 10d, 90d, 270d, ZXZ ), 20d | +----------------------------------------------------------------+ 1 row in set (0.01 sec)
It is possible to extract the beginning and end of a spherical line object using the sline_beg and sline_end functions.
Furthermore, there is a function for inputing a line
using Euler transformation trans
and line length length:
where the line length length must be
given in radians.
Example 18. A line created with its transformation and length
The same line as in Example 17, but using transformation and line length.
mysql> SELECT sstr( sline ( strans( '10d, 90d, 270d, ZXZ' ), 20.0*pi()/180.0 ) ); +--------------------------------------------------------------------+ | sstr( sline ( strans( '10d, 90d, 270d, ZXZ' ), 20.0*pi()/180.0 ) ) | +--------------------------------------------------------------------+ | ( 10d, 90d, 270d, ZXZ ), 20d | +--------------------------------------------------------------------+ 1 row in set (0.01 sec)
You can use the function
to create a spherical ellipse. The first parameter
center is the center of ellipse. The
parameter major_rad and
minor_rad are the major and the minor
radii of the ellipse in radians. If the major radius is smaller
than the minor radius, mysql_sphere swaps the
values automatically. The last parameter
incl is the inclination angle in radians.
For more informations about ellipses, see Section 3.6.
Example 19. Create an ellipse
An ellipse with a center at 20° of longitude and 0° of latitude. The minor radius is part of the equator and has a size of 5°. The major radius has 10°. ( sstr casts a given mysql_sphere data type to a readable string)
mysql> SET sphere_outmode="DEG";
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT sellipse ( spoint( '( 20d, 0d )' ), 10.0*pi()/180.0, 5.0*pi()/180.0, pi()/2.0 );
+---------------------------------------------------------------------------------+
| sellipse ( spoint( '( 20d, 0d )' ), 10.0*pi()/180.0, 5.0*pi()/180.0, pi()/2.0 ) |
+---------------------------------------------------------------------------------+
| PGVsPodE50oYV8Y/h0TnShhXtj8YLURU+yH5PwAAAAAAAAAAh0TnShhX1j88bGU+AA== |
+---------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT sstr( sellipse ( spoint( '( 20d, 0d )' ), 10.0*pi()/180.0, 5.0*pi()/180.0, pi()/2.0 ) );
+-----------------------------------------------------------------------------------------+
| sstr( sellipse ( spoint( '( 20d, 0d )' ), 10.0*pi()/180.0, 5.0*pi()/180.0, pi()/2.0 ) ) |
+-----------------------------------------------------------------------------------------+
| <{ 10d , 5d }, (20d , -0d) , 90d> |
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
The aggregate function
can be used to create a polygon from a set of spherical points. Here the same restrictions apply as for using the input function of spherical polygon (see Section 3.8). The function returns NULL, if the polygon couldn't be created.
Example 20. Create a spherical polygon using a set of spherical points
Create a table and fill it with some spherical points with a unique ID. Then, create two polygons with different edge sequences ( sstr casts a given mysql_sphere data type to a readable string).
mysql> SET sphere_outmode="DEG";
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE points ( i int PRIMARY KEY, p BLOB );
mysql> INSERT INTO points VALUES (1, spoint( '( 0d, 0d)' ) );
mysql> INSERT INTO points VALUES (2, spoint( '(10d, 0d)') );
mysql> INSERT INTO points VALUES (3, spoint( '( 0d,10d)') );
mysql> SELECT spoly_aggr(data.p) FROM ( SELECT p FROM points ORDER BY i ASC ) AS data ;
+--------------------------------------------------------------------------------------------------------+
| spoly_aggr(data.p) |
+--------------------------------------------------------------------------------------------------------+
| PHBnPkAAAAAAAAAAAwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAh0TnShhXxj8AAAAAAAAAAAAAAAAA
AAAAh0TnShhXxj88Z3A+AA== |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.28 sec)
mysql> SELECT sstr( spoly_aggr(data.p) ) FROM ( SELECT p FROM points ORDER BY i ASC ) AS data ;
+-----------------------------------+
| sstr( spoly_aggr(data.p) ) |
+-----------------------------------+
| {(0d , 0d),(10d , 0d),(0d , 10d)} |
+-----------------------------------+
1 row in set (0.03 sec)
sql> SELECT sstr( spoly_aggr(data.p) ) FROM ( SELECT p FROM points ORDER BY i DESC ) AS data ;
+-----------------------------------+
| sstr( spoly_aggr(data.p) ) |
+-----------------------------------+
| {(0d , 10d),(10d , 0d),(0d , 0d)} |
+-----------------------------------+
1 row in set (0.02 sec)
Similar to spherical polygons, you can use the aggregate function
to create a spherical path using a set of spherical points. There are the same restrictions as with the input function of spherical path (see Section 3.7). The function returns NULL if the path couldn't be created.
Example 21. Create a spherical path using a set of spherical points
Create a table and put in some spherical points with a unique ID. Then, create a spherical path from it ( sstr casts a given mysql_sphere data type to a readable string).
mysql> SET sphere_outmode="DEG";
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE points ( i int PRIMARY KEY, p BLOB );
mysql> INSERT INTO points VALUES (1, spoint( '( 0d, 10d)') );
mysql> INSERT INTO points VALUES (2, spoint( '( 0d, 0d)') );
mysql> INSERT INTO points VALUES (3, spoint( '( 0d,-10d)') );
mysql> SELECT spath_aggr(data.p) FROM ( SELECT p FROM points ORDER BY i ASC ) AS data ;
+--------------------------------------------------------------------------------------------------------+
| spath_aggr(data.p) |
+--------------------------------------------------------------------------------------------------------+
| PHBhPkAAAAAAAAAAAwAAAAAAAAAAAAAAAAAAAIdE50oYV8Y/AAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAgETnShhXxr88YXA+AA== |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> SELECT sstr( spath_aggr(data.p) ) FROM ( SELECT p FROM points ORDER BY i ASC ) AS data ;
+------------------------------------+
| sstr( spath_aggr(data.p) ) |
+------------------------------------+
| {(0d , 10d),(0d , 0d),(0d , -10d)} |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT sstr( spath_aggr(data.p) ) FROM ( SELECT p FROM points ORDER BY i DESC ) AS data ;
+------------------------------------+
| sstr( spath_aggr(data.p) ) |
+------------------------------------+
| {(0d , -10d),(0d , 0d),(0d , 10d)} |
+------------------------------------+
1 row in set (0.01 sec)
The function
creates an sbox object with its first parameter
south_west as the southwest edge and its
second parameter northeast as the
north-east edge of the coordinates range.
Example 22. Create a spherical box using edges
A coordinate range between 0° and +10° in latitude and longitude.
mysql> SELECT sbox( spoint( '(0d,0d)' ), spoint( '(10d,10d)' ) ); +-----------------------------------------------------------+ | sbox( spoint( '(0d,0d)' ), spoint( '(10d,10d)' ) ) | +-----------------------------------------------------------+ | PGJ4PgAAAAAAAAAAAAAAAAAAAACHROdKGFfGP4dE50oYV8Y/PHhiPgA= | +-----------------------------------------------------------+ 1 row in set (0.01 sec)