Creating a KML file from MySQL data with PHP

Placemarks from KML

I recently had a need for a PHP script that queries a MySQL database for location-based point data (a LatLong coordinate and some descriptive information) and then creates a KML-file from that data on the fly. A file like this can then easily be loaded into Google Maps, Google Earth and other geospatial applications. In my case, I loaded it into a Google Maps API-based map viewer.

Because a KML-file is in essence a text-based XML-file, it is very easy to create it from a script. The most important part of this is that is must adhere to the correct formatting. For details on this, consult the KML standard definition – especially if you want to include anything other than placemarkers.

If you have a need for a similar implementation, feel free to copy the code below and use it in your project. Just create a new PHP file (e.g. “kml.php”) and paste the script into it. Don’t forget that you will have to modify it for your database setup and table columns (mine were id, name, description, lng, lat). Other than that, it should just work.

You can check whether it works if you paste the file’s URL into the Google Maps search field. The placemarks should then just show up on the map.

<?php

// Define the database info
// -- Modify for your case --
define("DB_SERVER", "mysql");
define("DB_USER", "root");
define("DB_PASS", "");
define("DB_NAME", "my_database");
define("DB_TABLE", "my_db_table");

// Important so that the filetype is correct
header("Content-type: application/xml");

// Print the head of the document
printf("<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<kml xmlns=\"http://earth.google.com/kml/2.0\">
<Document>");

// You can include a default map view using the following lines
// -- Delete this block if you don't need it --
printf("<LookAt>
  <latitude>42.390185</latitude>
  <longitude>-72.528412</longitude>
  <range>1200</range>
  <tilt>0</tilt>
  <heading>0</heading>
</LookAt>");

if ($db = mysqli_connect(DB_SERVER, DB_USER, DB_PASS)) {

  mysqli_select_db($db, DB_NAME);

  $query = "SELECT * FROM " . DB_TABLE;

  // You could add some WHERE statements here to filter the data

  // DESC means newest first
  $query .= " ORDER BY id DESC";

  // Finally query the database data
  $result = mysqli_query($db, $query);

  // Now iterate over all placemarks (rows)
  while ($row = mysqli_fetch_object($result)) {

    // This writes out a placemark with some data
    // -- Modify for your case --
    printf('
      <Placemark id="%d">
        <name>%s says:</name>
        <description>%s</description>
        <Point>
          <coordinates>%f,%f</coordinates>
        </Point>
      </Placemark>',
    htmlspecialchars($row->id),
    htmlspecialchars($row->name),
    htmlspecialchars($row->description),
    htmlspecialchars($row->lng),
    htmlspecialchars($row->lat)
    );

  };

  // Close the database connection
  mysqli_close($db);

};

// And finish the document
printf("
</Document>
</kml>");

?>

This code is really just applicable for simple KML generators. For a more in-depth look at this, go to this page on Google’s Help on KML. Admittedly there also could be a bit more error-handling.

Comments and Reactions