GoogleEarth Dynamic KML

Description

Google Earth with OSM fuel stations

Wouldn't it be cool to show OpenStreetMap data/poi's into GoogleEarth ? And even cooler would be to download only data that's visible in the current view. And super cool would be to retrieve just what you want by adjusting an url parameter. Well, good news.. it all can be done, and it's even not that hard to make.

Important legal note

Do not use use data from Google Earth to add or change anything in Open Street Map, ie. don't look at a satellite image from Google and add or change streets of other things. Somebody has a copyright on the data shown in Google Earth and using any data from it will compromise the OSM database. See the Legal FAQ for more information.

HowTo

Requirements:

• A server with
  • Some kind of database with openstreetmap data (i'm using the postgres mapnik database of the dutch tileserver)
  • PHP with a script to deliver the dynamic KML data
• GoogleEarth

Server Side

First create a table with poi's into your database. If you have already a mapnik setup, then you can use the planet_osm_point table like in the code below. Change the login data and database related stuff for your system. Maybe you also have to correct the transform (4326) for your projection ! Put the data.php onto your server.

data.php

<?php
	// Creates the KML/XML 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);


	// database stuff
	$pgsql['user'] = 'mapnik'; // username
	$pgsql['db'] = 'osm';      // database
	
	// currently we only support amenity in the url
	$what = $_GET['amenity'];
	if ($what == '') {
		$what = 'restaurant';
	}
	
	$connect_string = ' user=' . $pgsql['user'] . ' dbname=' . $pgsql['db'];
	$pgcon = pg_connect($connect_string); 
	if ($pgcon) { // connected!	
		
		$bbox = $_GET['BBOX']; // get the bbox param from google earth
		list($bbox_south, $bbox_west, $bbox_east, $bbox_north) = split(",", $bbox); // west, south, east, north
                // Get the data from the Database Table (planet_osm_point)
		$sql = "SELECT osm_id, name, x(transform(way,4326)) as lon, y(transform(way, 4326)) as lat FROM planet_osm_point WHERE (amenity='" . $what . "') AND (box(point(" . $bbox_south . "," . $bbox_west . "),point(" . $bbox_east . "," . $bbox_north . ")) ~ transform(way,4326)) LIMIT 100";
	
		// perform query
		$query = pg_query($pgcon, $sql);
		if ($query) {
			if (pg_num_rows($query) > 0) { // found something

				// Iterates through the results, creating one Placemark for each row.
				while ($row = pg_fetch_array($query))
				{
					 // 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['osm_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);

					  // 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['lon'] . ','  . $row['lat'];
					  $coorNode = $dom->createElement('coordinates', $coorStr);
					  $pointNode->appendChild($coorNode);
				}

			} else { // nothing found
			}
		}
		pg_close($pgcon);
	} else {
		// no valid database connection
	}

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

Client Side / Google Earth

Create a network link in GoogleEarth

• goto My places, right click, add, Network Link
• give it a name
• insert the link to your data.php like : http://yourdomain.org/data.php?amenity=fuel
• Open tab refresh and select View based refresh, after camera stops, 4 secs
• hit Ok and the network link icon should indicate it's loading data when you are done zooming into a area

Background info

When you pan/zoom into a new area, after 4 seconds GoogleEarth will download data from our network link url, with some parameters, like amenity and the boundingbox of the current view. The php script will use this information to query the database and build the kml output.

Future stuff

• Remove fixed amenity implementation, so you can query every possible field
• add polygon support, so you can show ways / borders / buildings / area's

links

• Google Earth
• Dynamic KML video on YouTube
• KML reference
• OpenLayers_Dynamic_POI
• OSM_in_Google_Earth
• Query-to-map Supports also an kml-output to Google Earth