sing PHP and MySQL to create KML ~ COMPUTATIONAL SCIENCES

sing PHP and MySQL to create KML

Posted by Unknown on 6:04 PM with No comments

This tutorial is intended for developers who are familiar with PHP and MySQL and want to learn how to generate KML from a MySQL database. For this tutorial, you will be creating two scripts that dynamically generate KML from a database of locations in Seattle. The first script creates a collection of points, with two types of places—restaurants and bars—delineated by distinguishing icons. When the user clicks a marker, a balloon displays name and address information. The second script creates a line that connects all the restaurants. The tutorial also shows how to create a Google Map that displays the KML files and a NetworkLink file that points to the KML file and enables the user to open it in Google Earth.

This tutorial is based on the article Using PHP/MySQL with Google Maps written by Pamela Fox, which shows how to export data from a MySQL table to Google Maps using PHP. You can skip the first two steps of this tutorial if you have read Pamela's article. The remainder of the tutorial is significantly different since it deals with KML.

The tutorial is broken up into the following steps:

Step 1: Creating the table

When you create the MySQL table, you want to pay particular attention to the lat and lng attributes. With the current zoom capabilities of Google Maps, you should need only 6 digits of precision after the decimal. To keep the storage space required for our table at a minimum, you can specify that the lat and lng attributes are floats of size (10,6). That will let the fields store 6 digits after the decimal, plus up to 4 digits before the decimal, e.g. -123.456789 degrees. Your table should also have an id attribute to serve as the primary key, and a type attribute to distinguish between restaurants and bars.

Note: This tutorial uses location data that already has latitude and longitude information needed to plot corresponding markers. If you're trying to use your own data that doesn't yet have that information, use a batch geocoding service to convert the addresses into latitudes/longitudes. Some sites make the mistake of geocoding addresses each time a page loads, but doing so results in slower page loads and unnecessary repeat geocodes. It's always better to hard-code the latitude/longitude information when possible.

You can create the table data in one of two ways—either using the phpMyAdmin interface or using SQL commands. Here's how you would create the table using the phpMyAdmin interface.

Here's the corresponding SQL statement that creates the table. phpsqlajax_createtable.sql:

CREATE TABLE 'markers' (
 'id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 'name' VARCHAR( 60 ) NOT NULL ,
 'address' VARCHAR( 80 ) NOT NULL ,
 'lat' FLOAT( 10, 6 ) NOT NULL ,
 'lng' FLOAT( 10, 6 ) NOT NULL ,
 'type' VARCHAR( 30 ) NOT NULL
) ENGINE = MYISAM ;

Step 2: Populating the table

After creating the table, it's time to populate it with data. Sample data for 10 Seattle places is provided below. In phpMyAdmin, you can use the IMPORT tab to import various file formats, including CSV (comma-separated values). Microsoft Excel and Google Spreadsheets both export to CSV format, so you can easily transfer data from spreadsheets to MySQL tables through exporting/importing CSV files.

Here's the sample data in CSV format. phpsqlajax_data.csv:

Pan Africa Market,'1521 1st Ave, Seattle, WA',47.608941,-122.340145,restaurant
Buddha Thai & Bar,'2222 2nd Ave, Seattle, WA',47.613591,-122.344394,bar
The Melting Pot,'14 Mercer St, Seattle, WA',47.624562,-122.356442,restaurant
Ipanema Grill,'1225 1st Ave, Seattle, WA',47.606366,-122.337656,restaurant
Sake House,'2230 1st Ave, Seattle, WA',47.612825,-122.34567,bar
Crab Pot,'1301 Alaskan Way, Seattle, WA',47.605961,-122.34036,restaurant
Mama's Mexican Kitchen,'2234 2nd Ave, Seattle, WA',47.613975,-122.345467,bar
Wingdome,'1416 E Olive Way, Seattle, WA',47.617215,-122.326584,bar
Piroshky Piroshky,'1908 Pike pl, Seattle, WA',47.610127,-122.342838,restaurant

Here's a screenshot of the import options used to transform this CSV into table data:

Here are the corresponding SQL statements. phpsqlajax_data.sql:

INSERT INTO 'markers' ('name', 'address', 'lat', 'lng', 'type') VALUES ('Pan Africa Market', '1521 1st Ave, Seattle, WA', '47.608941', '-122.340145', 'restaurant');
INSERT INTO 'markers' ('name', 'address', 'lat', 'lng', 'type') VALUES ('Buddha Thai & Bar', '2222 2nd Ave, Seattle, WA', '47.613591', '-122.344394', 'bar');
INSERT INTO 'markers' ('name', 'address', 'lat', 'lng', 'type') VALUES ('The Melting Pot', '14 Mercer St, Seattle, WA', '47.624562', '-122.356442', 'restaurant');
INSERT INTO 'markers' ('name', 'address', 'lat', 'lng', 'type') VALUES ('Ipanema Grill', '1225 1st Ave, Seattle, WA', '47.606366', '-122.337656', 'restaurant');
INSERT INTO 'markers' ('name', 'address', 'lat', 'lng', 'type') VALUES ('Sake House', '2230 1st Ave, Seattle, WA', '47.612825', '-122.34567', 'bar');
INSERT INTO 'markers' ('name', 'address', 'lat', 'lng', 'type') VALUES ('Crab Pot', '1301 Alaskan Way, Seattle, WA', '47.605961', '-122.34036', 'restaurant');
INSERT INTO 'markers' ('name', 'address', 'lat', 'lng', 'type') VALUES ('Mama\'s Mexican Kitchen', '2234 2nd Ave, Seattle, WA', '47.613975', '-122.345467', 'bar');
INSERT INTO 'markers' ('name', 'address', 'lat', 'lng', 'type') VALUES ('Wingdome', '1416 E Olive Way, Seattle, WA', '47.617215', '-122.326584', 'bar');
INSERT INTO 'markers' ('name', 'address', 'lat', 'lng', 'type') VALUES ('Piroshky Piroshky', '1908 Pike pl, Seattle, WA', '47.610127', '-122.342838', 'restaurant');

Step 3: Using PHP to output KML

At this point, you should have a table named "markers" filled with sample data. You now need to write some PHP statements to export the table data into a KML format. If you've never written PHP to connect to a MySQL database, you should visit php.net and read up on mysql_connect, mysql_select_db, my_sql_query, and mysql_error.

You should put your database connection information in a separate file. This is generally a good idea whenever you're using PHP to access a database, as it keeps your confidential information in a file that you won't be tempted to share. In the Maps API forum, we've occasionally had people accidentally publish their database connection information when they were just trying to debug their code. The file should look like this, but with your own database information filled in. phpsqlajax_dbinfo.php:

<?
 $username = 'username';
 $password = 'password';
 $database = 'database';
 $server = 'server'
?>

Using PHP5's DOM functions to output KML

Here's where the new stuff starts. In Pamela Fox's earlier article, she presented PHP 4 code that used the dom_xml extension to create a simple markers file that was later parsed using JavaScript. For this tutorial, you want to produce KML. Instead of going through JavaScript, you can specify Placemark styling directly in KML. This tutorial will show code that uses both PHP 5's integrated DOM libraries, and the PHP 4 dom_xml extension.

First, check your configuration or try initializing a DOMDocument() to determine if your server's PHP has DOM functionality on. If you do have access to the DOM, you can use it to create XML nodes, append child nodes, and output an XML document. Since KML is an XML markup language, this works for KML as well. If DOM is not available on your server, try using either the dom_xml or echo method described below.

Once you've determined that you can continue with DOM, start by creating different placemarks for each row in the markers table. In PHP, initialize a new XML document and create the "kml" parent node. Add the KML namespace as an attribute. After creating the basic structure of a KML <document> element, construct the two styles—one for restaurants and one for bars—that will later be referenced by Placemarks through the <styleUrl> element.

Next, connect to the database, execute a

SELECT
*

(select all) query on the markers table, and iterate through the results. For each row in the table (each location), create a new <Placemark> element. Extract information from the row and use it to create child elements of the <Placemark>, <name>, <description>, <styleUrl>, and <Point>. Assign the <styleUrl> element a value depending on the value of the type column for that row. Then give the <Point> element a child element <coordinates>, and combine the values of the lng and lat columns as its value.

The PHP file below creates a KML file with the proper HTML headers. It assigns the value of the name column to the <name> element, and the value of the address to the <description> element. After generating the KML from this script, you should verify the results with a text editor or browser. phpsql_genkml.php:

<?php
require('phpsqlajax_dbinfo.php');

// Opens a connection to a MySQL server.

$connection = mysql_connect ($server, $username, $password);

if (!$connection) 
{
  die('Not connected : ' . mysql_error());
}
// Sets the active MySQL database.
$db_selected = mysql_select_db($database, $connection);

if (!$db_selected) 
{
  die('Can\'t use db : ' . mysql_error());
}

// Selects all the rows in the markers table.
$query = 'SELECT * FROM markers WHERE 1';
$result = mysql_query($query);

if (!$result) 
{
  die('Invalid query: ' . mysql_error());
}

// Creates the Document.
$dom = new DOMDocument('1.0', 'UTF-8');

// Creates the root KML element and appends it to the root document.
$node = $dom->createElementNS('http://earth.google.com/kml/2.1', 'kml');
$parNode = $dom->appendChild($node);

// Creates a KML Document element and append it to the KML element.
$dnode = $dom->createElement('Document');
$docNode = $parNode->appendChild($dnode);

// Creates the two Style elements, one for restaurant and one for bar, and append the elements to the Document element.
$restStyleNode = $dom->createElement('Style');
$restStyleNode->setAttribute('id', 'restaurantStyle');
$restIconstyleNode = $dom->createElement('IconStyle');
$restIconstyleNode->setAttribute('id', 'restaurantIcon');
$restIconNode = $dom->createElement('Icon');
$restHref = $dom->createElement('href', 'http://maps.google.com/mapfiles/kml/pal2/icon63.png');
$restIconNode->appendChild($restHref);
$restIconstyleNode->appendChild($restIconNode);
$restStyleNode->appendChild($restIconstyleNode);
$docNode->appendChild($restStyleNode);

$barStyleNode = $dom->createElement('Style');
$barStyleNode->setAttribute('id', 'barStyle');
$barIconstyleNode = $dom->createElement('IconStyle');
$barIconstyleNode->setAttribute('id', 'barIcon');
$barIconNode = $dom->createElement('Icon');
$barHref = $dom->createElement('href', 'http://maps.google.com/mapfiles/kml/pal2/icon27.png');
$barIconNode->appendChild($barHref);
$barIconstyleNode->appendChild($barIconNode);
$barStyleNode->appendChild($barIconstyleNode);
$docNode->appendChild($barStyleNode);

// Iterates through the MySQL results, creating one Placemark for each row.
while ($row = @mysql_fetch_assoc($result))
{
  // Creates a Placemark and append it to the Document.

  $node = $dom->createElement('Placemark');
  $placeNode = $docNode->appendChild($node);

  // Creates an id attribute and assign it the value of id column.
  $placeNode->setAttribute('id', 'placemark' . $row['id']);

  // Create name, and description elements and assigns them the values of the name and address columns from the results.
  $nameNode = $dom->createElement('name',htmlentities($row['name']));
  $placeNode->appendChild($nameNode);
  $descNode = $dom->createElement('description', $row['address']);
  $placeNode->appendChild($descNode);
  $styleUrl = $dom->createElement('styleUrl', '#' . $row['type'] . 'Style');
  $placeNode->appendChild($styleUrl);

  // Creates a Point element.
  $pointNode = $dom->createElement('Point');
  $placeNode->appendChild($pointNode);

  // Creates a coordinates element and gives it the value of the lng and lat columns from the results.
  $coorStr = $row['lng'] . ','  . $row['lat'];
  $coorNode = $dom->createElement('coordinates', $coorStr);
  $pointNode->appendChild($coorNode);
}

$kmlOutput = $dom->saveXML();
header('Content-type: application/vnd.google-earth.kml+xml');
echo $kmlOutput;
?>

Using PHP 4's dom_xml to output KML

PHP 4's dom_xml functions are very similar to PHP 5's DOM. phpsql_genkml2.php:

<?php
require('phpsqlajax_dbinfo.php');

