Types

pg_orrery defines nine fixed-size base types and one SQL composite type that represent the core data structures of orbital mechanics. Each base type has a fixed on-disk size, a text I/O format for readability, and accessor functions for extracting individual fields.

tle

Size: 112 bytes

A Two-Line Element set, the standard orbital element format maintained by NORAD and distributed by CelesTrak and Space-Track. Internally stores all SGP4/SDP4-relevant fields in parsed, double-precision form.

Input Format

Standard two-line TLE text. Both lines are concatenated into a single-quoted string with a newline separator:

SELECT '1 25544U 98067A   24001.50000000  .00016717  00000-0  10270-3 0  9025
2 25544  51.6400 208.9163 0006703  30.1694  61.7520 15.50100486 00001'::tle;

Constructor

Function	Signature	Description
`tle_from_lines`	`tle_from_lines(line1 text, line2 text) → tle`	Constructs a TLE from two separate line strings. Useful when lines are stored in separate columns.

SELECT tle_from_lines(
  '1 25544U 98067A   24001.50000000  .00016717  00000-0  10270-3 0  9025',
  '2 25544  51.6400 208.9163 0006703  30.1694  61.7520 15.50100486 00001'
);

Accessor Functions

Function	Return Type	Description
`tle_norad_id(tle)`	`int4`	NORAD catalog number
`tle_intl_desig(tle)`	`text`	International designator (e.g. `98067A`)
`tle_epoch(tle)`	`timestamptz`	Epoch as a PostgreSQL timestamp
`tle_inclination(tle)`	`float8`	Inclination in degrees
`tle_raan(tle)`	`float8`	Right Ascension of Ascending Node in degrees
`tle_eccentricity(tle)`	`float8`	Eccentricity (dimensionless)
`tle_arg_perigee(tle)`	`float8`	Argument of perigee in degrees
`tle_mean_anomaly(tle)`	`float8`	Mean anomaly in degrees
`tle_mean_motion(tle)`	`float8`	Mean motion in revolutions/day
`tle_bstar(tle)`	`float8`	B* drag coefficient (1/earth-radii)
`tle_period(tle)`	`float8`	Orbital period in minutes
`tle_perigee(tle)`	`float8`	Perigee altitude in km (above WGS-72 ellipsoid)
`tle_apogee(tle)`	`float8`	Apogee altitude in km (above WGS-72 ellipsoid)
`tle_age(tle)`	`interval`	Age of the TLE relative to `now()`

WITH iss AS (
  SELECT '1 25544U 98067A   24001.50000000  .00016717  00000-0  10270-3 0  9025
2 25544  51.6400 208.9163 0006703  30.1694  61.7520 15.50100486 00001'::tle AS tle
)
SELECT tle_norad_id(tle)    AS norad_id,
       tle_inclination(tle) AS inc_deg,
       tle_eccentricity(tle) AS ecc,
       tle_period(tle)       AS period_min,
       tle_perigee(tle)      AS perigee_km,
       tle_apogee(tle)       AS apogee_km,
       tle_epoch(tle)        AS epoch,
       tle_age(tle)          AS age
FROM iss;

eci_position

Size: 48 bytes

Earth-Centered Inertial position and velocity in the True Equator Mean Equinox (TEME) reference frame. Position components are in kilometers; velocity components are in km/s. This is the native output frame of the SGP4/SDP4 propagator.

Accessor Functions

Function	Return Type	Unit	Description
`eci_x(eci_position)`	`float8`	km	X position (TEME)
`eci_y(eci_position)`	`float8`	km	Y position (TEME)
`eci_z(eci_position)`	`float8`	km	Z position (TEME)
`eci_vx(eci_position)`	`float8`	km/s	X velocity (TEME)
`eci_vy(eci_position)`	`float8`	km/s	Y velocity (TEME)
`eci_vz(eci_position)`	`float8`	km/s	Z velocity (TEME)
`eci_speed(eci_position)`	`float8`	km/s	Magnitude of velocity vector
`eci_altitude(eci_position)`	`float8`	km	Geocentric altitude (distance from Earth center minus WGS-84 equatorial radius)

WITH iss AS (
  SELECT '1 25544U 98067A   24001.50000000  .00016717  00000-0  10270-3 0  9025
2 25544  51.6400 208.9163 0006703  30.1694  61.7520 15.50100486 00001'::tle AS tle
)
SELECT eci_x(pos)        AS x_km,
       eci_y(pos)        AS y_km,
       eci_z(pos)        AS z_km,
       eci_speed(pos)    AS speed_kms,
       eci_altitude(pos) AS alt_km
FROM iss, sgp4_propagate(tle, now()) AS pos;

geodetic

Size: 24 bytes

WGS-84 geodetic coordinates: latitude, longitude, and altitude above the reference ellipsoid.

Accessor Functions

Function	Return Type	Unit	Description
`geo_lat(geodetic)`	`float8`	degrees	Geodetic latitude (-90 to +90, north positive)
`geo_lon(geodetic)`	`float8`	degrees	Geodetic longitude (-180 to +180, east positive)
`geo_alt(geodetic)`	`float8`	km	Altitude above WGS-84 ellipsoid

WITH iss AS (
  SELECT '1 25544U 98067A   24001.50000000  .00016717  00000-0  10270-3 0  9025
2 25544  51.6400 208.9163 0006703  30.1694  61.7520 15.50100486 00001'::tle AS tle
)
SELECT geo_lat(g) AS lat,
       geo_lon(g) AS lon,
       geo_alt(g) AS alt_km
FROM iss, subsatellite_point(tle, now()) AS g;

topocentric

Size: 32 bytes

Observer-relative coordinates: azimuth, elevation, slant range, and range rate. This is the output of all *_observe functions.

Accessor Functions

Function	Return Type	Unit	Description
`topo_azimuth(topocentric)`	`float8`	degrees	Azimuth measured clockwise from true north (0-360)
`topo_elevation(topocentric)`	`float8`	degrees	Elevation above the local horizon (-90 to +90)
`topo_range(topocentric)`	`float8`	km	Slant range from observer to target
`topo_range_rate(topocentric)`	`float8`	km/s	Rate of change of range. Positive = receding from observer.

-- Where is Saturn from Boulder right now?
SELECT topo_azimuth(t)   AS az,
       topo_elevation(t) AS el,
       topo_range(t)     AS range_km
FROM planet_observe(6, '40.0N 105.3W 1655m'::observer, now()) AS t;

observer

Size: 24 bytes

An observer’s geodetic location on Earth. Used as input to all topocentric observation functions.

Input Format

A compact string encoding latitude, longitude, and optional altitude:

'40.0N 105.3W 1655m'

Latitude: decimal degrees followed by N or S
Longitude: decimal degrees followed by E or W
Altitude: meters followed by m (optional, defaults to 0)

SELECT '40.0N 105.3W 1655m'::observer;      -- Boulder, CO
SELECT '51.4769N 0.0005W 11m'::observer;     -- Greenwich Observatory
SELECT '35.6762N 139.6503E 40m'::observer;   -- Tokyo
SELECT '33.9S 18.5E 0m'::observer;           -- Cape Town

Constructor

Function	Signature	Description
`observer_from_geodetic`	`observer_from_geodetic(lat float8, lon float8, alt_m float8 DEFAULT 0) → observer`	Construct from numeric lat/lon (degrees) and altitude (meters). Latitude: north positive. Longitude: east positive.

-- These are equivalent:
SELECT '40.0N 105.3W 1655m'::observer;
SELECT observer_from_geodetic(40.0, -105.3, 1655);

pass_event

Size: 48 bytes

A satellite pass over an observer location, with AOS (Acquisition of Signal), maximum elevation, and LOS (Loss of Signal) timestamps plus geometry.

Accessor Functions

Function	Return Type	Unit	Description
`pass_aos_time(pass_event)`	`timestamptz`		Time the satellite rises above the horizon (or minimum elevation threshold)
`pass_max_el_time(pass_event)`	`timestamptz`		Time of maximum elevation (closest approach)
`pass_los_time(pass_event)`	`timestamptz`		Time the satellite sets below the horizon (or minimum elevation threshold)
`pass_max_elevation(pass_event)`	`float8`	degrees	Peak elevation during the pass
`pass_aos_azimuth(pass_event)`	`float8`	degrees	Azimuth at AOS
`pass_los_azimuth(pass_event)`	`float8`	degrees	Azimuth at LOS
`pass_duration(pass_event)`	`interval`		Duration from AOS to LOS

WITH iss AS (
  SELECT '1 25544U 98067A   24001.50000000  .00016717  00000-0  10270-3 0  9025
2 25544  51.6400 208.9163 0006703  30.1694  61.7520 15.50100486 00001'::tle AS tle
)
SELECT pass_aos_time(p)      AS rise,
       pass_max_el_time(p)   AS culmination,
       pass_max_elevation(p) AS max_el,
       pass_los_time(p)      AS set,
       pass_aos_azimuth(p)   AS rise_az,
       pass_los_azimuth(p)   AS set_az,
       pass_duration(p)      AS dur
FROM iss, predict_passes(tle, '40.0N 105.3W 1655m'::observer,
                         now(), now() + interval '24 hours', 10.0) AS p;

heliocentric

Size: 24 bytes

Heliocentric position in the ecliptic plane of J2000.0, measured in Astronomical Units (AU). This is the output of planet_heliocentric and kepler_propagate.

Accessor Functions

Function	Return Type	Unit	Description
`helio_x(heliocentric)`	`float8`	AU	X position (ecliptic J2000)
`helio_y(heliocentric)`	`float8`	AU	Y position (ecliptic J2000)
`helio_z(heliocentric)`	`float8`	AU	Z position (ecliptic J2000)
`helio_distance(heliocentric)`	`float8`	AU	Distance from the Sun (vector magnitude)

-- Heliocentric positions of all eight planets
SELECT body_id,
       helio_x(h) AS x_au,
       helio_y(h) AS y_au,
       helio_z(h) AS z_au,
       helio_distance(h) AS dist_au
FROM generate_series(1, 8) AS body_id,
     planet_heliocentric(body_id, now()) AS h;

orbital_elements

Size: 72 bytes

Classical Keplerian orbital elements for comets and asteroids. Stores nine doubles: osculation epoch, perihelion distance, eccentricity, inclination, argument of perihelion, longitude of ascending node, time of perihelion passage, absolute magnitude H, and slope parameter G.

Input Format

A parenthesized tuple of nine values:

SELECT '(2460605.5,2.5478,0.0789126,10.58664,73.42937,80.2686,2460319.0,3.33,0.12)'::orbital_elements;

The fields in order are: (epoch_jd, q_au, e, inc_deg, omega_deg, Omega_deg, tp_jd, H, G).

Constructor

Function	Signature	Description
`oe_from_mpc`	`oe_from_mpc(line text) → orbital_elements`	Parses one MPCORB.DAT fixed-width line. Converts MPC packed epoch, computes q and tp from (a, e, M).

-- Parse (1) Ceres from an MPCORB.DAT line
SELECT oe_from_mpc(
  '00001    3.33  0.12 K24AM  60.07966  73.42937   80.26860  10.58664  0.0789126  0.21406048   2.7660961  0 MPO838504  8738 115 1801-2024 0.65 M-v 30k MPCLINUX   0000      (1) Ceres              20240825'
);

Accessor Functions

Function	Return Type	Description
`oe_epoch(orbital_elements)`	`float8`	Osculation epoch (Julian date)
`oe_perihelion(orbital_elements)`	`float8`	Perihelion distance q (AU)
`oe_eccentricity(orbital_elements)`	`float8`	Eccentricity
`oe_inclination(orbital_elements)`	`float8`	Inclination (degrees)
`oe_arg_perihelion(orbital_elements)`	`float8`	Argument of perihelion (degrees)
`oe_raan(orbital_elements)`	`float8`	Longitude of ascending node (degrees)
`oe_tp(orbital_elements)`	`float8`	Time of perihelion passage (Julian date)
`oe_h_mag(orbital_elements)`	`float8`	Absolute magnitude H (NaN if unknown)
`oe_g_slope(orbital_elements)`	`float8`	Slope parameter G (NaN if unknown)
`oe_semi_major_axis(orbital_elements)`	`float8`	Semi-major axis a = q/(1-e) in AU. NULL for e ≥ 1.
`oe_period_years(orbital_elements)`	`float8`	Orbital period a^1.5 in years. NULL for e ≥ 1.

-- Parse Ceres and extract key parameters
WITH ceres AS (
  SELECT oe_from_mpc(
    '00001    3.33  0.12 K24AM  60.07966  73.42937   80.26860  10.58664  0.0789126  0.21406048   2.7660961  0 MPO838504  8738 115 1801-2024 0.65 M-v 30k MPCLINUX   0000      (1) Ceres              20240825'
  ) AS oe
)
SELECT oe_epoch(oe)           AS epoch_jd,
       oe_perihelion(oe)      AS q_au,
       oe_eccentricity(oe)    AS ecc,
       oe_inclination(oe)     AS inc_deg,
       oe_semi_major_axis(oe) AS a_au,
       oe_period_years(oe)    AS period_yr,
       oe_h_mag(oe)           AS h_mag
FROM ceres;

equatorial

Size: 24 bytes

Apparent equatorial coordinates of date. Stores three double-precision values: right ascension (radians internally, displayed as hours in the range [0, 24)), declination (radians internally, displayed as degrees in the range [-90, 90]), and distance in km.

For solar system bodies, these are J2000 coordinates precessed to the date of observation via IAU 1976 precession. For satellites, these are TEME frame RA/Dec, which approximates coordinates of date to arcsecond accuracy.

Input Format

A parenthesized tuple of three values:

(ra_hours,dec_degrees,distance_km)

SELECT '(4.29220000,20.60000000,885412345.678)'::equatorial;

Accessor Functions

Function	Return Type	Unit	Description
`eq_ra(equatorial)`	`float8`	hours	Right ascension [0, 24)
`eq_dec(equatorial)`	`float8`	degrees	Declination [-90, 90]
`eq_distance(equatorial)`	`float8`	km	Distance (0 for stars without parallax)

-- Geocentric RA/Dec of Jupiter
SELECT eq_ra(e)       AS ra_hours,
       eq_dec(e)      AS dec_deg,
       eq_distance(e) AS dist_km
FROM planet_equatorial(5, now()) AS e;

-- Compare RA/Dec of all planets
SELECT body_id,
       CASE body_id
         WHEN 1 THEN 'Mercury' WHEN 2 THEN 'Venus'
         WHEN 4 THEN 'Mars'    WHEN 5 THEN 'Jupiter'
         WHEN 6 THEN 'Saturn'  WHEN 7 THEN 'Uranus'
         WHEN 8 THEN 'Neptune'
       END AS planet,
       round(eq_ra(e)::numeric, 4)  AS ra_h,
       round(eq_dec(e)::numeric, 4) AS dec_deg
FROM unnest(ARRAY[1,2,4,5,6,7,8]) AS body_id,
     planet_equatorial(body_id, now()) AS e;

observer_window

Type: SQL composite (variable-length)

A query-time parameter that defines a ground observer’s visibility window. Unlike the seven base types above, observer_window is a SQL composite type --- it is not designed for table storage, but as an argument to the &? visibility cone operator.

Fields

Field	Type	Description
`obs`	`observer`	Ground location (latitude, longitude, altitude)
`t_start`	`timestamptz`	Start of observation window
`t_end`	`timestamptz`	End of observation window
`min_el`	`float8`	Minimum elevation angle in degrees

Construction

Construct with ROW(...)::observer_window syntax:

-- Eagle, Idaho: 6-hour window, 10 deg minimum elevation
SELECT tle
FROM catalog
WHERE tle &? ROW(
    observer('43.6977N 116.3535W 760m'),
    '2024-01-01 01:00:00+00'::timestamptz,
    '2024-01-01 07:00:00+00'::timestamptz,
    10.0
)::observer_window;