elbow grease and geocoding – making a map of Vermont’s public libraries

The really great thing about nearly any computer problem you might have is that it’s very unlikely that you are the first person to have it. So if you have access to the internet and Google (to get you to other online help sources like Stack Exchange and other random app sites) you can find a way to do what you want to do, often.

I am working on a long term project. I am trying to visit all of Vermont’s 183 libraries. One of the things I will need to do to get started on that project is to make a map. The Vermont Department of Libraries makes the location of most of these libraries available in an Excel spreadsheet (thanks!) I just needed to figure out how to make that spreadsheet into a map. I toyed around with the Vermont Center for Geographic information but was having trouble making a CSV file that would satisfy Silverlight’s obscure criteria. And then I found a site that would generate a KML file (for Google Earth/Maps) from a CSV file. And again, I was close, but couldn’t quite get it to work. Googling further I found this impressive site, BatchGeo, which basically says “Hey click here and paste your data and we’ll make our best guess as to how it works and then draw you a map!” I had decently clean data. I clicked and pasted, and this was the result.

View Vermont’s Public Libraries in a full screen map

Oddly, the same data file pasted in to Google winds up looking not quite so clean thanks to quirky handling of ampersands and the non-standard address format of the original data file. But who cares, all I needed was one map. 183 libraries (the most per capita of any state in the US) here I come! You can read more about Vermont’s libraries in this report by the Department of Libraries.

8 Responses to “elbow grease and geocoding – making a map of Vermont’s public libraries”

  1. Daniel Ransom Says:

    Fun! I like the map. Out of curiosity, could you edit the data so that when a map viewer clicked on a particular library’s flag, they open a link out to that library’s website?

  2. jessamyn Says:

    I think so, yes. My table had a column for URLs but I decided to do proof of concept first. I think you could do this!

  3. James Fox Says:

    #bookmarked

  4. john, who is incognito and definitely not at work Says:

    Was the ampersand thing the difference in what sites allow in URLs? Some sites enforce valid HTML, so ampersands in links must be encoded (as &) while others allow just the plaintext ampersand.

  5. john, who is incognito and definitely not at work Says:

    Oh, and cool project. :-)

  6. Jodi Says:

    You might as well join the 251 Club while you’re at it. http://www.vt251.com/
    Here’s a question: are any of Vermont’s towns not served by a public library (without a fee)? For example, residents of my town of Duxbury can get a free membership at the nearby Waterbury library.

  7. jessamyn Says:

    I am a (plus) member of the 251 Club! And I’m not sure about service. The Department of Libraries says that 98% of Vermont’s population (not area) is served by either a local or contracted library. This can change from year to year. We have a town next to mine that determines yearly whether to support our library so that their residents can go there. If they don’t decide to support them with taxes, theoretically the residents would have to pay a fee somewhere. There are a lot of libraries in Vermont that don’t have any user fees (smaller ones usually) so you could make the argument that no one has NO access to a library but there are definitely people who don’t have access to a nearby one. I suspect that even that remaining two percent are served by a volunteer library but I don’t know for certain.

  8. Helen Says:

    *gasp* I love it! I have so many of these already since VT libraries ARE my 251 Club mission, but this will a super huge help in the ones I missed because I didn’t have cell service when I was looking. YAY FOR YOU!