Programming in Heaven

Sathish Kumar P, Remote Sensing Group, Defence Laboratory, Jodhpur

June 2008

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

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

The tutorial is broken up into the following steps:

Creating the table
Populating the table
Using JAVA to output KML
Displaying your KML Files

Step1 : Create 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. This link contains a good list of geocoders: http://groups.google.com/group/Google-Maps-API/web/resources-non-google-geocoders

Here’s the SQL statement that create the table.

CREATE TABLE markers (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60) NOT NULL, address VARCHAR(60) 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.

Here are the SQL statements.

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 JAVA to output KML

At this point, you should have a table named "markers" filled with sample data. You now need to write some JAVA code to export the table data into a KML format.

Using JAVA’s JAXP DOM functions to output KML

Here’s where the new stuff starts. In Pamela Fox’s and Mano Marks’s earlier article, they presented PHP4 and PHP 5 code. This tutorial will show code that uses JAVA’s integrated JAXP DOM libraries.

First we have to make conncetion between MySQL and Java Using JDBC which can be downloaded from the following link and should be set to the CLASSPATH. The code for the connection look like this.

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/googlemap";

Connection con = DriverManager.getConnection(url, "root", "root");

Once you have connected to the database. In Java, 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&gt element, construct the two styles—one for restaurants and one for bars—that will later be referenced by Placemarks through the <styleUrl&gt element.

Next, 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&gt element. Extract information from the row and use it to create child elements of the <Placemark&gt, <name&gt, <description&gt, <styleUrl&gt, and <Point&gt. Assign the <styleUrl&gt element a value depending on the value of the type column for that row. Then give the <Point&gt element a child element <coordinates&gt, and combine the values of the lng and lat columns as its value.

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

GenKMLPlaceMarker.java

import java.io.*;

import java.io.IOException;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

import javax.xml.transform.Result;

import javax.xml.transform.Source;

import javax.xml.transform.Transformer;

import javax.xml.transform.TransformerFactory;

import javax.xml.transform.dom.DOMSource;

import javax.xml.transform.stream.StreamResult;

import com.google.marker;

import org.w3c.dom.Document;

import org.w3c.dom.Element;

