Ok JLWest, then let's analyse what kind of information we've got so far (and I apologise in case it is perhaps obvious for you, but for good communication it is necessary)
What you get is the following:
opr Dept Arrv Plane EPOCH time Lat Lon
AAL KMKE KPHX B787 1592610243 33.4333 -111.999
No idea what "opr" is but, that is not important for us right now.
1) You have a call-sign (airport) for departure and arrival,
2) you have a lat/lon for the plane (wherever it might be located in the universe.. uhm.. I meant earth
).
3) to that lat/lon location of the plane, there is an associated epoch time.
What I need to do is determine the Local time at the departure airport and the local time at the arrival airport regardless of the position of the aircraft.
(*1)
I have a file of 45,000 airports with the lat/lon timezone offset.
That is an important piece of information on which things can be build.
Please correct me if wrong:
- For all 45,000 airports you have their respective latitude and longitude coordinates and additionally their timezone offset.
- I assume you are able to lookup this information, by using the callsign of the airport that you were given.
- That would mean you are able to determine lat/lon for departure as well as arrival location, complemented with their respective timezone offset.
That also means that you are able to approx. determine where the plane is situated (near departure or arrival location). But, we have to take into account that this means nothing, as a plain could f.e. fly its flight in the opposite direction of what you would expect (I have currently no idea how you would be able to detect that, as there seems a vital piece of information missing to be able to determine that. Feel free to express your thoughts on that).
In addition I have the FAA Aircraft designated table with all the airplanes of the world and there cruising speed.
As you already discovered with regards to your questions on the forum, you can calculate the approx duration of a flight by using that information, based on the lat/lon of the departure/arrival locations (again keep in mind flights flying in opposite direction is a possibility).
Assuming a flight always takes the shortest route (straight line) from departure to arrival location, you are then able to determine the direction of the flight (west to east, east to west, south to north, north to south and all in between).
Do take note that a plane flying a straight line is a myth, especially with countries/regions where there are airspace restrictions (and there are many of those, also in western peaceful countries).
Now, that poses a bit of a problem (at least for me) as I have no idea how you would determine, in case your data was taken when the plane was mid-air, when exactly the plane departured. It is simply imprecise without additional information. I expect you would comment on that...
(*1)
Have you taken notice of what winni tried to hammer into your ... uhm by lack of better wording, skull ?
1. convert plane epoch to UTC
2. use plane lat-lon to determine how long we've been flying since departure (lat/lon departure location, i.c.w. plane speed & taking into account other time-wasters during departure).
3. you subtract that time (2) from (1) to calculate UTC departure time.
4. you would then be able to calculate UTC arrival time
At this point you have all necessary data, only in UTC time and not in local time, for that we need the timezones (actually their offsets to UTC), and you would need to know if daylight saving time is active or not at the departure and arrival locations.
Local time = UTC time + timezone offset + DST (when active)
i.o.w. you use universal time as an absolute measurement, and after you're done calculating convert those universal times to their corresponding local times.
Does that make sense to you ?