Batch-converting CSV to Google Maps KML to illustrate Slovenian name signs in Carinthia

Inspired by a tweet by DaddyD (which is totally true: the “graphic” by Kleine Zeitung is just a failed table), I figured there should be an easy way of generating maps from a set of places. In this case, to illustrate the places in Carinthia affected by the new agreements concerning Slovenian name signs. Google Maps to the rescue! It allows you to create custom maps with markers (and lines, polygons, etc.) at arbitrary positions—the problem is just that it’s pretty complicated to do that by hand for a bunch of places.

Being a geek, I wanted to automate that, of course. The workflow would be as follows:

  1. create a spreadsheet with columns for the place name, additional text (in this case, the Slovenian place name), additional information for the Google geocoder (region etc.), and the desired marker color, using OpenOffice or whatever,
  2. export that spreadsheet to CSV,
  3. have a Python script batch-geocode all the places and put corresponding markers in a KML file,
  4. import the KML file to Google Maps.

You can download the UTF-8-encoded CSV file for the Slovenian place names.
However, the interesting part is the Python script, of course:

from geopy import geocoders
import csv

geocoder = geocoders.Google()
data = csv.reader(open('data.csv', 'r'), delimiter='\t', quotechar='"')
kml = []
print "Geocoding places"
for line in data:
	place, place_extra, sub_region, region, country, color = [value.decode('utf-8') for value in line]
	if not color:
		color = 'blue'
	query = place
	if sub_region: query += u", " + sub_region
	if region: query += u", " + region
	if country: query += u", " + country
	try:
		name, (lat, lng) = geocoder.geocode(query.encode('utf-8'), exactly_one=True)
		kml.append((u"<Placemark><name>%s/%s</name>" + \
			u"<Style><IconStyle><Icon><href>http://www.google.com/intl/en_us/mapfiles/ms/icons/%s-dot.png</href></Icon></IconStyle></Style>" + \
			u"<Point><coordinates>%s,%s</coordinates></Point></Placemark>") % \
			(place, place_extra, color, lng, lat))
	except ValueError:
		print u"Place not found: %s/%s" % (place, place_extra)
print "Writing data.kml"
kml = u"\n".join(kml)
kml = u"""<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<name>Exported KML data</name>
%s
</Document>
</kml>
""" % kml
out = open('data.kml', 'w')
out.write(kml.encode('utf-8'))
out.close()
print "Done"

You can also download the whole Python script. Given the file data.csv, it looks up all the place names using the geopy Google geocoder and exports the places to a KML file data.kml. You can download the resulting KML file.

The KML file can then be easily imported into a custom Google Maps map, which results in this map:

Kärntner Ortstafeln auf einer größeren Karte anzeigen

Blue markers denote places with Slovenian names according to the old regulation and new VfGH decisions since 2001; red markers represent places with a Slovenian population of more than 17.5%; and yellow markers are considered “room for negotation”.

The general CSV-to-KML converter could be used for many more beautiful maps, of course.

UPDATE: Made some manual corrections to the markers, thanks to comments on Twitter and derStandard.at.