Adding KML output for Google Earth

Tips submitted by ASP.NET Maker users

Adding KML output for Google Earth

Postby dvhthomas » Tue Aug 04, 2009 4:55 am

I'm no expert with templates and so on, but I got a minimally invasive way of output KML files from a list view. In the Table-specific - List Page - Page_Load for the page you want put the following:

// Page Load event
public void Page_Load() {
if (HttpContext.Current.Request.QueryString["export"] != "kml") return;
using (IDataReader reader = LoadRecordset())
{
using (var kml = new KML.KmlDataWriter(reader, 5, 3))
{
var output = kml.GeneratePlacemarks();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/vnd.google-earth.kml+xml";
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
HttpContext.Current.Response.Write(output.ToString());
HttpContext.Current.Response.End();
}
}
}

You can change the two numbers in the line using (var kml = new KML.KmlDataWriter(reader, 5, 3)) to different values. The first one is the zero-based index of the column in the table containing the display text for points in Google Earth, e.g., a Project Name column. The second number is the zero-based column index of the Name column, e.g., Project Number. So in this case we see that the 6th column is the description and the 4th column is the name (again…zero based!)
Adjust the ASP.NET settings for your project so that it uses C# 3.5 and separate code-behind files.

Now that you’ve got the web site generated use your favorite text editor that does not change file formatting to open up the web page for the table you want KML output for. In our example the table is called tArchive so look for the file called [OutputDir]\tArchive.aspx.
Look for the following text (or something very similar). In a typical example it is at around line 47:
<table class="ewBasicSearch">
<tr>
<td><span class="aspnetmaker">
<a href="<%= tArchive_list.PageUrl %>cmd=reset">Reset search criteria</a>&nbsp;
<a href="tArchivesrch.aspx">Advanced Search</a>
<% if (tArchive_list.sSrchWhere != "" && tArchive_list.lTotalRecs > 0) { %>
<a href="javascript:void(0);" onclick="ew_ToggleHighlight(tArchive_list, this, '<%= tArchive.HighlightName %>');">Hide highlight</a>
<% } %>
</span></td>
</tr>
</table>
Insert a line like this:
&nbsp;<a href="tArchivelist.aspx?export=kml">Export KML</a>&nbsp;
And you should now have a section looking like this:
<table class="ewBasicSearch">
<tr>
<td><span class="aspnetmaker">
<a href="<%= tArchive_list.PageUrl %>cmd=reset">Reset search criteria</a>&nbsp;
<a href="tArchivesrch.aspx">Advanced Search</a>
&nbsp;<a href="tArchivelist.aspx?export=kml">Export KML</a>&nbsp;
<% if (tArchive_list.sSrchWhere != "" && tArchive_list.lTotalRecs > 0) { %>
<a href="javascript:void(0);" onclick="ew_ToggleHighlight(tArchive_list, this, '<%= tArchive.HighlightName %>');">Hide highlight</a>
<% } %>
</span></td>
</tr>
</table>
Save and close the file. It doesn’t actually matter where you put this snippet, and you can put it on any list page in the web site. But having it near the other links makes sense.

Finally, put a copy of the KmlDataWriter.cs file in the [OutputDir]\App_Code directory. You can now browse to your website and try the KML export link on a list.

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Web;
using System.Xml.Linq;

namespace KML
{
public class KmlDataWriter : IDisposable
{
private const string BalloonStyle = "information-table-template";
private const string InformationRowType = "InformationRowType";
public static readonly XNamespace KmlNamespace = "REPLACE_WITH_HTTP_AND_COLON//www.opengis.net/kml/2.2";
private readonly IDictionary<string, int> _dataColumns = new Dictionary<string, int>();

private readonly IDataReader _datasource;
private readonly int _descriptionColumnIndex;
private readonly int _nameColumnIndex;
private XElement _dataSchema;
private int _indexX = -1;
private int _indexY = -1;
private XElement _style;

public KmlDataWriter(IDataReader datasource)
: this(datasource, 0, 0)
{
}

/// <summary>
/// The default column for the display name is the first one, but you can
/// override this
/// </summary>
public KmlDataWriter(IDataReader datasource, int nameColumnIndex)
: this(datasource, nameColumnIndex, 0)
{
}

/// <summary>
/// The default column for the display name is the first one, but you can
/// override this
/// </summary>
public KmlDataWriter(IDataReader datasource, int nameColumnIndex, int descriptionColumnIndex)
{
if (datasource == null)
throw new ArgumentNullException("datasource");
_datasource = datasource;
_nameColumnIndex = nameColumnIndex;
_descriptionColumnIndex = descriptionColumnIndex;
}

public int NameColumnIndex
{
get { return _nameColumnIndex; }
}

public string XColumn { get; private set; }
public string YColumn { get; private set; }

#region IDisposable Members

public void Dispose()
{
if (_datasource == null) return;
if (!_datasource.IsClosed) _datasource.Close();
_datasource.Dispose();
}

#endregion

/// <summary>
/// Finds candidate spatial columns in a datasource and tracks them
/// for usage in KML export operations.
/// </summary>
/// <returns></returns>
public bool ContainsSpatialColumns()
{
bool foundBothXandY = false;
CreateDataSchema(_datasource);

foreach (var column in _dataColumns)
{
string findThis = column.Key.ToLower();

foreach (var x in SpatialColumnNames.X)
{
if (findThis == "x" || findThis.Contains(x))
{
_indexX = column.Value;
XColumn = column.Key;
break;
}
}

foreach (var y in SpatialColumnNames.Y)
{
if (findThis == "y" || findThis.Contains(y))
{
_indexY = column.Value;
YColumn = column.Key;
break;
}
}

if (_indexX > -1 & _indexY > -1)
{
foundBothXandY = true;
break;
}
}

return foundBothXandY;
}

public XDocument GeneratePlacemarks()
{
if (_indexX == -1 & _indexY == -1)
{
bool containsSpatial = ContainsSpatialColumns();
if (!containsSpatial)
throw new ApplicationException("Either the X or Y columns are not present in the data source.");
}

var root = new XElement(KmlNamespace + "kml");
var document = new XElement(KmlNamespace + "Document",
new XComment("The display style"), _style,
new XComment("The database schema"), _dataSchema,
new XComment("The data points - one for each row in the database"));
root.Add(document);


while (_datasource.Read())
{
string x = _datasource.GetValue(_indexX).ToString();
string y = _datasource.GetValue(_indexY).ToString();

if (!string.IsNullOrEmpty(x) & !string.IsNullOrEmpty(y))
{
string name = _datasource.GetValue(_nameColumnIndex).ToString();
string description = _datasource.GetValue(_descriptionColumnIndex).ToString();

var schemaData = new XElement(KmlNamespace + "SchemaData",
new XAttribute("schemaUrl",
"#" + InformationRowType + "Id"));
foreach (var column in _dataColumns)
{
string dataPoint = _datasource.GetValue(column.Value).ToString();
schemaData.Add(new XElement(KmlNamespace + "SimpleData",
new XAttribute("name", column.Key),
HttpUtility.HtmlEncode(dataPoint)));
}

document.Add(
new XElement(KmlNamespace + "Placemark",
new XElement(KmlNamespace + "name", name),
new XElement(KmlNamespace + "styleUrl", "#" + BalloonStyle),
new XElement(KmlNamespace + "description", description),
new XElement(KmlNamespace + "Point",
new XElement(KmlNamespace + "coordinates",
string.Format("{0},{1},0", x, y))),
new XElement(KmlNamespace + "ExtendedData", schemaData)));
}
}

var kml = new XDocument(new XDeclaration("1.0", "utf-8", "no"), root);
return kml;
}

private void CreateDataSchema(IDataReader reader)
{
var schema = new XElement(KmlNamespace + "Schema",
new XAttribute("name", InformationRowType),
new XAttribute("id", InformationRowType + "Id"));

DataTable table = reader.GetSchemaTable();

foreach (DataRow row in table.Rows)
{
string columnName = row["ColumnName"].ToString();
int columnIndex = reader.GetOrdinal(columnName);
_dataColumns.Add(columnName, columnIndex);
}


var html = new StringBuilder("<table border=\"1\" cellpadding=\"2\"><tr><th>Item</th><th>Value</th></tr>");
const string htmlRow = "<tr><td>{0}</td><td>$[{1}/{0}]</td></tr>";

foreach (var column in _dataColumns)
{
schema.Add(new XElement(KmlNamespace + "SimpleField",
new XAttribute("type", "string"),
new XAttribute("name", column.Key),
new XElement(KmlNamespace + "displayName", column.Key))
);
html.AppendFormat(htmlRow, column.Key, InformationRowType);
}

html.Append("</table>");
var style = new XElement(KmlNamespace + "Style",
new XAttribute("id", BalloonStyle),
new XElement(KmlNamespace + "BalloonStyle",
new XElement(KmlNamespace + "text",
new XCData(html.ToString())
)));

_dataSchema = schema;
_style = style;
}
}

public class SpatialColumnNames
{
public static IList<string> X = new List<string> { "lon", "long", "longitude" };
public static IList<string> Y = new List<string> { "lat", "latitude" };
}
}

You can now click the Export KML link if your data has an x and y (long and lat) column.
dvhthomas
 

Re: Adding KML output for Google Earth

Postby dvhthomas » Tue Aug 04, 2009 4:56 am

BTW - change the URL in the KmlDataWriter class - the forum software didn't like having a URL in there so I changed it.
dvhthomas
 


Return to User Submited Tips (ASP.NET Maker)