public class GenKMLPlaceMarker {

public int id;

public String name;

public String address;

public float lat;

public float lng;

public String type;

public static void main(String[]args ){

Statement stmt;

ResultSet rs;

GenKMLPlaceMarker KML = new GenKMLPlaceMarker();

try {

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/googlemap";

Connection con = DriverManager.getConnection(url, "root", "vreena81");

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();

DocumentBuilder builder = factory.newDocumentBuilder();

TransformerFactory tranFactory = TransformerFactory.newInstance();

Transformer aTransformer = tranFactory.newTransformer();

Document doc = builder.newDocument();

Element root = doc.createElement("kml");

root.setAttribute("xmlns", "http://earth.google.com/kml/2.1");

doc.appendChild(root);

Element dnode = doc.createElement("Document");

root.appendChild(dnode);

Element rstyle = doc.createElement("Style");

rstyle.setAttribute("id", "restaurantStyle");

Element ristyle = doc.createElement("IconStyle");

ristyle.setAttribute("id", "restaurantIcon");

Element ricon = doc.createElement("Icon");

Element riconhref = doc.createElement("href");

riconhref.appendChild(doc.createTextNode("http://maps.google.com/mapfiles/kml/pal2/icon63.png"));

rstyle.appendChild(ristyle);

ricon.appendChild(riconhref);

ristyle.appendChild(ricon);

dnode.appendChild(rstyle);

Element bstyle = doc.createElement("Style");

bstyle.setAttribute("id", "barStyle");

Element bistyle = doc.createElement("IconStyle");

bistyle.setAttribute("id", "barIcon");

Element bicon = doc.createElement("Icon");

Element biconhref = doc.createElement("href");

biconhref.appendChild(doc.createTextNode("http://maps.google.com/mapfiles/kml/pal2/icon27.png"));

bstyle.appendChild(bistyle);

bicon.appendChild(biconhref);

bistyle.appendChild(bicon);

dnode.appendChild(bstyle);

stmt = con.createStatement();

rs = stmt.executeQuery("SELECT * FROM markers");

while(rs.next()){

KML.id = rs.getInt("id");

KML.name = rs.getString("name");

KML.address = rs.getString("address");

KML.lat = rs.getFloat("lat");

KML.lng = rs.getFloat("lng");

KML.type = rs.getString("type");

Element placemark = doc.createElement("Placemark");

dnode.appendChild(placemark);

Element name = doc.createElement("name");

name.appendChild(doc.createTextNode(KML.name));

placemark.appendChild(name);

Element descrip = doc.createElement("description");

descrip.appendChild(doc.createTextNode(KML.address));

placemark.appendChild(descrip);

Element styleUrl = doc.createElement("styleUrl");

styleUrl.appendChild(doc.createTextNode( "#" + KML.type+ "Style"));

placemark.appendChild(styleUrl);

Element point = doc.createElement("Point");

Element coordinates = doc.createElement("coordinates");

coordinates.appendChild(doc.createTextNode(KML.lng+ "," + KML.lat));

point.appendChild(coordinates);

placemark.appendChild(point);

}

Source src = new DOMSource(doc);

Result dest = new StreamResult(new File("c:/PlaceMarkers.kml"));

aTransformer.transform(src, dest);

System.out.println("Completed.....");

} catch (Exception e){

System.out.println(e.getMessage());

}

Checking that the KML output works

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

<?xml version="1.0" encoding="UTF-8"?&gt
<kml xmlns = "http://earth.google.com/kml/2.1"&gt
<Document&gt
<Style id="restaurantStyle"&gt
<IconStyle id="restuarantIcon"&gt
<Icon&gt
<href&gthttp://maps.google.com/mapfiles/kml/pal2/icon63.png</href&gt
</Icon&gt
</IconStyle&gt
</Style&gt
<Style id="barStyle"&gt
<IconStyle id="barIcon"&gt
<Icon&gt
<href&gthttp://maps.google.com/mapfiles/kml/pal2/icon27.png</href&gt
</Icon&gt
</IconStyle&gt
</Style&gt
<Placemark id="placemark1"&gt
<name&gtPan Africa Market</name&gt
<description&gt1521 1st Ave, Seattle, WA</description&gt
<styleUrl&gt#restaurantStyle</styleUrl&gt
<Point&gt
<coordinates&gt-122.340141,47.608940</coordinates&gt
</Point&gt
</Placemark&gt
<Placemark id="placemark2"&gt
<name&gtBuddha Thai & Bar</name&gt
<description&gt2222 2nd Ave, Seattle, WA</description&gt
<styleUrl&gt#barStyle</styleUrl&gt
<Point&gt
<coordinates&gt-122.344391,47.613590</coordinates&gt
</Point&gt
</Placemark&gt
<Placemark id="placemark3"&gt
<name&gtThe Melting Pot</name&gt
<description&gt14 Mercer St, Seattle, WA</description&gt
<styleUrl&gt#restaurantStyle</styleUrl&gt
<Point&gt
<coordinates&gt-122.356445,47.624561</coordinates&gt
</Point&gt
</Placemark&gt
<Placemark id="placemark4"&gt
<name&gtIpanema Grill</name&gt
<description&gt1225 1st Ave, Seattle, WA</description&gt
<styleUrl&gt#restaurantStyle</styleUrl&gt
<Point&gt
<coordinates&gt-122.337654,47.606365</coordinates&gt
</Point&gt
</Placemark&gt
<Placemark id="placemark5"&gt
<name&gtSake House</name&gt
<description&gt2230 1st Ave, Seattle, WA</description&gt
<styleUrl&gt#barStyle</styleUrl&gt
<Point&gt
<coordinates&gt-122.345673,47.612823</coordinates&gt
</Point&gt
</Placemark&gt
<Placemark id="placemark6"&gt
<name&gtCrab Pot</name&gt
<description&gt1301 Alaskan Way, Seattle, WA</description&gt
<styleUrl&gt#restaurantStyle</styleUrl&gt
<Point&gt
<coordinates&gt-122.340363,47.605961</coordinates&gt
</Point&gt
</Placemark&gt
<Placemark id="placemark7"&gt
<name&gtMama's Mexican Kitchen</name&gt
<description&gt2234 2nd Ave, Seattle, WA</description&gt
<styleUrl&gt#barStyle</styleUrl&gt
<Point&gt
<coordinates&gt-122.345467,47.613976</coordinates&gt
</Point&gt
</Placemark&gt
<Placemark id="placemark8"&gt
<name&gtWingdome</name&gt
<description&gt1416 E Olive Way, Seattle, WA</description&gt
<styleUrl&gt#barStyle</styleUrl&gt
<Point&gt
<coordinates&gt-122.326584,47.617214</coordinates&gt
</Point&gt
</Placemark&gt
<Placemark id="placemark9"&gt
<name&gtPiroshky Piroshky</name&gt
<description&gt1908 Pike pl, Seattle, WA</description&gt
<styleUrl&gt#restaurantStyle</styleUrl&gt
<Point&gt
<coordinates&gt-122.342834,47.610126</coordinates&gt
</Point&gt
</Placemark&gt
</Document&gt
</kml&gt

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&gt. 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&gt element in the Placemarks. Then query the database for all the coordinates, ordered by the id of the row.

Here's a sample JAVA script that creates a <linestring&gt 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,

import java.io.File;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

import javax.xml.transform.Result;

import javax.xml.transform.Source;

import javax.xml.transform.Transformer;

import javax.xml.transform.TransformerFactory;

import javax.xml.transform.dom.DOMSource;

import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;

import org.w3c.dom.Element;

public class GenKMLLineString {

public float lat;

public float lng;

public static void main(String[]args ){

Statement stmt;

ResultSet rs;

GenKMLLineString KML = new GenKMLLineString();

try {

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/googlemap";

Connection con = DriverManager.getConnection(url, "root", "vreena81");

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();

DocumentBuilder builder = factory.newDocumentBuilder();

TransformerFactory tranFactory = TransformerFactory.newInstance();

Transformer aTransformer = tranFactory.newTransformer();

Document doc = builder.newDocument();

Element root = doc.createElement("kml");

root.setAttribute("xmlns", "http://earth.google.com/kml/2.1");

doc.appendChild(root);

Element fnode = doc.createElement("Folder");

root.appendChild(fnode);

Element placemark = doc.createElement("Placemark");

fnode.appendChild(placemark);

placemark.setAttribute("id", "linestring1");

Element name = doc.createElement("name");

name.appendChild(doc.createTextNode("My Path"));

placemark.appendChild(name);

Element desc = doc.createElement("description");

desc.appendChild(doc.createTextNode("This is the path that" +

" I took through my favorite restaurants in Seattle"));

placemark.appendChild(desc);

Element ls = doc.createElement("LineString");

placemark.appendChild(ls);

Element extr = doc.createElement("extrude");

extr.appendChild(doc.createTextNode("1"));

ls.appendChild(extr);

Element am = doc.createElement("altitudeMode");

am.appendChild(doc.createTextNode("relativeToGround"));

ls.appendChild(am);

Element cord = doc.createElement("coordinates");

stmt = con.createStatement();

ls.appendChild(cord);

rs = stmt.executeQuery("SELECT * FROM markers");

while(rs.next()){

KML.lat = rs.getFloat("lat");

KML.lng = rs.getFloat("lng");

cord.appendChild(doc.createTextNode(KML.lng + "," + KML.lat+",100 "));

}

Source src = new DOMSource(doc);

Result dest = new StreamResult(new File("c:/LineString.kml"));

aTransformer.transform(src, dest);

System.out.println("Completed.....");

} catch (Exception e){

System.out.println(e.getMessage());

}

in the order they were entered into the database.

The Output of the program “LineString.kml” is

<?xml version='1.0' encoding='UTF-8'?&gt
<kml xmlns='http://earth.google.com/kml/2.1'&gt
<Folder&gt
  <Placemark id='linestring1'&gt
    <name&gtMy path</name&gt
    <description&gtThis is the path that I took through my favorite restaurants in Seattle</description&gt
    <LineString&gt
      <extrude&gt1</extrude&gt
      <altitudeMode&gtrelativeToGround</altitudeMode&gt
      <coordinates&gt-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&gt
   </LineString&gt
  </Placemark&gt
</Folder&gt
</kml&gt

Step 4: Displaying your KML files

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 KML. Place the PlaceMarker.kml and LineString.kml in the Apache server on the local machine. 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 Network.kml

<?xml version='1.0' encoding='UTF-8'?&gt
<kml xmlns = 'http://earth.google.com/kml/2.1'&gt
<Folder&gt
  <NetworkLink&gt
    <Link&gt
      <href&gthttp://localhost/PlaceMarkers.kml</href&gt
      <refreshMode&gtonInterval</refreshMode&gt
      <refreshInterval&gt3600</refreshInterval&gt
    </Link&gt
  </NetworkLink&gt
  <NetworkLink&gt
    <Link&gt
      <href&gthttp://localhost/LineString.kml</href&gt
      <refreshMode&gtonInterval</refreshMode&gt
      <refreshInterval&gt3600</refreshInterval&gt
    </Link&gt
  </NetworkLink&gt
</Folder&gt
</kml&gt

Display in Google Earth

Open up javamysql_kmlns.kml with Google Earth. You will see this

Display in Google Map

To view the same file in Google Maps, you need to do to publish the three KML Network.kml, LineString.kml, PlaceMarkers.kml files to the Web. Since Google Map API does not publish file from the local machine. You can use Google Page to publish the KML. Check out the article Using Google Pages to Host Your KML by Mano Mark. So the Network.kml file will be changed where the marked position will be address where you have hosted the KML files.

<?xml version='1.0' encoding='UTF-8'?&gt
<kml xmlns = 'http://earth.google.com/kml/2.1'&gt
 <Folder&gt
   <NetworkLink&gt
     <Link&gt
       <href&gthttp://www.example.com/KML/PlaceMarkers.kml</href&gt
       <refreshMode&gtonInterval</refreshMode&gt
       <refreshInterval&gt3600</refreshInterval&gt
     </Link&gt
   </NetworkLink&gt
   <NetworkLink&gt
     <Link&gt
       <href&gthttp://www.example.com/KML/LineString.kml</href&gt
       <refreshMode&gtonInterval</refreshMode&gt
       <refreshInterval&gt3600</refreshInterval&gt
     </Link&gt
   </NetworkLink&gt
 </Folder&gt
</kml&gt

Create a map and add the link to the NetworkLink file. For instance:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"&gt
<html xmlns="http://www.w3.org/1999/xhtml"&gt
<head&gt
<meta http-equiv="content-type" content="text/html; charset=utf-8"/&gt
<title&gtGoogle Maps JavaScript API Example</title&gt
<script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAAtKujG6JWpKnIbUzaWJbgrBTwM0brOpm-All5BF6PoaKBxRWWERRB5bka_jDtlq568NN-lXP1FfLh3w"
type="text/javascript"&gt</script&gt
<script type="text/javascript"&gt

//<![CDATA[
var map;
var geokml;

function load() {
if (GBrowserIsCompatible()) {
map = new GMap2(document.getElementById("map"));
map.addControl(new GLargeMapControl());
map.addControl(new GMapTypeControl());
map.setCenter(new GLatLng(47.613976,-122.345467), 13);
geoXml = new GGeoXml("http://www.example.com/KML//Network.kml")
map.addOverlay(geoXml);
}

}

//]]&gt
</script&gt
</head&gt
<body onload="load()" "&gt
<div id="map" style="width: 700px; height: 500px"&gt</div&gt
</body&gt
</html&gt

Which produces a map like this:

I would like to thanks Mano Mark and Pamela Fox for their Article on KML and Google Map.

Programming in Heaven

Thursday, June 5, 2008

Using JAVA/MySQL to create KML

Step1 : Create the Table

Step 2: Populating the table

Step 3: Using JAVA to output KML

Using JAVA’s JAXP DOM functions to output KML

Checking that the KML output works

Making a Line

Step 4: Displaying your KML files

Display in Google Earth

Display in Google Map

Blog Archive

About Me