Data mining with gr-air-modes and sqlite.

I spent some time doing data entry last night.  I was too tired to write a script to scrape data, so I just manually keyed in the list of callsigns and units from banterops.com so I could identify some aircraft that I couldn't track.

This allowed me to see a fair bit more info about what was in the air.

Adventures with sqlite and gr-air-modes

In the last week I've been dabbling with sqlite.  Whilst dabbling, it occurred to me that I already had a potentially interesting sqlite database to play with. I've been putting my rtl-sdr dongle to good use, and have gr-air-modes running full-time on the linux box in the shack, so I'd already collected a large ammount of Mode-S data to try mining with sqlite queries.

I figured out that probably the most interesting thing to do would be to determine which aircraft didn't broadcast their positions.

My intial attempts to get lucky with select queries yielded either a syntax error, or an empty result.  I decided to reduce the degree of ignorance that I was applying to the problem, and resorted to google.

After google hit me with the cluebat, I  cooked up a query:

SELECT ident.ident
FROM ident LEFT JOIN positions
ON ident.icao = positions.icao
WHERE positions.icao is NULL;

This produced a bunch of callsigns, but ran like a dog. As the database I was working with grew larger, I began to have locking issues and had to bodge my working branch of gr-air-modes more heavily to keep it running.

Some more googling dug up a faster query format for me:

SELECT  ident.ident
FROM ident AS ident
LEFT JOIN (select distinct icao from positions) AS positions
ON ident.icao = positions.icao
WHERE positions.icao IS NULL;

This ran much faster than my original (0.4 secs vs. 4.5 secs on a 10MB db).

I decided to create a view, stored permanently in the database, so I didn't have to keep repeating myself:

CREATE VIEW "Untracked"
AS SELECT ident.icao, ident.ident
FROM ident AS ident
LEFT JOIN (select distinct icao from positions) AS positions
ON ident.icao = positions.icao
WHERE positions.icao IS NULL ;

The view can be invoked by

SELECT * from untracked; 

I've not yet been able to get the icao column contents formatted as hex within the query results.

I'm currently going through the process of merging my gr-air-modes tweaks back to the mainline upstream, as they were originally based on the rtl-sdr fork. I've added timestamps to all the entries in the ident table, and I'm just about to bodge the code to store all received ident message instances in the database

I've been doing all of this on Linux, using both the sqlite3 shell utility, or with Sqliteman, both of which are working well. 

RTL-SDR port of gr-air-modes feeding Xastir

After some finger trouble getting the RTL-SDR port of gr-air-modes working, I've now bodged that too for a Xastir feed.  I'm surprised to say this, but compared to the USRP B100 & WBX combination, this is working amazingly well.  So far I've had good tracks out to 137 miles, whereas the best I'd got in 6 days with the USRP/WBX was 90 miles.

This includes two tracks in Dutch airspace, one arriving and one departing.  Pretty amazing for a device that was overpriced at £25!

And a bit further again, 157 miles:

He came right past me and then on out to the west, being tracked for 213 miles.  Note that the packet counts are the number of position reports being forwarded to Xastir.  Many more ADS-B frames are being received than that.

And something out in the North Sea. Didn't see anything that far out up there with the USRP/WBX.

More ...

ADS-B in Xastir with gr-air-modes

I find that while gr-air-modes is great for decoding ADS-B data, using Google Earth to view it in is a bit of a drag.  As I tend to have Xastir running on my laptop most of the time, I thought that it would be cool to plot aircraft tracks in that instead.   I decided on a very dirty hack to feed ADS-B data from gr-air-modes into Xastir. You can see, in the screen shot above, data received with a USRP B100 and WBX  with a discone in the loft and a cheap preamp.

In order to get this to work, you will have to edit the modified modes_kml.py file and replace mycall and mypass:

mycall = "MYCALL"
mypass="MYPASS"

with something suitable, typically generated with the callpass utility.  Then configure Xastir to Enable Server Ports, and you should see something like this in the output of uhd_modes.py

/usr/local/bin/xastir_udp_client localhost 2023 MYCALL MYPASS "4196473>ADS-B:=5247.99N/00139.38E^035/462/A=033350BAW768  "
Received: ACK

If you don't get an ACK, it isn't going to work.

The file is modified from vanilla upstream, not from the rtl-sdr fork.

You must enable the kml output in order to get this to work, something like this:

./uhd_modes.py -g 50 -K ~/adsb.kml -a

Please don't feed (igate) ADS-B data into the APRS-IS. I don't think it is wanted.  I'f I'm wrong in this regard please let me know.

If you need to get some maps in your Xastir, I recommend enabling Automaps for  Online/OSM_tiled_Mapnik.geo in the Map Chooser properties dialog.