// Opens a connection to a MySQL server.
$connection=mysql_connect ($server, $username, $password);
if (!$connection) 
{
  die('Not connected : ' . mysql_error());
}
// Sets the active MySQL database.
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) 
{
  die ('Can\'t use db : ' . mysql_error());
}

// Selects all the rows in the markers table.
$query = 'SELECT * FROM markers WHERE 1';
$result = mysql_query($query);
if (!$result) 
{
  die('Invalid query: ' . mysql_error());
}

// Creates the Document.
$dom = new domxml_new_doc('1.0');

// Creates the root KML element and appends it to the root document.
$node = $dom->create_element_ns('http://earth.google.com/kml/2.1', 'kml');
$parNode = $dom->append_child($node);

// Creates a KML Document element and append it to the KML element.
$dnode = $dom->create_element('Document');
$docNode = $parNode->append_child($dnode);
 
//Creates the two Style elements, one for restaurant and one for bar, and append the elements to the Document element.
$restStyleNode = $dom->create_element('Style');
$restStyleNode->set_attribute('id', 'restaurantStyle');
$restIconstyleNode = $dom->create_element('IconStyle');
$restIconstyleNode->set_attribute('id', 'restaurantIcon');
$restIconNode = $dom->create_element('Icon');
$restHref = $dom->create_element('href', 'http://maps.google.com/mapfiles/kml/pal2/icon63.png');
$restIconNode->append_child($restHref);
$restIconstyleNode->append_child($restIconNode);
$restStyleNode->append_child($restIconstyleNode);
$docNode->append_child($restStyleNode);
$barStyleNode = $dom->create_element('Style');
$barStyleNode->set_attribute('id', 'barStyle');
$barIconstyleNode = $dom->create_element('IconStyle');
$barIconstyleNode->set_attribute('id', 'barIcon');
$barIconNode = $dom->create_element('Icon');
$barHref = $dom->create_element('href', 'http://maps.google.com/mapfiles/kml/pal2/icon27.png');
$barIconNode->append_child($barHref);
$barIconstyleNode->append_child($barIconNode);
$barStyleNode->append_child($barIconstyleNode);
$docNode->append_child($barStyleNode);

// Iterates through the MySQL results, creating one Placemark for each row.
while ($row = @mysql_fetch_assoc($result))
{
  // Creates a Placemark and append it to the Document.
  $node = $dom->create_element('Placemark');
  $placeNode = $docNode->append_child($node);
  // Creates an id attribute and assign it the value of id column.
  $placeNode->set_attribute('id', 'placemark' . $row['id']);

  // Create name, and description elements and assigns them the values of the name and address columns from the results.
  $nameNode = $dom->create_element('name',htmlentities($row['name']));
  $placeNode->append_child($nameNode);
  $descNode = $dom->  create_element('description', $row['address']);
  $placeNode->append_child($descNode);
  $styleUrl = $dom->create_element('styleUrl', '#' . $row['type'] . 'Style');
  $placeNode->append_child($styleUrl);
 // Creates a Point element.
  $pointNode = $dom->create_element('Point');
  $placeNode->append_child($pointNode);
   
  // Creates a coordinates element and gives it the value of the lng and lat columns from the results.
  $coorStr = $row['lng'] . ','  . $row['lat'];
  $coorNode = $dom->create_element('coordinates', $coorStr);
  $pointNode->append_child($coorNode);
}

$kmlOutput = $dom->dump_mem(TRUE, 'UTF-8');
header('Content-type: application/vnd.google-earth.kml+xml');
echo $kmlOutput;
?>

As you can see, most of the differences are solved by turning the camel case (createElement) functions into all lower case with an underscore ("_") to separate the words in the function name. The exception to that rule is domxml_new_doc, which PHP 5 replaces with DOMDocument. Also, using dom_xml, you set the encoding when you dump the file to memory, not when you create the file.

Using PHP's echo to output KML

If you don't have access to PHP's DOM functions, then you can simply output the KML with the echo function.

Connect to the database and execute the SELECT * (select all) query on the markers table.
Create an array of strings that make up the basic structure of the KML document.
Then iterate through the query results, adding an element to the array for each row in the table (each location).
Create the Placemark element for that row, passing the name column through the htmlentities function in case there are any special entities in them.
Finish the script by joining the array into one big string, echoing out the headers, and then echoing out the KML string.

The PHP file that does all this is shown below. phpsql_genkml3.php:

<?php
require('phpsqlajax_dbinfo.php');

 // Opens a connection to a MySQL server.
$connection = mysql_connect ($server, $username, $password);
if (!$connection) 
{
  die('Not connected : ' . mysql_error());
}

// Sets the active MySQL database.
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) 
{
  die ('Can\'t use db : ' . mysql_error());
}

 // Selects all the rows in the markers table.
 $query = 'SELECT * FROM markers WHERE 1';
 $result = mysql_query($query);
 if (!$result) 
 {
  die('Invalid query: ' . mysql_error());
 }

// Creates an array of strings to hold the lines of the KML file.
$kml = array('<?xml version="1.0" encoding="UTF-8"?>');
$kml[] = '<kml xmlns="http://earth.google.com/kml/2.1">';
$kml[] = ' <Document>';
$kml[] = ' <Style id="restaurantStyle">';
$kml[] = ' <IconStyle id="restuarantIcon">';
$kml[] = ' <Icon>';
$kml[] = ' <href>http://maps.google.com/mapfiles/kml/pal2/icon63.png</href>';
$kml[] = ' </Icon>';
$kml[] = ' </IconStyle>';
$kml[] = ' </Style>';
$kml[] = ' <Style id="barStyle">';
$kml[] = ' <IconStyle id="barIcon">';
$kml[] = ' <Icon>';
$kml[] = ' <href>http://maps.google.com/mapfiles/kml/pal2/icon27.png</href>';
$kml[] = ' </Icon>';
$kml[] = ' </IconStyle>';
$kml[] = ' </Style>';

// Iterates through the rows, printing a node for each row.
while ($row = @mysql_fetch_assoc($result)) 
{
  $kml[] = ' <Placemark id="placemark' . $row['id'] . '">';
  $kml[] = ' <name>' . htmlentities($row['name']) . '</name>';
  $kml[] = ' <description>' . htmlentities($row['address']) . '</description>';
  $kml[] = ' <styleUrl>#' . ($row['type']) .'Style</styleUrl>';
  $kml[] = ' <Point>';
  $kml[] = ' <coordinates>' . $row['lng'] . ','  . $row['lat'] . '</coordinates>';
  $kml[] = ' </Point>';
  $kml[] = ' </Placemark>';
 
} 

// End XML file
$kml[] = ' </Document>';
$kml[] = '</kml>';
$kmlOutput = join("\n", $kml);
header('Content-type: application/vnd.google-earth.kml+xml');
echo $kmlOutput;
?>

Checking that the KML output works

Call this PHP script from the browser to make sure it's producing valid KML. If the script is working correctly, the KML output looks like this. phpsqlkml_expectedoutput.kml:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns = "http://earth.google.com/kml/2.1">
  <Document>
    <Style id="restaurantStyle">
      <IconStyle id="restuarantIcon">
      <Icon>
        <href>http://maps.google.com/mapfiles/kml/pal2/icon63.png</href>
      </Icon>
      </IconStyle>
    </Style>
    <Style id="barStyle">
      <IconStyle id="barIcon">
      <Icon>
        <href>http://maps.google.com/mapfiles/kml/pal2/icon27.png</href>
      </Icon>
      </IconStyle>
      </Style>
    <Placemark id="placemark1">
      <name>Pan Africa Market</name>
      <description>1521 1st Ave, Seattle, WA</description>
      <styleUrl>#restaurantStyle</styleUrl>
      <Point>
        <coordinates>-122.340141,47.608940</coordinates>
      </Point>
    </Placemark>
    <Placemark id="placemark2">
      <name>Buddha Thai &amp; Bar</name>
      <description>2222 2nd Ave, Seattle, WA</description>
      <styleUrl>#barStyle</styleUrl>
      <Point>
        <coordinates>-122.344391,47.613590</coordinates>
      </Point>
    </Placemark>
    <Placemark id="placemark3">
      <name>The Melting Pot</name>
      <description>14 Mercer St, Seattle, WA</description>
      <styleUrl>#restaurantStyle</styleUrl>
      <Point>
        <coordinates>-122.356445,47.624561</coordinates>
      </Point>
    </Placemark>
    <Placemark id="placemark4">
      <name>Ipanema Grill</name>
      <description>1225 1st Ave, Seattle, WA</description>
      <styleUrl>#restaurantStyle</styleUrl>
      <Point>
        <coordinates>-122.337654,47.606365</coordinates>
      </Point>
    </Placemark>
    <Placemark id="placemark5">
      <name>Sake House</name>
      <description>2230 1st Ave, Seattle, WA</description>
      <styleUrl>#barStyle</styleUrl>
      <Point>
      <coordinates>-122.345673,47.612823</coordinates>
      </Point>
    </Placemark>
    <Placemark id="placemark6">
      <name>Crab Pot</name>
      <description>1301 Alaskan Way, Seattle, WA</description>
      <styleUrl>#restaurantStyle</styleUrl>
      <Point>
        <coordinates>-122.340363,47.605961</coordinates>
      </Point>
    </Placemark>
    <Placemark id="placemark7">
      <name>Mama's Mexican Kitchen</name>
      <description>2234 2nd Ave, Seattle, WA</description>
      <styleUrl>#barStyle</styleUrl>
      <Point>
        <coordinates>-122.345467,47.613976</coordinates>
      </Point>
    </Placemark>
    <Placemark id="placemark8">
      <name>Wingdome</name>
      <description>1416 E Olive Way, Seattle, WA</description>
      <styleUrl>#barStyle</styleUrl>
      <Point>
        <coordinates>-122.326584,47.617214</coordinates>
      </Point>
    </Placemark>
    <Placemark id="placemark9">
      <name>Piroshky Piroshky</name>
      <description>1908 Pike pl, Seattle, WA</description>
      <styleUrl>#restaurantStyle</styleUrl>
      <Point>
        <coordinates>-122.342834,47.610126</coordinates>
      </Point>
    </Placemark>
 </Document>
</kml>

Making a Line

One of the best things about databases is their ability to combine information. For instance, a natural expression of a series of points is a line, or in KML, a <linestring>. This is actually simpler to accomplish than creating a series of points. Create a script that creates the structure of a single Placemark. Place a <linestring> element in the Placemarks. Then query the database for all the coordinates, ordered by the id of the row.

Here's a sample PHP script that creates a <linestring> between all the restaurants, in order of their id, at a 100 meter altitude, with extrusion. While that won't show up on Google Maps, in Google Earth this script creates a 100-meter-tall wall running through all the restaurant locations in Google Earth, in the order they were entered into the database. phpsql_genkml_ls.php:

<?php
require('phpsqlajax_dbinfo.php');

// Opens a connection to a MySQL server
$connection = mysql_connect ($server, $username, $password);

if (!$connection) 
{
   die('Not connected : ' . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);

if (!$db_selected) 
{
  die ('Can\'t use db : ' . mysql_error());
}

// Select all the rows in the markers table

$query = " SELECT GROUP_CONCAT(lng, ',', lat, ',', '100' separator ' ') AS coordinates FROM markers WHERE type = 'restaurant';";

$result = mysql_query($query);
if (!$result) 
{
  die('Invalid query: ' . mysql_error());
}

// Start KML file, create parent node
$dom = new DOMDocument('1.0','UTF-8');

//Create the root KML element and append it to the Document
$node = $dom->createElementNS('http://earth.google.com/kml/2.1','kml');
$parNode = $dom->appendChild($node);

//Create a Folder element and append it to the KML element
$fnode = $dom->createElement('Folder');
$folderNode = $parNode->appendChild($fnode);

//Iterate through the MySQL results
$row = @mysql_fetch_assoc($result);

//Create a Placemark and append it to the document
$node = $dom->createElement('Placemark');
$placeNode = $folderNode->appendChild($node);

//Create an id attribute and assign it the value of id column
$placeNode->setAttribute('id','linestring1');

//Create name, description, and address elements and assign them the values of 
//the name, type, and address columns from the results

$nameNode = $dom->createElement('name','My path');
$placeNode->appendChild($nameNode);
$descNode= $dom->createElement('description', 'This is the path that I took through my favorite restaurants in Seattle');
$placeNode->appendChild($descNode);

//Create a LineString element
$lineNode = $dom->createElement('LineString');
$placeNode->appendChild($lineNode);
$exnode = $dom->createElement('extrude', '1');
$lineNode->appendChild($exnode);
$almodenode =$dom->createElement(altitudeMode,'relativeToGround');
$lineNode->appendChild($almodenode);

//Create a coordinates element and give it the value of the lng and lat columns from the results

$coorNode = $dom->createElement('coordinates',$row['coordinates']);
$lineNode->appendChild($coorNode);
$kmlOutput = $dom->saveXML();

//assign the KML headers. 
header('Content-type: application/vnd.google-earth.kml+xml');
echo $kmlOutput;
?>

The output of that script looks like this. phpsqlkml_expectedoutput_ls.kml:

<?xml version='1.0' encoding='UTF-8'?>
<kml xmlns='http://earth.google.com/kml/2.1'>
  <Folder>
    <Placemark id='linestring1'>
      <name>My path</name>
      <description>This is the path that I took through my favorite restaurants in Seattle</description>
      <LineString>
        <extrude>1</extrude>
        <altitudeMode>relativeToGround</altitudeMode>
        <coordinates>-122.340141,47.608940,100 -122.356445,47.624561,100 
                     -122.337654,47.606365,100 -122.340363,47.605961,100
                     -122.342834,47.610126,100
        </coordinates>
     </LineString>
    </Placemark>
  </Folder>
</kml>

Step 4: Displaying your KML files

Display in Google Earth

You can now easily display this data in Google Earth. The best way to do it is to create a NetworkLink file that points to the script. If you are updating your data frequently, you can set the refresh rate to match how often you update it. Here's an example of a file that would accomplish that. phpmysql_kmlnl.kml:

<?xml version='1.0' encoding='UTF-8'?>
<kml xmlns = 'http://earth.google.com/kml/2.1'>
  <Folder>
    <NetworkLink>
      <Link>
        <href>http://example.com/phpsql_genkml.kml</href>
        <refreshMode>onInterval</refreshMode>
        <refreshInterval>3600</refreshInterval>
      </Link>
    </NetworkLink>
    <NetworkLink>
      <Link>
        <href>http://example.com/phpsql_genkml_ls.kml</href>
        <refreshMode>onInterval</refreshMode>
        <refreshInterval>3600</refreshInterval>
      </Link>
    </NetworkLink>
  </Folder>
</kml>

Change the <href> element to the path to the script on your server. Open up phpmysql_kmlnl.kml with Google Earth. You will see this:

Displaying the NetworkLink in Google Earth

To view the same file in Google Maps, all you need to do is create a map and add the link to the script or to the NetworkLink file. For instance:

function load() 
{
  var map;
  var geoXml;

  if (GBrowserIsCompatible()) 
  {
    map = new GMap2(document.getElementById('map'));
    map.addControl(new GSmallMapControl());
    map.addControl(new GMapTypeControl());
    geoXml = new GGeoXml('http://example.com/phpmysql_kmlnl.kml');
    map.addOverlay(geoXml);
    map.setCenter(new GLatLng(47.613976,-122.345467), 13);
  }
}

Which produces a map like this:

Step 5: Where to go from here

So now that you have this database, what do you do with it? Well, the great thing about databases is that you can add to them. The great thing about KML served out of a database is that you can refresh your content. Put those together, and you get a lot of power.

And there's much more you can do in KML. Take advantage of some features unique to Google Earth, such as <NetworkLink> files that use <viewFormat>. This feature allows the <networklink> to send parameters to your script. You can use these parameters to modify what data gets sent back. Or use <TimeStamp> and <TimeSpan> which allow you to do animation of KML Placemarks over a time period. Create more complex table structures to store things like <Polygons> in related tables. Or create a web page that allows other people to enter data into your database, which then gets refreshed the next time the script is called. The possibilities are endless.

COMPUTATIONAL SCIENCES

Tuesday, December 30, 2014