Adding aircraft_reg and aircraft_model on daily report

[davmasc]

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

[biekerc]

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…

[davmasc]

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.

[obj]

@biekerc

Not sure if dump1090 even „knows“ type and registration

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.

[foxhunter]

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

[davmasc]

Dump1090 MySQL Database Script with Alert and Filter

writes Dump1090 data to database and e-mail alerts on special events. you can set how often the script writes to database and looks for alert condition. you can specify the area (lat/lon/alt) to be observed and filter for special hex and/or flight numbers. [screen] [screen] please do not abuse my script to generate and publish the flightaware-mlat-data anywhere - this data is only meant for your personal use. you find additional information here: FlightAware MLAT Network Announcement git i…

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.

[biekerc]

@davmasc

the output data of this project

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.

[foxhunter]

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.

[davmasc]

@foxhunter

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

Maybe one of these files?

[NigelRichardson]

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

[foxhunter]

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.

[bukadako]

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

[davmasc]

This is exactly my goal.

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

[NigelRichardson]

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

[foxhunter]

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

[bukadako]

@ 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.

[NigelRichardson]

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.

[foxhunter]

@NigelRichardson

I’ve managed to do this in Excel now linking the daily report to a database

Looks like we’re doing the same.