3. Data types

3.1. Overview

pgSphere provides spherical data types for storing with PostgreSQL. Furthermore, there is a data type to do transformations.

Table 1. Data types

SQL type namespherical type
spoint point (position)
strans Euler transformation
scircle circle
sline line
sellipse ellipse
spoly polygon
spath path
sbox coordinate range

3.2. Point

A spherical point is an object without expanse but with a position. Usage cases are:

A spherical point (or position) is given by two values: longitude and latitude. Longitude is a floating point value between 0 and . 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 can not use it with latitude values.

Example 1. A position specified using longitude and latitude in radians


sql> SELECT spoint '(0.1,-0.2)';
              

Example 2. A position specified using longitude and latitude in degrees


sql> SELECT spoint '( 10.1d, -90d)';
              

Example 3. A position specified using longitude and latitude (DMS)


sql> SELECT spoint '( 10d 12m 11.3s, -13d 14m)';
              

Example 4. A position specified using longitude in HMS, and latitude in RAD


sql> SELECT spoint '( 23h 44m 10s, -1.4321 )';
              

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.

3.3. Euler transformation

A Euler transformation is done with three counterclockwise object rotations around following the axes: x-axis, y-axis, or z-axis. Usage cases are:

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.


sql> SELECT strans '20d, -270d, 70.5d, XZY';
              

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.


sql> SELECT strans '2d 20m, 10d, 0';
              

3.4. Circle

A spherical circle is an area around a point, where all points inside the circle have a distance less than or equal to the radius of the circle. Usage cases are:

A circle is specified using a spherical point (spoint) and a radius :

< point , radius >

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.


sql> SELECT scircle '< (0d, 90d), 5d >';
              

3.5. Line

A spherical line is part of a great circle (meridian) that has a beginning and an end and hence, a direction. Usage cases are:

To allow lines with a length larger than 180, the input i syntax is a 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:

Example 8. A line input

A line starting at position (200d,+20d) and ending at position (200d,-10d).


sql> SELECT sline '( -90d, -20d, 200d, XYZ ), 30d ';
              

3.6. Ellipses

Within pgSphere, ellipses are defined as :

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.

Usage cases are:

An ellipse always has:

Hence, the input syntax is:

< { rad_1, rad_2 }, center, pos >

Note:

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. The major radius is 10, the minor radius is 5.


sql> SELECT sellipse '< { 10d, 5d } , ( 20d, 0d ), 90d >';
               

3.7. Path

A spherical path is a concatination of spherical lines. Usage cases are:

Paths within pgSphere are simplified lists of positions. The input syntax is :

{pos1,pos2[,pos3[,pos4[,...]]]}

Note:

Example 10. Path input example

A path going from (10d,0d) to (80d,30d) via (45d,15d).


sql> SELECT spath '{ (10d,0d),(45d,15d),(80d,30d) } ';
              

3.8. Polygon

A spherical polygon is a closed spherical path where line segments cannot be crossed. One main usage case are areas on the earth and sky sphere. Polygons within pgSphere have the same input syntax as paths:

{pos1,pos2,pos3[,pos4[,... ]]}

Note:

Example 11. Input of polygon

A polygon going from (270d,-10d). via (270d,30d) to (290d,10d) back to (270d,-10d)


sql> SELECT spoly '{ (270d,-10d), (270d,30d), (290d,10d) } ';
              

3.9. Coordinates range

A spherical box is a coordinates range. Hence, you can select objects within a longitude range and latitude range. The box is represented using two spherical points: the southwest (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:

Example 12. Input of a full latitude range

A full latitude range between +20 and +23.


sql> SELECT sbox '( (0d,20d), (0d,23d) )';
              

Example 13. A simple coordinates range

A coordinate range between -10 and +10 in latitude and 350 and 10 in longitude.


sql> SELECT sbox '( (350d,-10d), (10d,+10d) )';