Thread: merge table
05-20-04, 15:23 #1Registered User
- Join Date
- May 2004
Unanswered: merge table
I am trying to merge data from a poorly designed table to make it better. There’s a table called “Track”. This track table is o track all flight coming with a lot of different sector/track but same ACID. I want to merge them into one and sort by start time with loop through all the distinct ACIDs.
o Loop through all flights that have the same acid, going in order from the flight that has the earliest first trackpoint, to the flight that has the latest first trackpoint.
For each flight pair, consider merging the flight if any of the following 5 conditions are true. If none are true, do not merge the flights:
1. The flights have the same origin and destination
2. The flights have the same origin, and at least one of the flights has an unknown destination
3. The flights have the same destination, and at least one of the flights has and unknown origin
4. The flights do not have conflicting departure or arrival airports, and the flights have the same beacon code.
5. The flights do not have conflicting departure or arrival airports, and both flights have trackpoints, and the first flight ends either after the second flight begins, or less than two minutes before the second flight begins. In other words (first_flight.end_time – second_flight.start_time) < 2 minutes.
• If the flights are a possible merge, check whether both flights have trackpoints.
1. If both flights have trackpoints, compare the start time of the first flight to the end time of the last flight. One of three cases will occur:
• If the first track ends 30 or more minutes before the second track begins, do not merge the flights.
• If the first track ends between 0 and 30 minutes before the second track begins, compute the distance in nautical miles between the last point of the first track and the first point of the second track. Also compute the time in hours between the points. If either of the following two conditions are true, merge the flight by tacking the trackpoints of the second flight on to the end of the first. If neither is true, do not merge the flights.
o The time gap is less than 15 seconds (.0041667 hrs) and the distance is less than 5 nautical miles.
o The time gap is 15 or more seconds, and the distance in nautical miles divided by the time in hours is less than 1200 nautical miles per hour.
• If the first track ends at the same time or after the second track begins, there is a time overlap between the flights. Follow the procedure for deciding whether to merge flights when there is a time overlap, which is given below.
2. If one or both of the flights does not have trackpoints, merge the flights
Track (track_index varchar2(22), acid varchar2(7), start_time date, dep_aprt varchar2(4), end_time date, arr_aprt varchar2(4), abc varchar2(5), acft_quip varchar2(1), acft_type varchar2(4), ads varchar2(5), trackpoints sdo_geometry, source varchar2(3), num_actf number, flight_index number(38), tcas_heavy varchar2(1))
How can I write the script to make the track table merge itself since this table is yearly partition? I need to merge the data by weekly… I am freakout and fried… all helps will be appreciate.
05-20-04, 15:36 #2Registered User
- Join Date
- Jul 2003
This can be overwhelming.
I find it easier (and less mind-blowing) to code in steps.
Since you listed your rules above, consider those your steps.
Just code to accomplish one at a time and add on the next rule after your code runs correctly for each step.
This way it does not seem overwhelming like when you look at the BIG picture.
If you only had 2 rules it would be easy right? Right!
So, start from there and add one at a time.- The_Duck
you can lead someone to something but they will never learn anything ...
05-20-04, 15:53 #3Registered User
- Join Date
- May 2004
I still dunno where to start since I am new in SQL. Any suggestion how? The rule is from the user gave me... to think and help him out... and he is so nicely to pass it to me when I am just joined in into the staff...