There is a map in our ILL office. ILL staff indicate libraries that we loan materials to by sticking a white pin in the map and they indicate libraries that lend to us by sticking a gold pin in the map. If you are a map person, or even if you are just a library person, it interesting to check out places where things from our library have gone and it is interesting to see where things have come from.
I thought it would be interesting to see if I could duplicate the map on Google maps – and for the fun of it see if I could create a simple way to update the map. The first thing I needed to do was get a handle on KML. KML is a flavor of XML that Google uses to encode data about maps. Once I had a handle on KML, I needed to find a way to get the information to populate the KML.
Like most university libraries, my library uses OCLC to facilitate lending between other libraries and us. Additionally, the library uses ILLiad to manage that work. Unfortunately, I don’t have direct access to the data in ILLiad, I’m not even 100% sure what data I could get out of it. But I could get our ILL Librarian to get me a list of OCLC symbols. And this is where things start to get interesting.
In order to get information about a library, I used the WorldCat Registry API. From the registry I can get the name of the parent institution, name of the library, address of the library, the latitude and longitude of the library (very important for Google Maps), and a host of other information. Pretty much everything I needed to populate my KML data.
So I wrote up a little php script that read through my list of OCLC symbols and searched the WorldCat Registry API for each one. Fairly quickly I realized that many more symbols were failing than should. Most often this was a result of a symbol having more than one location to choose from. So I tweaked my script to go through each location returned and make its best guess at which one was correct. Which pretty much meant the first record that had a reasonably complete address.
Now I had a way to get the information about the libraries my library had been engaging in ILL with, but how was I going to get it into Google Maps. At first I just crammed it directly into KML format. In my hopeful first days the script generated live data. However, as you might imagine, this resulted in considerable lag time and ultimately Google just ignored my data. Next I output the KML data into a separate file.
Outputting the data was all well and good, but ultimately posed some challenges for updating the data. I had always intended that the data would be able to be updated. Entires needed to be added, deleted or changed. I could just periodically run a new list of OCLC symbols through my script and overwrite the old file with new, updated data with any new entries and minus any that needed to be dropped. However, I still had some failed WorldCat Registry searches that needed to be handled. And not all of the ILL from my library goes to places that have an OCLC symbol. So I was hoping to be able to manually enter data, when necessary,
My first solution was to update the KML file using the XML support in PHP. While this was fun as a mental exercise, it was pretty obvious that I needed something a little more flexible and easy to deal with. I went about moving my data into a MySQL database. However, around this time I saw someone talking about Google’s Fusion Tables. Fusion Tables seemed like a perfect fit for my project. Fusion Tables are built to work with Google’s other products, like Google Maps. Fusion Tables are smart enough to know when they are dealing with location data (most of the time) and you can import the data into a map without converting it to KML.
Ultimately, I didn’t go with Fusion Tables. I wasn’t sure that I ultimately wanted to host this library data in my personal gmail account. And I suspected that other people in the library would have some issues around managing the data this way. So at first I stuck with my MySQL database. But then my university contracted with Google for Gmail accounts with Google Docs and Sites and … No Fusion Tables. But I could access a Google Spreadsheet through the API. And I could easily share the document with others in the library without having to ask for a personal email. It was worth a try.
Ultimately, I ended up storing the data in a Google Docs spreadsheet. I have a script that initially populated the data and two other scripts. The first script lets to put in an OCLC symbol and have the data automatically added to spreadsheet. This script handles the multiple WorldCat Registry entries with more subtlety, allowing the user to select the addess that they want to use, even providing a map as a visual aid. The other script allows someone to manually enter an address and add it to the spreadsheet. Again, with visual aids and the whole nine yards.
I should mention, that I found the latitude and longitude data in the WorldCat Registry to be less than reliable. It was frequently absent. Sometimes it was transposed, so the lat was the long and vice versa. Sometime, I don’t know where it came from. So I started using the Google Maps API to get latitude and longitude coordinates based on the address in the WorldCat Registry.
For the sake of my presentation here, I moved the data back into Fusion Tables. Here’s how it looks:
It’s not clear that WP is playing nice with Fusion Tables right now, so I’m not sure that is going to show up, so here is the link –