Aviation, Software Development, Stratux, Technology

Stratux – what’s in that database?

1 Comment

If you’re a hacker, and you’re a data geek.. you probably want to know what’s in that Stratus SQLite database!

Get a SQLite client

I wasn’t able to find a SQLite client on the Straux image, so I installed one with apt-get:


sudo apt-get install sqlite3

Next, run a basic function test to see if it installed and will run:


sqlite3

SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

Cracking open the data file

The sqlist logging / database file is in /var/log, and is named stratux.sqlite.

Cracking open the database and checking the schema I found:


sqlite3 stratux.sqlite

SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite>
sqlite> .tables

dump1090_terminal messages startup traffic
es_messages mySituation status
gps_attitude settings timestamp

Stratux SQLite Tables

Some of these tables contain alot of data:

Table Name Record Count
dump1090_terminal 313290
messages 307
startup 29
traffic 47038
mySituation 0
status 5350
timestamp 252097
settings 0
es_messages 463973
gps_attitude 0
es_messages table

This contains some interesting things. Here are the last 5 records from the database:


select * from es_messages order by timestamp_id desc limit 5;

469502|0001-01-01 00:39:09.91 +0000 UTC|{"Icao_addr":11265096,"DF":0,"CA":0,"TypeCode":0,"SubtypeCode":0,"SBS_MsgType":7,"SignalLevel":0.002207,"Tail":null,"Squawk":null,"Emitter_category":null,"OnGround":false,"Lat":null,"Lng":null,"Position_valid":false,"NACp":null,"Alt":22625,"AltIsGNSS":false,"GnssDiffFromBaroAlt":null,"Vvel":null,"Speed_valid":false,"Speed":null,"Track":null,"Timestamp":"2017-04-24T19:04:54.804Z"}|296790
469503|0001-01-01 00:39:09.97 +0000 UTC|{"Icao_addr":10846969,"DF":0,"CA":0,"TypeCode":0,"SubtypeCode":0,"SBS_MsgType":7,"SignalLevel":0.039045,"Tail":null,"Squawk":null,"Emitter_category":null,"OnGround":false,"Lat":null,"Lng":null,"Position_valid":false,"NACp":null,"Alt":12075,"AltIsGNSS":false,"GnssDiffFromBaroAlt":null,"Vvel":null,"Speed_valid":false,"Speed":null,"Track":null,"Timestamp":"2017-04-24T19:04:54.901Z"}|296790
469498|0001-01-01 00:39:09.53 +0000 UTC|{"Icao_addr":11265096,"DF":0,"CA":0,"TypeCode":0,"SubtypeCode":0,"SBS_MsgType":7,"SignalLevel":0.003431,"Tail":null,"Squawk":null,"Emitter_category":null,"OnGround":false,"Lat":null,"Lng":null,"Position_valid":false,"NACp":null,"Alt":22600,"AltIsGNSS":false,"GnssDiffFromBaroAlt":null,"Vvel":null,"Speed_valid":false,"Speed":null,"Track":null,"Timestamp":"2017-04-24T19:04:54.426Z"}|296789
469499|0001-01-01 00:39:09.53 +0000 UTC|{"Icao_addr":10687795,"DF":11,"CA":1,"TypeCode":0,"SubtypeCode":0,"SBS_MsgType":8,"SignalLevel":0.001740,"Tail":null,"Squawk":null,"Emitter_category":null,"OnGround":null,"Lat":null,"Lng":null,"Position_valid":false,"NACp":null,"Alt":null,"AltIsGNSS":false,"GnssDiffFromBaroAlt":null,"Vvel":null,"Speed_valid":false,"Speed":null,"Track":null,"Timestamp":"2017-04-24T19:04:54.441Z"}|296789
469500|0001-01-01 00:39:09.69 +0000 UTC|{"Icao_addr":10687795,"DF":0,"CA":0,"TypeCode":0,"SubtypeCode":0,"SBS_MsgType":7,"SignalLevel":0.000488,"Tail":null,"Squawk":null,"Emitter_category":null,"OnGround":false,"Lat":null,"Lng":null,"Position_valid":false,"NACp":null,"Alt":28025,"AltIsGNSS":false,"GnssDiffFromBaroAlt":null,"Vvel":null,"Speed_valid":false,"Speed":null,"Track":null,"Timestamp":"2017-04-24T19:04:54.626Z"}|296789

Selected one of the records and decoded the JSON message:

