Mapping Meth
This is a guide to mapping locations using Google Maps that I created for students in my multimedia journalism class at the University of Tennessee.
Like any news story, we start with a question or hypothesis:
Methamphetamines have long been a problem in East Tennessee, and they seem to be making a resurgence. I want to know if this is a problem spread across our community or if it is a problem only in certain parts of town.
So my questions are: Where have meth busts happened over time? Are there parts of town especially high in the number of meth-related incidents?
Now we have to find a dataset that will help us answer this question. Luckily, the Tennessee Dangerous Drugs Task Force has a searchable index of all meth incidents in the state of Tennessee.
If you mess around with the database, you should see that you can search by city, county, etc. You can also filter for either any meth incidents or just homes or businesses that have been quarantined.
We’ll start by mapping just the quarantined house in Knox County. You’ll see that there are a bit more than 30 houses that have been quarantined in the last 10 years.
We could probably select the whole table and copy+paste into Excel or Google Spreadsheets, but sometimes the doesn’t work. In order to avoid pasting issues, I use a simple little Chrome plugin for scraping information for websites. You can download the plugin for free from the Chrome store.
Once you have the plugin, you can selected any piece or group of data, right-mouse click, choose “scrape similar,” and the plugin will scrape all the similar data and dump it to Google Spreadsheet.
So now we can go back to the list of quarantined homes and scrape the data. You should just have to select one row of data to get it to work.
Sidenote: I try to clean the data as much as possible before I actually pull it into the spreadsheet. So in this case, I deleted the case number and the county. Regardless my data should come into a spreadsheet and look something like this:
So we have the data, but how do we map it. We will actually use a second Google product to actually do the mapping: Google Fusion Tables. But first we need to clean the data a bit.
Right now, the address is spread across five columns. To get Fusion Tables to understand the address as an address, we need to collapse the address into one column. To combine string values (i.e., regular text), we can use the ampersand (&) within a formula.
=A2 & B2
If you enter that formula in a new cell, you will see it didn’t quite work. What was the problem? Any ideas how to fix it?
All we need to do is add a space between A2 & B2. We can do this by putting a space between quotes in between a set of ampersands, like so:
=A2 & “ “ & B2
This formula says, the new cell should have the content from call A2, then a space and then the content from cell B2. We can repeat this process, making the whole address with the right punctuation. Here is the formula I used:
=A2&” “&B2&”, “&C2&”, “&D2&” “&E2
We can place that formula in the first open cell in row 2, hit enter, and then copy+paste the formula into the rest of the cells.
Mapping the data using Google’s My Maps
We now have our data ready and need to place it on a map. In this example, I will use the My Maps function in Google. My Maps is good for mapping datasets with less than 100 or so data points. If you have more than 500, you should use Fusion Tables (see directions for using Fusion Tables below).
For this one we can easily use My Maps. Go to mymaps.google.com and click “Create New Map.” The new map will open. On the left side of the screen, you’ll see the toolbar. From this window, we will use the import option to bring our meth data into the map.
Once you point Google to the right data, you will see a couple pop-up windows. The first will ask you where to find the location data. Click the right column label and click “Continue.”
The second pop-up will ask you to select the title of the markers that will be placed on the map. For this example, I used the quarantine date. After you select a title, click “Finish” and your map should appear.
At this point, your map should look like the above map. The teardrop marker is the Google’s default, but you can change this to just about anything. My Maps has a number of different built in option, and it allows you to upload custom icons. You can get to these options by control+clicking on any of the markers and clicking on the paintbucket (i.e., style button).
You can also change the icons, so they are not all the same. This is done up in the toolbar by clicking the “Uniform style” button.
Once you are finished messing with the icons, you can share or embed your map. Click the share button and then make sure you make your map public. You can then copy the link and share it however you want.
You can also embed the map by going back to the main map page, clicking the dots to the right of your map title, and choosing “Embed on my site.”
Here’s directions if you want to use Fusion Tables
We now need to import the data into to Google Fusion Tables.
Like many Google products, Fusion Tables is still considered to be a beta product. Because of this, you need to turn on Fusion Tables within your Google account.
Once it’s installed, go back to your Google Drive and under “create” you should be able to create a Fusion Table. When you choose to create a Fusion Table you will get to this screen:
Choose “Google Spreadsheet.” The spreadsheet you created in Step 3 & 4 should pop up. If it doesn’t just look through your Google Drive until you find it. When you find it, click it and follow the directions. Eventually, your data should show up in Fusion Tables.
Next, we just need to map the data. Just click the Map tab. Seriously! It’s that easy. The only problem that might arise is that Fusion Table might not understand what column you want to map. If this does sink your project, all you have to do select “Address” instead of “City” in the drop down.
Finally, we need to publish the map on our website. Choose “Publish” on the map tab. Fusion Table will give you a warning telling you that if you want to publish the map you need to make the table public. Do that. Then copy the embed code and paste it in your blog or website.
