very complicated queries (for me at least).

Chris Knipe Thu, 14 Apr 2005 01:19:02 -0700

It's official.  I need help ;)

Let's do the table structures quickly.

mysql> DESCRIBE Airports;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| EntryID   | smallint(5) unsigned |      | PRI | NULL    | auto_increment |
| CountryID | smallint(5) unsigned |      | MUL | 0       |                |
| Name      | varchar(50)          |      | MUL |         |                |
| IATA      | char(3)              |      | MUL |         |                |
| ICAO      | varchar(4)           |      | MUL |         |                |
| Long      | varchar(50)          |      |     |         |                |
| Lat       | varchar(50)          |      |     |         |                |
| Alt       | int(11)              |      |     | 0       |                |
| City      | varchar(200)         |      |     |         |                |
+-----------+----------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)


Sample data.
mysql> SELECT * FROM FlightData LIMIT 1\G
*************************** 1. row ***************************
     EntryID: 3471
     Network: V
   TimeStamp: 2005-04-14 07:27:42
     Tracker: df0363ea-fe06-1027-b1dd-00e01884a424
PilotCallSign: AAL133
    PilotCID: xxxx
PilotRealName: xxxxx
  CurHeading: 124
      CurAlt: 34887
       CurGS: 469
       Plane: H/DC10/W
 Transponder: 7207
       QNHHg: 29.80
       QNHMb: 1009.14
     Enroute: 06:00:00
        Feul: 10:00:00
  AirportDep: KLAX
  AirportDes: MMMX
  AirportAlt:
 PositionCur: 32.06073/-114.70269
 PositionDep: 33.942536/-118.408075
 PositionDes: 19.435278/-099.072222
        PLGS: 480
       PLAlt: 35000
   PLDepTime: 640
PLFlightType: I
  FlightPlan: HOLTZ5 JLI ASUTA ALTAR PPE ZCL GABTU
     Remarks: SEL/CJ-AG  /V/
  ActDepTime: 06:40:00
       Logon: 06:32:49

mysql> SELECT * FROM Airports LIMIT 1\G
*************************** 1. row ***************************
 EntryID: 1
CountryID: 4
    Name: Cape Town Int.
    IATA: CPT
    ICAO: FACT
    Long: N34* 56.76'
     Lat: E69* 15.89'
     Alt: 592
    City: Cape Town
1 row in set (0.00 sec)

PLEASE PLEASE PLEASE note however on FlightData. Due to the data being captured every x ammounts of seconds, we need to work with the NEWEST RECORDS ONLY. A quick example of a data entry for a flight: mysql> SELECT TimeStamp, Tracker, CurHeading AS HDG, CurAlt AS ALT, CurGS AS KIAS, PositionCur AS POS, AirportDep AS Dept, AirportDes AS Dest, Plane FROM FlightData WHERE PilotCallSign='FFT546' GROUP BY PositionCur ORDER BY TimeStamp; +---------------------+--------------------------------------+-----+-------+------+------------------+------+------+---------------------+ | TimeStamp | Tracker | HDG | ALT | KIAS | POS | Dept | Dest | Plane | +---------------------+--------------------------------------+-----+-------+------+------------------+------+------+---------------------+ | 2005-04-14 07:27:47 | e1ea0d0d-fe06-1027-b1dd-00e01884a424 | 000 | 20370 | 377 | 51.9539/-177.535 | PADK | RJCJ | 1/A319/M-SDGIJKRW/C | | 2005-04-14 07:28:08 | e1ea0d0d-fe06-1027-b1dd-00e01884a424 | 000 | 21241 | 382 | 51.9629/-177.605 | PADK | RJCJ | 1/A319/M-SDGIJKRW/C | | 2005-04-14 07:30:17 | e1ea0d0d-fe06-1027-b1dd-00e01884a424 | 000 | 24675 | 403 | 52.0093/-177.966 | PADK | RJCJ | 1/A319/M-SDGIJKRW/C | | 2005-04-14 07:32:08 | e1ea0d0d-fe06-1027-b1dd-00e01884a424 | 000 | 26705 | 414 | 52.0486/-178.282 | PADK | RJCJ | 1/A319/M-SDGIJKRW/C | | 2005-04-14 07:34:09 | e1ea0d0d-fe06-1027-b1dd-00e01884a424 | 000 | 28432 | 424 | 52.0969/-178.684 | PADK | RJCJ | 1/A319/M-SDGIJKRW/C |

