Comment by JosephRedfern

2 years ago

Very pretty! As it happens, I too have started ingesting ADS-B Data into ClickHouse recently, but have nothing nearly as beautiful as this.

I'm hitting the airplanes.live API every 10 seconds using ClickHouse's URL table function and storing in a MergeTree: https://github.com/JosephRedfern/airhoover/blob/main/airhoov.... Would love to use refreshable materialised views for this, but at the moment there's no append functionality (refresh only), so have to use Python to to trigger the query.

There's an open instance here: https://airhoover.joesstuff.co.uk/play?user=default#U0VMRUNU.... Only 1.5 days or so of data, I truncated before setting up tiered storage (local disk + backblaze b2).

Cool being able to e.g. get a break-down of aircraft type by operator (https://airhoover.joesstuff.co.uk/play?user=default#U0VMRUNU...) or who (supposedly) flies their planes fastest (https://airhoover.joesstuff.co.uk/play?user=default#U0VMRUNU...)