AirNav Systems Forum

Is there any explanation why the end of the day Air Nav reports sometimes differ from what i see on the screen and what i hear on the radio?
Let's explain, yesterday's Kuwait Air Nav report shows :

43C04E  RRR6620                  ZZ175   BC-A  UK - Air Force          2009/02/15 16:29:28 

When i saw it on the live radar at that time it was showing RRR6636, and it was also on the radio like that.

I know SBS isn't always correct, i know data is maintained by volunteers, however i thought the Air Nav report was generated from the same source as where the live radar screen is coming from. Is that correct, or are they two different /non communicating systems??

Thanks for your comments.

Regards Pieter,

There is a bug which Airnav are aware of.  This means that the reporter function shows the first callsign recieved by your box that hasn't been deleted, even if this is many days before you ask for the report.  The only way currently around this is to delete old data from yesterday and before daily.  So that only callsigns from todays flights are left.  This will ensure that the oldest recorded callsign picked up by reporter is from the day that you are actually recording.

The BC-A type can be edited by using the database explorer.  In this case it should of course read C17.

One solution to this problem is to run your own SQL code to generate a correct daily log.  This lists ALL the Flight IDs used by an aircraft yesterday.  An example of my daily report at http://www.tarbat.gofreeserve.com/data.html

Uses the following SQL code:

SELECT DISTINCT 
  Aircraft.Registration AS "Reg",
  Flights.Callsign AS "Flight ID",
  Flights.Route AS "Route",
  Aircraft.AircraftTypeSmall AS "ICAO Type",
  Aircraft.Airline AS "Airline",
  Aircraft.AircraftTypeLong AS "Aircraft",
  substr(Flights.EndTime,1,16) AS "Flight Ended",
  Aircraft.ModeS AS "Mode S"
FROM
 Aircraft
 LEFT OUTER JOIN Flights ON (Aircraft.ModeS=Flights.ModeS)
WHERE
  ((date('now','-1 day') = substr(Aircraft.LastTime,1,4)||"-"||substr(Aircraft.LastTime,6,2)||"-"||substr(Aircraft.LastTime,9,2)) AND 
  (Flights.StartTime IS NULL)) OR 
  ((date('now','-1 day') = substr(Flights.EndTime,1,4)||"-"||substr(Flights.EndTime,6,2)||"-"||substr(Flights.EndTime,9,2)))
ORDER BY
  Aircraft.Registration,
  Flights.EndTime,
  Aircraft.ModeS

So, for example, yesterday Ryanair aircraft reg. EI-DCT flew as RYR19BK (EIDW-EVRA), RYR19ED (EVRA-EIDW), RYR856 (EIDW-ENTO), and RYR857 (ENTO-EIDW).

Quote from: ACW367 on February 17, 2009, 01:24:45 AM
The only way currently around this is to delete old data from yesterday and before daily.  So that only callsigns from todays flights are left. 

Could you explain how to manualy delete the old data?
Tarbat's solution sounds a bit complicated for me.

Pieter

AK01

On mylog, the 'delete old data' button is on the tools dropdown menu. This button allows you to delete data from the 'flights for selected aircraft' area for all flights, without deleting the full aircraft details.  If you are about to create a reporter for todays date 17 Feb.  What you need to do is enter the deletion date of 16/02/2009 23:59:59.   This will delete the flights for all aircraft from before todays date.  When you do the reporter you will then find the flight number reported is the first one from the day you asked for.

I've now setup a daily task on my computer to run a "correct" daily report, and output that to HTML.  If anyone else wants to try this, the attached ZIP file contains the following files:
1. sqlite3.exe - command line interface for SQLite databases, from http://www.sqlite.org/
2. sql.bat - a windows batch file to run the SQL
3. sql.txt - SQL statements to run the report

