Ryan Malloy
b33d63034b
New equatorial type (24 bytes: RA/Dec/distance) captures apparent coordinates of date — what the observation pipeline computes at precession step 3 but was discarding before hour angle conversion. Matches telescope GoTo mount conventions. 24 new SQL functions (82 → 106 total): - equatorial type I/O + 3 accessors (eq_ra, eq_dec, eq_distance) - Satellite RA/Dec: eci_to_equatorial (topocentric), eci_to_equatorial_geo (geocentric) - Solar system equatorial: planet/sun/moon/small_body_equatorial - Atmospheric refraction: Bennett (1982) with domain clamp at -1 deg - Refracted pass prediction: predict_passes_refracted (horizon at -0.569 deg) - Stellar proper motion: star_observe_pm, star_equatorial_pm (Hipparcos/Gaia convention) - Light-time correction: planet/sun/small_body_observe_apparent, *_equatorial_apparent - DE equatorial variants: planet_equatorial_de, moon_equatorial_de Also includes v0.8.0 orbital_elements type (MPC parser, small_body_observe), GiST 0-based indexing fix, llms.txt updates, and doc improvements. All 18 regression suites pass. Zero build warnings (GCC + Clang).
517 lines
22 KiB
SQL
517 lines
22 KiB
SQL
-- pg_orrery -- Orbital mechanics types and functions for PostgreSQL
|
||
--
|
||
-- Types: tle, eci_position, geodetic, topocentric, observer, pass_event
|
||
-- Provides SGP4/SDP4 propagation, coordinate transforms, pass prediction,
|
||
-- and GiST indexing on altitude bands for conjunction screening.
|
||
--
|
||
-- All propagation uses WGS-72 constants (matching TLE mean element fitting).
|
||
-- Coordinate output uses WGS-84 (matching modern geodetic standards).
|
||
|
||
-- ============================================================
|
||
-- Shell types (forward declarations)
|
||
-- ============================================================
|
||
|
||
CREATE TYPE tle;
|
||
CREATE TYPE eci_position;
|
||
CREATE TYPE geodetic;
|
||
CREATE TYPE topocentric;
|
||
CREATE TYPE observer;
|
||
CREATE TYPE pass_event;
|
||
|
||
|
||
-- ============================================================
|
||
-- TLE type: Two-Line Element set
|
||
-- ============================================================
|
||
|
||
CREATE FUNCTION tle_in(cstring) RETURNS tle
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION tle_out(tle) RETURNS cstring
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION tle_recv(internal) RETURNS tle
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION tle_send(tle) RETURNS bytea
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE TYPE tle (
|
||
INPUT = tle_in,
|
||
OUTPUT = tle_out,
|
||
RECEIVE = tle_recv,
|
||
SEND = tle_send,
|
||
INTERNALLENGTH = 112,
|
||
ALIGNMENT = double,
|
||
STORAGE = plain
|
||
);
|
||
|
||
COMMENT ON TYPE tle IS 'Two-Line Element set — parsed mean orbital elements for SGP4/SDP4 propagation';
|
||
|
||
-- TLE accessor functions
|
||
|
||
CREATE FUNCTION tle_epoch(tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_epoch(tle) IS 'TLE epoch as Julian date (UTC)';
|
||
|
||
CREATE FUNCTION tle_norad_id(tle) RETURNS int4
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_norad_id(tle) IS 'NORAD catalog number';
|
||
|
||
CREATE FUNCTION tle_inclination(tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_inclination(tle) IS 'Orbital inclination in degrees';
|
||
|
||
CREATE FUNCTION tle_eccentricity(tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_eccentricity(tle) IS 'Orbital eccentricity (dimensionless)';
|
||
|
||
CREATE FUNCTION tle_raan(tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_raan(tle) IS 'Right ascension of ascending node in degrees';
|
||
|
||
CREATE FUNCTION tle_arg_perigee(tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_arg_perigee(tle) IS 'Argument of perigee in degrees';
|
||
|
||
CREATE FUNCTION tle_mean_anomaly(tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_mean_anomaly(tle) IS 'Mean anomaly in degrees';
|
||
|
||
CREATE FUNCTION tle_mean_motion(tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_mean_motion(tle) IS 'Mean motion in revolutions per day';
|
||
|
||
CREATE FUNCTION tle_bstar(tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_bstar(tle) IS 'B* drag coefficient (1/earth-radii)';
|
||
|
||
CREATE FUNCTION tle_period(tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_period(tle) IS 'Orbital period in minutes';
|
||
|
||
CREATE FUNCTION tle_age(tle, timestamptz) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_age(tle, timestamptz) IS 'TLE age in days (positive = past epoch, negative = before epoch)';
|
||
|
||
CREATE FUNCTION tle_perigee(tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_perigee(tle) IS 'Perigee altitude in km above WGS-72 ellipsoid';
|
||
|
||
CREATE FUNCTION tle_apogee(tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_apogee(tle) IS 'Apogee altitude in km above WGS-72 ellipsoid';
|
||
|
||
CREATE FUNCTION tle_intl_desig(tle) RETURNS text
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_intl_desig(tle) IS 'International designator (COSPAR ID)';
|
||
|
||
CREATE FUNCTION tle_from_lines(text, text) RETURNS tle
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_from_lines(text, text) IS
|
||
'Construct TLE from separate line1/line2 text columns';
|
||
|
||
|
||
-- ============================================================
|
||
-- ECI position type: True Equator Mean Equinox (TEME) frame
|
||
-- ============================================================
|
||
|
||
CREATE FUNCTION eci_in(cstring) RETURNS eci_position
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION eci_out(eci_position) RETURNS cstring
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION eci_recv(internal) RETURNS eci_position
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION eci_send(eci_position) RETURNS bytea
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE TYPE eci_position (
|
||
INPUT = eci_in,
|
||
OUTPUT = eci_out,
|
||
RECEIVE = eci_recv,
|
||
SEND = eci_send,
|
||
INTERNALLENGTH = 48,
|
||
ALIGNMENT = double,
|
||
STORAGE = plain
|
||
);
|
||
|
||
COMMENT ON TYPE eci_position IS 'Earth-Centered Inertial position and velocity in TEME frame (km, km/s)';
|
||
|
||
-- ECI accessor functions
|
||
|
||
CREATE FUNCTION eci_x(eci_position) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION eci_y(eci_position) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION eci_z(eci_position) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION eci_vx(eci_position) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION eci_vy(eci_position) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION eci_vz(eci_position) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION eci_speed(eci_position) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION eci_speed(eci_position) IS 'Velocity magnitude in km/s';
|
||
|
||
CREATE FUNCTION eci_altitude(eci_position) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION eci_altitude(eci_position) IS 'Approximate geocentric altitude in km (radius - WGS72_AE)';
|
||
|
||
|
||
-- ============================================================
|
||
-- Geodetic type: WGS-84 latitude/longitude/altitude
|
||
-- ============================================================
|
||
|
||
CREATE FUNCTION geodetic_in(cstring) RETURNS geodetic
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION geodetic_out(geodetic) RETURNS cstring
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION geodetic_recv(internal) RETURNS geodetic
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION geodetic_send(geodetic) RETURNS bytea
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE TYPE geodetic (
|
||
INPUT = geodetic_in,
|
||
OUTPUT = geodetic_out,
|
||
RECEIVE = geodetic_recv,
|
||
SEND = geodetic_send,
|
||
INTERNALLENGTH = 24,
|
||
ALIGNMENT = double,
|
||
STORAGE = plain
|
||
);
|
||
|
||
COMMENT ON TYPE geodetic IS 'Geodetic coordinates on WGS-84 ellipsoid (lat/lon in degrees, altitude in km)';
|
||
|
||
CREATE FUNCTION geodetic_lat(geodetic) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION geodetic_lon(geodetic) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION geodetic_alt(geodetic) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
|
||
-- ============================================================
|
||
-- Topocentric type: observer-relative az/el/range
|
||
-- ============================================================
|
||
|
||
CREATE FUNCTION topocentric_in(cstring) RETURNS topocentric
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION topocentric_out(topocentric) RETURNS cstring
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION topocentric_recv(internal) RETURNS topocentric
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION topocentric_send(topocentric) RETURNS bytea
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE TYPE topocentric (
|
||
INPUT = topocentric_in,
|
||
OUTPUT = topocentric_out,
|
||
RECEIVE = topocentric_recv,
|
||
SEND = topocentric_send,
|
||
INTERNALLENGTH = 32,
|
||
ALIGNMENT = double,
|
||
STORAGE = plain
|
||
);
|
||
|
||
COMMENT ON TYPE topocentric IS 'Topocentric coordinates relative to observer (azimuth, elevation, range, range rate)';
|
||
|
||
CREATE FUNCTION topo_azimuth(topocentric) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION topo_azimuth(topocentric) IS 'Azimuth in degrees (0=N, 90=E, 180=S, 270=W)';
|
||
|
||
CREATE FUNCTION topo_elevation(topocentric) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION topo_elevation(topocentric) IS 'Elevation in degrees (0=horizon, 90=zenith)';
|
||
|
||
CREATE FUNCTION topo_range(topocentric) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION topo_range(topocentric) IS 'Slant range in km';
|
||
|
||
CREATE FUNCTION topo_range_rate(topocentric) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION topo_range_rate(topocentric) IS 'Range rate in km/s (positive = receding)';
|
||
|
||
|
||
-- ============================================================
|
||
-- Observer type: ground station location
|
||
-- ============================================================
|
||
|
||
CREATE FUNCTION observer_in(cstring) RETURNS observer
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION observer_out(observer) RETURNS cstring
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION observer_recv(internal) RETURNS observer
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION observer_send(observer) RETURNS bytea
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE TYPE observer (
|
||
INPUT = observer_in,
|
||
OUTPUT = observer_out,
|
||
RECEIVE = observer_recv,
|
||
SEND = observer_send,
|
||
INTERNALLENGTH = 24,
|
||
ALIGNMENT = double,
|
||
STORAGE = plain
|
||
);
|
||
|
||
COMMENT ON TYPE observer IS 'Ground observer location (accepts: 40.0N 105.3W 1655m or decimal degrees)';
|
||
|
||
CREATE FUNCTION observer_lat(observer) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION observer_lat(observer) IS 'Latitude in degrees (positive = North)';
|
||
|
||
CREATE FUNCTION observer_lon(observer) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION observer_lon(observer) IS 'Longitude in degrees (positive = East)';
|
||
|
||
CREATE FUNCTION observer_alt(observer) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION observer_alt(observer) IS 'Altitude in meters above WGS-84 ellipsoid';
|
||
|
||
CREATE FUNCTION observer_from_geodetic(float8, float8, float8 DEFAULT 0.0) RETURNS observer
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION observer_from_geodetic(float8, float8, float8) IS
|
||
'Construct observer from lat (deg), lon (deg), altitude (meters). Avoids text formatting round-trips.';
|
||
|
||
|
||
-- ============================================================
|
||
-- Pass event type: satellite visibility window
|
||
-- ============================================================
|
||
|
||
CREATE FUNCTION pass_event_in(cstring) RETURNS pass_event
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION pass_event_out(pass_event) RETURNS cstring
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION pass_event_recv(internal) RETURNS pass_event
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION pass_event_send(pass_event) RETURNS bytea
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE TYPE pass_event (
|
||
INPUT = pass_event_in,
|
||
OUTPUT = pass_event_out,
|
||
RECEIVE = pass_event_recv,
|
||
SEND = pass_event_send,
|
||
INTERNALLENGTH = 48,
|
||
ALIGNMENT = double,
|
||
STORAGE = plain
|
||
);
|
||
|
||
COMMENT ON TYPE pass_event IS 'Satellite pass event (AOS/MAX/LOS times, max elevation, AOS/LOS azimuths)';
|
||
|
||
CREATE FUNCTION pass_aos_time(pass_event) RETURNS timestamptz
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION pass_aos_time(pass_event) IS 'Acquisition of signal time';
|
||
|
||
CREATE FUNCTION pass_max_el_time(pass_event) RETURNS timestamptz
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION pass_max_el_time(pass_event) IS 'Maximum elevation time';
|
||
|
||
CREATE FUNCTION pass_los_time(pass_event) RETURNS timestamptz
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION pass_los_time(pass_event) IS 'Loss of signal time';
|
||
|
||
CREATE FUNCTION pass_max_elevation(pass_event) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION pass_max_elevation(pass_event) IS 'Maximum elevation in degrees';
|
||
|
||
CREATE FUNCTION pass_aos_azimuth(pass_event) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION pass_aos_azimuth(pass_event) IS 'AOS azimuth in degrees (0=N)';
|
||
|
||
CREATE FUNCTION pass_los_azimuth(pass_event) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION pass_los_azimuth(pass_event) IS 'LOS azimuth in degrees (0=N)';
|
||
|
||
CREATE FUNCTION pass_duration(pass_event) RETURNS interval
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION pass_duration(pass_event) IS 'Pass duration (LOS - AOS)';
|
||
|
||
|
||
-- ============================================================
|
||
-- SGP4/SDP4 propagation functions
|
||
-- ============================================================
|
||
|
||
CREATE FUNCTION sgp4_propagate(tle, timestamptz) RETURNS eci_position
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION sgp4_propagate(tle, timestamptz) IS
|
||
'Propagate TLE to a point in time using SGP4 (near-earth) or SDP4 (deep-space). Returns TEME ECI position/velocity.';
|
||
|
||
CREATE FUNCTION sgp4_propagate_safe(tle, timestamptz) RETURNS eci_position
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE PARALLEL SAFE;
|
||
COMMENT ON FUNCTION sgp4_propagate_safe(tle, timestamptz) IS
|
||
'Like sgp4_propagate but returns NULL on error instead of raising an exception. For batch queries with potentially invalid TLEs.';
|
||
|
||
CREATE FUNCTION sgp4_propagate_series(tle, timestamptz, timestamptz, interval)
|
||
RETURNS TABLE(t timestamptz, x float8, y float8, z float8, vx float8, vy float8, vz float8)
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE
|
||
ROWS 100;
|
||
COMMENT ON FUNCTION sgp4_propagate_series(tle, timestamptz, timestamptz, interval) IS
|
||
'Propagate TLE over a time range at regular intervals. Returns time series of TEME positions.';
|
||
|
||
CREATE FUNCTION tle_distance(tle, tle, timestamptz) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION tle_distance(tle, tle, timestamptz) IS
|
||
'Euclidean distance in km between two TLEs at a reference time';
|
||
|
||
|
||
-- ============================================================
|
||
-- Coordinate transform functions
|
||
-- ============================================================
|
||
|
||
CREATE FUNCTION eci_to_geodetic(eci_position, timestamptz) RETURNS geodetic
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION eci_to_geodetic(eci_position, timestamptz) IS
|
||
'Convert TEME ECI position to WGS-84 geodetic coordinates at given time';
|
||
|
||
CREATE FUNCTION eci_to_topocentric(eci_position, observer, timestamptz) RETURNS topocentric
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION eci_to_topocentric(eci_position, observer, timestamptz) IS
|
||
'Convert TEME ECI position to topocentric (az/el/range) relative to observer';
|
||
|
||
CREATE FUNCTION subsatellite_point(tle, timestamptz) RETURNS geodetic
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION subsatellite_point(tle, timestamptz) IS
|
||
'Subsatellite (nadir) point on WGS-84 ellipsoid at given time';
|
||
|
||
CREATE FUNCTION ground_track(tle, timestamptz, timestamptz, interval)
|
||
RETURNS TABLE(t timestamptz, lat float8, lon float8, alt float8)
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE
|
||
ROWS 100;
|
||
COMMENT ON FUNCTION ground_track(tle, timestamptz, timestamptz, interval) IS
|
||
'Ground track as time series of subsatellite points (lat/lon in degrees, alt in km)';
|
||
|
||
CREATE FUNCTION observe(tle, observer, timestamptz) RETURNS topocentric
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION observe(tle, observer, timestamptz) IS
|
||
'Propagate TLE and compute observer-relative look angles in one call. Returns topocentric (az/el/range/range_rate).';
|
||
|
||
CREATE FUNCTION observe_safe(tle, observer, timestamptz) RETURNS topocentric
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE PARALLEL SAFE;
|
||
COMMENT ON FUNCTION observe_safe(tle, observer, timestamptz) IS
|
||
'Like observe() but returns NULL on propagation error. For batch queries with potentially invalid/decayed TLEs.';
|
||
|
||
|
||
-- ============================================================
|
||
-- Pass prediction functions
|
||
-- ============================================================
|
||
|
||
CREATE FUNCTION next_pass(tle, observer, timestamptz) RETURNS pass_event
|
||
AS 'MODULE_PATHNAME' LANGUAGE C STABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION next_pass(tle, observer, timestamptz) IS
|
||
'Find the next satellite pass over observer (searches up to 7 days ahead)';
|
||
|
||
CREATE FUNCTION predict_passes(tle, observer, timestamptz, timestamptz, float8 DEFAULT 0.0)
|
||
RETURNS SETOF pass_event
|
||
AS 'MODULE_PATHNAME' LANGUAGE C STABLE STRICT PARALLEL SAFE
|
||
ROWS 10;
|
||
COMMENT ON FUNCTION predict_passes(tle, observer, timestamptz, timestamptz, float8) IS
|
||
'Predict all satellite passes over observer in time window. Optional min_elevation in degrees.';
|
||
|
||
CREATE FUNCTION pass_visible(tle, observer, timestamptz, timestamptz) RETURNS boolean
|
||
AS 'MODULE_PATHNAME' LANGUAGE C STABLE STRICT PARALLEL SAFE;
|
||
COMMENT ON FUNCTION pass_visible(tle, observer, timestamptz, timestamptz) IS
|
||
'True if any pass occurs over observer in the time window';
|
||
|
||
|
||
-- ============================================================
|
||
-- GiST operator support functions
|
||
-- ============================================================
|
||
|
||
-- Overlap operator: do orbital keys overlap in altitude AND inclination?
|
||
CREATE FUNCTION tle_overlap(tle, tle) RETURNS boolean
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
-- Altitude distance operator (altitude-only, for KNN ordering)
|
||
CREATE FUNCTION tle_alt_distance(tle, tle) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE OPERATOR && (
|
||
LEFTARG = tle,
|
||
RIGHTARG = tle,
|
||
FUNCTION = tle_overlap,
|
||
COMMUTATOR = &&,
|
||
RESTRICT = areasel,
|
||
JOIN = areajoinsel
|
||
);
|
||
|
||
COMMENT ON OPERATOR && (tle, tle) IS 'Orbital key overlap (altitude band AND inclination range) — necessary condition for conjunction';
|
||
|
||
CREATE OPERATOR <-> (
|
||
LEFTARG = tle,
|
||
RIGHTARG = tle,
|
||
FUNCTION = tle_alt_distance,
|
||
COMMUTATOR = <->
|
||
);
|
||
|
||
COMMENT ON OPERATOR <-> (tle, tle) IS '2-D orbital distance in km: L2 norm of altitude-band gap and inclination gap (radians × Earth radius). Returns 0 when both dimensions overlap.';
|
||
|
||
|
||
-- ============================================================
|
||
-- GiST operator class for 2-D orbital indexing (altitude + inclination)
|
||
-- ============================================================
|
||
|
||
-- GiST internal support functions
|
||
CREATE FUNCTION gist_tle_compress(internal) RETURNS internal
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION gist_tle_decompress(internal) RETURNS internal
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION gist_tle_consistent(internal, tle, smallint, oid, internal) RETURNS boolean
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION gist_tle_union(internal, internal) RETURNS internal
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION gist_tle_penalty(internal, internal, internal) RETURNS internal
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION gist_tle_picksplit(internal, internal) RETURNS internal
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION gist_tle_same(internal, internal, internal) RETURNS internal
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE FUNCTION gist_tle_distance(internal, tle, smallint, oid, internal) RETURNS float8
|
||
AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
|
||
|
||
CREATE OPERATOR CLASS tle_ops
|
||
DEFAULT FOR TYPE tle USING gist AS
|
||
OPERATOR 3 && ,
|
||
OPERATOR 15 <-> (tle, tle) FOR ORDER BY float_ops,
|
||
FUNCTION 1 gist_tle_consistent(internal, tle, smallint, oid, internal),
|
||
FUNCTION 2 gist_tle_union(internal, internal),
|
||
FUNCTION 3 gist_tle_compress(internal),
|
||
FUNCTION 4 gist_tle_decompress(internal),
|
||
FUNCTION 5 gist_tle_penalty(internal, internal, internal),
|
||
FUNCTION 6 gist_tle_picksplit(internal, internal),
|
||
FUNCTION 7 gist_tle_same(internal, internal, internal),
|
||
FUNCTION 8 gist_tle_distance(internal, tle, smallint, oid, internal);
|