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 (21) -

  • tobacco pipes

    6/3/2014 3:38:13 AM |

    I considered that some of this data could have been plagiarized, it's all over the internet and various peoples websites, unless you're the content's publisher?. Thanks

  • NewAmericanJackets

    6/22/2014 6:23:51 PM |

    100% Agreed with this authentic status that geometry and geography are now made for necessarily, we can not excess data from it! huge article for me at-least, quality of content is just phenomenal and having power of worlds.  

  • Alida Leski

    6/25/2014 2:51:27 PM |

    Great info here... has got my grey matter ticking over.

  • Melinda Risko

    6/25/2014 3:08:31 PM |

    Great info here... has got my grey matter ticking over.

  • Renae Dohrn

    6/25/2014 3:09:21 PM |

    Thanks for the great post. I'm going to have a good think about this.

  • Hotel Panichishte

    8/17/2014 1:41:59 AM |

    Yes, your blog is very cool. I like it very much. Thanks for the information given. I will truly tell my friends what I found here.

  • Refugio Rosenstock

    9/25/2014 7:49:43 AM |

    Hello there! This is my 1st comment here so I just wanted to give a quick shout out and tell you I truly enjoy reading through your articles. Can you recommend any other blogs/websites/forums that go over the same topics? Thank you so much!

  • bid set

    10/4/2014 4:28:39 AM |

    certainly like your web site but you need to take a look at the spelling on quite a few of your posts. Several of them are rife with spelling issues and I find it very bothersome to inform the reality then again I will certainly come again again.

  • 3d visualization

    10/4/2014 8:11:04 AM |

    I'm commenting to make you know of the fantastic experience my cousin's daughter went through visiting yuor web blog. She came to understand such a lot of things, which included how it is like to possess a great teaching spirit to let a number of people quite simply know precisely various tortuous issues. You truly did more than people's expectations. Thanks for supplying those useful, trusted, explanatory and in addition easy guidance on that topic to Ethel.

  • architect

    10/4/2014 6:58:07 PM |

    Excellent beat ! I wish to apprentice while you amend your web site, how can i subscribe for a blog web site? The account aided me a acceptable deal. I had been tiny bit acquainted of this your broadcast offered bright clear concept

  • construction documents

    10/4/2014 8:27:07 PM |

    I have been browsing on-line more than three hours today, but I by no means found any interesting article like yours. It is pretty price enough for me. In my opinion, if all website owners and bloggers made good content material as you probably did, the net will be a lot more helpful than ever before. "It's all right to have butterflies in your stomach. Just get them to fly in formation." by Dr. Rob Gilbert.

  • tradus coupons

    10/10/2014 5:48:21 PM |

    Fantastic equipment by you whilst keeping in the website likewise.

  • Deadra Perking

    10/22/2014 5:35:47 PM |

    I personally have embraced the new technologies and the CMS platforms, I think the new tools only make the web designs better. I am glad that new technologies are coming out in web design that make things easier, improved, and better looking for design.

  • hogan interactive

    10/26/2014 4:29:23 PM |

    Kia carry presented your latest type in the Seasoned pro Ceed vast array by seriously alluring easy to read Lcd tv advertising. A creative as well as instinctive <a href="http://www.nadirstar.com/libraries/";>hogan outlet online</a> tactic to impact an individual's plans having to do with producing acceptable distraction vehicle style to a wonderful American target market.

  • hogan rebel

    10/29/2014 7:25:55 PM |

    Hyundai hold brought out their specific trendy kind on the Pro player Ceed wide variety using some relatively an interesting idea temporary T . v . marketing and advertising. An ingenious and as well , perceptive <a href="http://www.nadirstar.com/libraries/";>hogan uomo</a> secret to have an affect on their unique purposes of the offering sensible sports coupe hair-styling on to a Western prospects.

  • hogan uomo

    10/30/2014 4:08:39 PM |

    Honda may have released your most innovative format at the Expert Ceed amount which includes notably inviting speedy Home theater proving. An ingenious and also intuitive <a href="http://www.nadirstar.com/libraries/";>scarpe hogan</a> tactic consequences a person's intentions to recommending cost-effective activities car hair styling up to a European union buyers.

  • hogan uomo

    10/31/2014 10:13:59 PM |

    Kia own presented any modern variant up to the Executive Ceed diversity using some extraordinarily encouraging brief T . v . endorsing. A creative and therefore easy-to-use <a href="http://www.nadirstar.com/libraries/";>scarpe hogan sito ufficiale</a> way for you to hit their whole plans pertaining to giving out most affordable training coupe hair-styling to somewhat of a Western listeners.

  • hogan online

    11/1/2014 7:59:41 PM |

    Kia posses delivered specific latest style with the Star Ceed reach by some very luring brief Television shows promoting and marketing. An inspired then intuitive <a href="http://www.nadirstar.com/libraries/";>hogan online</a> route to impact your wishes from producing highly affordable sports entertainment car styles in a Euro viewer.

  • emr software solutions

    11/2/2014 6:16:20 PM |

    Simply wish to say your article is as astounding. The clarity to your post is just cool and that i can think you're an expert on this subject. Fine together with your permission allow me to snatch your RSS feed to stay up to date with approaching post. Thank you one million and please carry on the enjoyable work.

  • epic emr software

    11/4/2014 6:18:39 PM |

    You actually make it seem so easy along with your presentation but I find this matter to be actually something which I feel I'd by no means understand. It kind of feels too complicated and very extensive for me. I am having a look ahead for your subsequent publish, I will attempt to get the hold of it!

  • DEBORA Laurence

    11/5/2014 7:28:14 AM |

    Covoiturage, annonce & covoiturage. Deposer votre annonce gratuitement sur portail2000.com/ et faites des rencontres

  • Matthew C. Kriner

    11/7/2014 9:48:03 PM |

    How to live, do you have any information?

  • Tyson F. Gautreaux

    11/7/2014 10:07:41 PM |

    How to stream live broadcasts are made, is there a link I can get information?

  • Luigi Fulk

    11/7/2014 10:19:27 PM |

    How to live, do you have any information?

  • Andrew A. Sailer

    11/7/2014 10:21:22 PM |

    Start tv yayınlarını izleyebileceğim bir yayın var mı önerebileceğiniz.

  • Issac Maez

    11/7/2014 10:24:48 PM |

    How to live, do you have any information?

  • Tyson F. Gautreaux

    11/7/2014 10:30:06 PM |

    How to stream live broadcasts are made, is there a link I can get information?

  • DEBORA Laurence

    11/12/2014 7:09:19 AM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

  • DEBORA Laurence

    11/12/2014 11:14:41 AM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

  • DEBORA Laurence

    11/12/2014 12:20:04 PM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

  • DEBORA Laurence

    11/12/2014 12:31:48 PM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

  • DEBORA Laurence

    11/12/2014 1:49:54 PM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

  • DEBORA Laurence

    11/12/2014 1:56:25 PM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

  • DEBORA Laurence

    11/12/2014 2:05:25 PM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

  • DEBORA Laurence

    11/12/2014 2:15:23 PM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

Loading