Spotty Past

By now you've probably seen the Census Dotmap that everyone is talking about. It is "a map of every person counted by the 2010 US Census. The map has 308,450,225 dots - one for each person." When you look at it holistically, it's kinda cool, but you might not feel like there are any particular insights.

Let's see, the east side of the US is more heavily populated than the west. People love to live along coastlines. You can pick out metropolitan areas and assign them a name with ease. In some ways, it's not very different than some other maps we've seen...like the one of the US at night.

Source

But I like maps. I think there are stories in them. And I'd like to tell you one based on this particular point.

Click to embiggen, if you don't believe there's a town there.

I know, it's too small to see at this scale, but at the end of that arrow is the town where I grew up. So, here it is up close:

You are here.

Remember, each of those dots is a person---about 6000 of them. And when I look at this, I not only "see" the neighborhoods where my friends once lived, but also something of the topography. Can you tell where the main road (and train tracks) go through town? Can you tell where the university is, with its abundance of students? Would it surprise you to learn that there's a mountain at the southeast edge of town (where the dots line up, but go no further)? Even without the street labels, I can make a pretty good guess of where my mother's house is, because there is an empty space on the map for the elementary school---just a block away from the house.

Does this help?

What about this? You can definitely see the mountains better.

The interesting thing to me that the Census Dot map does is that while it doesn't hold surprises at a large-scale, the more I poke around in different towns, the more small-scale questions I have. I see things with this map, in terms of use of space and concentrations of population, that I can't see with the other maps.

Useful or not for schools? I think it's another tool in the arsenal. What do those distributions of population tell you about the needs for access to public services---and is that happening? What about arts and culture? School buses are getting pretty sophisticated these days with GIS data, but I still wonder if there is something to learn from a look at population. What would this map tell us about the community we serve?

Go play and tell me what you divine from the dots: past, present, and future.

Using Add-ins: MapCite

Out of the box, Excel is awesome all by itself. And yet there are those who endeavour to kick things up a notch by inventing add-ins...a kind of extreme macro. This specialized code enables Excel to do all sorts of new tricks. In previous posts, we've looked at Sparklines and ASAP Utilities.

I was recently pointed toward MapCite and made some time to give it a try. With MapCite, you can visualize your data using a Bing map embedded in Excel. The add-in looks something like this*:

*See the Bonus Round at the bottom of this post for more information.

For my data set, I pulled from one we've seen here before, the 5th grade state science test results from 2011. I added district address information.

Now, we're ready to "Geocode Data." Select the data---not the columns, or MapCite will tell you it can't handle that much work---for the addresses. Then click the "Geocode Data" button.
 

 In the pop-up window, fill in the information. Then, click "Geocode."

 Holy cow! We've just generated a whole new set of data:

There's more where that came from...

Now, you can add the mapping features. Select the data in the Latitude and Longitude columns, then click "Add Data."

In the pop-up window, indicate the required information and click "Finish."

Let's have a look at what we have wrought. Click on the "Show/Hide Map Pane" button. Here is our first view:

Not too exotic, but that's because MapCite automatically clusters the pins so things don't look messy.  Here's an unclustered look. Note that when you click on one of the pins, the row in the worksheet with the matching data is automatically highlighted. Keep in mind that you can also change the base map that is used.

You can also use the HeatMap feature to take a look:

You may be wondering why I bothered using science data when we just used the addresses of all the districts. It is a bit of a head-scratcher. But, with filtering tools in Excel, you can choose which groups to look at: small schools, those which scored above the state average, etc. You can also add GPX data (data that shows a route that was followed).

What I like about the add-in is that it's easy to use and that I can see the map in my spreadsheet. I don't have to upload my data elsewhere and pull it into another application. However, at this point, MapCite is fairly limited in features. You can make different pins for different pieces of a data set, but you can't show more than one set at a time. It's great to have things on a map, but I need to derive more meaning than just concentration. As such, classroom applications (other than what students might look at it) are limited. I think new features will come in time. I've been promised an upgrade to a Pro account when it's ready for release, and will let you know what else you can do.

Have a look around the MapCite website or YouTube channel for additional information. Better yet, give it a try for yourself.

Bonus Round
I say "like this," because I couldn't get the add-in to install properly. Although MapCite was very responsive to my inquiry for tech support, we couldn't figure out why Excel was making the add-in invisible. I ended up kludging things by creating a new tab for the ribbon called "MapCite," then dragging the groups from the tab-that-refused-to-show into my kludged one. Tech support said that they haven't had any issues similar to this one, so don't let my experience put you off. Also, if you have any clue why Excel shows the MapCite add-in as being active, but won't put it on the ribbon, I'd love to hear how to fix this.

Introducing Mapalist

Nearly two years ago, I had an assistant superintendent of a large school district tell me that one of his biggest wishes for displaying data would be to overlay his student achievement data with a Google Map. At the time, I thought it was a very intriguing idea, but at the time, I was unaware of any tool which would automate that process. It seemed unlikely that anyone would actually take the time to build a map in Google Maps (or Google Earth), student-by-student. I had seen visitor maps on websites that somehow captured IP addresses and then pulled them into a map displayed on the sidebar, but I was sure that required way more code than I was interested in dealing with.

In the intervening time period, I have discovered several tools for mashing a spreadsheet of data and a Google Map. The first link sent my way was for MapAList: "a wizard for creating and managing customized maps of address lists."

I pulled some public data off the state website and stripped off what I needed (name of school, address, score on 10th grade 2010 math test) in Excel. I then uploaded the spreadsheet into GoogleDocs and logged into MapAList. After fussing a bit with the settings, this is the result (or click this link):



What you're looking at is a map representing nearly every high school in the state and their performance on the 2010 10th grade state math test. (Be sure to zoom in so you can have a better view of things.) My divisions by percent meeting standard are arbitrary. Perhaps other pieces of data would be more valuable to show. But for proof of concept, it's a start.

I do find it interesting to see just how much the Cascades really divide our state geographically---there is a clear division of east and west with nary a school district in sight. The map also gives an interesting view of population. While not every school is the same size, the effect of clustered pins provides a different way to think about distribution. The yellow and green "outliers" definitely grab your attention. What's happening in those schools that are all by themselves (in terms of geography) and are doing all right?

Right now, you are restricted to two pieces of data/information showing in the pop-up for each pin. This is a bit of a limitation---I would like to show school district name or % free/reduced lunch or size of school or ethnicity data in addition to school name and score. The tool is also clunky if you want to go back and change any settings---for the most part, you just have to start over. The map will autoupdate if your spreadsheet changes, you're just stuck with the labels and appearance of the things.

As an educator, what are the uses for a tool like this? Might I want to mash my district achievement data (however that's defined) with a map? Would I see intriguing things as the neighbourhoods changed or gain other insights? I do believe that one would have to be very careful of running afoul of FERPA. I'd want to leave student names off the spreadsheet---they're unnecessary in some ways if the goal is just to visualize the interaction between geography and achievement.

I mentioned at the beginning that there are other tools which will accomplish similar goals. We'll take a look at those in future posts, as well as add the dimension of time to mix.

Adapted from Mapping Out the Big Picture, a post originally written for my other blog, What It's Like on the Inside.