Aircraft details database


#1

I have been running PiAware for several weeks and previously the Pi supplied to Planeplotter until just over two years ago. The problem I have is that over 1/3 of the aircraft I see don’t list any details such as “Registration” or “Type”. How do I update the database/data which I assume must be somewhere on the Pi. I searched the forum and found no useful info on the subject.


#2

Here is a starting point: https://github.com/flightaware/dump1090/blob/master/tools/README.aircraft-db.md


#3

What an insane way to mess up a database file and convert it into dozens of incomprehensible JSON files!!! The SQL file hasnt been updated since 2015 and the Python script to convert it to csv crashes with a syntax about the file type in both Windows and Linux. There is no way I am going to spend time trying to understand how it is supposed to work! Luckily ADS-B Exchange and FR24 who have a more up to date database.

It is odd that all that effort has been put into creating the fantastic detailed statistics about each users system performance but nothing has been done about to enable a user to have an up to date aircraft database!


#4

Well, I’m happy to take pull requests that improve it. Let me know.

You can also use external programs such as modesmixer, VRS, etc, if they fit better with what you want - dump1090 is deliberately fairly minimal when it comes to the UI.

From that vague description, you’re probably trying to run a python3 script under python2.


#5

It would be good to have access to an upto data database, does any one have access to such?

I have a local database that is updated via VRS (database plugin), but naturally that is only good for planes I see (England)… If we could devise a simple way to collaborate and build a bigger up to date database we’d solve the ‘out of date’ issue.

(I use a variation of the script to prepare weekly db\ json files for my dump1090)


#6

Could always collectively collate one.
I am updating my local aircraft in Perth (AUS) i can share that if anyone wants 7C, i also get a few unknown Middle East carriers.


#7

With the help of MySql and a Aircraft Database (hard work- with daily amendment) my Mutability 1.15 is up to date.


#8

The FlightAware-provided data included with dump1090-fa should be up to date, for those aircraft that it covers.


#9

can the PP basestation.sqb. or Basestation’s .sqb - has frequent updates - be used for this task ?


#10

Easy! Since you already have VRS and the plugin installed, you can build a database yourself.

Create a receiver in VRS to receive data from ADS-B Exchange. Point the writer plugin to get data from that receiver.

Be prepared for a big basestation.sqb file. After only one week the file will easily reach 700MB in size. After a few months it can reach 6GB in size.


#11

Can that be done, if so how?


#12

Yes, fair enough. I am adding extra details to mine, rather than just those that are the default


#13

Instructions here:

https://www.adsbexchange.com/data/#

Be warned that it’s a lot of data. VRS closing slows down substantially. Ensure you don’t display the traffic on the VRS map.


#14

It was certainly Python 2 I was running in Ubuntu as that is all there was to download but dont know what was on Windows as uninstalled it yesterday.


#15

Ok. I’d need to see the exact error you’re getting to help further. The scripts in tools/ are a mix of python2, python3, and javascript.


#16

I used Planeplotter a few years ago and it was easy to add details of individual aircraft to your copy of the database I think automatically from Airframes although I think Airframes were not keen on it! I often added or updated items on Airframes.

I can see that a SQL database is OK for a Windows system but may not be suitable running on a low powered RPi but I cannot fathom out the logic of the JSON database files. I hoped it was as simple as adding your own data to a JSON file in the format (ICAO number) (Registration number) (Aircraft type) but the format varies from file to file and sometimes within a file itself. I have searched many of the files but yet to find one containing ICAO numbers so adding data directly to the JSON files does not seem practical!


#17

The json files are not meant to be edited directly by a human. Create or modify a CSV file with the data you want and run it through the toolchain in tools/ to regenerate the json files.


#18

This looks like a good place for me to offer a “Thank You” and “I’m Impressed” to the developers who who came up with the tools to extract data from an sqb and put it into the json files.

I’m a old gray haired developer ( ASM, C, C++) who is fairly new to raspi and adsb. I recently made changes to the tools on my system to automate extracting more fields from my VRS installation (with the Database Writer add-on installed), and update the json files. I also re-worked the dbloader logic so it would automatically query the VRS database for the most up-to-date info on planes that weren’t found in json. I found out how to do all of that from bits and pieces of threads on this forum.

In the process of doing this I see what a really good idea it was to do the db extraction into json.so sump1090 could perform well given the hardware limitations.

If I was starting this project fresh today, and knew it would never run on anything other than a raspi 3 with an external USB SSD, I might come up with a different design. But for something that is going to run on early generation Pi or a Pi-zero, this is a brilliant piece of software engineering. I understand that it isn’t consumer-grade ease of use to modify the data, but the fact that there is a database of planes that large, offering that quick lookup performance, on a zero, is an amazing feet. Well done!


#19

The CSV is an obvious starting point but the problem is getting or generating an up to date one! A Basestation.sqb file would be a good source if I can find a recent one as it is a flat file so easy to remove unwanted data then export the table as CSV.

The trouble is I still need to generate a vrs.csv using the Python script so I can check I have the correct format! I have installed Python 3.7.0 on Windows but still get the error message mentioned earlier.

F:\Downloads>vrs-to-csv.py BasicAircraftLookup.sqb >vrs.csv
File “F:\Downloads\vrs-to-csv.py”, line 7
" <!DOCTYPE html
^
SyntaxError: invalid syntax

I had to add the " because the DOCTYPE does not show in the preview


#20

I think you have downloaded github’s webpage with a html-formatted version of the script, not the raw script itself. You need to follow the “raw” button at the top of https://github.com/flightaware/dump1090/blob/master/tools/vrs-to-csv.py (or, ideally, you’d get a git client and clone the whole repository…)

If you’re just trying to get an example CSV, though, there’s already one in the repository: https://github.com/flightaware/dump1090/blob/master/tools/vrs.csv.xz (you’ll need to decompress it with e.g. unxz)