Northwind Meets Virtual Earth – Generate VE Maps with LINQ

With Visual Basic 9 and LINQ you can easily create XML from multiple data sources including relational data, other XML sources or any other queryable object. Since most modern systems interact with each other in some form of XML the possibilities are endless. SOAP, XAML, HTML, RSS can all be created easily with LINQ to XML in Visual Basic 9. For instance, what if we wanted to display all our customers in the Northwind database on a map generated by Microsoft Virtual Earth?

Virtual Earth allows you to pass it an RSS document of items specifying their latitude and longitude to easily map out multiple locations in the world. There are a couple different formats you can pass it and one is the GeoRSS standard. All we have to do is create this XML by obtaining the latitude and longitude from the addresses we have in our customers table and then pass this GeoRSS to Virtual Earth. We can grab the latitude and longitude of our customers in the United States using the service at http://geocoder.us. This service can return a set of coordinates from any US address in a variety of formats including REST-ful RDF. We can use this service in our LINQ query in order to create the GeoRSS from our customers table in the Northwind database.

Assuming you already have a connection in Server Explorer to Northwind (or another database with addresses will do), first add a new “LINQ to SQL classes” item to your project, name it Northwind.dbml and then drag the Customers table onto the designer from the Server Explorer. The next thing to do is to import the geo namespace at the top of our code file because we’ll be using it to return the location information in the geo namespace from the XML that is returned from the geocoder.us service.

Imports <xmlns:geo=‘ mce_href=’http://www.w3.org/2003/01/geo/wgs84_pos#”>’>http://www.w3.org/2003/01/geo/wgs84_pos#>
<channel>
<title>Northwind Customer Locations</title>
<link></link>
<%= From Customer In db.Customers _
Let Desc = Customer.Address & “, “ & Customer.City _
Let Address = Customer.Address & “,” & Customer.PostalCode _
Where Customer.Country = “USA” AndAlso Customer.Region = “OR” _
Select <item>
<title><%= Customer.ContactName %></title>
<description><%= Desc %></description>
<%= GetGeoCode(Address).Descendants %>
</item> %>
</channel>
</rss>

In this query we’re building up the GeoRSS and calling a user defined function called GetGeoCode that accepts the address of the customer and returns the latitude and longitude. Also notice that we’re using the Let keyword in the query in order to create query variables for description and address which are being used as we build the <item> elements. The GetGeoCode function will return an XElement of the location if one was found. The Descendants method on the XElement is then called back up in the query in order to place just the <geo:lat> and <geo:long> nodes into the GeoRSS.

Function GetGeoCode(ByVal address As String) As XElement
Dim url = “http://geocoder.us/service/rest/?address=” & Server.UrlEncode(address)

Try
Dim geo = XElement.Load(url)

Return <location>
<%= geo.<geo:Point>.<geo:long> %>
<%= geo.<geo:Point>.<geo:lat> %>
</location>

Catch ex As Exception
Return <location></location>
End Try

End Function


Now that we have the GeoRSS we can pass this to Virtual Earth to create our map. For example, we can just create a simple ASP.NET application and save the GeoRSS above to a session variable. The default page contains the JavaScript code we’re going to need to send the GeoRSS to Virtual Earth and a <div> section with the id=”myMap” that identifies the area to place the map on the page. Take a look at the Virtual Earth documentation for more information on the API.

<%@ Page Language=”vb” AutoEventWireup=”false”
CodeBehind=”Default.aspx.vb” Inherits=”NorthwindVirtualEarth._Default” %>

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN”
“http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>

<html xmlns=”http://www.w3.org/1999/xhtml” >
<
head runat=”server”>
<title>Northwind Customers on Virtual Earth</title>
<link href=”style.css” rel=”stylesheet” type=”text/css” />

<script type=”text/javascript”
src=”http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=5″>
</script>
<script type=”text/javascript”>
var map = null;
var layerid=1;
function GetMap()
{
map = new VEMap(‘myMap’);
map.LoadMap();
var l = new VEShapeLayer();
var veLayerSpec = new VEShapeSourceSpecification(VEDataType.GeoRSS, “georss.aspx”, l);
map.ImportShapeLayerData(veLayerSpec, null);
}
</script>
</
head>
<
body id=”body” runat=”server” >
<form id=”form1″ runat=”server”>
<h1>Northwind Customers on Virtual Earth</h1>
<div id=’myMap’ style=”position: relative; width: 800px; height: 400px;”>
<asp:Label ID=”lblStatus” runat=”server” Text=”No items found” Visible=”False”></asp:Label>
</div>
</form>
</
body>
</
html>


The VB code-behind for the Default.aspx page simply checks to see if there were any <item> elements returned from our geoRSS query above and if so, dynamically adds the code to call the GetMap Javascript function in the onload event of the body.

If geoRSS…<item>.Count > 0 Then
Session(“georss”) = geoRSS

Me.body.Attributes.Add(“onload”, String.Format(“GetMap()”))
Else
Me.lblStatus.Visible = True
Session(“georss”) = <rss></rss>
End If


Another page called GeoRss.aspx is just a blank page that simply returns the GeoRSS stored in the session variable that the JavaScript calls to get the content.

Public Partial Class GeoRSS
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim georss As XElement = CType(Session(“georss”), XElement)

Response.ContentType = “text/xml”
Response.Write(georss.ToString())

End Sub
End
Class



The key takeaway here is that in one LINQ statement we queried over multiple data sources, the Northwind Database and the geocoder.us service, to create a single XML document that conformed to the GeoRSS standard and passed that to the Virtual Earth service to generate our map. As you can see, it’s pretty easy to create XML, in this case RSS, from multiple sources with LINQ and Visual Basic 9. The code is attached.


And if you’re interested in creating dynamic maps from your data using WPF, don’t forget to check out Scott Wisniewski’s DevCenter featured article Create Dynamic Maps with Visual Basic 9.0 and WPF.


Enjoy!

NorthwindVirtualEarth.zip