U2U Blog

for developers and other creative minds

Loading GPX waypoints with Excel PowerQuery

Plotting data on maps is becoming popular: We can use the geometry and geography data type in SQL Server, use the Reporting Services maps, Excel PowerView allows mapping coordinates on a map, and the recently released PowerMap plug-in in Excel offers beautiful visualizations of map data.

image

But how can we easily get coordinates into these tools? Some tools connect with the Bing lookup service: you provide an address, Bing translates this into latitude-longitude coordinates. But sometimes we want to explicitly insert coordinates into the database. For instance, imagine you collecting location data with a smart phone or GPS device. Many of these devices store information in GPX or KML format. In this article, we discuss how we can load the waypoint coordinates from a GPX file into Excel. From there we can use it in Excel Power Map or Excel Power View reports, or we can use the SQL Server Import/Export wizard to import the Excel file into a SQL Server table, from which we can then use Reporting Services on this.

PowerQuery

PowerQuery is a free plugin for Excel 2010 and 2013. If you haven’t done so, download it from the Microsoft download site, and run the installer. Once installed, we can start loading different types of files into it, filter and manipulate the data, and in the end stored the result in either a regular Excel sheet, or in a PowerPivot model. Since GPX files are just XML files, we can load the file that way. GPX files can store coordinates in different ways. The two most popular are as a track (a bunch of points who make up a walk or route) or as waypoints: markers or points of interest. In the first example, we will load a local gpx file extracting the trackpoints. To save you all the trouble of repeating the transformation steps, I have included the M script. To rerun this:

  1. Start Excel
  2. In the PowerQuery ribbon select Get External Data –> From Other Sources –> Blank Query
  3. Go to the View ribbon, and select Advanced Editor
  4. Paste the query you see below into the query window, and replace the path of the gpx file with the actual path on your machine
  5. Click Done
  6. In the lower right corner, select the destination: If you plan on using PowerMap or PowerView on this data, select the Load to Data Model option
  7. In the Home ribbon select Apply and Close
