Northwind Meets Virtual Earth - Generate VE Maps with LINQ

Northwind Meets Virtual Earth - Generate VE Maps with LINQ

  • Comments 13

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="http://www.w3.org/2003/01/geo/wgs84_pos#">

Now we can write a query to create the GeoRSS for our customers. Since the Northwind database contains mostly fictitious addresses you can change the addresses to real locations or we can select just the customers living in Oregon (OR) since there are a couple valid addresses there.

Dim db As New NorthwindDataContext
Dim geoRSS = _
<rss xmlns:geo="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!

Attachment: NorthwindVirtualEarth.zip
Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post
  • PingBack from http://www.absolutely-people-search.info/?p=2668

  • Pingback from http://oakleafblog.blogspot.com/2007/12/linq-and-entity-framework-posts-for.html

    --rj

  • I was converting Beth Massi&#39;s VB example of doing XLINQ from Linq to SQL to create a geoRSS to use

  • Beth,

    I wish I had found your - excellent, clearly explained, focused article about 4 or 5 hours ago ....  would have saved me from chasing my tail around the web looking for articles on SS 2005 & V Earth

    thanks a million

  • Thankyou for your example.

    What about the rest of the world though?

    I'm trying to construct a map based on Australian addresses. Do you know of a service that will allow me to do a reverse lookup in the same way?

    Thanks again

  • Beth, I REALLY want to get ur sample running BUT I am stopped.  It doesn't like the :    'NorthwindVirtualEarth._Default'.

    I get the following error :  

    Server Error in '/NorthwindVirtualEarth' Application.

    --------------------------------------------------------------------------------

    Parser Error

    Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

    Parser Error Message: Could not load type 'NorthwindVirtualEarth._Default'.

    Source Error:

    Line 1:  <%@ Page Language="vb" AutoEventWireup="false"

    Line 2:  CodeBehind="Default.aspx.vb" Inherits="NorthwindVirtualEarth._Default" %>

    Line 3:  

    Source File: /NorthwindVirtualEarth/Default.aspx    Line: 1

    --------------------------------------------------------------------------------

    Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433

    Also the  Imports System.Data.Linq   cannot be found.

    I am using Visual Studio,  .net 3.5 framework.  

    Any help would really be APPRECIATED.

    Paul Dykes

  • Hi PauleyHoyt,

    I just downloaded and tested the attached sample here just fine. Are you sure you are running Visual Studio 2008? It sounds like either the target framework got changed or there is something wrong with your environment. Can you verify this?

    -B

  • Beth, thank u VERY much for getting back.  Yes I am using Visual Studio 2008.  I am using  .net 3.5 framework and I even tried .net 2.0  The show-stopper is the Error 3 on loading the NorthwindVirtualEarth._Default and there is also the same load Error 4 on .geoRss.

    Note the parser error shows: "Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 "  YET the project properties shows Target Framework of  .NET Framework  3.5 !!!!  Don't understand this.

    Any Ideas.

    Thanks PauleyHoyt

    Error 3 Could not load type 'NorthwindVirtualEarth._Default'. C:\Documents and Settings\Paul Dykes\My Documents\Visual Studio 2008\Projects\NorthwindVirtualEarth\Default.aspx 1

    Error 4 Could not load type 'NorthwindVirtualEarth.GeoRSS'. C:\Documents and Settings\Paul Dykes\My Documents\Visual Studio 2008\Projects\NorthwindVirtualEarth\GeoRSS.aspx 1

    Error 5 Type 'NorthwindDataContext' is not defined. C:\Documents and Settings\Paul Dykes\My Documents\Visual Studio 2008\Projects\NorthwindVirtualEarth\Default.aspx.vb 11 23 C:\...\NorthwindVirtualEarth\

  • I just finished my last talk of the conference on LINQ to XML and it was lots of fun as always. I've

  • Hi

    I am trying the same with C# and VE 6.2. My georss.aspx return the rss file correctly if I run but when I give it in VEShapeSourceSpecification(VEDataType.GeoRSS, "georss.aspx", l); It says "Unable to load source file".

    If I take the output of georss.aspx and save as xml and give the xml file name in the above function, map loads correctly with the points. Am I missing anything.

    Thanks for the great article anyways.

  • Just ignore my question above, I was using XmlTextWriter in my georss.aspx and I had not mentioned the Response.ContentType = "text/xml";

    reponse is now a real xml file and VE seems to understand it.

    Now it works like a charm...

    thanks a lot.

  • Hi, i am new to programing and currently learning by myself. It would be great if you could post this program on C# instead?

    Thanks

  • Hi furball,

    XML literals syntax is not supported in C#. You have use the LINQ to XML API directly. I'm not sure how you would translate this program but here's some info on the syntax: http://msdn.microsoft.com/en-us/library/bb387061.aspx

    HTH,

    -B

Page 1 of 1 (13 items)