I, for one, don't have the time to work my way through your entire note;
there's just too much involved to be able to spare that much time from more
urgent work.
May I suggest that you resolve the problem *indirectly* by paring the
situation down to the absolute smallest and simplest model you can that
doesn't betray the true nature of what you're doing, get that working, and
then transpose your solution to your real situation? I know it is not as
direct as solving your actual problem but if you prototype in the way I've
suggested, you've got a lot better chance of solving the problem on your own
*and* understanding it thoroughly.
Rhino
----- Original Message -----
From: "Chris Knipe" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, April 14, 2005 4:16 AM
Subject: very complicated queries (for me at least).
> 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'
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005
>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
