Many applications display KML, including Google Earth, Google Maps, Google Maps for mobile, NASA WorldWind, ESRI ArcGIS Explorer, Adobe PhotoShop, AutoCAD, and Yahoo! Pipes. This makes it a suitable standard for creating interactive business intelligence products.
Understanding KML (Keyhole Markup Language), which is the XML compliant format for sharing data with Google Earth and other display interface applications, requires reading and adhering to the KML Reference documentation at http://code.google.com
However, generating KML via SQL Server 2005 can be easily accomplished by leveraging the FOR XML clause combined with some basic string manipulation for any cleanup tasks.
KML is essentially an XML compliant format for designating all of the relevant information that you wish to show in your favorite display application. All data dependent applications require some form of contract, and with Google Earth, this contract is called KML. Within a document, you will find the element, which contains style sheet information as well as the point, circle or polygon shape objects, their icons, names, descriptions, latitude, longitude and elevation attributes.
According to Google, You can create KML files with the Google Earth user interface, or you can use an XML or simple text editor to enter "raw" KML from scratch. KML files and their related images (if any) can be compressed using the ZIP format into KMZ archives. To share your KML and KMZ files, you can e-mail them, host them locally for sharing within a private internet, or host them publicly on a web server. Just as web browsers display HTML files, Earth browsers such as Google Earth display KML files. Once you've properly configured your server and shared the URL (address) of your KML files, anyone who's installed Google Earth can view the KML files hosted on your public web server.
A basic KML document might look like the following:
<?xml version="1.0" encoding="utf-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Placemark>
<name>My office</name>
<description>This is the location of my office.</description>
<Point>
<coordinates>-122.0821604290386,37.42041505714916</coordinates>
</Point>
</Placemark>
</kml>
What this is describing is a simple placemark control having the minimum properties to be displayed within Google Earth. We see we have a single Placemark element, and within that placemark, we have a name [required], a description [required], and a [Point], also required. A point in Google Earth contains either both the latitude and latitude of a point, assuming the elevation sticks to the surface of the earth, or you can choose to include a third parameter within the coordinates tag to specify altitude.
Essentially, all kml documents we create will be relevant to your data and can be augmented with additional attributes to further enhance the experience.
In many cases, you will have a collection of <Placemark> elements, each describing a single point on the globe. A collection of <Placemark> elements is contained within one or more <Folder> elements within the <kml> or <kml><Document/></kml> tags.
In our example, we will look at airport data from an extract from the FCC tower registration database, DDL and DML, included with this article, containing a restricted set of records.
In the sample above, only a single Placemark is demonstrated, but in real life, you will probably want to capture many Placemarks within one or more collections (or Folders). The KML specification allows for this and the FOR XML can be used efficiently for this purpose.
CREATE PROCEDURE genBasicKML
AS
BEGIN
DECLARE @kml XML
DECLARE @kmlout NVARCHAR(MAX)
SELECT @kml = CAST('<?xml version="1.0" encoding="utf-16" ?>' +
'<kml xmlns="http://www.opengis.net/kml/2.2">' +
'<Document>' +
' <name>BasicKML.kml</name>' +
' <open>1</open>' +
(
SELECT TOP(25) 'ExampleFolder' AS 'Folder/name'
, 1 AS 'Folder/open'
, AIRPORT_FACILITY_NAME AS 'Folder/Placemark/name'
'<br/>ID: ' + AIRPORT_SITE_ID +
'<br/>Type: ' + AIRPORT_FACILITY_TYPE +
'<br/>City: ' + CITY +
'<br/>County: ' + COUNTY +
'<br/>State: ' + STATE_CODE +
'<br/>' AS 'Folder/Placemark/description'
, CAST(((LONG_DEG + ( LONG_MIN / 60) + (LONG_SEC / 3600)) * -1) AS NVARCHAR) + ', ' + CAST(LAT_DEG + ( LAT_MIN / 60) + (LAT_SEC / 3600) AS NVARCHAR) + ', ' + HEIGHT_ELEVATION AS 'Folder/Placemark/Point/coordinates'
, '' AS 'Folder/Placemark/Icon/href'
FROM [dbo].[airport]
WHERE [STATE_CODE] = 'FL'
FOR XML PATH(''), ELEMENTS) + '</Document></kml>' AS XML)
--
-- Perform replacement of < and > with < and > respectively
--
SET @kmlout = REPLACE(REPLACE(CAST(@kml AS NVARCHAR(MAX)), '<', '<'), '>', '>')
SET @kmlout = REPLACE(@kmlout, 'utf-16', 'utf-8')
--
-- Return kmlout
--
SELECT @kmlout
END;
Looking at the query within the proc, you will find that the column alias specifies the XPath for the value selected, while the PATH('') directive says to use an empty name, the default is "<row>". The ELEMENTS directive returns the results as XML elements.
The query above also includes a string at the head of the result XML to specify the kml encoding and the opening Document element. A second string is appended to the back of the results to close the Document and kml root element cast as XML.
Finally, the resulting XML is cast to an NVARCHAR value to allow string replacement of tags in the 'Placemark/description' , and to replace the default encoding from UTF-16 to UTF-8.
A second sample proc definition has been included to show how to use style sheets to provide additional information within the balloon including driving directions to and from the Placemark, and HTML links.
This procedure is extensible and additional elements can be included including style sheets, custom icons, balloon markers, circles, and polygons to help tell a much richer story. Just be sure to check out the KML Specification before adding elements.
Additional information can be found at http://interactivebi.blogspot.com
Robert J. Green
http://www.linkedin.com/in/rjgreenwave
rjgreenwave@gmail.com
