FlightStats.com is a great resource for a demonstration of Microsoft GeoFlow. Yesterday's Airtran Flight 435 from CAK to MCO was a little delayed, but otherwise went fine. Using data from Flightstats.com and GeoFlow in Excel 2013, we take a look at the flight path and Altitude of the Journey.
UTC Time, Time At Departure, Time At Arrival, Time, Air speed, Altitude, Latitude and Longitude. Add a 'Category' column for the Under/Over 10K Feet segments to know when you can or cannot use your computer... Now create a Table, pull the Data to PowerPivot and get into actually flight path mapping in GeoFlow for the Visualization in Graphic style. Follow along with Bill as he shows us a few tricks in this demonstration of GeoFlow's capabilities in Episode #1674 - then apply your own ideas!
Today's Podcast is sponsored by "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Amazon.com: Excel 2013 In Depth (9780789748577): Jelen, Bill: Books
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
UTC Time, Time At Departure, Time At Arrival, Time, Air speed, Altitude, Latitude and Longitude. Add a 'Category' column for the Under/Over 10K Feet segments to know when you can or cannot use your computer... Now create a Table, pull the Data to PowerPivot and get into actually flight path mapping in GeoFlow for the Visualization in Graphic style. Follow along with Bill as he shows us a few tricks in this demonstration of GeoFlow's capabilities in Episode #1674 - then apply your own ideas!
Today's Podcast is sponsored by "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Amazon.com: Excel 2013 In Depth (9780789748577): Jelen, Bill: Books
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel episode 1676: Visualize Airline Flight with GeoFlow.
Well, hey, if you travel a lot there's a good chance you use flightstats.com and flightstats is great for tracking flights.
If you can find a flight that is currently en route and click on the flight number, they offer to show you your position on a map, right here but they also have this interesting link for “see position details,” which shows you the last 200 minutes of the flight if they've in fact been in flight for 200 minutes and at each point it shows you the latitude, the longitude, the altitude and also the airspeed.
So the flight that I frequently take is Akron-Canton to Orlando.
So actually while I was on that flight I used the AirTran Wi-Fi and started to download that data and then once I landed I was able to come back and get the complete set.
Now this is actually from a flight that I didn't take but it's the flight that I usually take.
So we have time, airspeed, altitude, be careful there the first position is always 40,000.
If they don't know what the position is they use 40,000, so you know those were still on the ground.
I then added “Category,” whether we're under 10,000 feet, can't use your computer, climbing, level flight and then descending and then back under 10,000 feet latitude and longitude.
So I said this would be an interesting thing to see in GeoFlow.
GeoFlow is the new Beta tool for Excel 2013, so I'm going to make this into a table, Ctrl+T and I don't have to rename it.
I am going to rename it though.
Just call it “flight data” and we will add this table to the data model.
So Power Pivot tab then Data Model.
I don't have to do anything else here so I go back to Excel and then on the Data tab go to 3D Map Explorer in 3D.
Now, what's nice is the data from flightstats has latitude and longitude so we just have to choose those and click “map it” and you see that they instantly draw in the flight path from Akron-Canton, right there down to Orlando but we can use some of GeoFlow’s tools to make this more interesting.
I'm going to add the category field to category which is going to change the colors so I can see at the ends there where it could not use the computer, climbing level flight and then descending.
They actually start descending in Georgia in order to land in Orlando.
And then let's add altitude as the height.
Okay, now we're going to use some of the fly tools here in GeoFlow.
That's interesting, we have one duplicate data point right there.
All right I'm going to hold down the Alt key and try and draw a big circle here and then I want to Ctrl + wheel mouse in and then Alt and drag up to tip.
All right, so now I can actually-- let me try the Alt trick again here to rotate a little bit more and Alt drag up to tip, there we go.
Alright so now I actually have a picture of this flight as it unfolded, alright and here let's use the time function.
I'll take the time field and drag that down to the time drop zone and we can now see the flight.
Let's go back to the beginning here.
Alright, let go and we actually see the flight as it unfolds.
Seems to be taking an average of a few different data points there.
Did you see that at the end they actually went South of the airport and then came back up.
Alright let's take a look at our time choices down here because this will really show the difference.
We choose Persist Time Data and play the animation you'll see that it keeps the past data.
Then let’s choose temporal accumulation, again this seems to be about the same.
I really don't understand the difference between those two at this point and hopefully before this comes out of beta they will explain that a bit better.
What if they give me a choice here to average this instead of sum it, that would allow that duplicate data point-- strange that flightstats is giving us a duplicate data point for that one and that actually makes everything taller because we don't have the really high average or the really high sum where there were two points at one minute.
So it's crazy they were landing from south to north.
Here's something interesting, so if we zoom in, I'm going to zoom in towards the Orlando Airport, you can actually get that picture of how they came down right over the west of downtown Orlando which is here and then landed from north to south and if we zoom in enough sure enough that last data point there is right on the airport.
I'll zoom out Ctrl + wheel Mouse up.
Wheel mouse down and we'll go up to the origin of the flight and zoom in and there again you can see the Akron-Canton Airport obviously they used that runway right there to take off and then started to turn to the south and it wasn't long one, two, three, four minutes into the flight we get the ding that it's okay to use portable electronic devices.
I just thought this was an interesting, interesting little view.
Sometimes you'll be on the airplane and say “oh I wonder what lake that is?” and you could actually with this, go back and see the exact path of where you flew the whole trip.
GeoFlow, brand-new product coming out from Microsoft for Excel 2013.
Currently in beta you can go search for GeoFlow if you have an Excel 2013 and check this out.
All right, well, hey I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel episode 1676: Visualize Airline Flight with GeoFlow.
Well, hey, if you travel a lot there's a good chance you use flightstats.com and flightstats is great for tracking flights.
If you can find a flight that is currently en route and click on the flight number, they offer to show you your position on a map, right here but they also have this interesting link for “see position details,” which shows you the last 200 minutes of the flight if they've in fact been in flight for 200 minutes and at each point it shows you the latitude, the longitude, the altitude and also the airspeed.
So the flight that I frequently take is Akron-Canton to Orlando.
So actually while I was on that flight I used the AirTran Wi-Fi and started to download that data and then once I landed I was able to come back and get the complete set.
Now this is actually from a flight that I didn't take but it's the flight that I usually take.
So we have time, airspeed, altitude, be careful there the first position is always 40,000.
If they don't know what the position is they use 40,000, so you know those were still on the ground.
I then added “Category,” whether we're under 10,000 feet, can't use your computer, climbing, level flight and then descending and then back under 10,000 feet latitude and longitude.
So I said this would be an interesting thing to see in GeoFlow.
GeoFlow is the new Beta tool for Excel 2013, so I'm going to make this into a table, Ctrl+T and I don't have to rename it.
I am going to rename it though.
Just call it “flight data” and we will add this table to the data model.
So Power Pivot tab then Data Model.
I don't have to do anything else here so I go back to Excel and then on the Data tab go to 3D Map Explorer in 3D.
Now, what's nice is the data from flightstats has latitude and longitude so we just have to choose those and click “map it” and you see that they instantly draw in the flight path from Akron-Canton, right there down to Orlando but we can use some of GeoFlow’s tools to make this more interesting.
I'm going to add the category field to category which is going to change the colors so I can see at the ends there where it could not use the computer, climbing level flight and then descending.
They actually start descending in Georgia in order to land in Orlando.
And then let's add altitude as the height.
Okay, now we're going to use some of the fly tools here in GeoFlow.
That's interesting, we have one duplicate data point right there.
All right I'm going to hold down the Alt key and try and draw a big circle here and then I want to Ctrl + wheel mouse in and then Alt and drag up to tip.
All right, so now I can actually-- let me try the Alt trick again here to rotate a little bit more and Alt drag up to tip, there we go.
Alright so now I actually have a picture of this flight as it unfolded, alright and here let's use the time function.
I'll take the time field and drag that down to the time drop zone and we can now see the flight.
Let's go back to the beginning here.
Alright, let go and we actually see the flight as it unfolds.
Seems to be taking an average of a few different data points there.
Did you see that at the end they actually went South of the airport and then came back up.
Alright let's take a look at our time choices down here because this will really show the difference.
We choose Persist Time Data and play the animation you'll see that it keeps the past data.
Then let’s choose temporal accumulation, again this seems to be about the same.
I really don't understand the difference between those two at this point and hopefully before this comes out of beta they will explain that a bit better.
What if they give me a choice here to average this instead of sum it, that would allow that duplicate data point-- strange that flightstats is giving us a duplicate data point for that one and that actually makes everything taller because we don't have the really high average or the really high sum where there were two points at one minute.
So it's crazy they were landing from south to north.
Here's something interesting, so if we zoom in, I'm going to zoom in towards the Orlando Airport, you can actually get that picture of how they came down right over the west of downtown Orlando which is here and then landed from north to south and if we zoom in enough sure enough that last data point there is right on the airport.
I'll zoom out Ctrl + wheel Mouse up.
Wheel mouse down and we'll go up to the origin of the flight and zoom in and there again you can see the Akron-Canton Airport obviously they used that runway right there to take off and then started to turn to the south and it wasn't long one, two, three, four minutes into the flight we get the ding that it's okay to use portable electronic devices.
I just thought this was an interesting, interesting little view.
Sometimes you'll be on the airplane and say “oh I wonder what lake that is?” and you could actually with this, go back and see the exact path of where you flew the whole trip.
GeoFlow, brand-new product coming out from Microsoft for Excel 2013.
Currently in beta you can go search for GeoFlow if you have an Excel 2013 and check this out.
All right, well, hey I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
