Operators & Indexes

pg_orrery defines operators on the tle and equatorial types with three operator classes (two GiST + one SP-GiST) that enable indexed queries over large catalogs. The TLE GiST index accelerates conjunction screening (orbit-to-orbit overlap). The SP-GiST index accelerates pass prediction (observer-to-orbit visibility). The equatorial GiST index accelerates nearest-neighbor sky queries (angular distance KNN).

---

Operators

&& (Overlap)

Tests whether two TLEs have overlapping orbital envelopes. The envelopes are defined by the altitude band (perigee to apogee) and inclination range. Overlap is a necessary but not sufficient condition for a conjunction: two satellites whose altitude bands and inclination ranges do not overlap can never come close to each other.

Signature

tle && tle → boolean

Description

Returns true if both of the following conditions hold:

1. The altitude bands overlap (one satellite’s perigee is below the other’s apogee, and vice versa)
2. The inclination ranges are compatible (the orbits could geometrically intersect)

Returns false if the orbits are guaranteed to never intersect based on these geometric bounds.

Note

This operator is conservative: it may return true for satellite pairs that never actually approach each other (false positive), but it will never return false for a pair that does (no false negatives). Use tle_distance for precise distance computation on the pairs that pass this filter.

Example

-- Find all satellites whose orbits could potentially intersect with the ISS
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 norad_id, name
FROM satellite_catalog, iss
WHERE satellite_catalog.tle && iss.tle;

---

<-> (Distance)

Computes the 2-D orbital distance between two TLEs, in kilometers. Combines altitude-band separation with inclination gap (converted to km via Earth radius), returning the L2 norm. Returns 0 only when both altitude bands AND inclination ranges overlap.

Signature

tle <-> tle → float8

Description

The distance metric combines two components:

• Altitude gap: minimum separation between perigee-to-apogee bands, in km
• Inclination gap: angular difference in radians, converted to km by multiplying by Earth’s radius (WGS-72: 6378.135 km)

The result is sqrt(alt_gap² + inc_km²). Two satellites at the same altitude but with a 90° inclination difference report ~6378 km distance. Two satellites at vastly different altitudes but similar inclinations are dominated by the altitude gap. A result of 0 means both the altitude bands and inclination ranges overlap.

Example

-- Orbital distance between ISS and a GEO satellite
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
),
geo AS (
  SELECT '1 28884U 05041A   24001.50000000 -.00000089  00000-0  00000-0 0  9997
2 28884   0.0153  93.0424 0001699 138.1498 336.5718  1.00271128 67481'::tle AS tle
)
SELECT round((iss.tle <-> geo.tle)::numeric, 1) AS orbital_dist_km
FROM iss, geo;

-- Order catalog by orbital proximity to a target satellite
WITH target AS (
  SELECT tle FROM satellite_catalog WHERE norad_id = 25544
)
SELECT norad_id, name,
       round((satellite_catalog.tle <-> target.tle)::numeric, 1) AS orbital_dist_km
FROM satellite_catalog, target
ORDER BY satellite_catalog.tle <-> target.tle
LIMIT 20;

---

GiST Operator Class: tle_ops

The tle_ops operator class enables GiST indexing on tle columns. With this index in place, the && (overlap) and <-> (distance) operators use index scans instead of sequential scans, making conjunction screening over large catalogs practical.

Creating the Index

CREATE INDEX idx_tle_gist ON satellite_catalog USING gist (tle tle_ops);

What Gets Indexed

The GiST index stores a bounding representation of each TLE’s orbital envelope:

• Altitude band: perigee altitude to apogee altitude (km, above WGS-72)
• Inclination range: orbital inclination (degrees)

These are extracted from the TLE’s mean motion and eccentricity at index creation time. The index does not store time-varying quantities; it represents the geometric envelope of the orbit as defined by the current osculating elements.

Index-Accelerated Queries

Overlap scan

-- Find all catalog objects that could intersect with the ISS orbit
-- Uses the GiST index to avoid a full catalog scan
WITH iss AS (
  SELECT tle FROM satellite_catalog WHERE norad_id = 25544
)
SELECT c.norad_id, c.name
FROM satellite_catalog c, iss
WHERE c.tle && iss.tle
  AND c.norad_id != 25544;

