i-0271045dc3ed46deb18eea5c62ca51f9-dottplott.png

As an archaeologist I often need to plot coordinates on maps and plans. At every scale, really: from individual finds on the plan of an excavation trench to the distribution of something across Europe. Just dots of varying shapes and colours on various background maps. Most often, it’s GPS data from field walking and metal detecting. My colleagues in contract archaeology and academe use ArcInfo for these things, but I’ve never had incentive or opportunity to learn to use it. Also, once you know the software, you still need a map to plot stuff on, and those are expensive. So I’ve been wondering if I could somehow plot my coordinate data via Google Docs in Google Maps. Free software, free maps, free updated aerial photographs.

Turns out, you can. And today I figured out how. I believe it was David Petts who nudged me in the direction of Google’s “Fusion Tables”. And Hans Persson (who is an inveterate geocacher) asked me to write my findings up on Aard.

1. Data formatting
Convert your coordinate data to decimal lat & long after the WGS84 datum and with a decimal dot, not the Swedish decimal comma. For instance, my house is at lat 59.289576 long 18.258234. Call the northings column “Latitude” and the eastings column “Longitude”. (There are Excel macros to do coordinate conversions. For the Swedish systems, I find Robert Larsson’s on-line conversion utility handy, though it doesn’t do batch jobs.)

You may also want to add a “Text” column to describe what each point marks, and an “Icon” column that takes entries like “small_red” and “large_blue”.

(The Map function is pretty smart and also happily works with street addresses or place names if you put them in a “Location” column.)

2. Where to put the data
Stick this data into a spreadsheet in Google Docs. Save and close the spreadsheet.

3. Plot your dots
Now click the Create button on the start page of Docs and select “Table (beta)”. Tell the software to grab the data from the Docs spreadsheet you just created. (At this stage you can also tell it to disregard any extraneous data columns.) I don’t quite know how to conceptualise the distinction between these tables and standard Docs spreadsheets. But for practical purposes, tables are useful because (unlike spreadsheets) they have a Visualize menu including a Map alternative. Use it and zoom in on your area of interest.

4. Colour your dots
At first, all of your dots will be small and red. To get the software to use the data you entered into the “Icon” column, (such as “large_blue”), click “Configure styles”, change the “Marker icon” settings to “Column”, and select “Icon”.

Tell me how you’re doing with this, Dear Reader, and I’ll update the entry as I learn more. The first thing I want to find out now is how to create a dynamic link between my spreadsheet and the map, so that any changes to the data appear automatically on my maps. At the moment I have to make a new table every time I change the spreadsheet. Also, the only way I currently know of to get maps out of the software is screen grab, which doesn’t make for great resolution.

