AirNav Systems Forum

AirNav Radar => AirNav Radar Discussion => Topic started by: lennox2604 on October 18, 2009, 04:43:19 PM

Title: Hourly count of Aircraft
Post by: lennox2604 on October 18, 2009, 04:43:19 PM

Hi

Does anybody have a SQL script which will extract the hourly count of flights.
 I have tried to get one up and running but to no avail.

The Mylog only gives for last hour

I have a cunning plan to use the information but first i need to extract it

Any help or script welcome



 

Title: Re: Hourly count of Aircraft
Post by: Aerotower on October 18, 2009, 05:23:21 PM

See this attachment.

Title: Re: Hourly count of Aircraft
Post by: lennox2604 on October 18, 2009, 06:26:40 PM

Missed the point here

What it does NOT do is tell you the number 0000-0100; 0100-0200 thus
18/10/09  0000-0100  4
18/10/09  0100-0200  6

It is just needs extracted from the Mylog not just last 10 minutes and last hour

and i cannot get a SQL statement to do it

Title: Re: Hourly count of Aircraft
Post by: Aerotower on October 19, 2009, 01:17:19 AM

had not noticed it. It is not possible to change the time to see the records per hour.
Cumps

Title: Re: Hourly count of Aircraft
Post by: tarbat on October 19, 2009, 07:32:26 AM

SELECT
  substr(Flights.EndTime,1,13) AS Date_Hour,
  COUNT(Flights.ModeS) AS Count_of_Flights
FROM
 Flights
GROUP BY
  Date_Hour
ORDER BY
  Date_Hour

Gives something like this:

(http://farm3.static.flickr.com/2503/4025556532_804c8a1a04_o.jpg)(http://farm3.static.flickr.com/2577/4025561160_1f8c0c9445_o.jpg)

Or you could use the same SQL query on the v_Flights view to get earlier dates.

Title: Re: Hourly count of Aircraft
Post by: Terre on October 19, 2009, 08:33:58 PM

Tarbat, a fine script you've made there. I had the idea to handle it with a pivot table but failed as it seems that certain sql commands need to be 'rewritten' to work in sqlite. By far your script is more elegant - Salute!