kNN by orbital distance

-- Find the 10 satellites with the closest orbits to the ISS
-- The <-> operator supports GiST index ordering (ORDER BY ... <-> ...)
-- IMPORTANT: use a scalar subquery for the probe TLE so the planner
-- can see it as a constant and activate index-ordered scan.
SELECT c.name,
       round((c.tle <-> (SELECT tle FROM satellite_catalog
                          WHERE tle_norad_id(tle) = 25544 LIMIT 1))::numeric, 1)
         AS orbital_dist_km
FROM satellite_catalog c
WHERE tle_norad_id(c.tle) != 25544
ORDER BY c.tle <-> (SELECT tle FROM satellite_catalog
                     WHERE tle_norad_id(tle) = 25544 LIMIT 1)
LIMIT 10;

CTE pattern prevents index ordering

A CTE like WITH iss AS (SELECT tle ...) makes the probe value opaque to the planner, forcing a full sequential scan and sort instead of an index-ordered traversal. Always use a scalar subquery (SELECT tle FROM ... LIMIT 1) for the probe argument. For small catalogs (< 100 rows) the difference is negligible; for large catalogs it is the difference between 2 ms and a full sort.

Two-stage screening

-- Full conjunction screening pipeline:
-- Stage 1: GiST index filters by orbital envelope overlap
-- Stage 2: Precise distance computation on surviving pairs
WITH iss AS (
  SELECT tle FROM satellite_catalog WHERE norad_id = 25544
),
candidates AS (
  SELECT c.norad_id, c.name, c.tle
  FROM satellite_catalog c, iss
  WHERE c.tle && iss.tle
    AND c.norad_id != 25544
)
SELECT norad_id, name,
       round(tle_distance(candidates.tle, iss.tle, now())::numeric, 1) AS dist_km
FROM candidates, iss
WHERE tle_distance(candidates.tle, iss.tle, now()) < 100
ORDER BY dist_km;

Performance

Benchmarked against a 66,440-object catalog (Space-Track + CelesTrak + SatNOGS):

Query	GiST	Seqscan	Matches	Speedup
ISS conjunction (&&)	4.6 ms	63.3 ms	9	5.8x
10 nearest to ISS (<-> KNN)	2.1 ms	—	10	Index-ordered (2-D orbital distance)
10 nearest to GEO sat (<-> KNN)	0.2 ms	—	10	Sparse regime

The GiST index (15 MB, 93 ms build) provides the clearest speedup for conjunction screening. The && operator reduces the search from 1,338 buffer hits (sequential scan) to 237 buffer hits (index scan). KNN queries traverse the tree by increasing distance without computing all distances upfront.

Tip

For small catalogs (< 100 TLEs), sequential scans may be faster than the index overhead. PostgreSQL’s query planner handles this decision automatically. The GiST index shows the largest relative speedup when the query returns few matches against a large catalog --- exactly the conjunction screening pattern.

Index Maintenance

The GiST index is maintained automatically by PostgreSQL on INSERT, UPDATE, and DELETE. When TLEs are refreshed (e.g., daily catalog updates), the index is updated in place. No manual REINDEX is needed under normal operation.

If you perform a bulk catalog replacement (truncate + reload), run REINDEX after loading:

TRUNCATE satellite_catalog;
COPY satellite_catalog FROM '/path/to/catalog.csv' WITH (FORMAT csv);
REINDEX INDEX idx_tle_gist;

---

GiST Operator Class: eq_gist_ops

The eq_gist_ops operator class enables GiST indexing on equatorial columns. With this index, the <-> operator (angular distance in degrees via the Vincenty formula) supports index-ordered KNN queries — PostgreSQL traverses the tree by increasing angular distance without computing all distances upfront.

Note

eq_gist_ops is the DEFAULT operator class for the equatorial type using GiST. No explicit operator class is needed in CREATE INDEX.

Creating the Index

CREATE INDEX idx_sky_eq ON sky_cache USING gist (eq);

