pi@raspberrypi:~ $ cat vrs-to-csv.py
#!/usr/bin/env python2
#
# Converts a Virtual Radar Server BasicAircraftLookup.sqb database
# to a CSV file suitable for feeding to csv-to-json.py
#
import sqlite3, csv, sys
from contextlib import closing
def extract(dbfile):
writer = csv.DictWriter(sys.stdout,
fieldnames=['icao24', 'r', 't'])
writer.writeheader()
with closing(sqlite3.connect(dbfile)) as db:
with closing(db.execute('SELECT a.Icao, a.Registration, m.Icao FROM Aircraft a, Model m WHERE a.ModelID = m.ModelID')) as c:
for icao24, reg, icaotype in c:
writer.writerow({
'icao24': icao24,
'r': reg,
't': icaotype
})
if __name__ == '__main__':
if len(sys.argv) < 2:
print >>sys.stderr, 'Reads a VRS sqlite database and writes a CSV to stdout'
print >>sys.stderr, 'Syntax: %s <path to BasicAircraftLookup.sqb>' % sys.argv[0]
sys.exit(1)
else:
extract(sys.argv[1])
sys.exit(0)
pi@raspberrypi:~ $
Progressing slowly! Using a recent Basestation.sqb file I deleted the unwanted columns and exported the table as a CSV file. Not a good idea to load into Excel to edit column names as it mangles up some of the icao24 addresses if the 5th character is “E”.
Next the commands to create the JSON database!
$ rm …/…/public_html/*.json
OK but would have been easier to understand with the full path.
$ xzcat vrs.csv.xz | nodejs ./filter-regs.js >vrs-pruned.csv
OK but as my csv file is not compressed presumably use “cat” instead. Action explained in .js file.
$ xzcat flightaware-20180720.csv.xz | nodejs ./filter-regs.js >fa-pruned.csv
No idea what this does. flightaware-20180720.csv.xz is not compressed not a csv to me looks to me like a Python script!
$./csv-to-json.py vrs-filtered.csv fa-filtered.csv …/public_html/db
Presumably I can just miss out fa-filtered.csv until I find what it is supposed to do!!
I will try on run it on my Ubuntu box first so I dont destroy the current working database on the pi!
Well in spite of a few errors installing the files required by filter-reg.js I have a “db” directory on my Linux box full of dozens of JSON files. Even using using sudo I didnt have write access to in the tools directory so the quick fix was to run the last script in my home directory. Tomorrow I will try it on the Pi!
Progress halted yesterday by the Pi overheating on what was probably the last day of our heatwave!
The problem I had with both flightaware-20180720.csv.xz & vrs.csv.xz was when downloaded and saved from the flightaware/dump1090 web page on github. The other files I downloaded the same way were all ok. The files downloaded via git clone were all ok.
I have created my own file called Aircraft.csv which I have copied to the Pi into /home/pi/dump1090/tools
This command removes aircraft registrations from csv file when it can be calculated so saving space:-
sudo cat Aircraft.csv | nodejs ./filter-regs.js >aircraft-pruned.csv
It runs ok on Ubuntu but I have to change the “tools” directory permissions to “777” otherwise the file aircraft-pruned.csv cannot be created. However when exactly the same is done on the Pi I get this error message even after a delete and new clone:-
/home/pi/dump1090/tools/node_modules/csv/node_modules/csv-generate/lib/index.js:71
Generator = function(options1 = {}) {
^
SyntaxError: Unexpected token =
at Module._compile (module.js:439:25)
at Object.Module._extensions…js (module.js:474:10)
at Module.load (module.js:356:32)
at Function.Module._load (module.js:312:12)
at Module.require (module.js:364:17)
at require (module.js:380:17)
at Object. (/home/pi/dump1090/tools/node_modules/csv/lib/index.js:5:12)
at Module._compile (module.js:456:26)
at Object.Module._extensions…js (module.js:474:10)
at Module.load (module.js:356:32)
The “^” in the error was pointing at “=” above it.
I have worked around all the other errors but am lost on this one so any help much appreciated!
Seems like a problem with one of the dependencies of filter-regs.js.
You can skip the filtering step if you want (and just feed your original csv file in), the filtering is just about reducing the size of the database when there’s redundant data there.
Success in spite of the Pi finally failing today by loosing USB after overheating due to the weather on Tuesday. So now running on a spare Pi and have an up to date aircraft database! So far all aircraft have “Registration” & “Type” except one which I think had a faulty ICAO address.
The part which would not run on the Pi for me shrinks the database by removing the registration number if it can be calculated from the address. So as not to break the Pi I did all the testing then ran all the commands on my Linux computer. I just had to FTP the “pruned” csv file to the Pi where the final command was run to generate the new JSON aircraft database.
In future any new aircraft could be manually added to the csv file with a text editor then the final command run to regenerate the database!
Thanks I did think of doing that. However the process reduces the file from 4.1Mb to 2.6MB but that may make the Pi find the required item a little quicker but difficult to say if the difference would be noticeable.
Updated to version 3.6.2 and back to the original situation with about 1/3 aircraft with no details. Luckily I did an upgrade not a clean install so just need to run the the csv-to-json.py command to replace the new json database with my data to get back to 100% aircraft details!
