Geocoding Historic Homes with Google Fusion Tables

Using data available from Wikipedia concerning historic homes constructed near the turn of 19th and 20th century, I have created a map of structures in Knoxville, Tennessee designed by George A. Barber, an architect.

I pulled the data from <> as a simple “copy” and “paste” operation into Apache Open Office Calc spreadsheet.

I saved the spreadsheet as a .csv file, comma delimited.

I added a new column and duplicated the street addresses. I deleted the parantheticals surrounding the street address, along with the name of the property.

I deleted the street address and parenthetical in column 1 to retain the name of the property.

After saving the .csv file again, I opened up my personal Google Drive account.

I added the “Google Fusion Tables” application from Google, and then selected “create new fusion table” as instructed in Google’s tutorial.

After importing the data, I ran into some problems concerning the division of street, city, and state.  From “File > Geocode” my “street” was not recognized immediately as a location address.  After changing the “street” drop down in the “Rows 1” view to “location,” I was able to direct the application to geocode based on the street address.

At present time, this is a very basic map.

I do like the ease with which it obtained the lat/long coordinates, and how it transformed the table data into “cards” with the pertinent information in a “pop-up” on the map.

I’m also happy that it can export the resultant geocoded map as KML.

For future work, I think it would be interesting to link a Flickr or other photo management system to the Geocoder.

I also understand it is possible to add a Google Street view image of the particular property.

However, it is necessary to obtain the location information in the form of lat/long for this to work.

It is unfortunate that Fusion Tables do not append the lat – long information to the table.

There is software available which can provide this information.

From my course in the Geography Department, I’m aware of this software:

The application of interest is listed under “Google Geocoder.”

Geocoding with Google Earth is accomplished through two programs: KMLGeocode and KMLReport. The first program reads Excel Worksheets or an XML export of a table from a relational database system and creates a KML file that can be loaded into Google Earth. Once the KML file is loaded, Google Earth will attempt to geocode each entry in it.  After the file is geocoded, it can be saved to a new KML file. This file will contain the coordinates of each

address found. The second program, KMLReport, reads that file and generates two files: one for

geocoded addresses and one for addresses that were not found. The file for geocoded address is written as a comma delimited text file that can be loaded into ArcGIS.

At the moment it seems like obtaining a street view would require me to obtain the lat-long coordinates for the data, the append it to the Fusion Table.

Fusion has some advantages, including automatic publishing to the web, the ability to easily update table data, and for “collaborative data entry.” I can see some potential applications for my neighborhood organization, or any other collaborative group with limited access to mapping technology (especially a library system or other local municipality that does not have thousands of dollars to spend on ESRI software).

Posted on September 30, 2013, in Geographic Information Librarianship and tagged , , , , , , , . Bookmark the permalink. 2 Comments.

  1. Certianly an interesting exercise. We see potential (given some corrections to your data), for a more formal use as a H-1 collective Data Base. We would consider pursuing
    the possibilities.

    • Thanks for the interest. The final product is, of course, only as good as the data – and it is probable that even with good data, the geocoding process isn’t entirely accurate. For example, some streets no longer exist. In that case, it would be more appropriate to store geographic coordinates, which can be done with Google’s Spreadsheet Mapper. A key consideration for the kind of data base you envision is what data to collect. This data set is somewhat limited in scope as it focuses on George Barber homes. And yes, fusion tables probably could work as a “data base.” At some point I plan to learn how to set up the editable tables because, of course, vital data about each property changes over time. Are you aware of any sources of data? I suppose I could check the Knox Heritage Web Site. I am also interested in seeing what type of spatial data is available from the National Historic Register – although that will have to wait until after the Federal government re-opens. It would be nice to have an online thematic map of historic points of interest within Knoxville’s national historic register neighborhoods. My next experiments with Fusion tables will likely be an easy but interesting task: creating a thematic map of properties identified in the “Searching for Suttree” resource online at .

Leave a Reply

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

You are commenting using your 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: