Using Datasette for Plane Tracks

A year ago I went on a side quest to refactor how I'm collecting and analyzing airplane data at home. Like most of my home projects, I spent a lot of time learning something new, but lost momentum before I could close things out. As part of a new year's resolution to finish loose ends, I'm writing a quick summary about what I learned and putting a snapshot of the code in github so I can revisit it later.

Airplane Logger

I've been running a PiAware server at home for several years that collects airplane positions from an SDR receiver I have in the garage. When I originally set this up, I wrote a tool called d1090-companion in Go to help record positions, generate daily track files, and allow me to visualize the results through a webserver. The tool has been running for years now, happily generating daily gzipped TSVs that I almost never look at because the maps API key I setup expired. I've always had thoughts about rewriting the companion, but my Go knowledge is out of date and I don't want to refresh it. Ultimately, I'd like something that lets me visualize the data better, filtering out boring stuff and highlighting unique planes.

Trying Out Datasette

While thinking about alternatives I came across Simon Willison's Datasette, which is a tool for making a mix tape of data that you can inspect with a browser. Willison's original idea was that a data provider should pack up their data into a SQLite file, put it on a read-only website, and then run a common web frontend that lets analysts do SQL queries on the data to slice it the way they want and plot the results. There are a number of plugins available for visualizing the data, including a map plotter that looked like it would be great for my airplane data. I found some other useful plugins, like reading from Parquet and GeoJSON files.

I initially started with the cluster-map plugin which lets you show individual Lon/Lat points on a map (with built-in clustering during zoom-out). The examples were great, but I didn't see an easy way to render tracks. I switched to using the GeoJSON plugins, which take a GeoJSON file and convert it to a SQLite DB that a map tool can plot. It took some tinkering, but I eventually confirmed that if I could export my tracks to GeoJSON, the tools would plot them ok. I wrote some Pandas code to parse my TSVs, clean them up, and convert the tracks to GeoJSON.

Good and Bad

There were a lot of things I liked about this approach. First, it meant I could just generate data files and hand them off to something else to render. Second, the SQL interface for Datasette is pretty powerful- once the page is up, I can put in queries to filter the tracks down (eg all ICAO's that start with AE). This feature motivated me to add columns at generation time (eg, IsMilitary) to make downstream search easier. Finally, there were just enough plugins I could do useful things without too much frontend knowledge.

I stalled out on the project though because there were a few things that weren't great for my use case. First, the Datasette version I used ran a rest API on the server to interface with the data. My PiAware is already loaded down with work, so I'm hesitant to put another API on it that isn't heavily used. Datasette-Lite looked like a promising alternative- it looks like it embeds the file processing in webassembly, so the client fetches the data and runs it on the client. Second, while the GeoJSON plotters were great, the plots were a little clunky and I had trouble figuring out the right way to render all the views I wanted. Datasette can probably do what I want, it just takes more effort than I want to put into it right now. Finally, there's still some data balance problems with the whole system. I probably need to downsample the tracks to get things to be more responsive.

Code

I've put a snapshot of the code at snippets/250221_planes_datasette so I can come revisit this someday. Looking through it again, I realize that another reason I stalled out was because I got obsessive about trying to identify all the big airline codes so I could filter them out. The private charter planes seem to be getting sneakier for how they label themselves so they blend in with everyone else. There's plenty of room for hanging yarn with Datasette though, so at some point I'll need to come back to this.