🌟 DuckDB Astro Extension

Comprehensive astronomical calculations and coordinate transformations for DuckDB with modern integrations.

🚀 Quick Start

Installation from Community Extensions (Recommended)

INSTALL astro FROM community;
LOAD astro;

-- Calculate angular separation between two stars
SELECT astro_angular_separation(45.0, 30.0, 46.0, 31.0) as separation_degrees;

-- Get physical properties of a Sun-like star
SELECT astro_body_star_main_sequence(1.0);

Manual Installation

1. Download the latest release from Releases
2. Load in DuckDB:

LOAD '/path/to/astro.duckdb_extension';

✨ Features

• 🌐 Coordinate Transformations: RA/Dec ↔ Cartesian, ICRS ↔ galactic frame
• 📐 Angular Calculations: Precise angular separation using Haversine formula
• 💫 Photometric Functions: Magnitude/flux conversions with zero-point support
• 🌫️ Dust Extinction: CCM89 + O'Donnell (1994) with UBVRIJHK bands
• 🌌 Cosmological Calculations: Luminosity and comoving distance
• 🔭 Sidereal Time & Observing: GMST/LMST, hour angle, equatorial → horizontal (alt/az)
• 🪐 Celestial Body Models: Stars, planets, brown dwarfs, black holes, asteroids
• 🛰️ Orbital Mechanics: Keplerian orbits, mean motion, position/velocity
• 🧊 3D Spatial Octree: Sector-based spatial indexing for N-body data
• 🔭 FITS File I/O: Read FITS tables and images directly into DuckDB (fits_hdus, fits_header, read_fits)

📊 Functions (62: 59 scalar + 3 table)

Every function is registered with parameter names, a description, an example and a category. They are all introspectable via duckdb_functions():

-- All astro functions of one category, with their inline help
SELECT function_name, parameters, description
FROM duckdb_functions()
WHERE list_contains(categories, 'astro.photometry')
ORDER BY function_name;

-- All categories shipped by this extension
SELECT DISTINCT unnest(categories) AS category
FROM duckdb_functions()
WHERE function_name LIKE 'astro\_%' ESCAPE '\'
   OR function_name IN ('fits_hdus', 'fits_header', 'read_fits')
ORDER BY category;

Category	Section below
astro.constants	Physical Constants
astro.units	Unit Conversions
astro.bodies	Celestial Body Models
astro.orbits	Orbital Mechanics
astro.dynamics	Dynamics
astro.frames	Frame Transforms
astro.sectors	Spatial Sectors (3D Octree)
astro.coordinates	Coordinates
astro.photometry	Photometry
astro.cosmology	Cosmology
astro.extinction	Dust Extinction (CCM89)
astro.sidereal	Sidereal Time & Observing
astro.fits	FITS File I/O

Coordinates

Category: astro.coordinates

Function	Description	Example
astro_angular_separation(ra1, dec1, ra2, dec2)	Angular distance (Haversine)	SELECT astro_angular_separation(45.0, 30.0, 46.0, 31.0);
astro_radec_to_xyz(ra, dec, dist)	RA/Dec to Cartesian STRUCT	SELECT astro_radec_to_xyz(45.0, 30.0, 10.0);

Frame Transforms

Category: astro.frames

Supported frames (case-insensitive): icrs ↔ galactic; barycentric is treated as a synonym for icrs.

Function	Description	Example
astro_frame_transform_pos(pos, from, to)	Transform position between frames	SELECT astro_frame_transform_pos(pos, 'icrs', 'galactic');
astro_frame_transform_vel(vel, from, to)	Transform velocity between frames	SELECT astro_frame_transform_vel(vel, 'icrs', 'galactic');

Photometry

Category: astro.photometry

Function	Description	Example
astro_mag_to_flux(mag, zp)	Magnitude to flux	SELECT astro_mag_to_flux(15.5, 25.0);
astro_flux_to_mag(flux, zp)	Flux to magnitude	SELECT astro_flux_to_mag(1000.0, 25.0);
astro_absolute_mag(app_mag, dist_pc)	Absolute magnitude	SELECT astro_absolute_mag(10.0, 100.0);
astro_distance_modulus(dist_pc)	Distance modulus	SELECT astro_distance_modulus(1000.0);

Dust Extinction (CCM89)

Category: astro.extinction

Interstellar dust extinction using the Cardelli, Clayton & Mathis (1989) extinction law with O'Donnell (1994) optical coefficients.

Function	Description	Example
astro_extinction_av(ebv, [rv=3.1])	A_V from E(B-V) and R_V	SELECT astro_extinction_av(0.5);
astro_extinction_alambda(λ_Å, av, [rv=3.1])	Extinction at wavelength	SELECT astro_extinction_alambda(5494.5, 1.0);
astro_extinction_band(band, av, [rv=3.1])	Extinction in photometric band	SELECT astro_extinction_band('V', 1.0);
astro_mag_deredden(mag, extinction)	Correct magnitude for extinction	SELECT astro_mag_deredden(15.5, 1.25);
astro_flux_deredden(flux, extinction)	Correct flux for extinction	SELECT astro_flux_deredden(1000.0, 1.0);
astro_color_excess(mag1, mag2, intrinsic)	Calculate color excess	SELECT astro_color_excess(12.5, 11.8, 0.3);

Default R_V=3.1 corresponds to the average for the Milky Way diffuse ISM.

Supported photometric bands: U (3650Å), B (4400Å), V (5494.5Å), R (6580Å), I (8060Å), J (12350Å), H (16620Å), K (21590Å)

Cosmology

Category: astro.cosmology

Hubble-law approximations, valid for z ≪ 1. H0 in km/s/Mpc.

Function	Description	Example
astro_luminosity_distance(z, h0)	Luminosity distance (Mpc)	SELECT astro_luminosity_distance(0.1, 70.0);
astro_comoving_distance(z, h0)	Comoving distance	SELECT astro_comoving_distance(1.0, 70.0);

Physical Constants

Category: astro.constants

IAU 2015 nominal values where applicable. All zero-argument functions.

Function	Value	Description
astro_const_c()	299792458 m/s	Speed of light
astro_const_G()	6.67430e-11 m³/(kg·s²)	Gravitational constant
astro_const_AU()	149597870700 m	Astronomical unit
astro_const_pc()	3.0857e16 m	Parsec
astro_const_ly()	9.4607e15 m	Light year
astro_const_M_sun()	1.989e30 kg	Solar mass
astro_const_R_sun()	6.957e8 m	Solar radius
astro_const_L_sun()	3.828e26 W	Solar luminosity
astro_const_M_earth()	5.972e24 kg	Earth mass
astro_const_R_earth()	6.371e6 m	Earth radius
astro_const_sigma_sb()	5.670374e-8 W/(m²·K⁴)	Stefan-Boltzmann constant

Unit Conversions

Category: astro.units

Function	Description	Example
astro_unit_AU(n)	n AU in meters	SELECT astro_unit_AU(1.0);
astro_unit_pc(n)	n parsec in meters	SELECT astro_unit_pc(1.0);
astro_unit_ly(n)	n light-years in meters	SELECT astro_unit_ly(1.0);
astro_unit_M_sun(n)	n solar masses in kg	SELECT astro_unit_M_sun(1.0);
astro_unit_M_earth(n)	n Earth masses in kg	SELECT astro_unit_M_earth(1.0);
astro_unit_length_to_m(val, unit)	Convert length to meters	SELECT astro_unit_length_to_m(1.0, 'pc');
astro_unit_mass_to_kg(val, unit)	Convert mass to kg	SELECT astro_unit_mass_to_kg(1.0, 'M_sun');
astro_unit_time_to_s(val, unit)	Convert time to seconds	SELECT astro_unit_time_to_s(1.0, 'yr');

Celestial Body Models

Category: astro.bodies

Returns STRUCT with: mass_kg, radius_m, luminosity_w, temperature_k, density_kg_m3, body_type

