h1

How should I do this formatting?

3 January, 2011

GPS Visualizer has this great tool for converting a GPS file to GPX but I have an unorthodox need for it: to convert a comma-separated list of coordinates from a LineString element out of Google Earth.

Google Earth copies elements as KML so you can copy an element such as a path from Google Earth and paste it into a text editor. Locating the LineString element in the XML is relatively easy but it would be nice if there was an easy way to convert the single string into the proper CSV format needed to run it through the converter (shame Google Earth doesn’t write GPX natively).

This is what a LineString looks like (no line breaks):

172.2384849468256,-41.46954787285365,0 172.2432306652765,-41.46826502144933,0 172.2482385664333,-41.47388161005981,0

This is what I need to plug into the conversion tool (with line breaks):

name,desc,longitude,latitude
WP01,Waypoint,172.2384849468256,-41.46954787285365
WP02,Waypoint,172.2432306652765,-41.46826502144933
WP03,Waypoint,172.2482385664333,-41.47388161005981

I currently do this manually by:

  1. Copy LineString string of coordinates out of pasted KML and into a new text editor instance
  2. Search and replace ,0  with \n
  3. Copy and paste into Google Docs spreadsheet (which has default waypoint names and column headers)
  4. Copy whole spreadsheet back into text editor
  5. Search and replace \t with null

It’s a bit time consuming and putting me off from plotting routes in Google Earth, and is prone to error so some macro or something that can do this simple step automatically would be great.

Ideas?

Advertisements

4 comments

  1. Morning,

    The easiest thing to do would be to use Excel or Google Spreadsheet (depending on what features are in Google Spreadsheet, as I haven’t used that for anything more complicated than budgeting) and some concatenation.

    Step 1, open source as CSV, so each value is in its own column.

    A -> 172… B -> -41… C -> 0

    Step 2, in column D put in WP01 in the top cell.
    Put in WP02 in the 2nd cell down, and then do a fill down for column D, which will put in the WP03, 04 etc.

    Step 3, create the final output.

    In column E, put in the following:
    =D1 & “,Waypoint,” & A1 & “,” & B1

    Copy and paste that all the way down, and it will create each line of the final GPX.

    Then you can just select all of Column E and copy and paste it into Notepad++

    Not very magical, but once you have it setup, just paste in the new waypoints into Columns A and B.

    Cheers, Tim.


  2. As a sample, try this: http://dev.aggregatedsolutions.com/kmltowp/ Let me know what you think.


  3. Thanks everyone for your help and ideas.

    James Peek wrote a script which I then adapted to a GreaseMonkey script GPS Visualizer KML KPX converter so the data is transformed when pasted into the GPS Visualizer page.


  4. Why not just run the KML file through GPS Visualizer’s converter without picking it apart first?



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: