Tweeting when Aircraft Overhead

As per my previous post (specifically the “Logging to database: (SQLite)” paragraph) I am logging detected flights to SQL database, with a small bit of code we can tweet when certain aircraft are detected overhead:

First create a twitter account if not done so already.

Next setup tweepy for python and get your twitter authentication tokens using this tutorial: https://realpython.com/twitter-bot-python-tweepy/

Finally replace the last line of the exiting write to database code at my GitHub with:

    for index, row in df1.iterrows():
        if df1['hex'][index] == 'HEX_CODE_YOU_WANT_TO_TWEET_ABOUT': 
            print("Found")
            import tweepy
            # Authenticate to Twitter
            auth = tweepy.OAuthHandler("AUTH_TOKEN","AUTH_TOKEN")
            auth.set_access_token("AUTH_TOKEN","AUTH_TOKEN")
            api = tweepy.API(auth)

            try:
                api.verify_credentials()
                print("Authentication OK")
                api.update_status('Tweet Text' + str(dateTime))

            except:
                print("Error during authentication")
        else:
            print("Hex was: ", df1['hex'][index])

    exit()

That’s it, happy tweeting!

Logging dump1090-fa to local database

As per my previous post I am feeding ADSB Exchange and Flight Radar24 from a RaspberryPi Zero and a USB DVB-T tuner.

This post is broken into two un-linked sections:
Section 1: Logging all flights to .csv file.

Section 2 (a): Deciding there was more flexibility with logging to database SQLite (incl. setup).
Section 2 (b): Solution to show all days flights on webpage with the insane logic of DB -> .csv -> webpage. Not pretty but it works.

Logging to .csv file:

I wanted to locally log flights that flew overhead each day but didn’t have the knowledge to put it all together until /u/gl0ckner/ on Reddit posted his work on logging flights to a .csv file. That didn’t work right out of the box for me so I made some tweaks and my slight modified version can be found on my GitHub. Simply download the file and run it manually by:

python3 /home/pi/flightlogger/flight_logger_csv.py

Or make executable and add to crontab to execute every minute as so:

chmod +x /home/pi/flightlogger/flight_logger_csv.py
crontab -e
* * * * * /usr/bin/python3 /home/pi/flightlogger/flight_logger_csv.py

A new .csv is created for each day. It works well. After a few days I thought it would be more helpful to query the data if it was in a database.

Note: So now a new entry is made to the database every minute regardless if the particular aircraft has been logged previously or not on the same day. I want to change it to say only log an aircraft if not logged in the last hour, this has been implemented in the below database option.

Logging to database: (SQLite)

After a comment by /u/Uncle_BBQ on the same Reddit post who submitted his work on this I thought I would give it a go. This script logs each overhead flight once into the database. It was my first time ever using a database and as usual it didn’t work right out of the box for me so I had to make a few tweaks, below is how to get it running:

#First install dependencies.
#They did not install properly for me from the script so did it manually.
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install sqlite3
pip3 install pandas
pip3 install numpy
pip3 install cython
pip3 install sqlalchemy
pip3 install psycopg2

Now we need to set up the database:

sqlite3 flightdata_1h.db
CREATE TABLE flightdata (date_time NUMERIC, date NUMERIC, time NUMERIC, hex TEXT, flight TEXT, alt_baro NUMERIC, alt_geom NUMERIC, gs NUMERIC, track NUMERIC, geom_rate NUMERIC, squawk NUMERIC, emergency TEXT, category TEXT, nav_qnh NUMERIC, nav_altitude_mcp NUMERIC, lat NUMERIC, lon NUMERIC);
.quit

Finally copy the file from my GitHub and run:

python3 /home/pi/flightlogger/flight_logger_sql.py

To see what’s in the database we can query it by:

sqlite3 flightdata_1h.db
SELECT date, time, hex, flight FROM flight_data;

Your output will look like this:

2020-03-21|20:56:23|406c39|VIR25B
2020-03-21|20:58:20|4077be|JCO7X

Finally to run the script every minute, make it executable and add to crontab:

chmod +x /home/pi/flightlogger/flight_logger_sql.py
crontab -e
* * * * * /usr/bin/python3 /home/pi/flightlogger/flight_logger_sql.py

You can query the database directly if needed, for example:

sqlite3 flightdata_1h.db
SELECT date_time, date, time, hex FROM flight_data ORDER BY DATE(DATE) desc LIMIT 100;

Updating webpage with flights that went overhead today.

Note: The perfect solution is when a webpage is requested the database is queried and the results delivered. Since I’m running SQLite and all tutorials were for SQL (and MariaDB wouldn’t run on RPi Zero) I went about it a different way. Through a cron job every hour the database is queried and the results pushed to a .csv file, this csv file is then put into a table and the webpage delivered whenever requested.

First create blank .csv file in /var/www/html/ and call it data.csv

To populate the .csv file I use:

#!/usr/bin/python3

# Import dependcies (probably don't need half of them, I just used an old file)
import os
import json
import csv
from dotenv import load_dotenv
from datetime import datetime
from datetime import date
from datetime import time
from datetime import timedelta
import requests
import pandas as pd
import numpy as np
import sqlalchemy
from sqlalchemy import create_engine

# Load env variables
load_dotenv(dotenv_path='')
db = 'sqlite:////home/pi/flightlogger/flightdata_1h.db'
db_table = 'flight_data'

# connect to database
engine = create_engine('sqlite:////home/pi/flightlogger/flightdata_1h.db')

# Get today's date
today = date.today()

# Get the current time
time = datetime.now().strftime("%H:%M:%S")