The above is not from the beginning (it is not a complete capture unfortunately). From the beginning (when the plane is switched off, standing at the gate), ALT will be within 50ft of Airport.Alt, and KIAS will be 0 (Plane is standing still)

Quick rundown of the tables...

FlightData keeps real-time track of flights across a certain airspace. Most of the data is irrelavent for what I need this. What *IS* relavent, will be TimeStamp (When last data was updated), Tracker (Unique flight identifier - UUID), PilotCallSign (Flight Number), CurAlt (Current Altitute), CurGS (Current Speed), Enroute (Flight Duration from DESTINATION), AirportDep (Departure Airport), AirportDes (Destination Airport), PLDepTime (Actual Departure Time - NOT the same as Enroute!!!).

Airports, is a simple database of airports in the airspace. What is relavent here, Name (Airport Name), IATA (3 Letter Name), ICAO (4 Letter Code), Alt (Airport Altitute).

All times are recorded in UCT (GMT), on all tables, and on all occurances.

Now, what I want to achive, is arrival / departure boards. I'll try and explain the departure board first, as this is BY FAR the most complicated. The arrival should be trival by modifying the clauses on the Departures query.

The departure board, should have a couple of fields of information it returns. Namely: Departure Time (FlightData.PLDepTime), Destination (FlightData.AirportDes), International / Domestic Destination (See notes), Boarding Status (See Notes)

Limits and what not. This should be relatively trival, but I obviously do not want to display Departures that already departed (this can be done via a time comparison). The same goes for Arrivals. There should also be a LIMIT on it to only return the x MOST soonest departures.

Notes for the departure board:

International / Domestic Destination. This is determined by comparing the first TWO letters of the 4 Letter ICAO code in the Airports Table. If the first two letters on FlightData.AirportDes is the same as the first two letters on the departing airport (specified on the WHERE clause), the destination is Domestic, otherwise, the Destination is a International flight.

Boarding Status: A couple of different states, determined by different data in the FlightData tables. 1) Boarding. FlightData.CurAlt is not more than 50ft for the value of Airports.Alt (Specified in the WHERE cluase) AND FlightData.CurGS = 0 (The plane is not moving). 2) Gate Closed. FlightData.CurAlt is not more than 50ft of the value of Airports.Alt, FlightData.CurGS is no more than 50 (The plane is Taxing) 3) Departed. FlightData.CurAlt is more than 200ft ABOVE the value of Airports.Alt (i.e at least 200ft in the air), FlightData.CurGS more than 160 4) Delayed. TIME ON THE DATABASE (in GMT) is more than FlightData.PLDepTime. All checks done on Boarding & Gate Closed also applies. 5) Cancelled. (AirportDes, PLDepTime) = NULL.

To put all this in laymans terms...

SELECT DepartureTime, Destination, FlightType, FlightStatus FROM FlightData JOIN / GROUP / IF / WHAT NOT.... WHERE Airports.ICAO='FACT' <- For Departures on Cape Town Int.

The arrivals are MUCH easier as I said, I will give it another go to do the arrivals myself after I've learned the tips and tricks on the Departure plates. I *SERIOUSLY* hope that someone can help me. This is not going to be easy...

What I have NO CLUE about, is how to get FlightType and FlightStatus to indicate the values I NEED based on what the data in the query is doing. I am GUESING this is going to be allot of JOINs, allot of GROUPs, and allot of IFs. I may be wrong though.


--
Chris.

I love deadlines. I especially love the whooshing sound they make as they fly by..." - Douglas Adams, 'Hitchhiker's Guide to the Galaxy'


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

very complicated queries (for me at least).

Reply via email to