blankblank blank

Archive for the 'spreadsheet' Category

Crowdsourcing And Coordinating Data Collection With Handheld GPS Units

Got an email today from someone with a data collection/coordination/assembly problem. They have multiple people out in the field with Garmin GPS units recording data, and bringing it back to a central location for collation/combination when they’re done. Since Garmin GPS units have limited capabilities for data storage – coordinates, name, comment, date and time, elevation, and that’s pretty much it – additional data associated with a point has to be recorded by hand. Once back from the field, data has to be downloaded from individual units, associated with the additional attribute data from forms, all the data combined together into a single dataset, then converted into GIS-friendly format. And the process they had come up with wasn’t really working well for them.

I think this highlights some of the major limitations of classic stand-alone handheld GPS units. They’re really designed for us in personal data collection, not combined data collection; assembling data from multiple units can take a lot of work. Plus, their limited data collection capabilities require offloading data attribute acquisition to other formats (e.g. pencil and paper), adding the addition problem of associating that data with coordinates later on. There are lots of professional solutions for these problems, like Trimble or MobileMapper GPS units, Terrasync and ArcPad software, but these can be complicated and expensive. I challenge you to find anyone with enough patience to use Terrasync for a single day without swearing at least once; I know I can’t  ;-).

For a few years, I’ve had CyberTracker on my list of potential topics to post on. CyberTracker is a terrific data acquisition and collation tool for field data with tons of great features, like custom data acquisition form design for easy data entry, moving maps, easy data collation and conversion to GIS-friendly format. Plus, the software is free, and the hardware is (relatively) affordable. But unfortunately, I think time and technology are passing Cybertracker by:

  • While the central data software runs on Windows, the field data collection software runs on old-school Palm OS and Windows Mobile. Palm OS is dead; Windows Mobile development has stopped with version 6.5, and only maintenance updates are scheduled. So the useful lifetime of any data collection system built around CyberTracker is limited. If you need a fast/cheap/short-term solution, CyberTracker is free for non-profit use, and you could probably pick up the hardware very cheaply on eBay. Long-term, I don’t see it having much of a future in its current form.
  • The data collation model is also becoming obsolete. With CyberTracker, you have to bring all the data units to a single data download/collation computer. While there are still places in the world where this might make sense, an increasingly-connected world means that data download/collation to the cloud instead of an individual computer makes more sense.

I’m really excited about the possibilities for crowdsourced geographic data collection and collation using portable devices running Android OS (yeah, iOS too). There are already several interesting apps for doing this, and I hope to cover some of those soon on my AndroGeoid website. And I suspect that the small number of apps that can currently do this will be quickly joined by far more apps, and far more capable apps, in the very near future. But that doesn’t help with my emailer’s current problem. I suspect there are many different ways you could do this, but here’s the first approach that came to me using all-free software and services.

1. Make sure that everyone on the project has a Google account (i.e. Gmail); completely free.

2. Have the project leader created a single main data spreadsheet on Google Docs, with all the desired data attributes (e.g. point name, coordinates, comments, additional data fields from the paper forms, etc.) and share a link to that spreadsheet with other project members so that they can edit it as well.

3. Project members can download data from their Garmin units using DNRGarmin, and then export the data from DNRGarmin in CSV format.

4. Load the data into the spreadsheet program of your choice, and add/edit data from data forms to make it conform to the data structure of the main Google Docs spreadsheet.

5. Copy the data cells in the spreadsheet program, and paste them into the main Google Docs spreadsheet. Note: Use Ctrl-C and Ctrl-V to copy and paste cells into Google Docs instead of using the Google Docs Edit menu to perform those operations, as the latter doesn’t seem to work for pasting data from different applications into Google Docs. You no longer have a single computer as a choke point for data entry; multiple people can add data to a spreadsheet at the same time, and Google Docs will coordinate data entry so that nothing is lost. And if you save the spreadsheet data from steps 3 and 4 as separate files, you’ll have backup copies of the original data as well.

For those who aren’t comfortable with working with spreadsheets, Google Docs lets you set up a “Form” to let anyone add data to a Google Docs spreadsheet directly; however, this increases the chances of coordinate data entry error.

6. Now that you have all the data centralized in Google Docs, you have lots of flexibility in how you can handle it:

  • Export the data in CSV format, and you can then import it into any GIS program that supports CSV data. If your GIS program doesn’t, use MapWindow to convert your CSV file into shapefile format first.
  • Unlike shapefile attribute tables, where adding/removing/re-ordering attribute data columns can be a pain, you can easily perform those operations in Google Docs and then re-export the data in CSV format.
  • Convert the data directly into a continuously-updated KML network link for display in Google Earth or Maps using Google’s Spreadsheet Mapper tool.
  • Use any of Google Docs built-in tools to analyze/plot/sort/visualize data, including their way-cool Fusion Tables.
  • And I’m sure there are more options I haven’t thought of.

Know an alternate approach? Have any additional ideas?  I welcome your links and suggestions in the Comments section below.

Excel Add-On For Map And Data Visualization

Claude Henri-Meledo writes to tell me about BeGraphic Lite, a free data visualization tool for Microsoft Excel. I don’t currently have easy access to Microsoft Excel (I’m working in OpenOffice exclusively), so I can’t try it out, but it looks interesting. It apparently does a wide variation of data graphics, not just maps; these include:

cascade chart / waterfall chart, marimekko / mekko chart, sankey diagram, dynamic diagram, pivot diagram, powerpivot charts, pivot chart, ishikawa cause-effect diagram, BCG matrix, ADL matrix, McKinsey 7S, RB profiler, DuPont chart, choropleth map, swot analysis model, vector glyph, business chart, business graph, business graphics, professional excel chart, mini-graphs, micro-charts, sparkline for excel, excel gauges, strategic maps, strategy map, dashboard software, moving bubble, motion chart, trend analyzer, scada system, control room, make a custom map in Excel, thematic map in PowerPoint, dynamic sales territories, mapping tool, geographical information system in excel2010, choropleth map,  and any innovative charts…

A few sample map screenshots from the website:



BeGraphic LIte works with the free vector graphic map data available at, which now has 7500 maps available (up from 4000 when I posted about the site in August 2009). Edit: You need to download the files in WMF format. There’s a Pro version of BeGraphic in the works for later this year, but the Lite version will still be free and available.

Creating A Thematic Map Using Just Excel

A video/demo tutorial on creating a thematic map using just Excel, no GIS or extra programs required. I’d rather use a GIS, but if you’re really comfortable with Excel, it’s worth a look. Note: Any time the video demo stops, it probably means it’s waiting for you to click on something on the screen, usually highlighted in blue.


HT to Askan Schmeisser.

Excel Spreadsheet Data To Google Earth Thematic Map

After yesterday’s post on thematic mapping in Google Maps, John Stewart  of the University of North Carolina’s MEASURE program wrote to tell me about their own thematic mapping tool, the E2G Thematic Mapper. This Microsoft Excel macro spreadsheet data and links it with polygon data for countries and their administrative units to create thematic maps for Google Earth:

Excel data to Google Earth thematic map

Image is from the excellent PDF manual included with the distribution. I can’t try it out because I don’t currently have a computer with Excel (2003 or 2007), and it doesn’t seem to run on Open Office Calc.

It also currently only has polygon data for sixteen countries:

  • Botswana
  • Côte d’Ivoire
  • Democratic Republic of the Congo
  • Ethiopia
  • Guyana
  • Haiti
  • Kenya
  • Mozambique
  • Namibia
  • Nigeria
  • Rwanda
  • Tanzania
  • Uganda
  • Vietnam
  • South Africa
  • Zambia

But John says that an update coming this summer will add polygon data for more countries, along with more macro features.

If you’re interested in field data acquisition, the entire Monitoring and Evaluation Systems section of the MEASURE site is worth a look.

Modify A KML Polygon File With Excel Data

Remy Paternoster writes to announce a new tool he’s created, an Excel app called KMLxl that imports a KML polygon/placemark file created in Google Earth, and lets you both attach data to each polygon and scale the height of the polygon to a variable value:


The process is straightforward, involving creating polygons and associated placemarks in Google Earth, importing the KML file into Excel with the app, adding data, and then exporting the results as a new KML file. You can update the Excel spreadsheet at any time with new data, and create a new KML that reflects the newer data. In addition to the app, Remy has an easy-to-follow step-by-step tutorial of the process in an accompanying PDF file.

Online Spreadsheet To KML Converter

Bill Clark, the author of the useful Google Earth Public Land Survey System (PLSS) tool, has a new online app for converting spreadsheet data into point KML files, and opening them directly in Google Earth. Spreadsheet files can be in XLS (Office 2003 or earlier for now, Office 2007 support coming), CSV, or TXT (tab-delimited format); the minimum data required includes only latitude and longitude (either decimal or degree-minute-second formats are acceptable). Optional additional data accepted includes:

  • Name for each point
  • Description (text for pop-up balloon)
  • Icon (use the table on the web app page to select a number corresponding to the desired icon graphic)
  • Formatting tags (size and color of icon, altitude, connecting lines)

Make sure the column headings are included, exactly as specified on the web page’s instructions; there’s an option on the web page to review the spreadsheet data online to make sure it’s correct. The data is formatted into KML at the server, then sent back to your computer to be opened in Google Earth; the original data is then deleted at the server end for security. If you want to save the data displayed in Google Earth, make sure you right-click on the data item in the Places window on the left, and save it in either KML or KMZ format.

XLS2KML – Another Excel To KML Converter, With A Few Extras

Note: Original website seems to have disappeared; I’ve uploaded a copy of the spreadsheet here.

I’ve posted before on several programs and websites that will convert spreadsheet point coordinate data (CSV or XLS) to Google Earth’s KML format (here and here). XLS2KML is another option, but it does a few things the others don’t:

- You can have additional data fields automatically appended to the Description section in the KML file

- You can automatically put points into folders and subfolders

- Columns labeled with case-insensitive KML descriptors (like Altitude, Range, Tilt, Heading, Icon, Time Start and End (for Timespan), etc. will have their data properly encoded in the proper KML format for that descriptor

- There are additional options for drawing paths, polygons and grids from this Excel spreadsheet, but they’re not well-documented with recently-added documentation. Still, you’d probably be better off converting line and polygon shapefiles into KML for the first two, or using GE-Path to create paths, polygons and grids, rather than doing it point by point with this method.


Download the spreadsheet from this site (see the link at the bottom). Click on the “Important” button near the top of the spreadsheet to get what little “Help” information come with this Excel macro-enabled spreadsheet.

A KML To CSV (And CSV To KML) Converter For Spreadsheets And Garmin POI Files

Newer Garmin GPS units support the upload of sets of POIs, containing both position data and descriptions of the Points Of Interest. The KMLCSV Converter is a Java application that lets you convert placemarks created in Google Earth and saved as a KML file into a CSV (Comma-Separated Values) file compatible with Garmin’s POI Loader; this CSV would also be compatible with most spreadsheet programs as well. The process is straightforward:

1. Create placemarks in Google Earth using the “Add Placemark” tool, search, or other means.

2. Add a description to the placemark.

3. Drag all the placemarks you want to save into a single folder.

4. Save the folder as a KML file

5. Convert it to a CSV file with the KMLCSV Converter Program (screenshot from website)


6. Upload the CSV POI file to your Garmin with the POI Loader program, or into your spreadsheet as a standard CSV file.

There’s a short PDF tutorial file that covers these steps quite clearly with screenshots.

You can also convert CSV files, either POI files or spreadsheet files created by yourself or someone else, into KML files for viewing in Google Earth; a search for “Garmin POI” files on the web will bring up links to many sources, both free and paid. But I prefer the program csv2kml for that purpose.