To use this, extract the zip file, and put the 3 files in your Radarbox folder (normally C:\Program FIles\Airnav Systems\AirNav RadarBox 2009

Then run the sql.bat file.  A file called report.htm will be created, that you can view in your internet browser.  You can also use Windows Task Scheduler to schedule sql.bat to run just after midnight each day.

Some explanation of the contents of each file:

1. sql.bat contains sqlite3 "Data\MyLog.db3" ".read sql.txt"
The first argument is the location of your MyLog database.  The second argument is a pointer to the file that contains your SQL statements.

2. sql.txt contains a series of statements to generate a daily report in HTML format.  A full explanation of all the statements that can be used is at http://www.sqlite.org/sqlite.html

Tarbat,

Many thanks for that, Great stuff. Now, if we could just have the squawk included...  :o)

Tom

Hi all, just been on the LHR webcam site, I don't know about a 5 minutes delay for networked data - the display is showing Feb 28 2009, 03:36 UTC, 5 HOURS in the future, the screen is continually showing BMA7PK landing on 27R and not updating.................!

Don't forget the site is still in beta. We are having problems with its ftp server so you may find some delays in the radar data.

Tarbat

Using yor SQL report bat.  Good stuff.  Can you advise how I could get the output filename based on the date.  That way I could have a history of reports?

Cheers

Graham

Hi AirNav, just been on the Heathrow site again, now showing 0308 UTC 1st March, so 8 hours ahead of real UTC!!

Please confirm it is Ok now.

Hi AirNav, yes, spot on now, complete with 5 minute delay!   7 hours and 55 minutes in advance had to be too good to be true!

Tks. It was an error with the computer running the screen shot application.

Just a quick note re my last post on this thread

I had some time and found a utility called Namedate that adds the date to the filename.  So just by adding a line in the SQL.BAT file to call this utility I'm renaming the file each time it is run.

Cheers

Graham

Tarbat,

I'd be interested in how you could tweak the SQL to reflect all flights of an aircraft on a particular day, even if that flight is still in progress.  As far as I can figure out, if a flight carries over to the new day, then it does not show in this report.

I've tried to tweak the SQL code, but I am not familiar enough with it to make it work the way I'd like to.

Thanks in advance,

Dave

Do I understand you to mean  started or ended on a particular day?
And ALL aircraft?

Exactly...something that would pull all aircraft active that day, regardless if the flight is still continuing.  The SQL above will pull all flights that have ended on a particular day, but if a flight starts at 2345 and continues past new day, then it isn't counted until the next day. 

I would love to be able to pull all aircraft active at my location for the day, regardless of when they took off or landed.

Quote from: hfradiopro on April 08, 2009, 01:19:29 PMThe SQL above will pull all flights that have ended on a particular day, but if a flight starts at 2345 and continues past new day, then it isn't counted until the next day.

The Flight Start Time can be unreliable if a flight gets interupted.  But this will do what you want:
SELECT DISTINCT
  Aircraft.Registration AS "Reg",
  Flights.Callsign AS "Flight ID",
  Flights.Route AS "Route",
  Aircraft.AircraftTypeSmall AS "ICAO Type",
  Aircraft.Airline AS "Airline",
  Aircraft.AircraftTypeLong AS "Aircraft",
  substr(Flights.EndTime,1,16) AS "Flight Ended",
  Aircraft.ModeS AS "Mode S"
FROM
Aircraft
LEFT OUTER JOIN Flights ON (Aircraft.ModeS=Flights.ModeS)
WHERE
  ((substr(Aircraft.LastTime,1,4)||"-"||substr(Aircraft.LastTime,6,2)||"-"||substr(Aircraft.LastTime,9,2) = date('now','-1 day')) AND
  (Flights.EndTime IS NULL)) OR
  (substr(Flights.EndTime,1,4)||"-"||substr(Flights.EndTime,6,2)||"-"||substr(Flights.EndTime,9,2) = date('now','-1 day')) OR
  (substr(Flights.StartTime,1,4)||"-"||substr(Flights.StartTime,6,2)||"-"||substr(Flights.StartTime,9,2) = date('now','-1 day'))
ORDER BY
  "Reg",
  "Mode S"

