20 July 2012

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. 


No comments: