Astronomy Querying for Space Objects

Astronomy Querying for Space Objects

I wanted to ask the small bodies of the solar system a question and get a table back in seconds. Which near-Earth objects pass closest this year? Which asteroids share an orbit shape with one I already care about? Each time, I wrote a one-off script, and each script answered exactly one question. So I put Metabase in front of SpaceDB, my local database of space objects, and now I filter and group by clicking.

This post covers how to run Metabase on your own machine, connect it to a database of small bodies, and ask first questions that return useful tables. It assumes Docker is installed and that you have a database of objects to point it at. SpaceDB is mine, and the post on how I built it is SpaceDB: Querying a Million Space Objects. If you build your own from the JPL feeds, the same steps apply.

What Metabase does for this job

Metabase is a web tool that sits on top of a database. You pick a table, add filters, group rows and sort them, and it shows the result as a table or a chart. You can save a result as a card and pin cards to a dashboard. When the point-and-click builder cannot express a question, it has a SQL editor. For astronomy data that mix helps, because most questions start simple (“objects larger than 500 meters”) and then grow a condition (“that pass within 10 lunar distances before 2030”).

Table size is the other reason. SpaceDB holds more than 1.5 million objects. A table that large is slow to scroll and fast to filter, which is where a query tool beats a spreadsheet.

Start Metabase in Docker

One command starts it:

docker run -d -p 3000:3000 --name metabase metabase/metabase

That runs the official image in the background, publishes port 3000 and names the container metabase. Open http://localhost:3000 in a browser. If the page does not load at once, the app is still starting, so reload after a moment.

The first screen is a setup wizard. You create an admin account, and it offers to connect a database. You can skip that step and add the database later, which is what I do. The Metabase documentation page “Running Metabase on Docker” covers where Metabase keeps its own data. Read it before you rely on saved dashboards, because cards you save live in that store and a removed container can take them along.

Connect the database

Open Admin settings, then Databases, then Add database. Pick the engine that matches yours. Enter the host, port, database name and a user.

Create a read-only user for Metabase. A query tool does not need write access to a table of space rocks, and a wrong click then cannot change data. After you save, Metabase scans the tables and fields. Open Browse data when the scan finishes and your tables should be listed. Keep the credentials out of anything you share. I never paste them into posts or chat.

Three questions to start with

The first is the close-approach question. In the builder, choose the close approaches table, filter distance to under a threshold and sort by date. The SQL version is below. The table and column names below are placeholders, so swap in the names from your own schema.

select designation, approach_time_tdb, distance_au, velocity_km_s
from close_approaches
where distance_au < 0.0257
order by approach_time_tdb;

The threshold 0.0257 au is 10 lunar distances. That is arithmetic. One lunar distance is 384,400 km and one au is 149,597,870.7 km, so 10 lunar distances is 3,844,000 divided by 149,597,870.7, about 0.0257 au. Expected result: a short list of near passes ordered by date, with the closest objects at the top if you sort by distance instead.

The second question asks which orbit classes hold the most objects. In the builder, summarize a count of rows and group by orbit class. Expected result: a bar chart with one bar per class. I do not know the counts yet and will not guess them.

The third question narrows to one class. JPL’s class code IEO covers Atira asteroids, whose orbits sit inside Earth’s orbit. Filter the objects table to that class and sort by eccentricity. Expected result: a short table, because that class is small.

Save each result as a card, then add the cards to one dashboard. Metabase can also download any result as a file, so you can hand a table to someone without giving them access to the database.

Read the units before you trust a number

JPL publishes the source data, and its conventions carry into any table built from it. Four are easy to miss.

Distances in the close-approach table are in au unless a query asks for another unit. The JPL API accepts a lunar distance written as 10LD in its dist-max parameter, which is handy when you query JPL directly.

The times in the close-approach records (the jd and cd fields) are in TDB, a time scale used for ephemerides, and not in UTC. The difference is under two minutes, which does not matter for a planning question and does matter if you compare against a clock. Say which scale your column holds. A column named approach_time hides the answer, so name it approach_time_tdb.

Field names in the JPL small-body query API are case sensitive. A typo returns an error and not a quiet empty result.

For a stable identifier, use spkid or pdes. The id field is internal to JPL’s database and is not meant for joining across sources.

Refresh from JPL when you need to

You do not need Metabase to reach JPL, but the two URLs below show what the feeds hold. The small-body query API is https://ssd-api.jpl.nasa.gov/sbdb_query.api. This example from its documentation returns the orbital elements for Atira near-Earth asteroids:

https://ssd-api.jpl.nasa.gov/sbdb_query.api?fields=full_name,epoch,e,a,q,i,om,w&sb-class=IEO

The close-approach API is https://ssd-api.jpl.nasa.gov/cad.api. It takes date-min, date-max and dist-max, and returns fields such as des, jd, cd, dist, v_rel and h. Check that the response’s signature.version reads 1.5 before you trust a parser written against it. JPL documents no rate limit for these APIs, so I fetch once and query the local copy.

Where the AI features fit

Metabase also advertises AI-assisted exploration. If you try it, read the SQL it generates before you trust the table. A query that runs and returns rows can still count the wrong thing.

Get an invite

I plan to add more objects over time. If you want to look at my Metabase instance, go to the Contact page and put Metabase in the message.

Subscribe to Just another Geek

Don’t miss out on the latest issues. Sign up now to get access to the library of members-only issues.
jamie@example.com
Subscribe