Fast Google Maps with FMAtlas.com

From Reporting Cookbook: www.forjournalists.com/cookbook

[edit] Introduction

An earlier version of this file is available at http://www.forjournalists.com/media/FMAtlas.pdf

Experienced Internet mappers can argue about whether the Google Maps programming interface is better than Microsoft's, or whether the imagery is more updated than the other.

For the inexperienced, though, neither option lets a reporter quickly create a map. FMAtlas.com, created by recovering journalists at Faneuil Media, lets anyone create a solid, professional map – for free – in minutes.

The maps generated by FMAtlas can be inserted into an existing Web page, with your links, logos, and other stuff all around it – so it looks exactly like a page from your Web site, because everything except the map itself is coming directly from your Web site.

FMAtlas works with Google Maps, and still allows you to use street maps, aerial photography, zooms and other basics of the Google service.

To create a map, you need:

• A free FMAtlas.com account
• The Web browser to handle most everything
• Spreadsheet software. You may already have Microsoft Excel. You can use Google Docs or a similar service online. A handy solution is the PortableApps.com version of OpenOffice.org, which can be run off a thumb drive or a CD.
• Most importantly, data that you want to map. You'll need a location of some sort, such as street addresses.

One caution: FMAtlas.com relies on Google Maps, which incorporates mapping data through other services. While all of these layers of services are offered for free at the moment, there is no guarantee they will remain indefinitely so. Then again, do you really need to guarantee your map will remain accessible 30 years from now?

[edit] Laying the groundwork

Begin by visiting http://www.fmatlas.com. Create a new account. Realize your account name will show up in the Internet address of your map; this is not a good time to register as LonelyGuy64 or DorkSlayer.

Second, open your spreadsheet. Browse through the columns to get a feel for what you have. Which columns can be excluded? Which columns will you have to give your audience some context with? Finally, are you giving them too much information to fit in the "map pin" display, or are you not providing enough stuff to be worthwhile?

From a technology standpoint, there are real limits to how many items you want to put in a map. From a practical standpoint, you can think as you would a piece of paper – will you have too many pins in a map for it to make sense? Is your information too complex or too voluminous to relate on, say, the electronic version of a Post-It note?

Third, figure out how you want the information to show up when someone clicks on each pin of your map. Try writing it out on a scrap of paper. Are there links? Should some information be bolded? Where do you need to force new lines?

This process of thinking visually is important to saving time as you begin the actual mapping process.

[edit] The basic FMAtlas format

FMAtlas will take only a specific kind of spreadsheet file configured for just three columns. They are:

Column A: Address or location information. Again, in just one column, you'd need to include the entire address: "1600 Pennsylvania Ave. N.W., Washington, D.C. 20006" for the White House. If you already have latitude and longitude, you can use them now; 38.897331,-77.037422 would get you the Oval Office.

Column B: A title for your map point. This is short: “The White House.”

Column C: Everything else. If you were working on a dam safety story, you might ultimately want your Hoover Dam entry to look something like this:

Built: 1931
Risk if dam breaks: Severe
Last inspection: 1 year ago
726 feet tall. Holds 157,900 acres of water. Concrete construction.

That doesn't look so bad. First, though, you have to mix some HTML – the language of Web pages – with some spreadsheet knowledge. It won't be tough.

[edit] Readying your data

You may already want to create your own map for your project already. Open up your data within your spreadsheet software, then jump ahead a few paragraphs to follow along with the demonstration.

If you're reading this so you'll know how to create your own map when the time is right, you'll need dummy data. Let's take a look at a demonstration with Massachusetts child car seat inspection stations, the places where expectant parents discover they have no clue how to keep their child alive. The data should be available through http://www.nhtsa.dot.gov/people/injury/childps/CPSFittingStations/CPSinspection.htm or http://tinyurl.com/2b28sh

You'll see a Massachusetts spreadsheet linked for download. Download it and open it in your spreadsheet software.

Immediately do a File: Save As, then pick a different filename. This preserves your original data in the original file in case you make a major mistake. It also gives you an opportunity to come up with an easier name, like CarSeat1.

Save it in your spreadsheet's native format, such as Microsoft Excel format. Do not at this point save it in a text format.

Everything you're doing is geared toward creating just three columns. So, create three columns. Click in A1. Click Insert: Column, then Insert: Column, then Insert: Column. You should have your goal in sight.

You have three truly blank columns, which you're trying to fill. Remember, FMAtlas is looking for an address, a title, and stuff to put in the information box. Normally, you'd wipe out any filler at the top besides the actual header row. You'd also put your own headers on the new columns: In A1, type MapAddress. In B1, type MapTitle. In C1, type MapStuff.

If you already know how to manipulate your data into those columns, you can simply skip around to the “Transfering your data” section. If you're using trying to learn with the car seat data, or have your own data, you need to stick with this.

[edit] Manipulating car seat data

The car seat data is hideous.

You'll find out there are extra columns hidden in the spreadsheet. Worse, literally half of your rows are hidden: Yes, Virginia, the federal government actually keeps half the car seat inspection sites from parents -- not out of Darwinism but out of incompetence.

