Wojtek Sczygiol's

I’ve been working on a J2ME “buddy finder” app for some time now. After a while my postgis-enabled database filled with more and more gis objects and I started looking for a quick and easy way to visualize the geo-data. Stumbling upon the article “Turn SQL into XML with PHP” I had an idea: Why not just convert the SQL data to xml, use XSLT to style it like KML and let GoogleEarth to all the visualization work?

Turned out to be quit easy. The PHP code snippet look like this:

$q2x = XML_Query2XML::factory(MDB2::factory('pgsql://_user_:_passwd_@localhost/DB'));
$sql = "SELECT id, login_name, full_name, email, recent_update, invisible, ";
$sql.= " Y(gis_location) as lat, X(gis_location) as lon from users";
$xml = $q2x->getFlatXML($sql, $rootTagName = 'buddies', $rowTagName = 'buddy');
$xsl = new DOMDocument;
$xsl->load('bi2kml.xsl');
$xslp = new XSLTProcessor;
$xslp->importStyleSheet($xsl);
header('Content-Type: application/vnd.google-earth.kml+xml');
echo $xslp->transformToXML($xml);

And this was the XSLT sheet:

<xsl:output method="xml" version="1.0" omit-xml-declaration="no"/>
<xsl:template match="/">
<kml xmlns="http://earth.google.com/kml/2.1">
<xsl:apply-templates/>
</kml>
</xsl:template>
<xsl:template match="buddies">
<Document>
<Style id="buddyIcon">
<Icon><href>images/icon-buddy-on.png</href></Icon>
</Style>
<xsl:apply-templates/>
</Document>
</xsl:template>
<xsl:template match="buddy">
<Placemark>
<styleUrl>#buddyIcon</styleUrl>
<name><xsl:value-of select="./login_name" /></name>
<description>
<xsl:value-of select="./full_name" />
<![CDATA[
<br/>
]]>
<xsl:value-of select="./email" />
</description>
<Point>
<coordinates> <xsl:value-of select="./lon" />, <xsl:value-of select="./lat" />, 0 </coordinates>
</Point>
</Placemark>
</xsl:template>

And this is how the end result looked: