711 lines
30 KiB
Plaintext
711 lines
30 KiB
Plaintext
# pg_orrery — Complete LLM Reference
|
||
|
||
> Celestial mechanics types and functions for PostgreSQL. Native C extension (v0.15.0) with 151 SQL objects (135 user-visible functions + 16 GiST support), 9 custom types + 1 composite, GiST/SP-GiST indexing. All functions PARALLEL SAFE.
|
||
|
||
- Source: https://git.supported.systems/warehack.ing/pg_orrery
|
||
- Docs: https://pg-orrery.warehack.ing
|
||
- Requires: PostgreSQL 14–18
|
||
- Install: `CREATE EXTENSION pg_orrery;`
|
||
|
||
## Types
|
||
|
||
All base types are fixed-size, STORAGE = plain, ALIGNMENT = double. No TOAST.
|
||
|
||
### tle (112 bytes)
|
||
|
||
Parsed Two-Line Element set for SGP4/SDP4 propagation. Text I/O is the standard two-line format.
|
||
|
||
```sql
|
||
-- Input: standard TLE two-line format (line1 + newline + line2)
|
||
SELECT '1 25544U 98067A 24001.50000000 .00016717 00000-0 10270-3 0 9002
|
||
2 25544 51.6400 208.5000 0007417 35.0000 325.0000 15.49000000000000'::tle;
|
||
|
||
-- Or from separate line columns:
|
||
SELECT tle_from_lines(line1, line2) FROM raw_tles;
|
||
```
|
||
|
||
Accessors: `tle_epoch(tle) → float8` (Julian date), `tle_norad_id(tle) → int4`, `tle_inclination(tle) → float8` (degrees), `tle_eccentricity(tle) → float8`, `tle_raan(tle) → float8` (degrees), `tle_arg_perigee(tle) → float8` (degrees), `tle_mean_anomaly(tle) → float8` (degrees), `tle_mean_motion(tle) → float8` (rev/day), `tle_bstar(tle) → float8` (1/earth-radii), `tle_period(tle) → float8` (minutes), `tle_age(tle, timestamptz) → float8` (days), `tle_perigee(tle) → float8` (km), `tle_apogee(tle) → float8` (km), `tle_intl_desig(tle) → text` (COSPAR ID).
|
||
|
||
### eci_position (48 bytes)
|
||
|
||
Earth-Centered Inertial position and velocity in TEME frame.
|
||
|
||
```sql
|
||
-- Output format: (x, y, z, vx, vy, vz) in km and km/s
|
||
-- Example: (4283.007,-2459.213,4717.924,3.837,5.662,-2.969)
|
||
```
|
||
|
||
Accessors: `eci_x`, `eci_y`, `eci_z` (km), `eci_vx`, `eci_vy`, `eci_vz` (km/s), `eci_speed(eci_position) → float8` (km/s), `eci_altitude(eci_position) → float8` (km, approximate geocentric).
|
||
|
||
### geodetic (24 bytes)
|
||
|
||
WGS-84 latitude, longitude, altitude.
|
||
|
||
```sql
|
||
-- Output format: (lat_deg, lon_deg, alt_km)
|
||
-- Example: (42.3601,-71.0589,408.123)
|
||
```
|
||
|
||
Accessors: `geodetic_lat`, `geodetic_lon` (degrees), `geodetic_alt` (km).
|
||
|
||
### topocentric (32 bytes)
|
||
|
||
Observer-relative azimuth, elevation, range, range rate.
|
||
|
||
```sql
|
||
-- Output format: (azimuth_deg, elevation_deg, range_km, range_rate_km_s)
|
||
-- Example: (185.234,45.678,1234.56,-2.345)
|
||
```
|
||
|
||
Accessors: `topo_azimuth` (degrees, 0=N 90=E 180=S 270=W), `topo_elevation` (degrees, 0=horizon 90=zenith), `topo_range` (km), `topo_range_rate` (km/s, positive=receding).
|
||
|
||
### observer (24 bytes)
|
||
|
||
Ground station location. Flexible text input.
|
||
|
||
```sql
|
||
-- Multiple input formats:
|
||
SELECT '40.0N 105.3W 1655m'::observer; -- DMS with cardinal directions
|
||
SELECT '40.0 -105.3 1655m'::observer; -- Decimal degrees (negative=W/S)
|
||
SELECT '40.0N 105.3W'::observer; -- Altitude defaults to 0m
|
||
|
||
-- Programmatic construction:
|
||
SELECT observer_from_geodetic(40.0, -105.3, 1655.0); -- (lat_deg, lon_deg, alt_m)
|
||
```
|
||
|
||
Accessors: `observer_lat` (degrees, +N), `observer_lon` (degrees, +E), `observer_alt` (meters).
|
||
|
||
### pass_event (48 bytes)
|
||
|
||
Satellite pass visibility window with AOS/MAX/LOS.
|
||
|
||
```sql
|
||
-- Output format: (aos_time, max_el_time, los_time, max_el_deg, aos_az_deg, los_az_deg)
|
||
```
|
||
|
||
Accessors: `pass_aos_time`, `pass_max_el_time`, `pass_los_time` (timestamptz), `pass_max_elevation` (degrees), `pass_aos_azimuth`, `pass_los_azimuth` (degrees), `pass_duration(pass_event) → interval`.
|
||
|
||
### heliocentric (24 bytes)
|
||
|
||
Ecliptic J2000 position in AU.
|
||
|
||
```sql
|
||
-- Output format: (x_au, y_au, z_au)
|
||
-- Example: (0.983271,-0.182724,0.000021)
|
||
```
|
||
|
||
Accessors: `helio_x`, `helio_y`, `helio_z` (AU), `helio_distance(heliocentric) → float8` (AU).
|
||
|
||
### orbital_elements (72 bytes)
|
||
|
||
Classical Keplerian elements for comets and asteroids.
|
||
|
||
```sql
|
||
-- Text I/O format: (epoch_jd, q_au, e, inc_deg, omega_deg, Omega_deg, tp_jd, H, G)
|
||
-- Example: (2460200.5,1.0123,0.2156,10.587,72.891,80.329,2460180.5,15.2,0.15)
|
||
|
||
-- From MPC MPCORB.DAT:
|
||
SELECT oe_from_mpc('00001 3.52 0.15 K249V 14.81198 ...fixed-width MPC line...');
|
||
```
|
||
|
||
Accessors: `oe_epoch` (JD), `oe_perihelion` (AU), `oe_eccentricity`, `oe_inclination` (degrees), `oe_arg_perihelion` (degrees), `oe_raan` (degrees), `oe_tp` (JD), `oe_h_mag` (NaN if unknown), `oe_g_slope` (NaN if unknown), `oe_semi_major_axis` (AU, NULL if e≥1), `oe_period_years` (NULL if e≥1).
|
||
|
||
### equatorial (24 bytes)
|
||
|
||
Apparent equatorial coordinates of date: RA, Dec, distance. Solar system bodies: J2000 precessed via IAU 1976. Satellites: TEME frame (~of-date to ~arcsecond).
|
||
|
||
```sql
|
||
-- Output format: (ra_hours, dec_degrees, distance_km)
|
||
-- Example: (4.29220000,20.60000000,885412345.678)
|
||
```
|
||
|
||
Accessors: `eq_ra(equatorial) → float8` (hours [0,24)), `eq_dec(equatorial) → float8` (degrees [-90,90]), `eq_distance(equatorial) → float8` (km; 0 for stars without parallax).
|
||
|
||
### observer_window (composite)
|
||
|
||
Query parameter bundle for SP-GiST visibility cone operator.
|
||
|
||
```sql
|
||
-- Constructed inline as a ROW:
|
||
SELECT * FROM satellites WHERE elements &? ROW(
|
||
'40.0N 105.3W 1655m'::observer,
|
||
'2024-01-01'::timestamptz,
|
||
'2024-01-02'::timestamptz,
|
||
10.0 -- min_elevation_degrees
|
||
)::observer_window;
|
||
```
|
||
|
||
Fields: `obs` (observer), `t_start` (timestamptz), `t_end` (timestamptz), `min_el` (float8, degrees).
|
||
|
||
## Body IDs
|
||
|
||
### Planets (VSOP87 convention)
|
||
|
||
| ID | Body | ID | Body |
|
||
|----|---------|----|---------|
|
||
| 0 | Sun | 5 | Jupiter |
|
||
| 1 | Mercury | 6 | Saturn |
|
||
| 2 | Venus | 7 | Uranus |
|
||
| 3 | Earth | 8 | Neptune |
|
||
| 4 | Mars | 10 | Moon |
|
||
|
||
### Galilean moons (0–3)
|
||
|
||
0=Io, 1=Europa, 2=Ganymede, 3=Callisto
|
||
|
||
### Saturn moons (0–7)
|
||
|
||
0=Mimas, 1=Enceladus, 2=Tethys, 3=Dione, 4=Rhea, 5=Titan, 6=Iapetus, 7=Hyperion
|
||
|
||
### Uranus moons (0–4)
|
||
|
||
0=Miranda, 1=Ariel, 2=Umbriel, 3=Titania, 4=Oberon
|
||
|
||
### Mars moons (0–1)
|
||
|
||
0=Phobos, 1=Deimos
|
||
|
||
## Functions by Domain
|
||
|
||
### Satellite — SGP4/SDP4 Propagation (25 functions)
|
||
|
||
```
|
||
sgp4_propagate(tle, timestamptz) → eci_position IMMUTABLE
|
||
sgp4_propagate_safe(tle, timestamptz) → eci_position IMMUTABLE -- NULL on error
|
||
sgp4_propagate_series(tle, start, end, step) → SETOF (t, x,y,z, vx,vy,vz) IMMUTABLE
|
||
tle_distance(tle, tle, timestamptz) → float8 IMMUTABLE -- km between two TLEs
|
||
|
||
eci_to_geodetic(eci_position, timestamptz) → geodetic IMMUTABLE
|
||
eci_to_topocentric(eci_position, observer, timestamptz) → topocentric IMMUTABLE
|
||
eci_to_equatorial(eci_position, observer, timestamptz) → equatorial IMMUTABLE -- topocentric RA/Dec (parallax-corrected)
|
||
eci_to_equatorial_geo(eci_position, timestamptz) → equatorial IMMUTABLE -- geocentric RA/Dec (observer-independent)
|
||
subsatellite_point(tle, timestamptz) → geodetic IMMUTABLE
|
||
ground_track(tle, start, end, step) → SETOF (t, lat, lon, alt) IMMUTABLE
|
||
|
||
observe(tle, observer, timestamptz) → topocentric IMMUTABLE -- propagate + observe in one call
|
||
observe_safe(tle, observer, timestamptz) → topocentric IMMUTABLE -- NULL on error
|
||
|
||
next_pass(tle, observer, timestamptz) → pass_event STABLE -- searches up to 7 days
|
||
predict_passes(tle, observer, start, end, min_el DEFAULT 0.0) → SETOF pass_event STABLE
|
||
predict_passes_refracted(tle, observer, start, end, min_el DEFAULT 0.0) → SETOF pass_event STABLE -- refracted horizon (-0.569°)
|
||
pass_visible(tle, observer, start, end) → boolean STABLE
|
||
|
||
tle_from_lines(text, text) → tle IMMUTABLE
|
||
observer_from_geodetic(lat_deg, lon_deg, alt_m DEFAULT 0.0) → observer IMMUTABLE
|
||
```
|
||
|
||
TLE accessors (15): `tle_epoch`, `tle_norad_id`, `tle_inclination`, `tle_eccentricity`, `tle_raan`, `tle_arg_perigee`, `tle_mean_anomaly`, `tle_mean_motion`, `tle_bstar`, `tle_period`, `tle_age`, `tle_perigee`, `tle_apogee`, `tle_intl_desig`, `tle_from_lines`.
|
||
|
||
### Solar System — VSOP87 + ELP2000-82B (14 functions)
|
||
|
||
```
|
||
planet_heliocentric(body_id int4, timestamptz) → heliocentric IMMUTABLE -- IDs 0-8
|
||
planet_observe(body_id int4, observer, timestamptz) → topocentric IMMUTABLE -- IDs 1-8
|
||
sun_observe(observer, timestamptz) → topocentric IMMUTABLE
|
||
moon_observe(observer, timestamptz) → topocentric IMMUTABLE
|
||
|
||
-- Equatorial RA/Dec (apparent, of date)
|
||
planet_equatorial(body_id int4, timestamptz) → equatorial IMMUTABLE -- geocentric
|
||
sun_equatorial(timestamptz) → equatorial IMMUTABLE
|
||
moon_equatorial(timestamptz) → equatorial IMMUTABLE
|
||
|
||
-- Light-time corrected (body at retarded time, Earth at observation time)
|
||
planet_observe_apparent(body_id int4, observer, timestamptz) → topocentric IMMUTABLE
|
||
sun_observe_apparent(observer, timestamptz) → topocentric IMMUTABLE
|
||
planet_equatorial_apparent(body_id int4, timestamptz) → equatorial IMMUTABLE
|
||
moon_equatorial_apparent(timestamptz) → equatorial IMMUTABLE
|
||
|
||
-- All _apparent() functions include annual aberration correction (~20 arcsec) + light-time
|
||
|
||
-- Constructor
|
||
make_equatorial(ra_hours float8, dec_deg float8, dist_km float8) → equatorial IMMUTABLE -- construct equatorial from components
|
||
```
|
||
|
||
### Nutation — IAU 2000B
|
||
|
||
```
|
||
nutation_dpsi(timestamptz) → float8 IMMUTABLE -- nutation in longitude (radians)
|
||
nutation_deps(timestamptz) → float8 IMMUTABLE -- nutation in obliquity (radians)
|
||
```
|
||
|
||
### Planetary Moons (8 functions)
|
||
|
||
```
|
||
galilean_observe(moon_id int4, observer, timestamptz) → topocentric IMMUTABLE -- L1.2 theory, IDs 0-3
|
||
saturn_moon_observe(moon_id int4, observer, timestamptz) → topocentric IMMUTABLE -- TASS 1.7, IDs 0-7
|
||
uranus_moon_observe(moon_id int4, observer, timestamptz) → topocentric IMMUTABLE -- GUST86, IDs 0-4
|
||
mars_moon_observe(moon_id int4, observer, timestamptz) → topocentric IMMUTABLE -- MarsSat, IDs 0-1
|
||
|
||
-- Equatorial RA/Dec for planetary moons (geocentric, of date)
|
||
galilean_equatorial(moon_id int4, timestamptz) → equatorial IMMUTABLE -- L1.2 theory, IDs 0-3
|
||
saturn_moon_equatorial(moon_id int4, timestamptz) → equatorial IMMUTABLE -- TASS 1.7, IDs 0-7
|
||
uranus_moon_equatorial(moon_id int4, timestamptz) → equatorial IMMUTABLE -- GUST86, IDs 0-4
|
||
mars_moon_equatorial(moon_id int4, timestamptz) → equatorial IMMUTABLE -- MarsSat, IDs 0-1
|
||
```
|
||
|
||
### Stars (5 functions)
|
||
|
||
```
|
||
star_observe(ra_hours float8, dec_degrees float8, observer, timestamptz) → topocentric IMMUTABLE
|
||
star_observe_safe(ra_hours float8, dec_degrees float8, observer, timestamptz) → topocentric IMMUTABLE -- NULL on error
|
||
star_equatorial(ra_hours, dec_degrees, timestamptz) → equatorial IMMUTABLE -- precesses J2000 to date
|
||
|
||
-- Proper motion (Hipparcos/Gaia convention: pm_ra = mu_alpha * cos(delta) in mas/yr)
|
||
star_observe_pm(ra_h, dec_deg, pm_ra_masyr, pm_dec_masyr, parallax_mas, rv_kms, observer, timestamptz) → topocentric IMMUTABLE
|
||
star_equatorial_pm(ra_h, dec_deg, pm_ra_masyr, pm_dec_masyr, parallax_mas, rv_kms, timestamptz) → equatorial IMMUTABLE
|
||
|
||
-- When parallax_mas > 0, annual stellar parallax is applied using Earth's heliocentric position (Green 1985)
|
||
```
|
||
|
||
RA in hours [0,24), Dec in degrees [-90,90]. Range returned as 0 (infinite distance) unless parallax > 0 in _pm variants.
|
||
|
||
### Comets & Asteroids — Keplerian + MPC (9 functions)
|
||
|
||
```
|
||
kepler_propagate(q_au, eccentricity, inc_deg, arg_peri_deg, raan_deg, perihelion_jd, timestamptz) → heliocentric IMMUTABLE
|
||
comet_observe(q_au, e, inc, omega, Omega, tp_jd, earth_x, earth_y, earth_z, observer, timestamptz) → topocentric IMMUTABLE
|
||
oe_from_mpc(text) → orbital_elements IMMUTABLE -- parse MPC MPCORB.DAT line
|
||
small_body_heliocentric(orbital_elements, timestamptz) → heliocentric IMMUTABLE
|
||
small_body_observe(orbital_elements, observer, timestamptz) → topocentric IMMUTABLE -- auto-fetches Earth via VSOP87
|
||
small_body_equatorial(orbital_elements, timestamptz) → equatorial IMMUTABLE -- geocentric RA/Dec
|
||
small_body_observe_apparent(orbital_elements, observer, timestamptz) → topocentric IMMUTABLE -- light-time corrected
|
||
small_body_equatorial_apparent(orbital_elements, timestamptz) → equatorial IMMUTABLE -- light-time corrected RA/Dec
|
||
```
|
||
|
||
orbital_elements accessors (11): `oe_epoch`, `oe_perihelion`, `oe_eccentricity`, `oe_inclination`, `oe_arg_perihelion`, `oe_raan`, `oe_tp`, `oe_h_mag`, `oe_g_slope`, `oe_semi_major_axis`, `oe_period_years`.
|
||
|
||
### Jupiter Radio (3 functions)
|
||
|
||
```
|
||
io_phase_angle(timestamptz) → float8 IMMUTABLE -- degrees [0,360)
|
||
jupiter_cml(observer, timestamptz) → float8 IMMUTABLE -- CML III degrees [0,360)
|
||
jupiter_burst_probability(io_phase_deg, cml_deg) → float8 IMMUTABLE -- 0-1 probability
|
||
```
|
||
|
||
### Interplanetary Transfers — Lambert Solver (2 functions)
|
||
|
||
```
|
||
lambert_transfer(dep_body int4, arr_body int4, dep_time, arr_time)
|
||
→ (c3_departure, c3_arrival, v_inf_departure, v_inf_arrival, tof_days, transfer_sma) IMMUTABLE
|
||
lambert_c3(dep_body int4, arr_body int4, dep_time, arr_time) → float8 IMMUTABLE -- departure C3 only, for pork chop plots
|
||
```
|
||
|
||
Body IDs 1–8 (Mercury–Neptune). C3 in km²/s², v_inf in km/s, TOF in days, SMA in AU.
|
||
|
||
### Atmospheric Refraction — Bennett 1982 (4 functions)
|
||
|
||
```
|
||
atmospheric_refraction(elevation_deg float8) → float8 IMMUTABLE -- degrees; standard atmosphere P=1010, T=10°C
|
||
atmospheric_refraction_ext(elevation_deg, pressure_mbar, temp_celsius) → float8 IMMUTABLE -- with Meeus P/T correction
|
||
topo_elevation_apparent(topocentric) → float8 IMMUTABLE -- geometric + refraction, in degrees
|
||
predict_passes_refracted(tle, observer, start, end, min_el DEFAULT 0.0) → SETOF pass_event STABLE -- horizon at -0.569° geometric
|
||
```
|
||
|
||
Bennett formula: `R = 1/tan(h + 7.31/(h + 4.4))` arcminutes. Domain guard: clamps at -1°, returns 0.0 below. At horizon (0°) refraction is ~0.57°, meaning satellites become visible ~35 seconds earlier.
|
||
|
||
### Equatorial Spatial — Angular Separation (2 functions)
|
||
|
||
```
|
||
eq_angular_distance(equatorial, equatorial) → float8 IMMUTABLE -- degrees, Vincenty formula (stable at 0° and 180°)
|
||
eq_within_cone(equatorial, equatorial, float8) → bool IMMUTABLE -- true if within radius_deg, cosine shortcut
|
||
```
|
||
|
||
Operator: `equatorial <-> equatorial → float8` (angular separation in degrees, commutative).
|
||
|
||
### DE Ephemeris — Optional High-Precision (23 functions)
|
||
|
||
All _de() functions fall back to VSOP87/ELP2000-82B when DE is unavailable. All STABLE (external file dependency).
|
||
|
||
```
|
||
planet_heliocentric_de(body_id int4, timestamptz) → heliocentric STABLE
|
||
planet_observe_de(body_id int4, observer, timestamptz) → topocentric STABLE
|
||
sun_observe_de(observer, timestamptz) → topocentric STABLE
|
||
moon_observe_de(observer, timestamptz) → topocentric STABLE
|
||
lambert_transfer_de(dep_body, arr_body, dep_time, arr_time) → RECORD STABLE
|
||
lambert_c3_de(dep_body, arr_body, dep_time, arr_time) → float8 STABLE
|
||
galilean_observe_de(moon_id, observer, timestamptz) → topocentric STABLE
|
||
saturn_moon_observe_de(moon_id, observer, timestamptz) → topocentric STABLE
|
||
uranus_moon_observe_de(moon_id, observer, timestamptz) → topocentric STABLE
|
||
mars_moon_observe_de(moon_id, observer, timestamptz) → topocentric STABLE
|
||
planet_equatorial_de(body_id int4, timestamptz) → equatorial STABLE -- geocentric RA/Dec via DE
|
||
moon_equatorial_de(timestamptz) → equatorial STABLE -- geocentric RA/Dec via DE
|
||
galilean_equatorial_de(moon_id int4, timestamptz) → equatorial STABLE -- geocentric RA/Dec via DE
|
||
saturn_moon_equatorial_de(moon_id int4, timestamptz) → equatorial STABLE -- geocentric RA/Dec via DE
|
||
uranus_moon_equatorial_de(moon_id int4, timestamptz) → equatorial STABLE -- geocentric RA/Dec via DE
|
||
mars_moon_equatorial_de(moon_id int4, timestamptz) → equatorial STABLE -- geocentric RA/Dec via DE
|
||
pg_orrery_ephemeris_info() → (provider, file_path, start_jd, end_jd, version, au_km) STABLE
|
||
|
||
-- Apparent DE variants (light-time + aberration, falls back to VSOP87)
|
||
planet_observe_apparent_de(body_id int4, observer, timestamptz) → topocentric STABLE
|
||
sun_observe_apparent_de(observer, timestamptz) → topocentric STABLE
|
||
moon_observe_apparent_de(observer, timestamptz) → topocentric STABLE
|
||
planet_equatorial_apparent_de(body_id int4, timestamptz) → equatorial STABLE
|
||
moon_equatorial_apparent_de(timestamptz) → equatorial STABLE
|
||
small_body_observe_apparent_de(orbital_elements, observer, timestamptz) → topocentric STABLE
|
||
```
|
||
|
||
Configure: `ALTER SYSTEM SET pg_orrery.ephemeris_path = '/path/to/de441.bin'; SELECT pg_reload_conf();`
|
||
|
||
### Orbit Determination (5 functions)
|
||
|
||
All return: `(fitted_tle, iterations, rms_final, rms_initial, status, condition_number, covariance, nstate)`. All STABLE.
|
||
|
||
```
|
||
tle_from_eci(positions eci_position[], times timestamptz[], seed tle DEFAULT NULL,
|
||
fit_bstar bool DEFAULT false, max_iter int4 DEFAULT 15, weights float8[] DEFAULT NULL) → RECORD
|
||
|
||
tle_from_topocentric(observations topocentric[], times timestamptz[], obs observer,
|
||
seed DEFAULT NULL, fit_bstar DEFAULT false, max_iter DEFAULT 15,
|
||
fit_range_rate DEFAULT false, weights DEFAULT NULL) → RECORD
|
||
|
||
tle_from_topocentric(observations topocentric[], times timestamptz[],
|
||
observers observer[], observer_ids int4[], -- multi-observer variant
|
||
seed DEFAULT NULL, fit_bstar DEFAULT false, max_iter DEFAULT 15,
|
||
fit_range_rate DEFAULT false, weights DEFAULT NULL) → RECORD
|
||
|
||
tle_from_angles(ra_hours float8[], dec_degrees float8[], times timestamptz[], obs observer,
|
||
seed DEFAULT NULL, fit_bstar DEFAULT false, max_iter DEFAULT 15, weights DEFAULT NULL) → RECORD
|
||
|
||
tle_from_angles(ra_hours float8[], dec_degrees float8[], times timestamptz[],
|
||
observers observer[], observer_ids int4[], -- multi-observer variant
|
||
seed DEFAULT NULL, fit_bstar DEFAULT false, max_iter DEFAULT 15, weights DEFAULT NULL) → RECORD
|
||
|
||
tle_fit_residuals(fitted tle, positions eci_position[], times timestamptz[])
|
||
→ SETOF (t, dx_km, dy_km, dz_km, pos_err_km) IMMUTABLE
|
||
```
|
||
|
||
## Rise/Set Prediction
|
||
|
||
Predicts next rise or set time for Sun, Moon, and planets using coarse 60-second scan + bisection to 0.1-second precision. Returns NULL for circumpolar bodies or bodies that never rise within the 7-day search window.
|
||
|
||
### Geometric (horizon = 0 deg)
|
||
|
||
```
|
||
sun_next_rise(obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE
|
||
sun_next_set(obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE
|
||
moon_next_rise(obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE
|
||
moon_next_set(obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE
|
||
planet_next_rise(body_id int4, obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE -- body_id 1-8 (Mercury-Neptune)
|
||
planet_next_set(body_id int4, obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE
|
||
```
|
||
|
||
### Refracted
|
||
|
||
```
|
||
sun_next_rise_refracted(obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE -- threshold -0.833 deg (refraction + semidiameter)
|
||
sun_next_set_refracted(obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE
|
||
moon_next_rise_refracted(obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE -- threshold -0.833 deg
|
||
moon_next_set_refracted(obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE
|
||
planet_next_rise_refracted(body_id int4, obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE -- threshold -0.569 deg (point source)
|
||
planet_next_set_refracted(body_id int4, obs observer, t timestamptz) → timestamptz STABLE STRICT PARALLEL SAFE
|
||
```
|
||
|
||
### Status Diagnostics
|
||
|
||
```
|
||
sun_rise_set_status(obs observer, t timestamptz) → text STABLE STRICT PARALLEL SAFE -- returns 'rises_and_sets', 'circumpolar', or 'never_rises'
|
||
moon_rise_set_status(obs observer, t timestamptz) → text STABLE STRICT PARALLEL SAFE
|
||
planet_rise_set_status(body_id int4, obs observer, t timestamptz) → text STABLE STRICT PARALLEL SAFE
|
||
```
|
||
|
||
## Constellation Identification
|
||
|
||
IAU constellation identification using Roman (1987) boundary table (CDS VI/42). Precesses J2000 coordinates to B1875.0 internally.
|
||
|
||
```
|
||
constellation(eq equatorial) → text IMMUTABLE STRICT PARALLEL SAFE -- 3-letter IAU abbreviation
|
||
constellation(ra_hours float8, dec_deg float8) → text IMMUTABLE STRICT PARALLEL SAFE -- J2000 RA hours [0,24) + Dec degrees [-90,90]
|
||
constellation_full_name(abbr text) → text IMMUTABLE STRICT PARALLEL SAFE -- full IAU name from abbreviation, NULL for invalid input
|
||
```
|
||
|
||
## Operators & Indexes
|
||
|
||
### GiST — tle_ops (DEFAULT for type tle)
|
||
|
||
```sql
|
||
CREATE INDEX ON satellites USING gist (elements);
|
||
```
|
||
|
||
| Operator | Meaning | Usage |
|
||
|----------|---------|-------|
|
||
| `&&` | Orbital key overlap (altitude band AND inclination range) | `WHERE a.elements && b.elements` |
|
||
| `<->` | 2-D orbital distance (km) — L2 norm of altitude gap + inclination gap | `ORDER BY elements <-> ref_tle LIMIT 10` |
|
||
|
||
### SP-GiST — tle_spgist_ops (opt-in)
|
||
|
||
```sql
|
||
CREATE INDEX ON satellites USING spgist (elements tle_spgist_ops);
|
||
```
|
||
|
||
| Operator | Meaning | Usage |
|
||
|----------|---------|-------|
|
||
| `&?` | Visibility cone check — could satellite be visible from observer? | `WHERE elements &? ROW(obs, t0, t1, 10.0)::observer_window` |
|
||
|
||
SP-GiST is a 2-level orbital trie (SMA → inclination) with query-time RAAN filter. Returns a conservative superset — survivors need `predict_passes()` for ground truth.
|
||
|
||
### GiST — equatorial_ops (DEFAULT for type equatorial)
|
||
|
||
```sql
|
||
CREATE INDEX ON sky_objects USING gist (position);
|
||
```
|
||
|
||
| Operator | Meaning | Usage |
|
||
|----------|---------|-------|
|
||
| `<->` (equatorial) | Angular separation in degrees (Vincenty formula), GiST-indexed KNN | `ORDER BY position <-> target LIMIT 10` or `WHERE pos1 <-> pos2 < 5.0` |
|
||
|
||
Supports KNN ordering (`ORDER BY ... <-> ... LIMIT N`) via GiST index scan. Handles RA wraparound at 0h/24h boundary.
|
||
|
||
## Common Query Patterns
|
||
|
||
### Observe a satellite
|
||
|
||
```sql
|
||
SELECT topo_elevation(observe(elements, '40.0N 105.3W 1655m'::observer, NOW()))
|
||
FROM satellites WHERE name = 'ISS';
|
||
```
|
||
|
||
### Batch propagation over a catalog
|
||
|
||
```sql
|
||
SELECT name,
|
||
topo_elevation(observe_safe(elements, '40.0N 105.3W'::observer, NOW())) AS el
|
||
FROM satellites
|
||
WHERE topo_elevation(observe_safe(elements, '40.0N 105.3W'::observer, NOW())) > 10;
|
||
```
|
||
|
||
### Predict passes for one satellite
|
||
|
||
```sql
|
||
SELECT pass_aos_time(p), pass_max_elevation(p), pass_duration(p)
|
||
FROM satellites,
|
||
LATERAL predict_passes(elements, '40.0N 105.3W 1655m'::observer,
|
||
NOW(), NOW() + '3 days'::interval, 10.0) AS p
|
||
WHERE name = 'ISS';
|
||
```
|
||
|
||
### SP-GiST accelerated pass prediction
|
||
|
||
```sql
|
||
SELECT s.name, p.*
|
||
FROM satellites s,
|
||
LATERAL predict_passes(s.elements, '40.0N 105.3W 1655m'::observer,
|
||
NOW(), NOW() + '1 day'::interval, 10.0) AS p
|
||
WHERE s.elements &? ROW(
|
||
'40.0N 105.3W 1655m'::observer, NOW(), NOW() + '1 day'::interval, 10.0
|
||
)::observer_window;
|
||
```
|
||
|
||
### Observe a planet
|
||
|
||
```sql
|
||
SELECT topo_azimuth(planet_observe(4, '40.0N 105.3W'::observer, NOW())) AS mars_az,
|
||
topo_elevation(planet_observe(4, '40.0N 105.3W'::observer, NOW())) AS mars_el;
|
||
```
|
||
|
||
### Tonight's visible planets
|
||
|
||
```sql
|
||
SELECT body_name, topo_elevation(obs) AS el, topo_azimuth(obs) AS az
|
||
FROM (VALUES (1,'Mercury'),(2,'Venus'),(4,'Mars'),(5,'Jupiter'),(6,'Saturn')) AS p(id, body_name),
|
||
LATERAL planet_observe(p.id, '40.0N 105.3W'::observer, NOW()) AS obs
|
||
WHERE topo_elevation(obs) > 0;
|
||
```
|
||
|
||
### GiST conjunction screening
|
||
|
||
```sql
|
||
SELECT a.name, b.name,
|
||
tle_distance(a.elements, b.elements, NOW()) AS dist_km
|
||
FROM satellites a, satellites b
|
||
WHERE a.id < b.id
|
||
AND a.elements && b.elements
|
||
AND tle_distance(a.elements, b.elements, NOW()) < 50;
|
||
```
|
||
|
||
### Observe a comet/asteroid from MPC data
|
||
|
||
```sql
|
||
-- From orbital_elements type:
|
||
SELECT topo_elevation(small_body_observe(oe, '40.0N 105.3W'::observer, NOW()))
|
||
FROM asteroids WHERE name = 'Ceres';
|
||
|
||
-- Bulk MPC import:
|
||
COPY mpc_raw(line) FROM '/path/to/MPCORB.DAT';
|
||
INSERT INTO asteroids (name, oe)
|
||
SELECT substring(line FROM 1 FOR 7), oe_from_mpc(line) FROM mpc_raw;
|
||
```
|
||
|
||
### Lambert transfer — Earth to Mars
|
||
|
||
```sql
|
||
SELECT * FROM lambert_transfer(3, 4,
|
||
'2026-07-01'::timestamptz,
|
||
'2027-01-15'::timestamptz);
|
||
-- Returns: c3_departure, c3_arrival, v_inf_departure, v_inf_arrival, tof_days, transfer_sma
|
||
```
|
||
|
||
### Pork chop plot grid
|
||
|
||
```sql
|
||
SELECT dep, arr, lambert_c3(3, 4, dep, arr) AS c3
|
||
FROM generate_series('2026-01-01'::timestamptz, '2026-12-01', '10 days') AS dep,
|
||
generate_series('2026-07-01'::timestamptz, '2027-06-01', '10 days') AS arr;
|
||
```
|
||
|
||
### Jupiter radio burst prediction
|
||
|
||
```sql
|
||
SELECT io_phase_angle(t) AS io_phase,
|
||
jupiter_cml('40.0N 105.3W'::observer, t) AS cml,
|
||
jupiter_burst_probability(io_phase_angle(t),
|
||
jupiter_cml('40.0N 105.3W'::observer, t)) AS prob
|
||
FROM generate_series(NOW(), NOW() + '24 hours', '15 minutes') AS t
|
||
WHERE jupiter_burst_probability(io_phase_angle(t),
|
||
jupiter_cml('40.0N 105.3W'::observer, t)) > 0.3;
|
||
```
|
||
|
||
### Orbit determination from observations
|
||
|
||
```sql
|
||
SELECT (tle_from_eci(
|
||
ARRAY[eci1, eci2, eci3, eci4, eci5],
|
||
ARRAY[t1, t2, t3, t4, t5]
|
||
)).*
|
||
-- Returns: fitted_tle, iterations, rms_final, rms_initial, status, condition_number, covariance, nstate
|
||
```
|
||
|
||
### Get RA/Dec for telescope GoTo
|
||
|
||
```sql
|
||
-- Planet RA/Dec (apparent, of date — what telescope mounts expect)
|
||
SELECT eq_ra(planet_equatorial(5, NOW())) AS jupiter_ra_hours,
|
||
eq_dec(planet_equatorial(5, NOW())) AS jupiter_dec_deg;
|
||
|
||
-- With light-time correction (Jupiter light-travel ~35-52 min)
|
||
SELECT eq_ra(planet_equatorial_apparent(5, NOW())) AS ra_h,
|
||
eq_dec(planet_equatorial_apparent(5, NOW())) AS dec_deg;
|
||
|
||
-- Star with proper motion (Barnard's Star from Hipparcos/Gaia catalog)
|
||
SELECT eq_ra(star_equatorial_pm(17.963472, 4.6933, -798.58, 10328.12, 545.4, -110.51, NOW())) AS ra_h,
|
||
eq_dec(star_equatorial_pm(17.963472, 4.6933, -798.58, 10328.12, 545.4, -110.51, NOW())) AS dec_deg;
|
||
```
|
||
|
||
### Apparent elevation with atmospheric refraction
|
||
|
||
```sql
|
||
-- Compare geometric vs apparent elevation
|
||
SELECT topo_elevation(obs) AS geometric_el,
|
||
topo_elevation_apparent(obs) AS apparent_el,
|
||
atmospheric_refraction(topo_elevation(obs)) AS refraction
|
||
FROM planet_observe(5, '40.0N 105.3W'::observer, NOW()) AS obs;
|
||
```
|
||
|
||
### Refracted satellite passes (extended visibility windows)
|
||
|
||
```sql
|
||
SELECT pass_aos_time(p), pass_max_elevation(p), pass_duration(p)
|
||
FROM satellites,
|
||
LATERAL predict_passes_refracted(elements, '40.0N 105.3W 1655m'::observer,
|
||
NOW(), NOW() + '3 days'::interval, 10.0) AS p
|
||
WHERE name = 'ISS';
|
||
```
|
||
|
||
### Angular separation and cone search
|
||
|
||
```sql
|
||
-- Find angular separation between two objects
|
||
SELECT planet_equatorial(5, NOW()) <-> moon_equatorial(NOW()) AS jupiter_moon_sep_deg;
|
||
|
||
-- Objects within 10 degrees of Jupiter
|
||
SELECT eq_within_cone(
|
||
star_equatorial(ra_h, dec_deg, NOW()),
|
||
planet_equatorial(5, NOW()),
|
||
10.0
|
||
) AS near_jupiter
|
||
FROM star_catalog;
|
||
```
|
||
|
||
### Rise and set times
|
||
|
||
```sql
|
||
-- When does the Sun next rise and set?
|
||
SELECT sun_next_rise('40.0N 105.3W 1655m'::observer, NOW()) AS sunrise,
|
||
sun_next_set('40.0N 105.3W 1655m'::observer, NOW()) AS sunset;
|
||
|
||
-- Refracted sunrise (accounts for atmospheric refraction + solar semidiameter)
|
||
SELECT sun_next_rise_refracted('40.0N 105.3W 1655m'::observer, NOW()) AS sunrise_refracted;
|
||
|
||
-- Check if body is circumpolar at high latitude
|
||
SELECT sun_rise_set_status('70.0N 25.0E'::observer, '2024-06-21'::timestamptz);
|
||
-- Returns: 'circumpolar' (midnight sun)
|
||
```
|
||
|
||
### Constellation identification
|
||
|
||
```sql
|
||
-- What constellation is Jupiter in right now?
|
||
SELECT constellation(planet_equatorial(5, NOW())) AS jupiter_constellation;
|
||
|
||
-- Full constellation name
|
||
SELECT constellation_full_name(constellation(planet_equatorial(5, NOW())));
|
||
|
||
-- From raw RA/Dec coordinates
|
||
SELECT constellation(6.75, -16.72) AS sirius_constellation; -- 'CMa'
|
||
```
|
||
|
||
## Error Handling
|
||
|
||
### _safe() variants
|
||
|
||
`sgp4_propagate_safe()`, `observe_safe()`, `star_observe_safe()` return NULL on error instead of raising exceptions. Use for batch queries over potentially invalid data.
|
||
|
||
### SGP4 error codes (raised by non-_safe functions)
|
||
|
||
| Code | Meaning |
|
||
|------|---------|
|
||
| -1 | Nearly parabolic orbit |
|
||
| -2 | Negative semi-major axis (decayed) |
|
||
| -3 | Orbit within Earth radius (continues with NOTICE) |
|
||
| -4 | Orbit within Earth radius (continues with NOTICE) |
|
||
| -5 | Negative mean motion |
|
||
| -6 | Kepler solver convergence failure |
|
||
|
||
### Input validation errors
|
||
|
||
- Lambert: same-body check, arrival before departure, invalid body_id (not 1–8)
|
||
- Stars: RA outside [0,24), Dec outside [-90,90]
|
||
- Comets: negative perihelion distance
|
||
- Observer: invalid coordinate format
|
||
|
||
## Key Constants
|
||
|
||
### WGS-72 (SGP4 propagation only)
|
||
|
||
```
|
||
mu = 398600.8 km³/s²
|
||
ae = 6378.135 km
|
||
J2 = 0.001082616
|
||
ke = 0.0743669161331734132 min⁻¹
|
||
```
|
||
|
||
### WGS-84 (coordinate output only)
|
||
|
||
```
|
||
a = 6378.137 km
|
||
f = 1/298.257223563
|
||
```
|
||
|
||
### Astronomical
|
||
|
||
```
|
||
AU = 149597870.7 km (IAU 2012)
|
||
Gauss k = 0.01720209895 AU^(3/2)/day
|
||
Obliquity J2000 = 23.4392911°
|
||
J2000 epoch = JD 2451545.0 (2000 Jan 1.5 TT)
|
||
c (light) = 173.1446327 AU/day (for light-time correction)
|
||
```
|
||
|
||
### Critical rule
|
||
|
||
TLEs are fitted against WGS-72 constants. Propagation MUST use WGS-72. Coordinate output uses WGS-84. Never mix. This is handled internally — all pg_orrery functions use the correct constants automatically.
|