How to create your own online map: BatchGeocode for spreadsheets

[flickr]photo:6804985083[/flickr]

Maps can be digital, too! The Streets for Cycling Plan 2020 process disappointingly hasn’t included online map crowdsourcing as a strategy to gather input from residents and to easily collect data digitally so that it could be more quickly collated, analyzed, and shared. Photo by Serge Lubomudrov. 

We constantly use maps on Grid Chicago, displaying photos of them, or embedding and linking to them. Here’re all the articles with embedded maps. This is the second of four tutorials on how to create your own online maps.

We use maps as a communication tool and a way to enhance our articles. I’m going to give you some basic knowledge to create your own map using online tools so that you can identify issues and solutions where you live, which you can easily share afterwards. I’ll describe four simple ways to create a map. Before that, though, I’ll describe how to choose one. Since there are four tutorials, I’m going to break them up into four articles (vote in the comments for the next tutorial I should write).

  1. Google Maps – Use this if you want to click on a map to create markers for places, or draw boundaries, paths, or roads. This is by far the easiest way to create a map to share with others, but is not a good choice if you have a table of data. This was the first tutorial.
  2. BatchGeocode – Use this if you have a spreadsheet or table of data that includes addresses for places.
  3. GeoCommons – Use this if you have a spreadsheet or table of data that includes addresses for places AND you want to add other geographic elements, like ward boundaries or bike lanes.
  4. Fusion Tables – Use this if you have a spreadsheet or table of data that you may want to join with other tabular data, or you want to run rudimentary math functions (like a spreadsheet) and then display the results on a map. You need to have geographic coordinates, a KML file, or rows with full addresses in them. It can import spreadsheets from Google Docs.

BatchGeocode

This is the fastest way to turn a spreadsheet or table of addresses into a map that you can share with people on a BatchGeocode-hosted webpage, or embed into your own webpage.

Your table must be set up like this: data should be in rows, and attributes should be in columns; put the attribute names for the columns in the first row (this is called the header row). You can have as many columns as you want, but you must have these three: ADDRESS, CITY, STATE (country is optional and if it’s missing, you can specify it in the process). Here’s an example table of bike shops in DuPage County from the Chicago Bike Shop Database:

Bike Shop Name Address City State
Bicycles Etc. 6460 College Rd Lisle Illinois
Bicycles Etc. 536 S State Route 59 Naperville Illinois
Bike Line of Naperville 1277 S. Naper Blvd Naperville Illinois
Endure It! Life 215 S Washington St Naperville Illinois
Endure It! Sports 504 W 5th Ave Naperville Illinois
Endure It! Sports 20 75th St Willowbrook Illinois
Glen Ellyn Cyclery 460 Roosevelt Rd Glen Ellyn Illinois
Hartley’s Cycle Shop 24 W Hinsdale Ave Hinsdale Illinois
J & R Cycle & Ski 716 S Main St Lombard Illinois
Koslow Cycle 21W415 North Ave Lombard Illinois
Midwest Cyclery 117 E Front St Wheaton Illinois
Performance Bicycle 2830 S Highland Ave Lombard Illinois
Performance Bicycle 428 S State Route 59 Ste 106 Naperville Illinois
Prairie Path Cycles 27W181 Geneva Rd Winfield Illinois
REI 17W160 22nd St Oakbrook Terrace Illinois
Spokes 69 Danada Sq E Wheaton Illinois
Spokes 1807 S Washington St Suite 112 Naperville Illinois
Stemples Cycle 494 S Spring Rd Elmhurst Illinois
The Bike Shop 495 N Main St Glen Ellyn Illinois
Trek Bicycle of Downers Grove 639 Ogden Ave Downers Grove Illinois
Urban Tri Gear 840 E Ogden Ave Westmont Illinois

How to turn a table into a map

  1. Go to BatchGeocode.com
  2. Select all the data in your table (you can copy it from the table above), including the header row, and paste it into the text box at the top of the page (see screenshot 1).
  3. Click “Validate & Set Options”. BatchGeocode quickly reads your header row and fills in the answers for some of the options. It will have automatically selected “United States” as the region, “Address” as the address, “City” as the city, and “State” as the state. If it didn’t, set these options now. (See screenshot 2.)
  4. BatchGeocode does give you the option to color code your data into “groups”. You must have a column in your table. You can name it “Category”, “Group”, “Topic” or anything else. Just set this option under “Group By / Thematic Value”. With the bike shop data, it may be useful to group them by City. (See screenshot 2.)
  5. If you have a lot of records in your table that are near each other (you’ll have to know this beforehand if it’s true), enable “marker clustering” under “Show Advanced Options” button. This means that instead of showing 5 markers at a single intersection in the zoomed out view, the map will display 1 marker. When you zoom in, the 5 markers will then appear.
  6. Another column that you can have is “Website” or “URL”: BatchGeocode will turn this link into a proper hyperlink that people can click on. Set this under “Show Advanced Options”.
  7. When you are done setting the advanced options, click “Make Google Map”. It usually takes about 2 seconds for every 5 records you have. The records will be geocoded (turning addresses into geographic coordinates) and a map will appear.
  8. After seeing the results on a map, click on “Save & Continue” to get the permanent link to your map, and the embedding code. (See screenshot 3.)
  9. Give your map a title (which you can change later), input your email address (so you can get the permanent link and embedding code), and click “Save Map”.  (See screenshot 4.)
  10. After you click “Save Map” you will be taken to a full-screen version of your map (view my map result). Check your email for the edit link – don’t share this link with anyone else. The only thing I like to edit is the permanent URL of my map. On this page you’ll also get the embedding code.

[flickr]photo:6828415539[/flickr]

Screenshot 1: Showing the tabular data pasted into the text field. 

[flickr]photo:6828415243[/flickr]

Screenshot 2: Showing the “Validate & Set Options” function. Always do this step before clicking “Map Now” or “Make Google Map”. 

[flickr]photo:6828414961[/flickr]

Screenshot 3: Showing the resulting map. You can edit misplaced markers here or after saving it. Click “Save & Continue” to get a box in which you enter a title and email address. 

[flickr]photo:6828414649[/flickr]

Screenshot 4: Showing the “Save & Continue” box in which you enter a title and email address. You should always input your email address so BatchGeocode can send you the edit link. 

2 thoughts on “How to create your own online map: BatchGeocode for spreadsheets”

    1. BatchGeocode can also assist you in grouping addresses. For example, if you wanted to make a walking school bus, it would show you were all the participants live. You could then draw two routes and put those addresses in the group. You would then re-run the map through BatchGeocode, but now group on that “route” column. Route 1 addresses would be blue, and Route 2 addresses would be green.

Leave a Reply

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