{
	"Icao_addr": 11265096,
	"DF": 0,
	"CA": 0,
	"TypeCode": 0,
	"SubtypeCode": 0,
	"SBS_MsgType": 7,
	"SignalLevel": 0.002207,
	"Tail": null,
	"Squawk": null,
	"Emitter_category": null,
	"OnGround": false,
	"Lat": null,
	"Lng": null,
	"Position_valid": false,
	"NACp": null,
	"Alt": 22625,
	"AltIsGNSS": false,
	"GnssDiffFromBaroAlt": null,
	"Vvel": null,
	"Speed_valid": false,
	"Speed": null,
	"Track": null,
	"Timestamp": "2017-04-24T19:04:54.804Z"
}

These seems to be the 1090 ‘es’ messages, however (at least this example) is missing tail number and squawk information. It’s interesting.., but not very actionable.

The next table I looked at was a completely different story!

traffic table

This looks like a good dataset to mine. With about 47,000 contacts int it.. this might be the basic dataset I’m looking for to use for some visualization.


select * from traffic limit 5;

1|11017168|N621VA|eaVRD947|0|0|0|1|-18.1550731147|6742|1|36.9193725586|-122.0635986328|13075|650|0|7|8|310|287|1|-1536|2016-02-26 01:22:53.426 +0000 UTC|0|0.27|0.16|0001-01-01 00:04:45.29 +0000 UTC|0001-01-01 00:04:45.4 +0000 UTC|0001-01-01 00:04:45.08 +0000 UTC|13075|0001-01-01 00:04:45.08 +0000 UTC|1|0|0.0|0.0|4
2|11017168|N621VA|eaVRD947|0|0|0|1|-19.4002028306|6742|1|36.9202423096|-122.0649414062|13050|650|0|7|8|310|287|1|-1472|2016-02-26 01:22:54.57 +0000 UTC|0|0.22|0.0|0001-01-01 00:04:46.33 +0000 UTC|0001-01-01 00:04:46.55 +0000 UTC|0001-01-01 00:04:46.55 +0000 UTC|13050|0001-01-01 00:04:46.55 +0000 UTC|1|0|0.0|0.0|7
3|11017168|N621VA|eaVRD947|0|0|0|1|-21.540961611|6742|1|36.9209747314|-122.0660552979|13025|650|0|7|8|310|287|1|-1472|2016-02-26 01:22:55.503 +0000 UTC|0|0.23|0.23|0001-01-01 00:04:47.32 +0000 UTC|0001-01-01 00:04:47.32 +0000 UTC|0001-01-01 00:04:47.48 +0000 UTC|13025|0001-01-01 00:04:47.48 +0000 UTC|1|0|0.0|0.0|10
4|11017168|N621VA|eaVRD947|0|0|0|1|-24.8545224734|6742|1|36.9220275879|-122.0676879883|13000|650|0|7|8|310|287|1|-1472|2016-02-26 01:22:56.453 +0000 UTC|0|0.14|0.14|0001-01-01 00:04:48.41 +0000 UTC|0001-01-01 00:04:48.41 +0000 UTC|0001-01-01 00:04:47.48 +0000 UTC|13025|0001-01-01 00:04:47.48 +0000 UTC|1|0|0.0|0.0|12
5|11017168|N621VA|eaVRD947|0|0|0|1|-22.2004294875|6742|1|36.9220275879|-122.0676879883|12975|650|0|7|8|310|287|1|-1408|2016-02-26 01:22:57.573 +0000 UTC|0|1.14|0.0|0001-01-01 00:04:48.41 +0000 UTC|0001-01-01 00:04:49.55 +0000 UTC|0001-01-01 00:04:49.44 +0000 UTC|12975|0001-01-01 00:04:49.44 +0000 UTC|1|0|0.0|0.0|14

Question.. what are the top 10 traffic events by Tail number?
Let’s find out! The simplest way I know of to do this.. and it offers the advantage that the select query against the large dataset with aggregation only runs ONCE.. is to get the parts you want, and the summary count and jam them into a temporary table. With SQLite, you can do it like this:


CREATE TABLE traffic_summary AS select Reg,Tail,count(*) as hits from traffic group by Tail;

That produced a summary table with 423 records in it:


select count(*) from traffic_summary;

423

Now.. who’s the nosiest one of them all?


select * from traffic_summary WHERE Tail>'' order by hits DESC limit 10;

N713FR|ea1435|660
N214NN|CPZ6073|624
N887NN|eaAL2508|564
|eaAL8215|553
N76503|UAL1010|539
N141SY|SKW5982|511
N363VA|VRD1935|501
N204NN|CPZ6074|494
N630VA|VRD941|491
|eaAAR284|478

Using a little sleuthing.. I found that ‘N713FR’ is a Frontier Airlines Airbus 321…

Upcoming Article: Creating some tools to automate acquisition of this information and find out just who’s flying over and how often.

One thought on “Stratux – what’s in that database?”

  1. Pingback: SQLite – get table schemaDavid DeMartini actual | David DeMartini actual

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.