No, I'm not going to mention the Planet again (but you can drop me a line if you want to join). I've been having far too much fun with Google Earth (yes, even despite the lack of a Mac version), and I thought I'd jot down a couple of notes on how to roll your own KML files with a few snippets of PHP.

Lots of people are sure to come up with much better and sophisticated examples, but my guess is that most of those will be about how to customize locations, so I decided to show how to generate a tree structure from your data points.

Input and Output

To illustrate one possible way to roll your own data points, let's say you have a result set that looks more or less like this:

Category,District,Name,Longitude,Latitude
Restaurants,Lisbon,Clube do Sushi,38.707420,-9.165709
...

And let's assume you want to display the data in Google Earth like a nested tree, like so:

-+- My Places
 |
 +-+- Art
   |
   +-+- The Hague
     |
     +- Martin Sjardijn

I'm going to assume it is all read from a comma-separated values file, so that you will be able to just grab my code and get something working quickly (if you're an entity-relationship zealot, please bear with me and assume the above is the result of a SQL join or something). I prefer working with "flat" tables to generate XML since I can rely on all columns being present, etc., etc.

Now, to render that in KML format for Google Earth, you have to create Folder tags for each category, with the data points as Placemarks. That's the important bit, so I'll say it again -

Multiple Placemarks have to be inside a Folder

And, of course, you can add other stuff to folders (other folders, polygons, etc.)

Parsing the File

My favorite way to go about this sort of thing (for small data sets) is to build a hash table (in fact, a tree) in memory, since in PHP or Python I can store (serialize or pickle) that to disk and save me the trouble of re-issuing the query and re-parsing the results (you'd be surprised at the sort of extra performance one can get by not issuing database queries willy-nilly).

So here's a function that does just that:

<?
function parseFile($szFilename) {
  $oFile = fopen($szFilename, "r");
  $header = fgets($oFile); // skip the CSV headers
  $aPlaces = array();
  while(($aFields = fgetcsv($oFile, 1024, ",")) !== FALSE) {
    list($category,$district,$name,$lng,$lat) = $aFields;
    $key = md5($lng.$lat); // unique identifier to remove duplicates from large sets
    $aSite = array( 'name' => $name, 'lat' => $lat, 'lng' => $lng );
    $aPlaces[$category][$district][$key] = $aSite;
  }
  fclose($oFile);
  return $aPlaces;
}
?>

Generating the KML

The result is a multi-level hash table that can be rendered as KML as such (there's actually a neater and über-geeky way to to this with Python lambdas, but hey, this is PHP):

<?
function renderTree($aPlaces) {
  $szBuffer = "<Folder><name>My Places</name>\n";
  foreach( $aPlaces as $category => $categories ) {
    $szBuffer .= "<Folder><name>$category</name>\n";
    foreach( $categories as $district => $districts ) {
      $szBuffer .= "<Folder><name>$district</name>\n";
      foreach( $districts as $place ) {
        extract($place);
        $szBuffer .= "<Placemark>";
        $szBuffer .= "<name>$name</name>";
        $szBuffer .= "<Point><coordinates>$lng,$lat,0</coordinates></Point>";
        $szBuffer .= "</Placemark>\n";
      }
      $szBuffer .= "</Folder>\n";
    }
    $szBuffer .= "</Folder>\n";
  }
  $szBuffer .= "</Folder>\n";
  return $szBuffer;
}
?>

Outputting It

Okay, so now all you have to do is:

<?
$szBuffer  = '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
$szBuffer .= '<kml xmlns="http://earth.google.com/kml/2.0">' . "\n";
$szBuffer .= renderTree(parseFile('table.csv'));
$szBuffer .= "</kml>";
header("Content-Type: text/xml; charset=utf8");
echo $szBuffer;
?>

The result is something like this (I've added a few newlines and spaces):

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Folder>
  <name>My Places</name>
  <Folder>
    <name>Art</name>
    <Folder>
      <name>The Hague</name>
      <Placemark>
        <name>Martin Sjardijn</name>
        <Point><coordinates>38.707420,-9.165709,0</coordinates></Point>
      </Placemark>
      <Placemark>
        <name>Someplace Else</name>
        <Point>...</Point>
      </Placemark>
    </Folder>
  </Folder>
</Folder>
</kml>

Just create a new "network link" placemark, set the URL to your PHP script, and presto - your data will show up on Google Earth.

As an example, here's what Bruno generated from his geocaching data (taken from here):

Circling Locations

Okay, but what if you don't want to display placemarks? Well, I knocked up a quick function to draw circles that may come in handy:

The above will generate hexagons of $r meters centered on the coordinates you specify, ready to insert into a coordinates tag like this:

<?
// ...
$szBuffer .= "<LineStyle><color>ffff0000</color></LineStyle>";
$szBuffer .= "<LineString><tessellate>1</tessellate><coordinates>" . circle($lng, $lat, 1000);
$szBuffer .= "</coordinates></LineString>";
?>

The lines will hug the ground, but you can have endless fun putting up cylinders by reading through the tutorial section on polygons.

View-Based Queries

However, the best trick in the book are View-Based queries - these are network link URLs that get queried by sending a bounding box in the BBOX parameter.

You can set the placemark properties to force a refresh when the camera stops, and Google Earth will invoke your PHP script with the view boundaries, therefore saving you the trouble of sending redundant data (that won't get displayed) or enabling you to send more detailed data as the view becomes more precise.

I've used this to excellent effect when querying a fairly large dataset (several thousand entries that generated hundreds of thousands of coordinates), reducing server traffic (and increasing navigation speed) by at least an order of magnitude.

Here's a modified version of the parseFile function above that builds a tree that contains just the locations within the current view (or the whole thing if it's not called with a bounding box):

<?
function parseFile($szFilename) {
  $oFile = fopen($szFilename, "r");
  $header = fgets($oFile); // skip the CSV headers
  $aPlaces = array();
  while(($aFields = fgetcsv($oFile, 1024, ",")) !== FALSE) {
    list($category,$district,$name,$lng,$lat) = $aFields;
    $key = md5($lng.$lat); // unique identifier to remove duplicates from large sets
    $aSite = array( 'name' => $name, 'lat' => $lat, 'lng' => $lng );
    if( isset($_GET['BBOX']) ) {
      list($bl_lng, $bl_lat, $tr_lng, $tr_lat) = preg_split('/,|\s/', $_GET['BBOX']);
      if( $bl_lng < $lng && $tr_lng > $lng && $bl_lat < $lat && $tr_lat > $lat ) {
        $aPlaces[$category][$district][$key] = $aSite;
      }
    }
    else {
      $aPlaces[$category][$district][$key] = $aSite;
    }
  }
  fclose($oFile);
  return $aPlaces;
}
?>