Friday, August 23, 2013

Quickly mapping a spreadsheet with addresses

There are many ways that you can quickly take a spreadsheet of data with street addresses and turn that into an interactive map. Sadly several of them require you to pay for a special license (e.g. Esri Maps for Office) or require knowledge most people don't have. Here are a few quick ways to do it for free without a lot of work.

If you are in a hurry, have separate fields for street address, city, and state, and want the simplest solution, go to http://batchgeo.com/ and simply paste in your data, and hit map now (or validate and set options to get more control over how it looks and which fields to use for the map). You will get a link to share with others to view the map, and you will get a special edit link too. The map will look something like this (although you can use different colors based on values in a field):


What if you want to be able to make updates to a spreadsheet online (perhaps via a form) and have the map update automatically? Start by making sure that you have a single field in your spreadsheet with the complete address (I recommend calling this field Address or FullAddress). If you have separate columns for street address (e.g. "123 Main St"), city, state, zip, and country, that's fine but you will need a new column that concatenates (combines) them. Fortunately this is pretty easy to do in Excel or Google spreadsheets or other similar software. Let's assume you have street address in column A, city in column B, state in C, zip in D, and country in E. In column F (e.g. cell F2 if you have the column names in row 1), you would type in the following:
=A2&", "&B2&", "&C2&", "&D2&", "&E2
This formula will combine each of the five fields, and separate each one by a comma and a space to match standard address formats. You can then copy this cell, select the range of cells where you need this new field (e.g. if you have 50 rows, select them all to row 50) and paste it in, which will give you the new combined address for all of your data.


Once that's done, the rest is pretty easy. There are two ways to proceed depending on your preference

If you have a single "title" field and a single "description" field you want to share, your data can be shared publicly, and plan to host the map on your own web page (i.e. you can host an html file or copy / paste html into a template),
the simplest way is probably to upload your spreadsheet to Google Drive, use this batch geocode page to generate longitude and latitude for your data (x and y coordinates), and then use this spreadsheet mapper page to generate a very simple map. It might look something like this (in this case I don't have a real title or description, so I just show phone and address):