What Gets Indexed

The GiST index stores a 24-byte float-precision spherical bounding box for each entry:

• RA range: [ra_low, ra_high] in radians. When ra_low > ra_high, the box wraps across 0h (covers [ra_low, 2pi) union [0, ra_high])
• Dec range: [dec_low, dec_high] in radians

Float precision (~0.12 arcsec bounding error at RA = 2pi) is more than sufficient for index pruning. Actual angular distance is computed in double precision via the Vincenty formula during recheck.

Index-Accelerated Queries

KNN nearest-neighbor

-- Find the 10 nearest sky objects to Jupiter
SELECT name,
       round((eq <-> planet_equatorial_apparent(5, NOW()))::numeric, 4) AS dist_deg
FROM sky_cache
ORDER BY eq <-> planet_equatorial_apparent(5, NOW())
LIMIT 10;

Cone search

-- Everything within 15 degrees of Vega, sorted by distance
SELECT name,
       round((eq <-> star_equatorial(18.616, 38.784, NOW()))::numeric, 2) AS dist_deg
FROM sky_cache
WHERE eq_within_cone(eq, star_equatorial(18.616, 38.784, NOW()), 15.0)
ORDER BY eq <-> star_equatorial(18.616, 38.784, NOW());

EXPLAIN verification

-- Confirm the planner uses the GiST index
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT name FROM sky_cache
ORDER BY eq <-> '(12.00000000,0.00000000,0.000)'::equatorial
LIMIT 3;
-- Should show: Index Scan using idx_sky_eq on sky_cache
RESET enable_seqscan;

RA Wrapping

Objects near 0h (e.g., in Pisces/Aquarius) and objects near 24h are correctly identified as neighbors. The bounding box merge and distance functions handle the RA discontinuity at 0h/24h explicitly. An object at RA = 23.9h and another at RA = 0.1h are approximately 3 degrees apart (at moderate declination), and the KNN traversal finds them as neighbors.

Polar Regions

Near the celestial poles (Dec approaching +/-90 degrees), RA becomes degenerate — a small patch of sky spans a wide RA range. Bounding boxes for polar objects may cover the full RA circle. This does not affect correctness (the Vincenty formula handles pole convergence naturally) but can degrade index selectivity for dense polar catalogs. For typical sky catalogs (fewer than 10,000 objects), the effect is negligible.

Design Notes

• KNN only (strategy 15, <-> ordering). No && overlap operator — meaningless for point types.
• Distance unit: degrees, matching eq_angular_distance().
• Lower-bound contract hardened: box boundaries widened by float epsilon before distance computation to guarantee KNN correctness under float-to-double promotion.

---

&? (Visibility Cone)

Tests whether a satellite could possibly be visible from a ground observer during a time window. This is a geometric superset filter — it may include satellites that do not produce an actual pass (false positives), but will never exclude one that does (no false negatives).

Signature

tle &? observer_window → boolean

The observer_window Type

The right argument is a composite type constructed with ROW(...)::observer_window:

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 (default 10.0 if NULL)

Description

Applies three geometric filters without SGP4 propagation:

1. Altitude filter: Rejects satellites whose perigee altitude exceeds the maximum visible altitude for the given minimum elevation angle
2. Inclination filter: Rejects satellites whose inclination + ground footprint angle cannot reach the observer’s latitude
3. RAAN filter: Projects the ascending node to the query midpoint via J2 secular precession and checks alignment with the observer’s local sidereal time. Automatically bypassed for query windows spanning a full Earth rotation (>= ~12 hours)

Returns true if the satellite passes all three filters. Returns false for degenerate TLEs with zero mean motion.

Note

The &? operator is designed as the first stage of a two-stage pipeline. Use it to reduce a 30,000-object catalog to a few hundred candidates, then run predict_passes() on the survivors for exact pass times and elevations.

Example

-- Which satellites might be visible from Eagle, Idaho tonight?
SELECT name
FROM satellite_catalog
WHERE tle &? ROW(
    observer('43.6977N 116.3535W 760m'),
    '2024-01-01 02:00:00+00'::timestamptz,
    '2024-01-01 08:00:00+00'::timestamptz,
    10.0
)::observer_window
ORDER BY name;