You also see a big header at the top -- "Massachusetts CPS Listing." You don't need the big header. Your goal is to have only column headers at the top. So let's delete that row – click on the “1” on the left to highlight the row; right click to pull up a menu, left click on the delete rows. You now have a blank line. Do the same thing, so your column headers take up the first two rows.

Earlier, you were supposed to insert three blank columns at the far left, so that Columns A, B and C are waiting for your data. Now you can put your headers at the top. In A1, type MapAddress. In B1, type MapTitle. In C1, type MapStuff. Let's start by building your address column, A.

The car seat data now begins at D3. Put your cursor in A3. See what location information you have. There's the street address in H3. There's a city in J3. There's a zip code in M3. So, the best you could make out of what you've got is something like 25 Winthrop Street, Worcester, 01604.

(Don't worry so much about the text manipulation side of things; your example will certainly vary. Many more tricks and a better explanation are available at the ForJournalists.com Reporting Cookbook wiki in the [Text editing with spreadsheets] article.)

You're missing one nugget of data: The state, which in our demo is from the Massachusetts file. The postal abbreviation we're missing is simply "MA". Your new goal is to have A3 look like "25 Winthrop Street, Worcester, MA 01604". To get there, you need something that the computer will see as H3, J3, MA M3. That means you need a few commas and spaces, plus the "MA" abbreviation for Massachusetts.

The Excel formula for combining columns is called Concatenate. Formulas start with an equals sign. (Again, you want to take a look at that wiki.)

Put your cursor in A3. Enter this:

=Concatenate(H3; ", "; J3; ", MA "; M3)

At the top of the screen, you should see the formula appear. Under your cursor, though, you should see the address, perfectly readable, just like you'd want. When you're still looking at box A3, you'll see a little notch in the bottom-right corner of the highlighted A3 square. Click on this notch; drag it down, and down, and down, until you hit the last item in your spreadsheet. The formula will copy down the column.

You should now have a working address field in the first column, one-third of what you FMAtlas needs to start mapping. Let's go for the second part of this: The title.

What do you want your title to be? Well, in the case of the car inspection places, probably just the name of the place. That shows up in D3 (and E3, and D4, and E4, yes). So you want your B column, your FMAtlas title, to simply reflect what's in your D column, the organization.

Put your cursor on B3. Use this formula:

=D3

See that notch in the bottom-right corner of B3 again? Click that, drag it down, and down, and down, and all of a sudden you have the formula copied and the organization name becoming your FMAtlas title.

You're two-thirds of the way done. Unfortunately, this was the easier two-thirds.

This is a good time to save your file.

The third column in FMAtlas will be the "stuff." Figure out what you'd want on your mapping “Post-It” note. Perhaps you want something like this:

Amesbury Police Department

*By Appointment Only*
Ask for: Dave
19 School Street, Amesbury
978-388-1212

We can probably leave off the state and ZIP code in the pop-up menu. We certainly want to include the phone number and note that it's by appointment only – that note field is actually the most important column of our "stuff" section, so we probably ought to put it first.

Of course, most of our entries don't have any notes. Most don't have contact information. So we need to deal with that accordingly. We simply do not want the first two lines to show up like this:

   **
   Ask for:

To cure that, you have to check for blank entries in the contact and note fields. If there are blank entries, you ignore them; if there's good stuff in there, you have to treat it.

Put your cursor in the D column. Do an Insert: Column. Do another Insert: Column. Your original data should now start in Column F, and the formulas you just created have been automatically updated to reflect the new locations.

Let's start with your new note field, which you'll put in Column D. The original notes field is now Column R. So put your cursor in D3. We have to check R3 to see if there's anything there; if there isn't, we don't want anything; if there is, we want an asterisk (*), the note, and another asterisk.

(Again, many more tricks and a better explanation are available at the Reporting Cookbook wiki, [Text_editing_with_spreadsheets http://forjournalists.com/cookbook/index.php?title=Text_editing_with_spreadsheets] )

The "If" formula lets us check for something. If the answer is yes, your spreadsheet will take the first part of the formula; if the answer is no, the spreadsheet will take the second part of the formula. So let's try this:

=If(R3=""; ""; concatenate("*"; R3; "*"))

There's one thing still missing here, though. We need to tell FMAtlas that the note field should be on a line by itself. The HTML, the Web page language, for a new line is <BR>. Let's tweak this slightly:

=If(R3=""; ""; concatenate("*"; R3; "*<BR>"))

And in D3 you see ... nothing. It worked! There are no notes in the original note field, so there should be nothing in the new note field.

Click the little handle in the bottom-right corner of D3, and drag it down and down. In the lines where you have an actual note in the R column, you'll see the note moved over to the D column.

Now we need to do the same thing for our contact column, which we'll make E.

Put your cursor on E3. We want the same sort of thing – if there's no contact, we want nothing. If there's a contact, we want it on a line by itself with some extra text, like “Ask for:”, followed by a space, the contact name, and then a new line. The original contact listing is in Column H.

So, in E3, we want this:

=If(H3=""; ""; concatenate("Ask for: "; H3; "<BR>"))

Great! Now we're getting the hang of this. Now we need to pull it all together.

Our original Post-It note would now look like this:

D3

E3

J3, L3

Q3

That is – special notes followed by a new line; contact info, followed by a new line; street address, a comma, city and a new line; and then the phone number. Remember our notes and contact fields already have a new line, if needed.

Put your cursor in C3. We're going to make our final FMAtlas column, the stuff:

=concatenate(D3; E3; J3; ", "; L3; "<BR>"; Q3)

For the last time, click on the thing in the bottom-right corner of C3, drag it down, drag it down.

This is a good time to save your file.

[edit] Transferring your data

This is still a good time to save your file.

At this point, you've got all of your address information compiled in Column A. You've got your title compiled in Column B. You've got the rest of your stuff, the Post-It stuff, compiled in Column C. At this point, you don't actually care about the rest (provided you've saved the file).

In short, you're ready to try to get your data saved in the right way, then send it on to FMAtlas.

Go to your prepared spreadsheet, which should still be saved in Excel or a similar actual spreadsheet format. Click on your first column (address information), in the first row with actual data in it; you don't need the headers. Click and drag to the third column (the stuff) all the way down to your final row. Hit Control-C or Edit: Copy.

All of your data is now in computer memory. Put it somewhere safe. DO NOT EVEN THINK FOR AN INSTANT YOU WANT TO PASTE THIS INTO A WORKBOOK ON YOUR OLD SPREADSHEET. (Nevertheless, you're going to do it sometime in the next few months, and loathe yourself for it.)

To create a new, safe home, click File: New: New Spreadsheet.

Put your cursor in A1. Click Edit: Paste: Paste Special. If you click Paste, you'll get something just ugly. Paste Special is your friend. You want to paste in everything but formulas – you only want the values, the interpreted contents, of each cell. Paste Special is the key.

Your stuff should appear. You're almost there.

Click File: Save As. You want to save it as text, comma-delimited, commonly called a CSV or comma-separated values. Find that in the type menu. Give it a useful filename, like carseatupload1.csv. Click Save.

Switch over to your Web browser. Go to [1]. Login.

A sample, but blank, map will pop up. You can ignore the rest for now, but you want to pick the pin color before uploading.

Now, click the “Bulk Upload” link at the top of the map display.

A box pops up. For map name, why not use something creative, like, “Massachusetts car seat inspection stations”?

For datafile, click the Browse button. Find your carseat1.csv file; you want the CSV file, not the Excel file. Pick it. Click Add.

FMAtlas will chug along for a minute. It really may take a minute. Your map should appear.

Now, if it does not: Switch back to your CSV spreadsheet. Delete all but, say, 10 rows. Do another File: Save As, and call it something like test.csv. Try uploading that, and see if it works.

Once things seem to work:

Click the My Atlas link in the top-right corner. Find your last map. Right-click on the View link, then left-click on Copy Link to Clipboard – the link is now in your computer's memory. You can now left-click, like normally, on View to check out your map.

Your (nearly) finished map should be, er, nearly finished. The Internet address (URL) of the map is in your computer's memory and can be Edit: Pasted anywhere. Now, you'll want to go into FMAtlas to monkey with the settings.

You'll want to set the size – for example, many embedded news maps might run about 400 pixels wide.

You'll want to set the zoom and centering – how much of your region should we see?

There are other things to play with, but the first crack will usually meet your needs. Now, hit the “Save” button in FMAtlas.

You can embed the map into your own Web page by using an IFRAME tag and the “View” link.

The map used in this example is available here:

http://fmatlas.com/view/stucka/20080121_massachusettscarseatinspectionstations or http://tinyurl.com/2lsg2e

[edit] Cleanup

Now that you've made it through the tutorial, you're probably realizing you can quickly generate a map. The second time you do this, it'll be faster; the third, very quickly.

You'll probably also realize how many problems your original data has; the car seat inspection station spreadsheet has hidden rows, hidden columns and even missing organization names.

You'll also realize you can make the map “Post-It” look better. You might realize you want a Web page link from each “Post-It.” You might want more bolding or italics or something else. You might want to add one of those “Get Directions” links. You can do all of that through FMAtlas.com and your spreadsheet.

None of this prevents you from learning Google Maps on your own. Mike Williams' well-regarded tutorials are available at: http://econym.org.uk/gmap/

And if FMAtlas isn't doing the trick, you can check out a somewhat similar service called ZeeMaps. A tutorial for that is available at: http://multimedia.journalism.berkeley.edu/tutorials/mashups/zeemaps

You can find tutorials for mapping, spreadsheets and other things for journalists at the Reporting Cookbook: http://forjournalists.com/cookbook

And the Investigative Reporters and Editors, with a related project called NICAR, have technical guides, tipsheets and invaluable mailing lists: http://www.ire.org