List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:April 14 2005 8:16am
Subject:very complicated queries (for me at least).
View as plain text  
It's official.  I need help ;)

Let's do the table structures quickly.

mysql> DESCRIBE FlightData;
+---------------+------------------------------+------+-----+---------------------+----------------+
| Field         | Type                         | Null | Key | Default 
| Extra          |
+---------------+------------------------------+------+-----+---------------------+----------------+
| EntryID       | int(10) unsigned             |      | PRI | NULL 
| auto_increment |
| Network       | enum('I','V')                |      |     | V 
|                |
| TimeStamp     | timestamp                    | YES  |     | 0000-00-00 
00:00:00 |                |
| Tracker       | varchar(38)                  | YES  |     | NULL 
|                |
| PilotCallSign | varchar(20)                  |      | MUL | 
|                |
| PilotCID      | mediumint(6) unsigned        |      | MUL | 0 
|                |
| PilotRealName | varchar(50)                  |      |     | 
|                |
| CurHeading    | tinyint(3) unsigned zerofill |      |     | 000 
|                |
| CurAlt        | smallint(5) unsigned         |      |     | 0 
|                |
| CurGS         | smallint(4) unsigned         |      |     | 0 
|                |
| Plane         | varchar(20)                  |      |     | 
|                |
| Transponder   | smallint(4) unsigned         |      |     | 0 
|                |
| QNHHg         | varchar(5)                   |      |     | 
|                |
| QNHMb         | varchar(8)                   |      |     | 
|                |
| Enroute       | time                         |      |     | 00:00:00 
|                |
| Feul          | time                         |      |     | 00:00:00 
|                |
| AirportDep    | varchar(4)                   |      |     | 
|                |
| AirportDes    | varchar(4)                   |      |     | 
|                |
| AirportAlt    | varchar(4)                   |      |     | 
|                |
| PositionCur   | varchar(22)                  |      |     | 
|                |
| PositionDep   | varchar(22)                  |      |     | 
|                |
| PositionDes   | varchar(22)                  |      |     | 
|                |
| PLGS          | smallint(4) unsigned         |      |     | 0 
|                |
| PLAlt         | varchar(10)                  |      |     | 
|                |
| PLDepTime     | varchar(4)                   | YES  |     | 0000 
|                |
| PLFlightType  | enum('','I','S','V')         |      |     | 
|                |
| FlightPlan    | text                         |      |     | 
|                |
| Remarks       | varchar(150)                 |      |     | 
|                |
| ActDepTime    | time                         |      |     | 00:00:00 
|                |
| Logon         | time                         |      |     | 00:00:00 
|                |
+---------------+------------------------------+------+-----+---------------------+----------------+

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' 

Thread
very complicated queries (for me at least).Chris Knipe14 Apr
  • Re: very complicated queries (for me at least).Rhino14 Apr