Posey's Tips & Tricks
Putting Excel 3D Maps To Work on the Family Farm
Can Microsoft's 3D mapping feature in Excel 2016 hold up in the real world?
It isn't exactly a secret that this has been a huge year for Microsoft product releases. Although I have been working hard to try to keep up with everything coming out of Redmond, I am only one person and there are only so many hours in a day. Hence, there are some new product features that I am just now getting a chance to try out. One such feature is the 3D mapping feature found in Excel 2016. Recently I had a chance to try this feature out with some real-world data. In doing so, I learned not just what is possible with Excel, but also with mapping data in general.
Before I tell you about my experiences, I need to fill you in on the back story. When I was a kid I spent weekends working on the family farm, which was about three hours from where I lived at the time. Even though I spent a good portion of my childhood on the farm, I had never even seen most of it because my chores were usually confined to one particular area.
Eventually, I grew up, got married, moved out of state and the rest, as they say, is history. Needless to say, I hadn't been to the farm in a very long time. So why am I telling you all of this? Well, through a somewhat strange turn of events, my brother recently inherited the family farm. Keep in mind that this is a huge piece of property that neither of us had ever even had the chance to explore. I knew that we needed to figure out what it was that he now owned so that he could figure out how to best use the property.
Thanks to a survey map I had a pretty good idea of where the boundaries were, but I didn't really know what fell within those boundaries. Because the farm isn't exactly in the middle of a densely populated area, applications such as Bing Maps and Google Earth provided relatively low resolution displays of the property.
My plan for exploring the farm was relatively simple. I outfitted an off-road vehicle with a couple of GPS loggers and a bunch of GoPro cameras. I also got my hands on a 3DR Solo drone so that I could get a bird's eye view of the place. The plan was to have my brother drive the vehicle around the perimeter of the property and then spend some time driving around the interior. All the while, the GPS loggers would be recording our position. I would be sitting in the back seat flying the drone, following the same route as the vehicle that I was riding in.
My plan worked great, except for two minor details. The first of these details was that by riding in the vehicle's back seat I got covered head to toe in mud. I made sure to thank my brother for that.
The other detail was that by the end of the day I had collected an overwhelming volume of data. Each GPS logger had acquired over 66,000 data points. The GoPro cameras collectively recorded about 80 GB of video data. I had to go home and put that data into some sort of meaningful format.
I will be the first to admit that analyzing the data has been a big job. My first idea was to import the GPS data from the GPS loggers into Bing Maps. However, Microsoft has temporarily removed the Bing Maps import function. Pulling the raw data into Google Earth wasn't an option either because the data was in an unsupported format.
To make a long story short, I imported the data into Microsoft Excel 2016. My original plan was to restructure the data and then create a CSV file that I could import into Google Earth. Doing so ended up working out really well. While I was using Excel, I decided to take the opportunity to experiment with the 3D maps feature.
For those who might not be familiar with the Excel 3D maps feature, it plots spreadsheet data on a map. I found that I was able to plot the vehicle's ground track pretty easily, as shown in Figure 1. Although not really relevant to my project, I found that I could also turn the map into a graph that showed my average speed at each location, as shown in Figure 2, or the amount of time spent at the various places where we had stopped throughout the day.
[Click on image for larger view.] Figure 1. I was able to plot the vehicle's track.
[Click on image for larger view.] Figure 2. This graph shows average speed by location.
Like I said, this type of view is irrelevant to this particular project, but it does demonstrate what Excel 2016 is capable of. Furthermore, plotting distance or time data onto the map in graph format proved to be an effortless process. I have no doubt that this technique will be put to good use next year. We have already discussed plans to use this same technique to chart crop yield and profitability by area. By doing so, we hope to be able to determine which portions of the farm are the most and least profitable so that my brother can make better business decisions for the following year.
So what about all of that video that I recorded? Well, I haven't had a chance to do much with it yet, but right now I am looking at a tool from an organization called Dashware that allows you to overlay GPS data onto video in the form of maps or gauges. By doing so, we will be able to tell exactly where each part of the video was filmed. I think that this will probably be especially useful for the video from the drone, which covered many more miles (using multiple batteries) than the off-road vehicle did.
In case you are wondering, I didn't have a GPS logger onboard the drone. However, the 3DR Solo runs Linux. The controller is also a Linux computer. As such, I was able to connect my Surface tablet to the drone after it was back on the ground and extract the flight logs. Using Excel 2016 and Google Earth, I was able to map not only the drone's flight path, but also its altitude, as shown in Figure 3.
[Click on image for larger view.] Figure 3. This is the flight path and altitude data for one of the drone's flights.
In my opinion, Excel's 3D Maps feature is a very welcome addition to Excel, but it does have some room for improvement. I wish that the 3D maps had an aerial view and that it had better map controls (such as a rotate feature). It could be that these types of features exist and I haven't found them yet, but an Internet search didn't turn up any results so I'm thinking that those features simply do not exist. Even so, I think that Excel's 3D mapping capabilities have enormous potential for the future.
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.