AirNav RadarBox
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
 


« previous next »
Pages: 1 [2] 3

Author Topic: AirNav Reports vs Live Radar screen  (Read 26455 times)

0 Members and 2 Guests are viewing this topic.

hfradiopro

  • Guest
Re: AirNav Reports vs Live Radar screen
« Reply #15 on: April 08, 2009, 12:51:45 AM »
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
Logged

jgrloit

  • Sr. Member
  • ****
  • Posts: 271
Re: AirNav Reports vs Live Radar screen
« Reply #16 on: April 08, 2009, 12:50:22 PM »
Do I understand you to mean  started or ended on a particular day?
And ALL aircraft?
Logged
Based in Hexham - Tyne Valley 
Best view for RB is North of a line between EGNT and EGNC  - includes OTA and Spade, to EGPH above 7500ft.   Can be TRUE mobile with Mobile Broadband feed to Network.

hfradiopro

  • Guest
Re: AirNav Reports vs Live Radar screen
« Reply #17 on: April 08, 2009, 01:19:29 PM »
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.
Logged

tarbat

  • ShipTrax Beta Testers
  • Hero Member
  • *
  • Posts: 4219
    • Radarbox at Easter Ross
Re: AirNav Reports vs Live Radar screen
« Reply #18 on: April 08, 2009, 03:39:29 PM »
Quote from: hfradiopro on April 08, 2009, 01:19:29 PM
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.

The Flight Start Time can be unreliable if a flight gets interupted.  But this will do what you want:
Code: [Select]
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:
Code: [Select]
.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).
« Last Edit: April 08, 2009, 03:52:19 PM by tarbat »
Logged

hfradiopro

  • Guest
Re: AirNav Reports vs Live Radar screen
« Reply #19 on: April 08, 2009, 04:13:32 PM »
Thanks for the quick response, Tarbat....I look forward to trying it out this evening. 

Dave
Logged

Brian

  • Jr. Member
  • **
  • Posts: 74
Re: AirNav Reports vs Live Radar screen
« Reply #20 on: April 11, 2009, 11:06:37 PM »
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*
Logged

John Racars

  • Hero Member
  • *****
  • Posts: 801
Re: AirNav Reports vs Live Radar screen
« Reply #21 on: June 04, 2009, 03:44:58 PM »
Quote from: tarbat on February 27, 2009, 06:38:43 PM
Then 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.
Logged
Best Regards from the Netherlands, John Racars
13 NM East of EHAM
-
ANRB:
Version: 3D - 5.00.072
Antenna: outside WiMo GP-1090 (with ECOFLEX 10 cableconnection)
PC: Windows 7 SP 1 - 64 bit
-
RadarBox24 station: EHAM4

Brian

  • Jr. Member
  • **
  • Posts: 74
Re: AirNav Reports vs Live Radar screen
« Reply #22 on: June 04, 2009, 04:40:10 PM »
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.
Logged

John Racars

  • Hero Member
  • *****
  • Posts: 801
Re: AirNav Reports vs Live Radar screen
« Reply #23 on: June 04, 2009, 04:52:59 PM »
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!
Logged
Best Regards from the Netherlands, John Racars
13 NM East of EHAM
-
ANRB:
Version: 3D - 5.00.072
Antenna: outside WiMo GP-1090 (with ECOFLEX 10 cableconnection)
PC: Windows 7 SP 1 - 64 bit
-
RadarBox24 station: EHAM4

frogger

  • New Member
  • *
  • Posts: 17
Re: AirNav Reports vs Live Radar screen
« Reply #24 on: November 09, 2011, 09:18:20 AM »
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:

Code: [Select]
.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?
Logged

frogger

  • New Member
  • *
  • Posts: 17
Re: AirNav Reports vs Live Radar screen
« Reply #25 on: November 13, 2011, 07:55:05 AM »
Could no One help me with my questions?

Thank you for your help.
Logged

tarbat

  • ShipTrax Beta Testers
  • Hero Member
  • *
  • Posts: 4219
    • Radarbox at Easter Ross
Re: AirNav Reports vs Live Radar screen
« Reply #26 on: November 13, 2011, 08:19:51 AM »
Quote from: frogger on November 09, 2011, 09:18:20 AM
- I want to export all logs ever registeterd.

Simply remove the WHERE statements:
Code: [Select]
WHERE
   substr(Flights.EndTime,1,4)="2011"
Or, in MyLog, use MENU - EXPORT TO CSV
« Last Edit: November 13, 2011, 09:17:32 AM by tarbat »
Logged

frogger

  • New Member
  • *
  • Posts: 17
Re: AirNav Reports vs Live Radar screen
« Reply #27 on: November 13, 2011, 11:03:54 AM »
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?
Logged

tarbat

  • ShipTrax Beta Testers
  • Hero Member
  • *
  • Posts: 4219
    • Radarbox at Easter Ross
Re: AirNav Reports vs Live Radar screen
« Reply #28 on: November 13, 2011, 11:13:07 AM »
Quote from: frogger on November 13, 2011, 11:03:54 AM
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.

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:

Code: [Select]
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
« Last Edit: November 13, 2011, 11:21:54 AM by tarbat »
Logged

frogger

  • New Member
  • *
  • Posts: 17
Re: AirNav Reports vs Live Radar screen
« Reply #29 on: November 13, 2011, 12:06:52 PM »
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?
Logged
Pages: 1 [2] 3
« previous next »