FlightAware Discussions

Adding aircraft_reg and aircraft_model on daily report

Continuing the discussion from Daily Log/E-Mail Aircraft Report directly on your RaspberryPi:

I ask if it is possible to add the registration and airplane model to the php code of the above discussion.
I’m not very used to programming, but I know that this data can be obtained from the ICAO code.
The file I’m referring to in ac_counter.php.

Thank you all in advance

This is the code in question.

Not sure if dump1090 even „knows“ type and registration, or if you need to use a separate database and combine the information…

Thanks for quick reply.
Yes, i want to know how to extrapolate extra data from ICAO 6 digit hex code and put them in the output file, changing the php code.

It does not; aircraft.json only has the ICAO address. Skyaware and variants do a lookup, on the browser side, of the ICAO address against a static database.

1 Like

Not possible. You will need a database which contain the information you’re looking for and do a query against the hex code.

That’s what i am doing afterwards. There are some databases available which contain many of the aircraft and their details. You might need to know how to query it via PHP

I am considering the output data of this project: I can see also registration, manufacturer, aircraft type and so on…
So it seems possible doing that.

You mean this table?

If you look closely, the columns to the right are the output of dump1090, with a column called hex with the ICAO-address, until column V.

It appears that that was then combined later on with a second table, which had a field AircraftID, and a field ModeS, again containing the ICAO, and additional information about the aircraft.

Combining two tables is easy if you know how to do it, but you will need a table/db that contains this information.

It’s not that simple as it might look. The table does not contain the aircraft information or anything else.
This is a screenshot of the first few lines from one of the original tables created by the script on my device. The last column is R

It’s not the MySQL dump but the other one “daily aircraft report” which creates a CSV every night for the day before.

The aircraft data is not part of the dump1090 stream, so it has to be queried using a different table.

Please read the github page for that MySQL script again. It clearly states this:

one line sample database output using an inner join to basestation.sqb:

So you have to have another DB containing the aircraft information.

Maybe one of these files?

The information that is required will be within the Basestation.sqb database and could be derived via the hex codes, but I don’t have the expertise to know how you would combine the database with the daily report output. I asked this question on the forum some months ago but didn’t get a reply from anyone

You can use any file which contain aircraft information and run a joined query on it.
I am currently doing it in Access after i imported the daily file.

It’s not necessarily a basestation.sqb, there are other files available as well.

My daily report (join with a second DB) looks like this:

This is exactly my goal.

Only if you want, can kindly report how did you do this project?

I would also be very interested to know how you achieved this if you are happy to share. I don’t have the IT skills to know how link databases

Many thanks

1 Like

That’s similar to how i do it.

I am currently using Microsoft Access.
Import both tables (the daily report and the master DB with aircraft information) and run a joined query view where the ModeS column is the joined one.

Voila - Table with both information.
But: You will not see the aircraft which are not in the master DB

@ davmasc

@ NigelRichardson

Very hard to explain. My mother tongue is German.

The last two years I’ve learned how to work with ‚mySQL‘ and how to create a SQL-retrieval….
mySQL server is running 24/7 containing some essential databases.

A php-Script is running parallel on this server, feeding mySQL-database.

With this ‚dailyreport‘ I got (made by php-script) I start my daily (manual) work to keep my aircraft-database up-to-date.

With some SQL-Querys I’m looking for new entry, increment the views-field, making additions or completion in this aircraft database. My references are relevant websites like airframes.org.

Then I run my final joined query to create my personal daily-report with all information I want. See my post above.

Actually I have only 300-400 records per day, normally there are more than 3.000.

2 Likes

No problem. I’ve managed to do this in Excel now linking the daily report to a database extracted from my Basestation.sqb. A little bit long-winded but it works.

2 Likes

Looks like we’re doing the same. :slight_smile:

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.