RadarBox Reporter Logs

[Allocator]

My V1.4 Reported log for today.  739 live aircraft - not bad considering this is a short time this morning and all of this evening. Note the A388 38105A  F-WWDD  A388  2008/02/21 08:39:52

http://www.ruyton.demon.co.uk/Downloads/RadarBoxLog20080221.txt

Also note that I don't have many unpopulated aircraft.  I've also realised that this is a time-order list - I thought that it was just random. 

However, I'd prefer it in Mode S or registration order without having to import it into Excel to sort.

[Spaice]

Is there a way to include the Airline data into the reports, so that the data could also be sorted in company order.  It seems strange to me that the report give registration, aircraft, date and time but nt airline.  Or have I missed something in the preference setup.

[tarbat]

Should be possible.  Using SQLite Maestro, I built a query to connect the Flights and Aircraft tables.  Generated the following SQL:

SELECT DISTINCT
  Flights.Route,
  Flights.Callsign,
  Flights.EndTime,
  Flights.Registration,
  Aircraft.Airline
FROM
 Flights
 INNER JOIN Aircraft ON (Flights.ModeS=Aircraft.ModeS)
ORDER BY
  Flights.Registration

The only thing you can't do is connect any tables in the MyLog database to tables in the NavData database.  It would have been better, in my opinion, if the MyLog and NavData databases were combined into one database.  Then you could easily link flights in the MyLog to data in the NavData tables.

One other thing.  In my opinion, the fields in all the tables would be better defined as VarChar fields rather than just plain Text fields.  You can do a lot more with VarChar fields in SQLite Maestro than you can with Text fields, such as apply filters interactively.

[Allocator]

Is there a way to include the Airline data into the reports, so that the data could also be sorted in company order.  It seems strange to me that the report give registration, aircraft, date and time but nt airline.  Or have I missed something in the preference setup.

No, not at the moment.

I think that it is important that users tell AirNav what they want from the report.  I don't use the reports other than for general interest, so I don't know what is best.  You can (in V1.4) export in CSV format from MyLog and work on that in a program such as Excel but it's not particularly easy.

This is what I would want out of the Reports, maybe you can add to it:

- Options to list in any of the following orders (the current listing is by time):

-- Mode S
-- Registration
-- Aircraft Type (short version)
-- Time (It does this already)
-- Company (Information not included in report at present)

I would like tick boxes to choose these settings.

OK AirNav Support, over to you.  I don't expect that we'll get these for V1.4 but maybe V1.4.1?

Thanks

Allocator

[Allocator]

A few minutes work with MS Excel with some of todays aircraft exported from MyLog in csv format can give something like this.  Sorry about the column spacing, it looks fine in notepad - must be the font used in the Forum here.  I'm sure that tarbat could produce something much neater using an SQL search and export.

