Overlay data on maps using XSLT, KML, and the Google Maps API, Part 2: Transform and use the data

Produce a KML overlay document and display it in Google Maps and Google Earth

In this two-part article series, you'll develop an application for a real estate brokerage to display all available apartment listings as clickable Placemarks on Google Maps. In Part 1, you created the first half of the application that collects the apartment listing information from the user, uses the Google Geocoder Web service to turn the street address into its geographical coordinates (longitude and latitude), and stores the coordinates in the database along with the address information. In Part 2, you will use this data to produce a KML overlay document and display it in Google Maps and Google Earth. First, you'll use stored procedures to produce XML from MySQL. Then with XSLT and a technique called Muenchian grouping, you'll transform the XML data into a KML document containing the overlay information—one Placemark for each apartment building. The pop-up balloon for each Placemark displays the available apartment listings in that building. Finally, you'll use the Google Maps API to display the KML overlay in a Google Map embedded within your own Web site.

Share:

Jake Miles (jacob.miles@gmail.com), Senior Technical Liason, Twistage Inc.

Photo of Jacob MilesJake Miles is a Senior Technical Liason at Twistage, Inc, currently working with Facebook, Myspace and OpenSocial applications, using Java, PHP, Adobe Flex and JavaScript. He has been working as a professional developer for 10 years and has been an avid student and tinkerer since he was 10. He also teaches on a volunteer basis.



09 September 2008

Also available in Chinese Japanese

Obtaining the source XML data for transformation through XSLT

Once you store all the address information and the corresponding latitude and longitude coordinates in the database, you'll first produce your data in XML format and then use an XSLT stylesheet to produce the final KML data. You can choose from a number of solutions. For this example, in which the database query only returns the rows of a single table, you might just write a small PHP function to wrap each column in an XML tag. Another solution for more complex data uses Erik Wetterberg's clever stored procedures for MySQL, available for download on Mysql Forge that let you construct arbitrarily complex hierarchical XML elements directly in the SQL query (see Resources for a link).

Using stored procedures to return XML from Mysql

Frequently used acronyms

  • API: application programming interface
  • CSS: Cascading stylesheets
  • HTML: Hypertext Markup Language
  • KML: Keyhole Markup Language
  • PHP: PHP Hypertext Preprocessor
  • URL: Uniform Resource Locator
  • XML: Extensible Markup Language
  • XSLT: Extensible Stylesheet Transformations
  • XSL: Extensible Stylesheet

Wetterberg's solution uses three stored procedures: xml_tag(), xml_escape() and xml_attr() to construct an XML tree from the columns in the rest of the select statement. xml_tag() takes four arguments: the name of the tag, the value of the tag (database column in the query), the attributes of the tag, and the subelements of the tag. The attributes and subelements of the tag are each a single string, so you compose them using concat(). Listing 1 demonstrates how to use these stored procedures to produce one XML element string per row, each containing one element per column. You can also just as easily turn the subelements into attributes of the row instead.

Listing 1. Retrieving MySQL results as XML elements in PHP using stored procedures
function fetchListingsAsXml() {

  $sql = <<<SQL

      select xml_tag('listing', 
             null, 
             null,
             concat(xml_tag('id', id, null, null),
                xml_tag('address', address, null, null),
                xml_tag('apt_no', apt_no, null, null),
                xml_tag('city', city, null, null),
                xml_tag('state', state, null, null),
                xml_tag('zipcode', zipcode, null, null),
                xml_tag('longitude', longitude, null, null),
                xml_tag('latitude', latitude, null, null),
                xml_tag('apt_type', apt_type, null, null),
                xml_tag('rent', rent, null, null),
                xml_tag('notes', xml_escape(notes), null, null)))
      as xml
      from listing;

SQL;

 return fetchMysqlXml('listings', $sql);
}

function fetchMysqlXml($tag, $sql) {

  if (! $result = mysql_query($sql)) {
    die (mysql_error());
  }

  $xml = '';
  while ($row = mysql_fetch_array($result)) {
    $xml .= $row[0];
  }

  return "<$tag>$xml</$tag>";
}

The SQL query uses the stored procedures to create one XML listing element per row of data returned, passing in null as the column to obtain its value from because its value is its subelements. You pass in null for the attributes parameter as well, and then again as its subelements parameter pass in the concatenation of subelement tags, each produced with further calls to xml_tag(), to provide a selected column as the value of the tag and null for the attributes and subelements parameters.

