XSLT+XML+geolocation=KML "BioStar users on a world map"

Today, I'be been appointed administrator of http://biostar.stackexchange.com/. It enables me to download a XML dump of the biostar database. Regarding the users, the XML file looks like this:

<?xml version="1.0" encoding="utf-8"?>
<Users>

(...)
<row>
<Id>30</Id>
<UserTypeId>4</UserTypeId>
<Reputation>13562</Reputation>
<Views>1485</Views>
<CreationDate>2010-02-25T17:27:15.56</CreationDate>
<LastAccessDate>2011-03-03T14:20:19.17</LastAccessDate>
<HasReplies>false</HasReplies>
<HasMessage>false</HasMessage>
<LastLoginDate>2011-03-03T11:15:36.54</LastLoginDate>
<LastLoginIP>XX.XX.XX.XX</LastLoginIP>
<Email>plindenbaum@yahoo.fr</Email>
<DisplayName>Pierre Lindenbaum</DisplayName>
<DisplayNameCleaned>pierre lindenbaum</DisplayNameCleaned>
<WebsiteUrl>http://plindenbaum.blogspot.com</WebsiteUrl>
<RealName>Pierre Lindenbaum</RealName>
<Location>France</Location>
<BadgeSummary>2=9 3=27</BadgeSummary>
<AboutMe>(...)</AboutMe>
<PreferencesRaw>[1|4][2|2][3|2][10|15]</PreferencesRaw>
</row>
(...)
</Users>

Using those information, it is now easy to create a google map/KML file plotting the biostar users. Neil has already achieved this task last october using ruby, GeoIP and data-scraping.

Here I've used the following simple XSLT stylesheet to create the KML file. This stylesheet is available at stackexchange-user2kml.xsl. Each time the XSLT stylesheet catches an element

<LastLoginIP>74.125.230.82</LastLoginIP>

it invokes a geolocalization service...

<xsl:variable name="url" select="concat('http://freegeoip.appspot.com/xml/',LastLoginIP)"/>
<xsl:message terminate="no">Downloading <xsl:value-of select="$url"/> ...</xsl:message>
<xsl:apply-templates select="document($url,/Response)" mode="geo"/>
</xsl:element>

returning another XML document http://freegeoip.appspot.com/xml/74.125.230.82

<?xml version="1.0" encoding="UTF-8"?>
<Response>
<Status>true</Status>
<Ip>74.125.230.82</Ip>
<CountryCode>US</CountryCode>
<CountryName>United States</CountryName>
<RegionCode>CA</RegionCode>
<RegionName>California</RegionName>
<City>Mountain View</City>
<ZipCode>94043</ZipCode>
<Latitude>37.4192</Latitude>
<Longitude>-122.057</Longitude>
</Response>

Result

xsltproc --stringparam title BioStar --stringparam anonymous yes \
stackexchange-user2kml.xsl Users.xml > biostar.kml

The anonymized KML file was then uploaded in google maps: Et voila;!:


View Biostar-anonymized 2011-03-03 in a larger map

That's it,

Pierre