Comments

  1. #1 Hans Persson
    December 14, 2011

    When I try this, I get “Import failed. Unable to complete import” both when I try to populate my table from a Google spreadsheet as in your instructions and when I try to populate it from a csv file on my computer (downloaded from Google Docs).

    And now, before posting this, I tried again just to be sure that I remembered the error message right, and now it works (at least via the Google spreadsheet). Perhaps you need to wait a few seconds for something to sync before Tables can read it?

    Now it works. I think my test run also solves a mystery cache for me. ;)

  2. #2 Simon Winter
    December 14, 2011

    You could embed the maps, although the embed code is not easy to find. And I think the maps update to reflect changes, but it might take a while. You have looked at what The Guardian did, with “deprivation” as their example domain? http://www.guardian.co.uk/news/datablog/2011/mar/31/deprivation-map-indices-multiple

  3. #3 Birger Johansson
    December 15, 2011

    Good luck! Anything that makes the stuff more user-friendly…Us middle-aged folks do not have the energy to spend days working out the procedures.

    Do you have the option to plot the depth of the finds, too?
    — — — —
    (OT) New model suggests early humans lost fur after developing bipedalism http://www.physorg.com/news/2011-12-early-humans-lost-fur-bipedalism.html
    (-if we indulge in binge drinking, will we eventually regress to a furry state? :-)

  4. #4 Anders Nilsson
    December 15, 2011

    I have tried to use googlemaps but I have found the maps to be too low res for my uses, plus the resolution limits on gps devices is so bad I end up drawing my own maps. I tried to use it for marking spots where I take soil samples in polluted areas.

  5. #5 Birger Johansson
    December 15, 2011

    Martin have you seen what they have “found” at Ararat (falls over laughing)?
    “Noah’s Ark Found. Again.” http://freethoughtblogs.com/dispatches/2011/12/13/noahs-ark-found-again/
    I love the comments at the link. BTW what is the map symbol for “biblical Ark” ? And shouldn’t we find the skeletons from the dinosaurs that perished when they fell from the ramp leading from the ark?

  6. #6 Joel Westerberg
    December 15, 2011

    Martin, in what format do you have the original data coordinates?

    I’d advice you of going the way of trying to convert the spreadsheet to .kml files. Then you can load the data into Google Earth, it’s also very easy to add to a google maps map on a site.

    Here’s pretty nice site with some tools: http://www.earthpoint.us/ExcelToKml.aspx

    You can also look into the openstreetmap project, it’s an open-source initiative for building maps wiki style. They have some good stuff for creating overlays, and because it’s not encumbered with immaterial rights like the google maps, it’s much easier to print hi-res.

    http://en.wikipedia.org/wiki/OpenStreetMap#Software

    http://wiki.openstreetmap.org/wiki/OpenLayers_Simple_Example

    The nice thing with openstreetmap is that you can easily fix errors in the map if you find any, and you can add an insane amount of detail to the area that you want to investigate if you’d like.

    You can also not only make points, but shapes, which is pretty nice for mapping out houses and stuff.

    If you like we could hook up for a coffee or something some day and I could help you hack together some sort of convertion utility if you need to convert massive amounts of SWEREF 99 to some coordinate system that makes sense for the rest of the world.

    I’ve brought in a couple of gps logs into google earth from when I have been out running, it’s pretty swift, and it’s the way most work on openstreetmap is done, People walk around to get the trace of roads, forests fields etc and then import the data into openstreetmap.

  7. #7 Jakob
    December 15, 2011

    When I record my detector finds here in Denmark I put the information in a spreadsheet (format kindly provided by Tobias Bondesson) and then use http://www.gpsvisualizer.com/ to convert it to a KML-file I can open in Google Earth. Much like Joel explained above.

    Btw, some cool presentations of archaelogical material can be made as KML-files for Google Earth. Prime example this one about Stonehenge: http://blogs.bournemouth.ac.uk/seeing-beneath-stonehenge/ . I could think of a few Scandinavian locations that could be presented that way, maybe even cooler.

  8. #8 Tobias
    December 16, 2011

    As Jakob points out, GPS-visualizer is, very good for plotting detector (and other) finds. It is also seems to support more column headings, and to be a bit more flexible, than Earthpoint. The possibility to put the plotted points in a folder structure within the kmz-file is extremely useful since you can then activate/deactivate an entire subset of plots with just one click (my standard folder structure is Location\Time period\Type of find). Unfortunately, only three levels are possible in the folder structure.

  9. #9 Birger Johansson
    December 16, 2011

    As a complete ignoramus about IT gadgets, I wonder if there is any risk of data being lost. Backup how often? Hardware vulnerability to virus?
    The neolithic (COBOL and earlier) paper records may be dusty but they are sturdy.
    — — — — — — — — — —

    (OT) Original offering found at Teotihuacan pyramid http://www.physorg.com/news/2011-12-teotihuacan-pyramid.html

  10. #10 Martin R
    December 17, 2011

    Birger: “Do you have the option to plot the depth of the finds, too?”

    If I had any data, I could colour-code by depth. But we never take any depth data for metal-detector finds as they are in thoroughly churned topsoil. And field-walking finds are of course at depth 0.

  11. #11 Martin R
    December 17, 2011

    Joel, Jacob, thanks for your suggestions! I’ll get back to you with questions.

    Joel: “in what format do you have the original data coordinates?”

    RT90, the recently replaced Swedish national grid.

  12. #12 CD
    December 19, 2011

    It didn’t work for me the first time. Here I go for a second try. Hope it works.

  13. #13 Diego
    July 14, 2012

    Thanks, works like a charm

  14. #14 Bertrand Muller
    Montpellier
    October 24, 2012

    easy and simple
    thanks for the trick

The site is undergoing maintenance presently. Commenting has been disabled. Please check back later!