Originally I wrote about Data and Cartography in 2017, that article was about making territory maps in Microsoft Excel with tracking data from the Spot Gen3 in Garmin BaseCamp and Microsoft MapPoint. This article extends that information by with using data from the Garmin InReach and using Microsoft Excel to create an video that has the a red line progress over the map such as Indiana Jones movies.
Step 1. Exporting data from Garmin InReach web site
It is pretty straight forward you select the date range you want with the built in filters (query tool) and select the message types.
Make sure you click the export button
Make sure you select GPX file format. To be honest I have not done a lot of work with KML data and have only tested this with GPX data.
Step 2. Import into Excel
Drag and drop the newly created GPX file directly into and open and black spreadsheet in Microsoft Excel ProPlus version
You will get the following pop ups.
Click yes
Click Yes
Click OK
Click Yes
Click OK
If that didn’t make sense you can watch this video of me doing it.
Step 3. Remove excess columns
There is a lot of data headers in the GPX file. You only really need 4 of the columns so you will want to delete around 100 columns – its rather simple as they are the ones without any data in them. Simply select (or bulk select) the columns with no data and click delete.
The following are the only columns you will actually use
- Latitude – named Lat52
- Longitude – named Lon53
- Elevation – named ns1:ele54
- Time – named ns1:time55
Step 4. Quick review and clean up
We wont worry about time – just yet. What we want to do here is just build a quick map to see if the data we put in works as expected and clean up any little oddities that might happen. This way if you made a mistake on the GPX file you can quickly verify it before going through all the animation steps. We will go over animations after this – but if you have the wrong data you want to find out sooner vs later.
Simply highlight the four columns of data and on the “Insert” ribbon select “3D Map” (not Maps)
Excel will do a pretty good job matching data types. You will want to map the latitude data to the latitude field, Longitude data to the longitude field and the elevation data to the height field.
In this example you will see it looks like there is a very sudden appearing mountain peak in the plains of Kansas. The reason for this is my Garmin InReach uploads location data every 10 minutes and Excel is doing a SUM calculation on this data. I must have been parked in a location for 20+ minutes and not turned off the device so it just kept sending the same exact location signal. If your data looks clean you can just skip to the next step.
If you click on the graphic that doesn’t look right it will give the information about that graphic segment. Since the height is a sum that would not be a valid number to search on but you can search on Latitude or Longitude – in this case I will use latitude 38.902135 therefore a search in excel for the number “2135” should find it.
Here you can see where the duplicate data exists in excel. Simply remove one of the rows of data to remove the duplicate
If you change any data in Excel such as removing a column, changing column names, or anything you need to click the “Refresh Data” button in order to refresh the map with the new data and not the cached data. This button is rather usefully as it fixes most things. if you are having any problems aligning or seeing fields always “refresh data”
You can see here that with the duplicate data removed the graphic now has a proper representation of altitude.
Step 5. Creating the animation with time
Now that the data looks right its time to clean up the “Time” field to get proper animation.
However even though it has the accurate time you will need to do some quick clean up so that excel can see “time” as “Time” and not as “Text”. If you look at the time time field you see there is actually Date and Time in the same field. it goes:
Year-Month-Day-T-Hour:Minute:SecondZ
I assume that “T” for time to delimit date from time.
I assume that “Z” is to indicate “Zulu Time” also known as “UTC” Coordinated Universal Time.
The important thing to take away from this is the “T” and the “Z” need to be removed and the cells reformatted.
Highlight the entire Time column, do a find replace by pressing “Ctrl”+”F”
In the “Find What” field put in “T”
in the “Replace with” field put in a space ” “
Then click “Replace all”
Highlight the entire Time column, do a find replace by pressing “Ctrl”+”F”
In the “Find What” field put in “Z”
in the “Replace with” leave it blank (no space)
Then click “Replace all”
Now that the time is cleaned up you need to format the cells
Select the entire Time column in this case “D” go ahead and “Right Click” the Column and select “Format cells…”
Then “Number” tab
In the “Category” column select “Time”
In the “Type” column select “3/14/12 13:30”
Then click “OK”
If those instructions don’t make sense here is a video of me formatting the 4 columns of data. modifying the label headers – removing text from fields – formatting column to data format.
Go back to 3D map
You already know how to make sure Longitude, Latitude and Height work you just need to ad Time from data to Time to in 3D map. if its not working – just click refresh data.
That its your done you now have an accurate representation of your trip in a 3D animation. However you might think that the animation is a bit “jerky” and you might want to smooth it out. Making the animation constant is in the next section.
Here is a video of the animation by date looking chunked up and not constantly smooth.
Step 6. Use constant time for duration
If the graphic doesn’t flow smoothly. The reason for this is because its using exact day and time. This makes it the animation look chunky. The reason is its using a 24 hour day format – the result being you ride for 10-12 hours and sleep for 8; so based on that time scale the animation does a lot of motion then nothing (because you were not moving) then a quick burst of animation and then another pause and so on.
A different approach that makes the animation very smooth and consistent is to drop the time format from the actual time to just a simple mathematic progression of time
Here I have added a new Column – you can do that or just over write the existing time column. To keep things super simple I started by entering the time as “12:01:00 AM”
Since this “smoother animation” does not use the date field you will need to format the cells to just be time and in this case it would be “1:30:55 PM” as the “Type”
Excel is very good at doing math and we are going to leverage that ability to make this very quick. The result we are looking for is to simply have the mathematical progression be 1 minute between data point. We started with E1 being 12:01:00 AM. so the progression would look like this.
Row Time
02 12:01:00 AM
03 12:02:00 AM
04 12:03:00 AM
However no one wants to a new time in each row so the idea is to use the Time Function built into Excel to do the work for us. starting on Row 3 type the following
=E2+Time(0,1,0)
What this is doing is saying “Take the row before me and get its time then add one minute to it. In the time function Time (Hours,Minutes,Seconds) by putting a 1 in the middle you are telling Excel to add a minute. You could use seconds as well.
Without a needing a date field (just using time) you have 1,440 minutes to play with so if you data set is under 1,440 rows minutes will work just fine.
If your data set is larger then use seconds Time(0,0,1) this will give your a data set of up to 86,400 rows. (24 hours * 60 minutes * 60 seconds = 86,400) If you have more then 86,400 rows of data you will need to add a date field – if anyone has that problem just email me.
You should see the Time function worked properly, and added a minute. All you need to do know, is grab the lower right hand corner of the cell and drag it to the bottom of you data and it will calculate the rest giving an increment of 1 minute per cell and making the animation very smooth.
Here is a video showing the final product and giving hints into other things you can easily change now that the data is working. You can very simply change the zoom level, orientation, background theme (such as state map or topographical), line color, line thickness, amount of height, line shape, etc. The idea here is I have gotten you through all the tough stuff and will leave the easy and fun stuff for you to play with. There is a “Create Video” button that will even allow you to add audio to the video. Hopefully this helps and hopefully you can have some fun making these animations.
TheMotoLizard