Or, if you're using my SQL bat file method:
.output data.html
.mode list
.header OFF
SELECT DISTINCT "<HTML><HEAD><Title>Radarbox Log - Yesterday</Title>" AS FIELD_1 FROM Aircraft;
SELECT DISTINCT "<STYLE type='text/css'> BODY { background: #000000; color: #FFFFFF; font-family: Arial; }</STYLE>" AS FIELD_1 FROM Aircraft;
SELECT DISTINCT "</HEAD><BODY>" AS FIELD_1 FROM Aircraft;
SELECT DISTINCT "<Table Border='1' Cellpadding='4' Cellspacing='1'>" AS FIELD_1 FROM Aircraft;
.mode html
.header ON
SELECT DISTINCT Aircraft.Registration AS "Reg",Flights.Callsign AS "Flight ID",Flights.Route AS "Route",Aircraft.AircraftTypeSmall AS "ICAO Type",Aircraft.Airline AS "Airline",Aircraft.AircraftTypeLong AS "Aircraft",substr(Flights.EndTime,1,16) AS "Flight Ended",Aircraft.ModeS AS "Mode S" FROM Aircraft LEFT OUTER JOIN Flights ON (Aircraft.ModeS=Flights.ModeS) WHERE ((date('now','-1 day') = substr(Aircraft.LastTime,1,4)||"-"||substr(Aircraft.LastTime,6,2)||"-"||substr(Aircraft.LastTime,9,2)) AND (Flights.StartTime IS NULL)) OR ((date('now','-1 day') = substr(Flights.EndTime,1,4)||"-"||substr(Flights.EndTime,6,2)||"-"||substr(Flights.EndTime,9,2))) OR ((date('now','-1 day') = substr(Flights.StartTime,1,4)||"-"||substr(Flights.StartTime,6,2)||"-"||substr(Flights.StartTime,9,2))) ORDER BY Aircraft.Registration,Flights.EndTime,Aircraft.ModeS;
.mode list
.header OFF
SELECT DISTINCT "</Table></BODY></HTML>" AS FIELD_1 FROM Aircraft;
.exit

Example report for YESTERDAY at http://www.tarbat.gofreeserve.com/data.html - you'll see 3 extra entries for flights started yesterday but ended today (8th April).

Thanks for the quick response, Tarbat....I look forward to trying it out this evening. 

Dave

A local person made this edited version with a 'sorting' feature on the html report page.

It uses 2 separate files for the CSS and script which the template then
links to (must be in same folder as the report output) on your server.

So go test it out!  I been using it the past few weeks and I like the sorting feature on the html reports.  Since you can sort it out by aircrafts, airlines, etc...

Maybe when Tarbat updates the 'RunSQL.zip' versions.  He can add this feature to the new releases.

Extra Link:
The source for the sorting came from this website.
http://www.cssjuice.com/16-sortable-table-techniques/

*Download the file below*

Quote from: tarbat on February 27, 2009, 06:38:43 PMThen run the sql.bat file.

Hi Tarbat,

I did do so. After running the BAT-file the DOS-screen apear shortly and that all. Nothing happens, no report.

John,
Make sure you are adding the files in the "AirNav RadarBox 2009" folder on your XP computer.  Then run it from that location.  Any other folder directory wont work without changing some code lines first.

Hi Brian,

Thank you. I did as Tarbat described. After restarting my PC all is working verry well so it looks.

"report.htm" was made!

Hi Tarbat!
I am using this script for my todays logs. I have now some questions, hopefully you can answer this:

- I want to export all logs ever registeterd. What must I write into the sql.txt? Now there stands for 2011:
WHERE
   substr(Flights.EndTime,1,4)="2011"
- I am only interested in this topics of the Database for export: Reg, Airline, Aircraft, End Altitude, Flight Ended.
How can I export this? My complete sql.txt is this one:

.output radarbox-log.htm
.mode list
.header OFF
SELECT DISTINCT "<html><head><title>Radarbox Report today</title><body><link rel=stylesheet href=stylesheet-table.css type=text/css \><script src=sorttable.js></script><table class=sortable>" AS FIELD_1 FROM Aircraft;
.mode html
.header ON
SELECT DISTINCT
   Aircraft.Registration AS "Reg",
  Flights.Callsign AS "Flight ID",
  Flights.Route AS "Route",
  Aircraft.AircraftTypeSmall AS "ICAO Type",
  Aircraft.Airline AS "Airline",
  Aircraft.AircraftTypeLong AS "Aircraft",
  Flights.StartAltitude AS "Start Altitude",
  Flights.EndAltitude AS "End Altitude",
  substr(Flights.EndTime,1,16) AS "Flight Ended"

FROM
Aircraft
LEFT OUTER JOIN Flights ON (Aircraft.ModeS=Flights.ModeS)
WHERE
   substr(Flights.EndTime,1,4)="2011"
ORDER BY
  Aircraft.Registration,
  Flights.EndTime,
  Aircraft.ModeS;
.mode list
.header OFF
SELECT DISTINCT "</table></body></html>" AS FIELD_1 FROM Aircraft;
.exit

- Is it possible to export these data (Reg, Airline, Aircraft, End Altitude, Flight Ended) direct to csv?

Could no One help me with my questions?

Thank you for your help.

Quote from: frogger on November 09, 2011, 09:18:20 AM- I want to export all logs ever registeterd.

Simply remove the WHERE statements:
WHERE
   substr(Flights.EndTime,1,4)="2011"

Or, in MyLog, use MENU - EXPORT TO CSV

Thank you Tarbat, now with deleting the WHERE-Satements, it exports more logs. But about a half of the logs were without the Flight Ended Date, the oldest Flight ended Date in my log was from the 8th November.
My Log in the radarbox-Software shows me older entries from september. This was not exported or without the Flight Ended-Data.
Why?

Wen I choose in MyLog Export to csv, it exports all mylog-data, but without the Flight end Time and the last altitude. Can I export this data also with MyLog or only with the SQL-Export above?

Quote from: frogger on November 13, 2011, 11:03:54 AMThank you Tarbat, now with deleting the WHERE-Satements, it exports more logs. But about a half of the logs were without the Flight Ended Date, the oldest Flight ended Date in my log was from the 8th November.

Okay, to get older flights you'll need to access the FlightsOld table as well.  You can do this using the VIEW called v_Flights, which is:
SELECT * FROM Flights UNION ALL SELECT * FROM FlightsOld

I would suggest using an SQLite database tool if you're wanting to do this level of data extraction.  I use SQLite Maestro, but others are available.

If you simply want to dump ALL the flights data from ALL time, then use this query:

SELECT DISTINCT
  v_Flights.Registration,
  v_Flights.EndTime,
  v_Flights.Callsign,
  v_Flights.Route,
  v_Flights.StartAltitude,
  v_Flights.EndAltitude,
  v_Flights.MsgCount,
  v_Flights.ModeS
FROM
v_Flights
ORDER BY
  v_Flights.Registration,
  v_Flights.EndTime,
  v_Flights.ModeS

Thank you again Tarbat, the query works perfect.
Now another question, hopefully you can answer it:
I have executed the query and I would like to have another table within the results: the table airline.
How looks the query within the data for the airline-name?

Quote from: frogger on November 13, 2011, 12:06:52 PMHow looks the query within the data for the airline-name?

SELECT DISTINCT
  v_Flights.Registration,
  v_Flights.EndTime,
  v_Flights.Callsign,
  v_Flights.Route,
  Aircraft.Airline,
  v_Flights.StartAltitude,
  v_Flights.EndAltitude,
  v_Flights.MsgCount,
  v_Flights.ModeS
FROM
v_Flights
LEFT OUTER JOIN Aircraft ON (v_Flights.ModeS=Aircraft.ModeS)
ORDER BY
  v_Flights.Registration,
  v_Flights.EndTime,
  v_Flights.ModeS

This works perfect. Thank you again!