let
Source = Xml.Tables(File.Contents("c:\MyGPXFile.gpx")),
#"Expand trk" = Table.ExpandTableColumn(Source, "trk", {"name", "desc", "number", "http://www.topografix.com/GPX/Private/TopoGrafix/0/1", "trkseg"}, {"trk.name", "trk.desc", "trk.number", "trk.http://www.topografix.com/GPX/Private/TopoGrafix/0/1", "trk.trkseg"}),
#"Expand trk.trkseg" = Table.ExpandTableColumn(#"Expand trk", "trk.trkseg", {"trkpt"}, {"trk.trkseg.trkpt"}),
#"Expand trk.trkseg.trkpt1" = Table.ExpandTableColumn(#"Expand trk.trkseg", "trk.trkseg.trkpt", {"Attribute:lat", "Attribute:lon"}, {"trk.trkseg.trkpt.Attribute:lat", "trk.trkseg.trkpt.Attribute:lon"}),
RemovedOtherColumns = Table.SelectColumns(#"Expand trk.trkseg.trkpt1",{"trk.trkseg.trkpt.Attribute:lat", "trk.trkseg.trkpt.Attribute:lon"}),
ChangedTypeWithLocale = Table.TransformColumnTypes(RemovedOtherColumns, {{"trk.trkseg.trkpt.Attribute:lat", type number}, {"trk.trkseg.trkpt.Attribute:lon", type number}}, "en-US")
in
ChangedTypeWithLocale
If you want to load the waypoints instead of a track, you can repeat the same steps, except using this script:
let
Source = Xml.Tables(Web.Contents("http://www.topografix.com/fells_loop.gpx")),
ChangedType = Table.TransformColumnTypes(Source,{{"time", type datetime}, {"Attribute:version", type number}, {"Attribute:creator", type text}}),
#"Expand wpt" = Table.ExpandTableColumn(ChangedType, "wpt", {"ele", "time", "Attribute:lat", "Attribute:lon"}, {"wpt.ele", "wpt.time", "wpt.Attribute:lat", "wpt.Attribute:lon"}),
RemovedOtherColumns = Table.SelectColumns(#"Expand wpt",{"wpt.ele", "wpt.time", "wpt.Attribute:lat", "wpt.Attribute:lon"}),
ChangedType1 = Table.TransformColumnTypes(RemovedOtherColumns,{{"wpt.time", type datetime}}),
ChangedTypeWithLocale = Table.TransformColumnTypes(ChangedType1, {{"wpt.ele", type number}, {"wpt.Attribute:lat", type number}, {"wpt.Attribute:lon", type number}}, "en-US")
in
ChangedTypeWithLocale
Notice that this last script loads the gpx file from the internet, replace Web.Content with File.Content if you want to load from a local file.

PowerMap

Once we have loaded the data in an Excel PowerPivot model, its easy to get it into a PowerMap. First, if you haven’t done so, download and install PowerMap. Open the Excel workbook in which you loaded the PowerQuery. We will use in the example the waypoints we loaded with the second example.

  1. Go to the Excel Insert ribbon, and click Map –> Launch Power Map
  2. The Power Map dialogue opens (you need to be connected to the internet, since it talks to Bing Maps) and prompts for the geographical information. Select the latitude and longitude fields, and specify explicitly which field is latitude and longitude:
    image
  3. Click Next. We now already see a map with the waypoints, but we can also plot extra information that is in the GPX file. For instance, we copied in this example also the time the waypoint was visited, and the elevation. So we can ask PowerMap to draw higher data bars for points with higher elevation, and we can put the time into the time axis:
    image
  4. Finally, by clicking the settings icon in the upper right corner we can make the data bars lower, less opaque and more narrow:
    image
  5. This leads now to this visualization:
    image
    You can use this as a starting point to learn and experiment more with Power Map. Or use the model we made earlier on in Power View reports (from the Excel Insert ribbon as well). 

I hope this gives you some inspiration to get started building your own visualizations on top of either GPS information you collect yourself, or information you find on internet or intranet. If you want to learn more on using these Excel data processing and reporting options (known as PowerBI), subscribe to one of our PowerBI classes.

 

 

Comments (10) -

  • backlinks 2015

    6/18/2015 10:07:55 PM |

    Wow! Just remarkable! Thanks so much for the information shared! I understand you have put a great deal of effort into this and also I intended to tell you exactly how thankful I am! There have to be a lot more blog sites like this online! I will definitely bookmark and also subscribe to your superior website! Hope you produced a lot more incredible things in the near future and also I will come back as well as review it! Keep up the great work!

  • high pr backlinks dofollow

    6/19/2015 5:57:19 AM |

    Informative! Intriguing! Quality! Prized possession! Famous! Fantastic! Incredible! Just all the wonderful words put on this content! If you maintain up the good job, thank you from the base of my heart and heart and I will certainly come check out once again!

  • Renaldo Malboeuf

    6/24/2015 9:42:19 AM |

    I want to install wordpress on one domain name but using that same wordpress have a select category come up under a different domain. Is there a way to do this?.

  • Cody Greider

    6/26/2015 12:28:58 AM |

    How does blogging/setting up a blog help a small to meduim size business grow?

  • buying high pr backlinks

    6/26/2015 2:31:21 AM |

    Outstanding! Merely all the nice words apply to this material! Thank you from the base of my heart and also soul and I will come see once more if you maintain up the excellent job!

  • google backlinks

    6/26/2015 3:52:04 AM |

    Not merely reputable, however also useful information. And also that is uncommon to come by these days! I have to say that I am really amazed and also will surely come back once again if you keep up the top quality and also worth of the content at this degree, or also obtain it on the next degree.

  • link building expert

    6/26/2015 3:56:54 AM |

    Incredible! Merely all the great words use to this material! Thank you from the bottom of my heart and also soul as well as I will come visit again if you maintain up the good work!

  • build high pr backlinks

    7/6/2015 4:41:25 AM |

    Simply outstanding! I recognize you have actually put a great deal of initiative right into this and I desired to inform you just how grateful I am! Hope you placed out even a lot more awesome stuff in the near future and I will come back and also read it!

  • google backlinks

    7/6/2015 9:06:15 AM |

    Wow! Merely awesome! Thank you so much for the details shared! I understand you have put a great deal of effort into this and also I would like to inform you just how grateful I am! There need to be much more blog sites such as this on the net! I will certainly subscribe and also bookmark to your impressive web site! Hope you produced much more remarkable things in the near future as well as I will return as well as read it! Maintain the great work!

  • Muriel Witham

    7/26/2015 5:22:46 AM |

    After Extensive research into online money making i came across a site that teaches you how to make money by simply filling some surveys, I have made over $2000 my first month just by simply filling out surveys! <A href="http://www.clkmg.com/gtx99/PaidSurveys";>Link</A>

Loading