Fusion Tables: Export Geocoded Coordinates

Warning!

Looks like google changed how they export the KML files making this guide irrelevant. In a post on the Fusion Tables group: “Yes, we made some updates to the KML export recently. If your data is an address, it will export to KML with thetag. If your data is lat-longs, it will export to KML with the tag. If you want this functionaly to still remain in Google Fusion Tables, voice your opinion on this thread on official group.”

Google Fusion Tables is a service by Google for managing and visualizing large data sets in the cloud. While working on a project involving large datasets of pharmacy and hospital locations I found Fusion Tables useful especially because it geocoded 60,000 addresses with no clear rate limitations and has a robust API and JSON support.
Unfortunately, Google doesn’t allow you to export the Latitude and Longitude coordinates from the geocoded datasets in CSV format only KML format.
This guide will allow you to convert Google Fusion Table KML to CSV format by using a custom XSL stylesheet. All the files including the examples shown are available on github here.

1. Prepare the data for Google Fusion Tables. Google likes the Addreses to be formated as “street city state zip”. My dataset had these values in separate columns so I used the CONCATENATE function in excel to combine the location information into one column.

2. Double click the little blue box on the lower left corner of the selected cell with the concatenate function to copy it down the entire column. Save your data as a .CSV file to import into Fusion Tables.

3. Import the data into Fusion Tables by clicking “New Table”  > “Import Table” then follow the on-screen instruction

4. To geocode the data click “Visualize” > “Map.” Pay attention to which column Google is using to geocode the dataset. Use “Edit” > “Modify Columns” to choose which columns contain location data.

5. Export the geocoded data by clicking “Export to KML”

6. Download the “kml2csv.xsl’ stylesheet in the same directory as the KML file

Download: KML2CSV.XSL from github

7. Now we have to do some minor editing to the KML file and apply a XSL stylesheet. Open the .KML file in your favorite text editor.
Find: <kml xmlns=”http://www.opengis.net/kml/2.2″>
Replace with: xml-stylesheet type="text/xsl" href="kml2csv.xsl" ?>

8. Delete: </kml>

9.  Save the file in .XML format

10.  Open the .XML file you just saved in Firefox (Google Chrome DID NOT work for me) to let it parse the file with the XSL stylesheet to convert the XML into a CSV format (this might take a while! with a 60 megabyte file my computer was unresponsive for 10-15 seconds, but it worked so have patience)

11. Copy and Paste the output into your favorite text editor (“File > Save as” in Firefox adds newlines and messes up the file)

12. Save the file as .CSV (in textedit I had to hit “Format > Make Plain Text” then save)

13. Now you should be able to import the .CSV into Excel complete with Latitude, Longitude, and Altitude coordinates

14 Comments

  1. Kyle April 3, 2011 Reply

    Thanks for the write up, using an XSL stylesheet is a great solution. Is it just me or has the lat/lon with and been replaced by address? In the past I was able to export the KML file and it was as you described, but recently all exports only have address.

    • Author
      mick_schroeder April 3, 2011 Reply

      I can confirm that I am still able to export coordinates in the KML file. Make sure the column with the combined address is set as “Type: Location” in the “Edit -> Modify Columns” menu and that the geocoding process has finished before exporting the KML file

      • Kyle April 4, 2011 Reply

        Thank you for your reply. I’ve tested it multiple times using multiple accounts with the same result. Here’s a sample exported file https://gist.github.com/902237 .

        • Suzi April 13, 2011 Reply

          I’m having the same problem. It’s as if fusion tables no longer exports coordinates…

          • Kyle April 15, 2011

            From Google: “We made some updates to the KML export recently. If your data is an address, it will export to KML with the address tag. If your data is lat-longs, it will export to KML with the coordinates tag.”

  2. nidele April 13, 2011 Reply

    thank for the how to
    but if i import 31000 address fusion table don’t see in the menu “map” available
    i have named the column in the same manner (address and title)
    can you help me?

    • Author
      mick_schroeder April 13, 2011 Reply

      You cannot display data on a map unless you have properly formatted column with geographic locations.

      Make sure the concatenated address column follows the format “street city state zip” like mine in the screenshot per this guide: (http://www.google.com/support/fusiontables/bin/answer.py?hl=en&answer=175922)

      The map visualization is greyed out probably because the system isn’t recognizing the address column as location data. After your ensure the data is formated properly, check on the data types of your columns (by choosing “Modify columns” in the Edit menu). You can instruct the system that a column has a particular data type.

      • nidele April 14, 2011 Reply

        yes I have the geocoding in fusion but when i use your howto i have same problem!. the xml doesn’t appear as your scheme and at the end of howto i obtain an xls file with the copy of xml file language.

        i think the problem could be that i have a different table with different column or the name of the column must be the same of your example

  3. nidele July 22, 2011 Reply

    unfortunaly i khow that this tutorial is old
    there are a thread here to puit a request to google
    http://code.google.com/p/fusion-tables/issues/detail?id=437

  4. Patrick November 22, 2011 Reply

    I have a dataset that only lists country names and city names that I ‘geocoded’ in fusion tables, they appropriately display on the fusion map, but I want an export with a x,y represented for each city. Basically, taking just the city and country name creating two new columns to display the general x,y of a city. Is this possible with this method?

  5. jaakl December 9, 2011 Reply

    You can still open the KML in Google Earth, save it there and get coordinates. Earth seems to use address tag to re-geocode data. So if you copy-paste placemarks/folder and save it in Google Earth, then it will have also coordinates. Drawback is that it it fails to geocode something, then you don’t have manual fixing possibility like in Fusion tables, you will see failures only later. Also you have to wait after opening KML in Google Earth for unknown time – when there is no added points to map anymore.

    • chris November 24, 2015 Reply

      Google Earth 7.1.5.1557 still works! Inconvenient – but it works.

  6. Icaro Brito February 16, 2012 Reply

    You can use Global Mapper to open the KML file and convert it to shapefile and a lot of other formats. Nice post!

    Regards from Brazil.

  7. Noah July 21, 2013 Reply

    Great post. Exactly what I needed.

    Will it work with just a city and site or do you need an address to get the long and lat coordinates?

Leave a reply

Your email address will not be published. Required fields are marked *

*