Who are these people and where are they coming from?
Using readily available data found in your ILS software and some free programs and services, you can get a pretty good idea of where your library patrons are coming from. You may have pockets of people who love to come to the library, and pockets who don’t come in as much. With a heat map like the ones I’ll show you how to create, you can get an idea of where your patrons come from, and for outreach, where your patrons are not coming from. In the heat map below, the red star marks the library, the green dots are patrons who visited the library in the past 3 months, and orange dots are people who have library cards, but have not come in for 3 months.
Let’s get started!
Where’s my data at?
Get your data. I started out by getting the addresses of all my library’s cardholders. Then, I found a field called “Circactive,” which records the last time the patron visited the library. I exported the data to a spreadsheet, and cleaned it up.
Whack up that data good!
As you might imagine, some of the address data I exported was pretty heinous. How neat this data is will determine how well your heat map comes out. I had two address fields, as you can see in the screenshot above, and they were used inconsistently if not interchangeably for PO Boxes and Physical Addresses. My physical addresses also had inconsistencies in spacing and punctuation that had to be cleaned up. Things like Home Town, NY or Home Town, N.Y. or Home Town, N. Y. or Home Town NY gave me different results when I tried to map them. I used the Sort option and the Find and Replace option to get the data as consistent as possible.
Add some control numbers.
After I cleaned up all my addresses, I added a new column with control numbers for patrons who visited the library in the past 3 months, patrons who did not visit the library in the past 3 months, and an address and control number for the library. The library got a control number of 1, patrons who visited the library were given the control number 2, and patrons who did not visit were given the control number 3. I sorted my data on the “Circactive column,” and then added the control numbers by filling the cells to my cutoff date. You could use an If Then formula to put in the numbers you want, too.
Pare your spreadsheet down to just your addresses and control numbers, or create a new spreadsheet with just your addresses and control numbers.
The easy way or the hard way?
Now that your data is all clean, and your control numbers are in place, we can start to map it. I map my data in two different ways. One way is quick, and one way takes a little longer and gives a little more control. The easy way is to use Google Fusion Tables to geocode, map, and mark up your data. The hard way isn’t that hard, it’s just a little more involved and requires a couple extra files, QGIS, maxrice’s Excel Geocoder Tool, and a TigerLINE Shapefile.
The easy way with Google Fusion Tables
Upload your spreadsheet of addresses and map it.
This way is pretty straight-forward. Log in to your Google Account, create a new Fusion Table and upload your spreadsheet to it. You’ll see that the Fusion Table is already dying to map your addresses. It should have a tab that says, “Map of [your data]” at the top. Click this tab, give the table a hint for the town and state your library is located in, and click the button to begin geocoding your data.
The geocoding may take a little while, but most of your addresses should end up on the map. There may be a limit to how many addresses you can geocode with a Fusion Table in a day. I was able to map my nearly 5,000 addresses in one attempt, but if you find a lot of your addresses not showing up, you may have hit the limit. Addresses that don’t show up might also be due to crumby formatting, as I mentioned earlier.
At this point, all your addresses will be on the map, but you won’t be able to tell the angels that use your library from the miscreants that haven’t been in for 6 months. To make the two classes and our library distinct, we’ll use the Buckets option, accessed from the Change Features button found on the left side of your map.
Put that data in buckets, buckethead.
With the Buckets, we can assign map markers or symbols based on a range. This is where our Control Numbers come in. You can see in the screenshot, any address with a Control Number between 1 and 2 will get a Large Green Marker on the map. Remember, I used a Control Number of 1 for my library, so that it would stand out on the map as a reference. Any addresses with Control Numbers between 2 and 3 will get Small Red Markers. Those are all the patrons who visited in the last three months, who got a Control Number of 2. Lastly, the non-vistors are given Small Blue Markers, because their Control Number of 3 is within the final range of 3 to 100. After you you assign these Buckets, your map should be ready to go.
The nice thing about your Google Fusion Table Map is how interactive it is, you can zoom in on streets and pan around to look at relevant, human recognizable landmarks, and features.
The “Hard” Way with QGIS.
Like I said before, the hard way isn’t really that hard, it’s just a little more involved and you need to get a couple extra files. The advantage to the QGIS way, is that you can add in relevant boundaries like ZIP Code and School District lines, that you can’t easily add to your Google Fusion Table Map.
Get your tools.
You’ll need the following free programs and files to make a Patron Heat Map with QGIS.
QGIS a free and open source geographic information system.
Excel Geocoding Tool – a nice little Excel File for converting street addresses to Latitude and Longitude Coordinates. The file is here if you have a problem with the site. (Bummer: this file will not work with OpenOffice Calc only Excel).
TigerLine Shapefile – These files from the Census Bureau are loaded into QGIS as Vector layers to Draw in your boundaries. I’m using ZIP Code boundaries for my Association Library, but you may want to use School District lines for your library. There are many more Shapefiles, but these are the ones I think are relevant to libraries.
Change your street addresses to latitude and longitude.
QGIS doesn’t want your data as Street, Town, State. It wants latitude and longitude. Here’s where the Excel Geocoder Tool comes in. Get a Bing Maps API key by following the directions in the file. Then copy and paste your neat (you cleaned them up, right?) street addresses into the Location Column and hit the Geocode All Rows button. This will take a little while. You can use your Bing Maps API key to geocode 100,000 addresses per day. My 5,000 addresses took a couple minutes to geocode. When your addresses are done geocoding the new latitude and longitude will appear and be given a confidence score.
In my experience, a High Confidence gave coordinates that were a couple hundred feet or less from the street addresses, but Medium Confidence coordinates were pretty far off in some cases. Sometimes it was only a couple miles, sometimes it was on the other side of the state, but two miles could be a big deal in trying to determine a small library’s reach. I was able to reduce my addresses with Medium Confidence to less than 150 by making sure my data was neat and consistent, as mentioned earlier.
Put your new latitude and longitude values together with their respective Control Numbers in a new spreadsheet, and save it as a CSV file for use with QGIS later.
Get the ZIP Codes Lines on your map.
Start up QGIS, and Click the Add Vector Layer Button on the left, which looks like a Graphy V, and choose the TigerLINE Shapefile you downloaded. You’ll be treated to an astronaut’s view of the United States with all our messy ZIP Codes drawn in. You can zoom in and hunt around for your area if you like.
Add your addresses from you CSV file.
Click the Add CSV Layer Button on the left side of QGIS and add the file we created with our latitude and longitude for our addresses and our Control Numbers. The Add CSV Layer button looks like a giant, threatening Comma.
When you import your latitude and longitude data, you need to define your latitude and longitude columns as x or y respectively. Some research might tell you why it should be one way or the other. I got lucky, and the way I tried first was right. There’s only two ways to put it and one is right and one is wrong, and when it’s wrong you’ll probably know it.
Add some pep to that map!
Now, all your addresses should be on the map hemmed in by your TigerLINE boundary lines. Just like in the Google Fusion Table, we need to tell QGIS to mark our addresses differently based on their control numbers.
When you double click on your ZIP Code layer or your addresses layer, you get options for styling your map and data. I always pick this avocado green color for my map, not because I like it, but because I think it’s the best color they have. Styling your visiting patrons, non-visiting patrons, and library is a little more involved.
Double click on your patron layer, click style, and change the drop-down box that says Single symbol to Rule-based. Then Click the Green Plus Sign in the lower left to add a rule.
Give the rule a good label (library, visitors, nonvistors), and click on the ellipses next to the Filter Field on the second line. Putting a rule together is kind of fun. Here, we’re using the Control Number Column under Fields and Values to define our style, and because we’re putting style on entries with Control Numbers equal to 3 here, we’re working with non-visiting patrons. Click OK, and you can start perusing all the symbols and colors to identify the group of entries that fit into your new rule. Make a rule for each of your groups. In this case, I need a rule for my library, my visitors, and my non-visitors.
For these non visiting patrons, I’m using an orangey 2.0 millimeter circle. You may want to make your library’s symbol bigger, so it can be easily seen when you zoom out on your map. Your map should now reflect your defined symbols, but there’s one last tweak to get this just right.
When I defined all my points, I found that I just had a big blob of nonuser symbols with some poor user symbols peeking out from underneath. Nonusers are the larger group, unfortunately, and they rendered last, so they were on top blocking out everything. I changed the order of the layers with the Rendering Order button found on the screen where we added our rules. The order in this screenshot puts the library on top of my visitors, who are on top of my non-visitors.
Enjoy your map!
I find these maps work well on bosses and library board members. You can adjust the zoom on your map and export it in a couple different file formats.
Bonus: add a street layer to your map.
On QGIS, Click on the Plugins Menu, Click Manage and Install Plugins. Search for and install the OpenLayers Plugin. Now Click Plugins, OpenLayers Plugin, and choose the type of map you’d like to add. The map below puts our points over a Google Hybrid Layer.
Let me know if you need help, or have any questions.