# Create a current time stamp
dateTime = datetime.strptime(datetime.now().strftime('%Y-%m-%d %H:%M:%S'), '%Y-%m-%d %H:%M:%S')

# Try to connect to database
try:
    df2 = pd.read_sql("SELECT * FROM flight_data WHERE flight_data.date_time > datetime('now','localtime', '-3600 seconds')", engine) #SQLite Syntax
    dbConnected = True

except:
    # If database does not exist or is unable to connect then print that
    print('Unable to connect to database.')
    # Set boolen value to False
    dbConnected = False

df2.to_csv("/var/www/html/data.csv", mode='a', header=False)

To clear the .csv file every night at midnight I use:

file = open("/var/www/html/data.csv","w")
file.write("index,date_time,hex,flight,alt_baro,alt_geom,gs,track,geom_rate,squawk,emergency,category,nav_qnh,nav_altitude_mcp,lat,lon,date,time\n")
file.close()

Cron jobs to run the above:

0 * * * * /usr/bin/python3 /home/pi/flightlogger/db_flight_to_csv.py
10 0 * * * /usr/bin/python /home/pi/flightlogger/csv_clear.py

The webpage located at /var/www/html/index.html is:

<!DOCTYPE html>
<html lang="en">
<!-- http://bl.ocks.org/ndarville/7075823 -->

    <head>
        <meta charset="utf-8">
        <style>
            table {
                border-collapse: collapse;
                border: 2px black solid;
                font: 12px sans-serif;
            }

            td {
                border: 1px black solid;
                padding: 5px;
            }
        </style>
    </head>
    <body>
        <!-- script src="http://d3js.org/d3.v3.min.js"></script -->
        <!-- script src="d3.min.js?v=3.2.8"></script -->
        <script src="d3.v3.min.js"></script>

        <script type="text/javascript"charset="utf-8">
            d3.text("data.csv", function(data) {
                var parsedCSV = d3.csv.parseRows(data);

                var container = d3.select("body")
                    .append("table")

                    .selectAll("tr")
                        .data(parsedCSV).enter()
                        .append("tr")

                    .selectAll("td")
                        .data(function(d) { return d; }).enter()
                        .append("td")
                        .text(function(d) { return d; });
            });
        </script>
    </body>
</html>

Give ‘pi’ user access to edit the .csv file:

sudo chown -R pi /var/www/html/data.csv 

You can view your webpage by entering the IP address of your device in a web browser.

That’s it!

Other references I used:
https://www.hackster.io/mjrobot/from-data-to-graph-a-web-journey-with-flask-and-sqlite-4dba35
https://towardsdatascience.com/sqlalchemy-python-tutorial-79a577141a91
https://docs.sqlalchemy.org/en/13/core/engines.html#postgresql
https://www.sqlitetutorial.net/sqlite-commands/

Flight Tracking on RPi Zero. (Updated to include ADSB Exchange)

Originally I ‘built my own’ FlightRadar24 node using their straightforward tutorial. Its cool and you get free premium membership on their mobile app but a lot of flights are censored which is silly as I can read them directly off my node but FR24 won’t let me see them on the app. So I ended up doing a fresh install and now I feed FR24 and also ADSB Exchange which don’t censor any flights and have some awesome local interfaces, see below for tutorial:

Start with a fresh Raspbian install on RPI. Then we install:

sudo apt-get install dump1090-fa
sudo apt-get install piaware #Not 100% sure if this line required
sudo bash -c "$(wget -O - http://repo.feed.flightradar24.com/install_fr24_rpi.sh)"

The FR24 setup should guide you through first setup if its your first time, other wise you can reconfigure if you have your existing FR24 key:

sudo fr24feed --reconfigure --fr24key=your_key

I restarted the service, not sure if necessary, everything should be feeding FR24 now.

sudo systemctl restart fr24feed

See if service is running:

fr24feed-status

You can also see status if you go the FR24 local web GUI at http://192.168.1.XXX:8754/

Next we setup feed to ADSB Exchange:

sudo bash -c "$(wget -nv -O - https://raw.githubusercontent.com/adsbxchange/adsb-exchange/master/install.sh)"

It should also guide you through first setup, very straight forward, when finished you can check if service running:

sudo systemctl status adsbexchange-feed

You can see if your data is getting to ADSB Exchange by going to https://www.adsbexchange.com/myip/ and you can view the global aggregated data at https://tar1090.adsbexchange.com/

There are a few other fantastic packages to install to see current stats of your system:

Tar1090 is an amazing package that shows you what your node is currently seeing, install below and then use web interface at http://192.168.1.XXX/tar1090/

sudo bash -c "$(wget -q -O - https://raw.githubusercontent.com/wiedehopf/tar1090/master/install.sh)"

Timelapse1090 shows historical flights and you can replay flights etc, works well but data only stored in RAM so lost over reboot, my next plan is to write this to a database (now done). (viewable at: http://192.168.1.XXX/timelapse/)

sudo bash -c "$(wget -q -O - https://raw.githubusercontent.com/wiedehopf/timelapse1090/master/install.sh)"

Graphs1090 is supposed to show you performance of your node, number of aircraft seen etc., didn’t get it working yet but works well for others: (viewable at: http://192.168.1.XXX/graphs1090/)

sudo bash -c "$(wget -q -O - https://raw.githubusercontent.com/wiedehopf/graphs1090/master/install.sh)"

You can also see the data stream from your node by the below CLI command:

nc localhost 30003

As an FYI below you can see the screenshots of the FR24 app showing the performance of my node a week after I set it up: