Improved Routing Information

[Chris11]

Need a username?

If you want sophistication like checking for duplicates, etc, etc then it is a bigger project. I was assuming it would be a data collection facility and that the manipulation and purification would be done in excel  or something like that

[DaveReid]

Programmers always shudder when they hear the words data and Excel in the same sentence  :-)

[tarbat]

Can I suggest TWO separate tables, one for takeoffs, and one for landings.  Each containing three fields:
CREATE TABLE TakeOff (
  ICAO      varchar(4),
  FlightID  varchar(8),
  DateTime  varchar(50)
);

CREATE TABLE Landing (
  ICAO      varchar(4),
  FlightID  varchar(8),
  DateTime  varchar(50)
);

Then, a simple query would allow you to match takeoffs and landings with the same FlightID:
SELECT
  TakeOff.FlightID,
  TakeOff.ICAO AS TakeOffAirport,
  Landing.ICAO AS LandingAirport,
  TakeOff.DateTime AS TakeOffTime,
  Landing.DateTime AS LandingTime
FROM
 TakeOff
 INNER JOIN Landing ON (TakeOff.FlightID=Landing.FlightID)
WHERE
  (TakeOff.DateTime < Landing.DateTime)

[DaveReid]

Surely that SQL would only return multi-leg flights which have a takeoff and landing at the same airport with the same FlightID ?

[tarbat]

Surely that SQL would only return multi-leg flights which have a takeoff and landing at the same airport with the same FlightID ?

Corrected and refined to check that take-off date/time is earlier than landing date/time.  Now, if someone could host the database, we could test this out.  Although it won't cope with multi-leg flights.

If anyone wants me to test this out, FOR TESTING ONLY, post you takeoffs and landings in the following format:

TAKEOFFS
"AAAA","FFFFFFFF","YYYY/MM/DD HH:MM:SS"
eg:
"EGLL","EZY153","2010/10/01 10:22:12"

LANDINGS
"AAAA","FFFFFFFF","YYYY/MM/DD HH:MM:SS"
eg:
"EGPE","EZY153","2010/10/01 11:54:35"

[DaveReid]

Corrected and refined to check that take-off date/time is earlier than landing date/time.  Now, if someone could host the database, we could test this out.  Although it won't cope with multi-leg flights.

Well my offer re hosting stands.

By the way, you don't need 2 separate tables, you can run a corresponding SQL query on a single table, regardless of whether rows have origin, or destination, or both, populated.

[tarbat]

By the way, you don't need 2 separate tables, you can run a corresponding SQL query on a single table, regardless of whether rows have origin, or destination, or both, populated.

Okay, I stand corrected.  I can't see how to do that in SQLiteMaestro, so I'll give up now and leave it to more expert people.

[DaveReid]

Programmers don't like the number 2 - in fact they only recognise three numbers:

0
1
lots

:-)

[bratters]

Breaking in for a moment  techies, I've just had 30 mins monitoring EGNX, working also in conjunction with the online EMA departure and arrivals board.

RYR 7VG landed - from Alicante according to board so should be OK to write route.

RYR17ML took off - for Bergamo according to board so should be OK to write route (Airnav has routing EGNX-LEGE - should be LIME)

TCX 91WC took off - for Teneriffe according to board - should be OK to write route.

Nothing sensational but it shows that in 30 minutes one man can crack 3 routes without much effort. So class, how many routes can 20 men working for a few days crack?

But just what do we do with the info?

[flybhx]

can I suggest holding fire on the project till the end of the month when the timetable change is complete

[DaveReid]

But just what do we do with the info?

Have a play with www.flightroutelookup.com/FlightRoute/FlightRouteUpdate.asp.

Feedback welcomed.

[tarbat]

By the way, you don't need 2 separate tables, you can run a corresponding SQL query on a single table, regardless of whether rows have origin, or destination, or both, populated.

Dave, can you give some pointers on how to achieve that - I can't see how.

In the meantime, my "2-table" database seems to work well, except for multi-leg flights.  I can input a list of departures from several airports into one table, and a list of arrivals from those airports into the other table, and the query automatically matches arrivals to departures and generates routes.  Just need some real data from other airports to test.

ALthough it also shows up flights that appear take-off and land at the same airport:

FlightID   T/O   LND   TakeOffTime            LandingTime
 CWL64   EGPE   EGPE   2010/09/29 09:58:29   2010/09/29 15:27:55
 CWL73   EGPE   EGPE   2010/09/29 10:07:00   2010/09/29 15:44:12
LOG779   EGPE   EGPE   2010/09/27 19:53:38   2010/09/29 16:47:40

[bratters]

But just what do we do with the info?

Have a play with www.flightroutelookup.com/FlightRoute/FlightRouteUpdate.asp.

Feedback welcomed.

Right Dave - 2 points - is it case sensitive and what is the date format? Looks dead simple for confirmed completed routes as opposed to half routes.

[AirNav Support]

DaveReid,

If your promoting your site again to help, then can you please confirm that you will let us hook into it for free as you do for other software?

If not please make that obvious to everyone. The last think we want is RadarBox users involved in something that they think will help there system when actually it won't at all.

[DaveReid]

Right Dave - 2 points - is it case sensitive and what is the date format? Looks dead simple for confirmed completed routes as opposed to half routes.

No, it's not case-sensitive (anything you enter in lower case is automatically uppercased before saving).

Date format is DD/MM/YYYY.

Not sure what you mean about only simple for completed routes - for a half route just leave the origin or destination blank, as appropriate.