This produces one XML tag per row in the result set but does not create the top-level element. For simplicity in creating the top-level element, the fetchMysqlXml() function takes the name of the top-level aggregate element and the query (which presumably uses these stored procedures to produce XML element strings), and concatenates the strings in the rows to produce the aggregate XML element containing one sub-element per row in the query result, as in Listing 2.

Listing 2. Database data transformed into XML
<listings>
<listing>
<id>10</id>
<address>3 Irving Place</address>
<apt_no>3A</apt_no>
<city>New York</city>
<state>NY</state>
<zipcode>10003</zipcode>
<longitude>-73.988639</longitude>
<latitude>40.734091</latitude>
<apt_type>studio</apt_type>
<rent>2800</rent>
<notes/>
</listing>
<listing>
<id>11</id>
<address>123 E.34th St.</address>
<apt_no>2D</apt_no>
<city>New York</city>
<state>NY</state>
<zipcode>10016</zipcode>
<longitude>-73.980182</longitude>
<latitude>40.746595</latitude>
<apt_type>1br</apt_type>
<rent>2300</rent>
<notes>great views, A/C incl.</notes>
</listing>
</listings>

Transforming the XML apartment listings into KML overlay data

With the database contents in XML format, you can use XSLT to transform the data into KML to produce data you can overlay on a map or import into Google Earth, which you'll do in the next section.

The data in this example represents a list of available apartment listings in Manhattan, New York, perhaps of an individual landlord or a real estate broker. The desired effect is to place bookmarks on a map of Manhattan, one bookmark per property with apartment listings. Click a bookmark to open a balloon that contains the name and description of the bookmark, where the description is any valid HTML you want to display in the balloon. In this example, the pop-up balloon of the bookmark will display all available listings in that bookmark's building.

Your goal is to transform the source XML data from Listing 2 into KML like that in Listing 3.

Listing 3. Sample KML output
<?xml version="1.0"?>
<kml xmlns="http://earth.google.com/kml/2.2">
    <Document>
        <name>Jake's Apartment Rentals</name>
        <description>A map of available apartments - click a bookmark to see 
                             	available apartments there.</description>
            <Placemark xmlns="">
                <name>Address 220 E. 22nd St.  (2 listings)</name>
                <description>
            <![CDATA[
                <div>APT 1A: $2250,  new kitchen, H/W floors</div>
                        <div>
                            APT 2S: $2500, loft space, sliding glass doors
                        </div>    
                     ]]>
               </description>
               <Point>
                   <coordinates>-73.982488, 40.737675</coordinates>
               </Point>
           </Placemark>
           <Placemark xmlns="">
               <name>Address 214 E. 73rd St.  (2 listings)</name>
               <description>
            <![CDATA[
                <div>APT 2C: $2000, everything brand new</div>
                        <div>
                          APT 4A: $2400, elevator, A/C incl, gorgeous
                        </div>    
                    ]]>
               </description>
               <Point>
                   <coordinates>-73.959088, 40.769987</coordinates>
               </Point>
           </Placemark>
       </Document>
</kml>

Listing 3 is a KML document that contains name, description, and Placemark elements, each of which represents a location on earth. Each of the Placemarks in this document contains a name, a description, and a Point object specifying the 3D coordinates of the Placemark in terms of longitude, latitude, and an optional altitude. Each Placemark becomes a bookmark on a Google Map. Click the bookmark to open the balloon containing the name in bold with the contents of the description beneath the name as in Figure 1.

Figure 1. A Placemark as it appears on a Google Map
A Placemark as it appears on a Google Map

Notice that each listing in the balloon appears on its own line. Since Google Maps renders the description verbatim as HTML, each line requires either a <br/> or must be wrapped in its own <div> element. You can include any HTML you like as the description, but you must wrap the contents of the description field in a CDATA element.

Creating the XSLT stylesheet

To transform the original XML data into KML, start with the stylesheet in Listing 4.

Listing 4. Skeletal XSLT stylesheet for transforming into KML
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:key name="listings-by-address" match="listing" use="address" />

  <xsl:template match="/">
    <kml xmlns="http://earth.google.com/kml/2.2">
      <Document>

    <name>Jake's Apartment Rentals</name>
    <description>A map of available apartments - click a bookmark to see 
               available apartments there.</description>        

    <xsl:apply-templates select="listings"/>

      </Document>
    </kml>    
  </xsl:template>

</xsl:stylesheet>

The <xsl:template> tag matches on the root of the source document, creates the skeletal KML document with its name and description, and then calls <xsl:apply-templates> on the root's one child element, listings.

The <xsl:key> at the top of the document will let you group listings by address. Because each Placemark represents one apartment building, within which can be many available apartments, you need to group the listings by apartment building when you render the Placemark element of the building. Since the incoming XML data just contains a flat list of <listing> elements, you'll group them by address in the stylesheet.

Grouping the apartment listings by apartment building address in XSLT

Here you'll use a grouping technique known as Muenchian grouping (see Resources for more information), in which you use an XSLT key to facilitate grouping. An XSLT key creates an index by which you can quickly refer to a set of elements matching a single criteria. This creates a natural grouping mechanism that the templates can then use, in addition to a few non-trivial tricks, to render one Placemark per group in the KML.

The grouping behavior requires that the stylesheet do two things:

  • Collect a set of group leaders, one per unique building address in the document, to create the Placemark element
  • For each unique building address, pass all listings with that address into a template to process and, in this case, render one <div> element per apartment within the Placemark

Starting the grouping process to collect unique addresses (group leaders)

The next <xsl:template> element matches on the listings tag, and kicks off this grouping behavior, as in Listing 5.

Listing 5. <xsl:template> for the listings element
<xsl:template match="listings">
    <xsl:apply-templates 
       select="listing[count(. | key('listings-by-address', address)[1]) = 1]" 
       mode="first-in-group"/>      
</xsl:template>

The contents of this template might go directly in the first template, but the code is easier to follow if you split it out. This template calls <xsl:apply-templates>, passing it one listing per unique building address in the document—the group leaders. Selecting these group leaders elements requires a somewhat complicated trick. The XPath expression listing[count(. | key('listings-by-address', address)[1]) = 1] will match all listing elements that pass the criteria in brackets. The expression key('listings-by-address', address) accesses the key you created at the top of the stylesheet. As this XPath expression tests each listing element in the document, it calls the key with the address of the currently-tested listing. The key returns all listings in the document with that address value. The [1] selects the first of these matching elements. The pipe symbol (|) creates a union of this first matching element with the currently-tested element [the period (.) represents the current element]. Since a union only contains unique elements (and rejects duplicates), the result of this union will either contain one or two elements—one if the currently-tested element is the first in the document with its address (the leader of its group), two if it is not the first with its address (not the leader). So the = 1 tests that the result of this union only contains one element, meaning that the currently-tested element is the first one in the document with its address. The result is that the XPath expression will collect one listing per unique building address in the document, the set of which will then be passed into the listing template, under the mode first-in-group.

The group-level XSLT template (creating the KML Placemark)

You can specify different modes for an <xsl:template> when you want to match elements that matching the same criteria but under different circumstances. It's equivalent to defining two functions that can accept the same arguments but do different things with them. In this case, you first create the Placemark element for the group and then process each listing within the group, both of which match on a vanilla listing element. You create two <xsl:templates> that match on listing elements. One will handle the group-level processing for the group leader (selected in the last template), and the other will handle processing of each listing within the group. Listing 6 shows the first of these templates.

Listing 6. The <xsl:template> that creates the group-level Placemark element
<xsl:template match="listing" mode="first-in-group">
    <Placemark>
      <name>
    <xsl:value-of select="address"/> 
    (<xsl:value-of select="count(key('listings-by-address', address))"/> listings)
      </name>
      <description>
    CDATA-START
    <xsl:apply-templates 
       select="key('listings-by-address', address)"
       mode="listing-within-group"/>    
            CDATA-END
      </description>
      <Point>
    <coordinates>
      <xsl:value-of select="longitude"/>,
      <xsl:value-of select="latitude"/>
    </coordinates>
      </Point>
    </Placemark>
  </xsl:template>

Again this template matches on listing elements but only when the first-in-group mode is specified, as it is when called from the previous template (in Listing 5) that collected the group leaders. This template is responsible for creating the Placemark element for the building, where the bookmark will appear on the map. The name of the Placemark is the address of the building with the number of listings in that building in parentheses. To obtain the listing count for the building, again use your address key to count the listings with the current address of the listing.

The <description> element is the part of the Placemark that appears under the name in the pop-up bubble when you click the Placemark on the map. Two things happen here. First, the stylesheet puts the words CDATA-START and CDATA-END in the resulting XML document. This is because the resulting document is KML to be fed into Google Maps, which will take the HTML contents of the description tag and put them verbatim into the Placemark's pop-up bubble, treating them as text rather than as XML elements. Therefore you need to wrap the contents of the <description> element in a CDATA construct, but you can't put one verbatim in the stylesheet or the XSLT processor will ignore its contents. The solution here is to leave text markers in place of the CDATA start and end tags, and to use a regular expression replacement in the calling PHP code to substitute the actual CDATA start and end markers in the result of the XSLT transformation, as in Listing 7.

Processing the individual listings

The second thing that happens inside the description element is the call to <xsl:apply-templates>, which selects all listings with the address of the current (group leader) listing, including the group leader itself. It passes them into the template matching on listing elements in listing-within-group mode (see Listing 7). After the description element, this template creates the point element, specifying the longitude and latitude of the Placemark on the map. You can also specify the altitude if it's appropriate to render the data.

Listing 7. <xsl:template> that creates the <div> element of each listing within the Placemark's description
<xsl:template match="listing" mode="listing-within-group">
    <div>
      APT <xsl:value-of select="apt_no"/>: 
      $<xsl:value-of select="rent"/>, 
      <xsl:value-of select="notes"/>
    </div>
</xsl:template>

This last template in the stylesheet matches on listings in listing-within-group mode, to create a <div> element for the listing containing its apartment number, rent, and miscellaneous notes about it. Here you can include any arbitrary HTML, including CSS style attributes.

Applying the stylesheet to the XML data in PHP

The last step is actually to apply the XSLT stylesheet to the XML data. To provide XSLT transformation ability, PHP 5 provides the XSL module built on libxslt, an implementation of the XSL specification (see Resources for a link). The XSL module enables you to apply an XSL document to an XML document as in Listing 8.

Listing 8. Producing the KML data in PHP
function doXslt($xml, $xsl) {

  $xmlDOM = DOMDocument::loadXML($xml);
  $xslDOM = DOMDocument::loadXML($xsl);
  
  $processor = new XSLTProcessor();  
  $processor->importStyleSheet($xslDOM);

  return $processor->transformToXML($xmlDOM);
}

$xml = fetchListingsAsXml();

$xsl = file_get_contents ('listings-xml-to-kml.xsl');

$kml = doXslt($xml, $xsl);

$kml = ereg_replace('CDATA-START', '<![CDATA[', $kml);
$kml = ereg_replace('CDATA-END', ']]>', $kml);

header("content-type: application/xml");
echo $kml;

The XSLTProcessor() function requires its source document and stylesheet as a DOMDocument object rather than as strings. Here doXslt() creates DOMDocuments from the provided $xml and $xsl strings, creates an XSLTProcessor, imports the supplied stylesheet into the processor, and transforms the provided XML document according to the stylesheet. You use ereg_replace() to substitute the actual CDATA tag for the CDATA-START and CDATA-END markers introduced in the stylesheet. With a content-type of xml, your KML feed is ready for Google Maps.


Viewing the KML overlay in Google Maps

To view the KML data overlaid on a Google Map, point your browser to http://maps.google.com/maps?q=http://YOUR-SERVER/listings-as-kml.php?v=2, where YOUR-SERVER is the URL of the server that runs your PHP script. Google caches KML files to avoid hitting your server every time someone views your KML data. To enforce a refresh you need to specify a query parameter that changes each time, such as v=2, which specifies a different number each time you want Google to re-fetch the KML data.

That URL will produce a Google Maps page zoomed to a depth that fits all of your Placemarks on the map in Figure 2.

Figure 2. Google Maps page displaying the KML data
Google Maps page displaying the KML data

To the left of the map, Figure 3 shows a list of all your Placemarks, with the <name> and <description> of KML document displayed above them.

Figure 3. The list of Placemarks to the left of the Google Map
The list of Placemarks to the left of the Google Map

Click one of the Placemarks in the map to display its pop-up balloon, as shown in Figure 4.

Figure 4. Pop-up balloon for Placemark
Pop-up balloon for Placemark shows the name and description

Displaying the KML overlay using the Google Maps API

You can also load the KML data into a map that you display using the Google Maps API (JavaScript), as in Listing 9.

Listing 9. Overlaying KML data using Google Maps API
function load() {
      if (GBrowserIsCompatible()) {

        var map = new GMap2(document.getElementById("map"));

        map.addControl(new GLargeMapControl());
        map.addControl(new GMapTypeControl());

        map.setCenter(new GLatLng(40.743884, -73.974666), 13);

        var kmlUrl = "http://YOUR-SERVER/listings-as-kml.php?v=" 
+ Math.round(Math.random() * 10000000000);

        var kml = new GGeoXml(kmlUrl); 
        map.addOverlay(kml);
      }
    }