---

SP-GiST Operator Class: tle_spgist_ops

The tle_spgist_ops operator class enables SP-GiST indexing on tle columns. The index builds a 2-level space-partitioning trie: semi-major axis at level 0 (altitude regime) and inclination at level 1 (latitude coverage). Equal-population splits ensure balanced trees across the dense LEO region.

Creating the Index

CREATE INDEX idx_tle_spgist ON satellite_catalog USING spgist (tle tle_spgist_ops);

Note

The SP-GiST operator class is not the default for the tle type. The GiST operator class (tle_ops) remains the default. Both can coexist on the same table — they serve different query patterns.

What Gets Indexed

The SP-GiST trie partitions TLEs by two static orbital elements:

• Level 0: Semi-major axis (computed from mean motion via Kepler’s 3rd law). Separates LEO, MEO, HEO, and GEO objects into altitude bins.
• Level 1: Inclination (in radians). Within each altitude bin, objects are further partitioned by orbital inclination.

Equal-population splits (floor(sqrt(n)) bins, clamped to [2, 16]) ensure dense orbital regimes like LEO get finer partitioning.

Eccentricity and highly elliptical orbits

The SP-GiST inner nodes store only SMA bin boundaries — they do not carry eccentricity information. This means the index cannot prune HEO (Highly Elliptical Orbit) satellites by altitude at the tree level. A satellite like CLUSTER II (SMA ~70,000 km, eccentricity 0.88) sits in a high-SMA bin alongside GEO satellites, but its actual perigee is only ~2,000 km. The leaf-level filter correctly identifies these using the full TLE (including eccentricity), so no false negatives occur — but HEO bins may produce slightly more candidates than a purely circular-orbit catalog would.

Index-Accelerated Queries

-- The &? operator uses the SP-GiST index when available
SELECT name
FROM satellite_catalog
WHERE tle &? ROW(
    observer('43.6977N 116.3535W 760m'),
    '2024-01-01 02:00:00+00'::timestamptz,
    '2024-01-01 08:00:00+00'::timestamptz,
    10.0
)::observer_window;

During the index scan, inner nodes are pruned by altitude band (level 0) and inclination range (level 1). The RAAN filter is applied at the leaf level. This avoids examining individual TLEs in entire subtrees that cannot produce visible passes.

Performance

The &? operator eliminates 84—90% of a satellite catalog without SGP4 propagation --- this is the primary value, regardless of whether a sequential scan or index scan evaluates it.

Benchmarked against a 66,440-object catalog:

Query	Seqscan	SP-GiST	Candidates	Pruned
2h, Eagle ID, 10°	12.1 ms	16.1 ms	10,763	83.8%
2h, Equator, 10°	12.1 ms	16.8 ms	10,174	84.7%
2h, Eagle ID, 45°	11.9 ms	16.9 ms	6,796	89.8%
24h, Eagle ID, 10°	12.5 ms	23.3 ms	61,426	7.5%

At 66k objects, the sequential scan is faster than the SP-GiST index for all tested scenarios. The &? operator is so cheap per evaluation (three floating-point comparisons) that tree traversal overhead exceeds the pruning benefit at this catalog size. The index is most effective for:

• Short query windows (1-6 hours): The RAAN filter aggressively eliminates satellites whose orbital plane is not currently aligned with the observer
• Higher minimum elevation (> 20 degrees): The altitude filter eliminates distant MEO/GEO objects
• Larger catalogs (200k+ objects): Tree-level pruning avoids examining individual TLEs in entire subtrees

For 24-hour query windows, the RAAN filter self-disables (full Earth rotation makes it meaningless), and only the altitude and inclination filters apply. The real value of the &? operator is as a gating filter before expensive SGP4 propagation, not the scan method itself.

Index Maintenance

Like the GiST index, the SP-GiST index is maintained automatically by PostgreSQL on INSERT, UPDATE, and DELETE. No manual REINDEX is needed under normal operation.