Function	Description	Example
astro_body_star_main_sequence(mass_M_sun)	Main sequence star	SELECT astro_body_star_main_sequence(1.0);
astro_body_star_white_dwarf(mass_M_sun)	White dwarf (Chandrasekhar)	SELECT astro_body_star_white_dwarf(0.6);
astro_body_star_neutron(mass_M_sun)	Neutron star (~11km radius)	SELECT astro_body_star_neutron(1.4);
astro_body_brown_dwarf(mass_M_jup)	Brown dwarf (13-80 M_jup)	SELECT astro_body_brown_dwarf(50.0);
astro_body_black_hole(mass_M_sun)	Black hole (Schwarzschild)	SELECT astro_body_black_hole(10.0);
astro_body_planet_rocky(mass_M_earth)	Rocky planet	SELECT astro_body_planet_rocky(1.0);
astro_body_planet_gas_giant(mass_M_jup)	Gas giant (Jupiter-like)	SELECT astro_body_planet_gas_giant(1.0);
astro_body_planet_ice_giant(mass_M_earth)	Ice giant (Neptune-like)	SELECT astro_body_planet_ice_giant(17.0);
astro_body_asteroid(radius_km, density)	Asteroid from size/density	SELECT astro_body_asteroid(500, 2000);

Orbital Mechanics

Category: astro.orbits

Keplerian two-body orbits. astro_orbit_make produces the orbit STRUCT consumed by the other functions.

Function	Description	Example
astro_orbit_period(a_m, M_kg)	Kepler orbital period (s)	SELECT astro_orbit_period(1.496e11, 1.989e30);
astro_orbit_mean_motion(a_m, M_kg)	Mean motion (rad/s)	SELECT astro_orbit_mean_motion(1.496e11, 1.989e30);
astro_orbit_make(a, e, i, omega, w, M0, epoch, M, frame)	Create orbit STRUCT	See examples below
astro_orbit_position(orbit, t)	Position at time t	SELECT astro_orbit_position(orbit, 2451545.0);
astro_orbit_velocity(orbit, t)	Velocity at time t	SELECT astro_orbit_velocity(orbit, 2451545.0);

Spatial Sectors (3D Octree)

Category: astro.sectors

Octree-like spatial index. Base cell size = 1e12 m at level 0; cell size = 1e12 / 2^level.

Function	Description	Example
astro_sector_id(x, y, z, level)	Get sector ID for position	SELECT astro_sector_id(1.0, 0.0, 0.0, 3);
astro_sector_center(sector)	Get sector center position	SELECT astro_sector_center(sector);
astro_sector_bounds(sector)	Get sector bounding box	SELECT astro_sector_bounds(sector);
astro_sector_parent(sector)	Get parent sector	SELECT astro_sector_parent(sector);

Dynamics

Category: astro.dynamics

Function	Description	Example
astro_dyn_gravity_accel(m1, pos1, m2, pos2)	Gravitational acceleration on body 1 due to body 2 (m1 is accepted for API symmetry but does not affect the result)	See examples below

FITS File I/O

Category: astro.fits

Function	Description	Example
fits_hdus(path)	List all HDUs (index, name, type)	SELECT * FROM fits_hdus('file.fits');
fits_header(path, hdu:=0)	Read header keywords from an HDU	SELECT * FROM fits_header('file.fits', hdu:=1);
read_fits(path, hdu:=NULL, header:=false, flatten:=true)	Read BINTABLE or IMAGE HDU as rows	SELECT * FROM read_fits('file.fits', hdu:=1);

read_fits supports:

• BINTABLE: each row in the table becomes a DuckDB row; columns are typed to the FITS column types
• IMAGE: with flatten:=true (default) each pixel becomes a row with coordinates; with flatten:=false returns one row with a nested LIST column
• header:=true: prepend _hdr_<keyword> columns from the HDU header

Sidereal Time & Observing

Category: astro.sidereal

Function	Description	Example
astro_jd_from_timestamp(ts)	Julian Date from TIMESTAMP or TIMESTAMPTZ	SELECT astro_jd_from_timestamp(now());
astro_gmst(jd)	Greenwich Mean Sidereal Time in hours [0,24)	SELECT astro_gmst(2451545.0);
astro_lmst(jd, lon_deg)	Local Mean Sidereal Time in hours [0,24)	SELECT astro_lmst(2451545.0, 10.0);
astro_hour_angle(ra_deg, lmst_h)	Hour angle in hours, in (-12, 12]	SELECT astro_hour_angle(180.0, 10.0);
astro_altaz_from_radec(ra, dec, lmst_h, lat_deg)	STRUCT{alt_deg, az_deg} — equatorial → horizontal	See examples below

astro_altaz_from_radec returns a STRUCT with two fields: alt_deg (altitude above horizon, [-90, 90]) and az_deg (azimuth measured from North through East, [0, 360)). Use it together with astro_lmst to ask "what is currently above the horizon at my observatory":

WITH obs AS (
    SELECT astro_lmst(astro_jd_from_timestamp(now()), 10.0) AS lmst_h  -- lon ~Germany
)
SELECT name, ra, dec,
       (astro_altaz_from_radec(ra, dec, (SELECT lmst_h FROM obs), 53.55)).alt_deg AS alt
FROM bright_stars
WHERE (astro_altaz_from_radec(ra, dec, (SELECT lmst_h FROM obs), 53.55)).alt_deg > 30
ORDER BY alt DESC;

Function Details

Coordinate Functions

astro_radec_to_xyz(ra, dec, distance) returns a STRUCT with the position in the ICRS frame. The distance unit is whatever you pass in — wrap it with astro_unit_pc(...) / astro_unit_AU(...) / astro_unit_ly(...) if you want SI meters.

SELECT astro_radec_to_xyz(45.0, 30.0, 10.0);
-- {'x_m': 6.123724..., 'y_m': 6.123724..., 'z_m': 5.0, 'frame': 'icrs'}

Body Models

Each body function returns a STRUCT with physical properties in SI units:

SELECT astro_body_star_main_sequence(1.0);  -- Sun-like star
-- {'mass_kg': 1.989e+30, 'radius_m': 6.957e+08, 'luminosity_w': 3.828e+26,
--  'temperature_k': 5778.0, 'density_kg_m3': 1408.0, 'body_type': 'star_main_sequence'}

SELECT astro_body_black_hole(10.0);  -- 10 solar mass black hole
-- {'mass_kg': 1.989e+31, 'radius_m': 29541.0, 'luminosity_w': 0.0,
--  'temperature_k': 0.0, 'density_kg_m3': 1.83e+18, 'body_type': 'black_hole'}

Unit Conversions

Flexible unit conversion with string identifiers:

-- Length: 'AU', 'pc', 'ly', 'km', 'm'
SELECT astro_unit_length_to_m(1.0, 'pc');  -- 3.0857e16

-- Mass: 'M_sun', 'M_earth', 'M_jup', 'kg'
SELECT astro_unit_mass_to_kg(1.0, 'M_sun');  -- 1.989e30

-- Time: 'yr', 'day', 'hr', 's'
SELECT astro_unit_time_to_s(1.0, 'yr');  -- 3.1557e7

🏗️ Building from Source

Prerequisites

• CMake 3.5+
• C++17 compatible compiler
• DuckDB development headers

Build Steps

# Clone repository with submodules (duckdb, extension-ci-tools, cfitsio)
git clone --recursive https://github.com/synapticore-io/astro-duck.git
cd astro-duck

# Build extension
make clean && make release

# Run tests
python test_astro.py

If you already cloned without --recursive, initialize the submodules with git submodule update --init --recursive.

📈 Performance

• 10,000 angular separations in ~0.003 seconds (vectorized C++)
• Vectorized execution, memory-efficient batch processing
• Scales to multi-million-row Parquet/FITS files via DuckDB's execution engine

🔧 Integration Examples

The snippets below are minimal patterns. For end-to-end recipes against real catalogs (Gaia, NASA Exoplanet Archive, simulation snapshots, dustmaps) see the Cookbook.

Coordinate Transformations

LOAD astro;

-- Convert catalog coordinates to Cartesian
SELECT
    name,
    astro_radec_to_xyz(ra, dec, distance_pc) as xyz
FROM stars;

-- Query objects within angular distance
SELECT name, ra, dec
FROM stars
WHERE astro_angular_separation(ra, dec, 180.0, 0.0) < 5.0;

-- Transform positions between reference frames (icrs, galactic, ...)
SELECT
    astro_frame_transform_pos(astro_radec_to_xyz(ra, dec, distance_pc), 'icrs', 'galactic') as galactic_pos
FROM stars;

Photometry Pipeline

LOAD astro;

-- Complete photometric analysis with dust extinction correction
SELECT
    object_id,
    mag_v,
    -- Calculate A_V from E(B-V) reddening
    astro_extinction_av(ebv, 3.1) as A_V,
    -- Get extinction in each band
    astro_extinction_band('V', astro_extinction_av(ebv, 3.1), 3.1) as A_V_band,
    astro_extinction_band('B', astro_extinction_av(ebv, 3.1), 3.1) as A_B_band,
    -- Deredden the observed magnitude
    astro_mag_deredden(mag_v, astro_extinction_band('V', astro_extinction_av(ebv, 3.1), 3.1)) as mag_v_dereddened,
    -- Convert to flux and deredden
    astro_flux_deredden(astro_mag_to_flux(mag_v, 25.0), astro_extinction_band('V', astro_extinction_av(ebv, 3.1), 3.1)) as flux_v_dereddened
FROM photometry;

-- Extinction at specific wavelength (H-alpha = 6563 Å)
SELECT astro_extinction_alambda(6563, 1.55, 3.1) as A_Halpha;

Cosmological Calculations

-- Calculate cosmological distances at different redshifts (H0 in km/s/Mpc)
SELECT
    z as redshift,
    astro_luminosity_distance(z, 70.0) as lum_dist_mpc,
    astro_comoving_distance(z, 70.0) as comoving_mpc
FROM generate_series(0.1, 2.0, 0.1) as t(z);

Body Model Analysis

-- Compare different stellar remnants
SELECT
    mass,
    (astro_body_star_white_dwarf(mass)).radius_m / 1000 as wd_radius_km,
    (astro_body_star_neutron(mass)).radius_m / 1000 as ns_radius_km,
    (astro_body_black_hole(mass)).radius_m / 1000 as bh_radius_km
FROM generate_series(0.5, 2.0, 0.1) as t(mass);

-- Exoplanet characterization
SELECT
    name,
    astro_body_planet_rocky(mass_earth) as rocky_model,
    astro_body_planet_gas_giant(mass_earth / 318.0) as gas_model
FROM exoplanets
WHERE mass_earth < 10;

📚 Documentation

• Cookbook — real-world recipes: Gaia cone search, HR diagrams, dust dereddening with CCM89, Hubble diagrams, exoplanet characterization, N-body spatial binning, ICRS↔galactic transforms, observation planning with sidereal time and alt/az. Each recipe is self-contained and runs without external data.
• Function reference: see the tables above
• Updating guide
• Deployment scripts

🤝 Contributing

Contributions are welcome! Please see CONTRIBUTING.md for guidelines.

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests
5. Submit a pull request

📖 How to Cite

If you use astro-duck in research that leads to a publication, please cite the software via its Zenodo DOI. The badge above resolves to the latest version; for a specific release use the version DOI.

Concept DOI (always-latest): 10.5281/zenodo.19482715

Version-specific DOIs (e.g. 10.5281/zenodo.19645905 for v1.4.1) are assigned by Zenodo when a GitHub Release is published and are listed on the corresponding release page.

GitHub also reads CITATION.cff and shows a "Cite this repository" button in the right sidebar with auto-generated BibTeX / APA / RIS / EndNote formats.

BibTeX

@software{bethge_astro_duck,
  author       = {Bethge, Björn},
  title        = {{astro: A DuckDB extension for astronomical calculations (astro-duck)}},
  year         = {2026},
  version      = {1.4.2},
  publisher    = {Zenodo},
  doi          = {10.5281/zenodo.19482715},
  url          = {https://doi.org/10.5281/zenodo.19482715}
}

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Acknowledgments

• DuckDB for the excellent database engine
• Astropy for astronomical calculation references
• The astronomical community for domain expertise

📞 Support

• Issues: GitHub Issues
• Discussions: GitHub Discussions
• DuckDB Discord: Join the community

---

Made with ❤️ for the astronomical and data science communities