1626   71025D   C130   Germany - Air Force
84-0110   AE01B3   LJ35   USA - Air Force
9H-AEK   4D2021   A320   Air Malta
9H-AEL   4D2022   A319   Air Malta
9K-AOB   706018   B772   Kuwait Airways
A6-EBD   8960CB   B773   Emirates
A6-EBQ   8960D8   B773   Emirates
A6-EHA   896144   A345   Etihad Airways
A6-EWB   896174   B772   Emirates
A6-EYG   89613C   A332   Etihad Airways
A6-EYP   8961A5   A332   Etihad Airways
A7-AEE   06A03E   A333   Qatar Airways
AP-BGY   7608F9   B772   Pakistan International Airlines - PIA
AP-BJA   760941   A321   Airblue
B-2428   78029F   B744   Great Wall Airlines
B-KAF   7801F0   B744   Dragonair Cargo
C-FMWV   C02202   B763   Air Canada
C-FMWY   C02205   B763   Air Canada
C-GNEQ   C06775   C680   Untitled
C-GZNC   C08801   B763   Zoom Airlines
CN-RNL   02001B   B737   Royal Air Maroc (RAM)
CS-DFC   4910C3   F2TH   Untitled (NetJets Europe)
CS-DFU   4910D5   C560   Untitled (NetJets Europe)
CS-DFX   4910D8   H25X   Untitled (NetJets Europe)
CS-DFY   4910D9   H25X   Untitled (NetJets Europe)
CS-DHM   49110D   C550   Untitled (NetJets Europe)
CS-DLD   491184   ...   Untitled (NetJets Europe)
CS-DRS   491253   H25X   Untitled (NetJets Europe)
CS-DXG   491307   C560   Untitled (NetJets Europe)
CS-DXN   49130E   ...   Untitled (NetJets Europe)
CS-DXP   491310   ...   Untitled (NetJets Europe)
CS-DXQ   491311   ...   Untitled (Netjets Europe)
CS-TJF   495146   A321   TAP Air Portugal
CS-TNI   4951C9   A320   TAP Portugal
CS-TTA   495281   A319   TAP Portugal
CS-TTH   495288   A319   TAP Air Portugal
CS-TTI   495289   A319   TAP Air Portugal
CS-TTP   495290   A319   TAP Portugal
CS-TTQ   495291   A319   TAP Portugal
D-ABEA   3C48A1   B733   Lufthansa
D-ABIK   3C492B   B735   Lufthansa
D-ABTF   3C4A86   B744   Lufthansa
D-ABTL   3C4A8C   B744   Lufthansa
D-ABVT   3C4AD4   B744   Lufthansa
D-ABVW   3C4AD7   B744   Lufthansa
D-ACPF   3C4E06   CRJ7   Lufthansa Regional (Lufthansa CityLine)
D-ACRC   3C4E43   CRJ2   Eurowings
D-ACRH   3C4E48   CRJ2   Lufthansa Regional (Eurowings)
D-ACRQ   3C4E51   CRJ2   Lufthansa Regional (Eurowings)

[tarbat]

If you want everything about the session, then this query pulls everything from the Log:

SELECT DISTINCT
  Flights.Registration,
  Flights.ModeS,
  Aircraft.AircraftTypeSmall,
  Aircraft.AircraftTypeLong,
  Aircraft.Airline,
  Aircraft.ModeSCountry,
  Flights.Callsign,
  Flights.Route,
  Flights.StartTime,
  Flights.EndTime,
  Flights.MsgCount
FROM
 Flights
 INNER JOIN Aircraft ON (Flights.ModeS=Aircraft.ModeS)
WHERE
  (Flights.Session = 50)
ORDER BY
  Flights.Registration,
  Flights.ModeS

Export as HTML gives the following attachment.

[attachment deleted by admin]

[Allocator]

Tarbat,

I'm using the demo version of SQLite Code Factory, but some of the SQL queries take ages to run and some just don't seem to finish at all.  I just get the "Retreving Data" graphic and that's it.  The only advantage I can see with SQL Maestro is that there is a "Cancel" button to stop the query!

SQLite Database Browser is obviously much simpler and can't do fancy things like link tables, but it's very fast - as is the simple query filter in MyLog V1.4.  The limitation with the MyLog filter is that you can trap single items (with wildcards) but you can't get for example - traffic between date 1 and date 2.

I know that you've done this with SQL, but the query just seem to take so long.  I must be doing something wrong with SQL Code Factory, maye I'm looking for a query within a query, or running 2 at the same time?

Ah, the query to listed above has just resolved - after about 5 minutes!  52198 rows fetched, so maybe it's just the size of my database.

[tarbat]

I'm using the MyLog database you provided.  1.25MB, and it's got 7119 flights, 3332 aircraft, and 50 sessions.  Running the above query takes 1.55 seconds.

If I run the same query, but for all sessions (i.e. remove the WHERE section), then it takes 19.25 seconds and returns 3920 rows.

Alternatively, instead of selecting a single session, selecting by a date range takes 6 seconds - eg:

WHERE
  (Flights.EndTime < 20080215000000) AND
  (Flights.StartTime >= 20080214000000)


[attachment deleted by admin]

[AirNav Support]

1.4 as Tarbat has mentioned will allow you to create your own reports.