Here the JavaScript creates the KML feed URL, appending a large random number to (almost) guarantee that Google fetches the fresh KML data. The GGeoXml object loads KML data and provides some utility functions, and the call to addOverlay() displays the KML data on the map in the page, as in Figure 5.

Figure 5. Overlaying KML data using Google Maps API
Overlaying KML data using Google Maps API

Viewing the KML overlay in Google Earth

You can also view your KML data in Google Earth. Save the KML data as a file on your local computer (with a .kml extension or Google Earth won't open it). First, download and install Google Earth (see Resources), and through the File menu open your KML file as in Figure 6.

Figure 6. KML overlay loaded into Google Earth
KML overlay loaded into Google Earth

In this picture the yellow push pins represent your Placemarks. Click one of them to display the same HTML you provided for the Google Maps pop-up bubble.


Summary

In this two-part article series, to turn street addresses into their geographical coordinates for storage in the database, you called the Google Geocoder Web service from PHP. You then produced XML data from MySQL using stored procedures, and an XSLT stylesheet to transform the data into a KML overlay to view in Google Maps and Google Earth.

At this point the sky's the limit. These articles only touched the surface of what's possible, especially since you can create 3D polylines and polygons in KML, not just Placemarks displaying textual information. You can leverage Google Maps, Google Earth, and the Google Geocoder on almost any Web site that deals with address information, and with XSLT you can transform any XML data that contains coordinate data into exciting KML overlays.


Download

DescriptionNameSize
Part 2 example source codegoogle-maps-series-code.tar100KB

Resources

Learn

  • Overlay data on maps using XSLT, KML, and the Google Maps API, Part 1: Tap into the Google Geocoder Web service (Jake Miles, developerWorks, August 2008): With Google's Geocoder Web service, look up postal codes and address coordinates for storage in a database. Add XSLT and KML to overlay a map with the locations in an example app for a real-estate brokerage.
  • XML output code snippets: Get Erik Wetterberg's clever stored procedures for Mysql from MySQL Forge.
  • Erik Wetterberg's blog: Visit Erik's blog for examples of XML output from xml output from SQL functions.
  • XSLT ninja Jeni Tennison's site: Learn how to take a list of elements and arrange them into groups using the Muenchian method.
  • KML Tutorial: KML is a file format used to display geographic data in an Earth browser such as Google Earth, Google Maps, and Google Maps for mobile. This tutorial from Google is a great place to start.
  • Google Maps API sign up: Sign up for a Google Maps API key.
  • Introducing Google's Geocoding Service: Learn about the newest addition to the Google Maps API that allows you to map locations without the use of third-party solutions to convert mailing addresses to their corresponding coordinates.
  • Google Maps API Reference: Find the error codes for the Geocoder service response.
  • Introduction to XSLT (Nicholas Chase, developerWorks, January 2007): Learn to create XSLT stylesheets. This tutorial also covers the basics of XPath, which enables you to select specific parts of an XML document.
  • Get started with XPath (Bertrand Portier, developerWorks, 2004): Explore XPath, the W3C standard. In this tutorial, learn what XPath is, the syntax and semantics of the XPath language, how to use XPath location paths, how to use XPath expressions, how to use XPath functions, and how XPath relates to XSLT.
  • XML.org: Visit this site to see information on a wide range of XML schemas and standards, including SOAP, WSDL, and SVG.
  • IBM XML certification: Find out how you can become an IBM-Certified Developer in XML and related technologies.
  • XML technical library: See the developerWorks XML Zone for a wide range of technical articles and tips, tutorials, standards, and IBM Redbooks.
  • developerWorks technical events and webcasts: Stay current with technology in these sessions.
  • The technology bookstore: Browse for books on these and other technical topics.
  • developerWorks podcasts: Listen to interesting interviews and discussions for software developers.

Get products and technologies

  • libxslt: Get PHP 5 and the XSL module built on libxslt, an implementation of the XSL specification. With the XSL module, apply an XSL document to XML.
  • Google Earth: Download and install Google Earth which offers maps and satellite images for complex or pinpointed regional searches.
  • IBM trial software for product evaluation: Build your next project with trial software available for download directly from developerWorks, including application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into XML on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=XML, Web development, Open source
ArticleID=335912
ArticleTitle=Overlay data on maps using XSLT, KML, and the Google Maps API, Part 2: Transform and use the data
publish-date=09092008