It probally a bit late regarding the report change. We haven't had many requests for it show different data.

[tarbat]

1.4 as Tarbat has mentioned will allow you to create your own reports.

Any chance of looking at my earlier comments about the database design.  Not for v1.4, but for a later version.

It would have been better, in my opinion, if the MyLog and NavData databases were combined into one database.  Then you could easily link flights in the MyLog to data in the NavData tables.

One other thing.  In my opinion, the fields in all the tables would be better defined as VarChar fields rather than just plain Text fields.  You can do a lot more with VarChar fields in SQLite tools (eg. SQLite Maestro) than you can with Text fields, such as apply filters interactively.

[lennox2604]

I have just joined and was looking foward to the new 1.4 and its new Mylog

I have seen the thread and am amazed that it would appear that Flight number is not included as statndard in Mylog
It is a bit like saying my name is John whereas the full name is John Smith not enough information supplied.
The very basic should be Time/date,Flight number, Registration, ModeS

If I have misread the initial report Apologies

So WHEN is D-day

[Allocator]

I have seen the thread and am amazed that it would appear that Flight number is not included as statndard in Mylog
It is a bit like saying my name is John whereas the full name is John Smith not enough information supplied.
The very basic should be Time/date,Flight number, Registration, ModeS

What we have to bear in mind here, is that we haven't actually told AirNav what we wanted with regard to MyLog and Report generation!

We said that we wanted the database rewritten in SQL and to make it easier to add and change data.  Whilst it is a good time to actually set out what we want, it might be better to get V1.4 as it is, see what it can do, then ask for additions.

For example, I have no particular interest in Flight ID, but to Lennox this is obviously very important.  I'm not to bothered about the time order listing, as I want to see if I've picked up particular registrations on certain days.  To take it further, I would also like to see altitude, squawk and position information avaialable in MyLog and Reports, but most users won't be interested in this.

So, this is a good time to tell Airnav what we might want, that is if we can all agree on some sort of common standard.

Of course, there is another way to access this data.  If the data out on port 7879 was formatted in a more useable way - csv with cr's for instance - then that would open up a whole new area for gathering information.

[b744]

This has been mentioned in PM to Airnav but while there's a thread here goes.
In addition to those extra fields above I'd like to see first and last altitude of a contact and also the callsign used. If a contact has been picked up with multiple callsigns then a new entry for each one as they represent a different flight.( ie short haul aircraft)The reason for asking for altitude info is if you come back to RB having been away for a while its nice to know if a certain aircraft that may be of interest has overflown at FL410 or just landed at your local airfield.
As has been said above, the option to pick what data is exported in my log format would be great and therefore the more data available, the happier  we'll all be as I'm sure we have different needs and wants.
rgds

[tarbat]

We said that we wanted the database rewritten in SQL and to make it easier to add and change data.  Whilst it is a good time to actually set out what we want, it might be better to get V1.4 as it is, see what it can do, then ask for additions.

Agreed.  Let's get v1.4, and then see how we'd like reporting facilities developed.  At least we can use SQLite tools that are available to create our own reports in the meantime.  After all, quite a bit of data is available in the MyLog database - here's the full list of fields:
 Sessions.MsgCount,
  Sessions.EndTime,
  Sessions.ID,
  Sessions.Location,
  Sessions.StartTime,
  Flights.Route,
  Flights.Callsign,
  Flights.EndTime,
  Flights.ModeS,
  Flights.MsgCount,
  Flights.Registration,
  Flights.Session,
  Flights.StartTime,
  Aircraft.LastTime,
  Aircraft.FirstTime,
  Aircraft.AircraftTypeLong,
  Aircraft.AircraftTypeSmall,
  Aircraft.Airline,
  Aircraft.ModeS,
  Aircraft.ModeSCountry,
  Aircraft.Registration

[lennox2604]

I know we all have different needs mine is registration and flight number

Sqlite can be interrogated to give the answers i want but it would have been nice to have been inbuilt or a set of options to export


Still let us see 1.4 when it comes and take it from there

Hopefully soon