Importing GeoRSS-Layer

In the previous parts we have seen how to create geospatial information ourselves using the geocoder in the Virtual Earth Platform, how to visualize these data and how to spatially aggregate them.

In this part we will discuss how we can retrieve data which has been created in a 3^rd party tool such as a geographic information system (GIS) and loaded previously into SQL Server 2008. We have had a look at this loading process and used as an example Safe FME to load some sample data from the Ordnance Survey UK MasterMap. We have also seen,previously, that we can retrieve geospatial data from SQL Server 2008 in various formats and we learned that one of these formats is the Geographic Markup Language (GML). GML has been defined as a standard by the Open Geospatial Consortium (OGC) which Microsoft recently joined as a Principal Member in October 2007. Furthermore, we learned that we can use various ways to overlay data on top of Virtual Earth. One of them is the GeoRSS feed. While GeoRSS is not standardized yet, there is one nice implementation which is based on GML. Since it is possible to retrieve data as GML from SQL Server 2008, we only need to add a tag at the beginning and the end to create such a GeoRSS-feed and then we can already import the data into a VEShapeLayer.

To import data from a GeoRSS-feed into a VEShapeLayer, we need to create a JavaScript like the one below. First we create a VEShapeSourceSpecification which determines that the source comes from a GeoRSS-feed and is to be imported into a specific VEShapeLayer (slGeoRSS). The source of the feed is specified by the parameter url. Once we have the specification we can already use the Virtual Earth method ImportShapeLayerData. This method has a mandatory parameter for the VEShapeSourceSpecification and 2 optional parameters for a callback function and a Boolean-value that specifies whether the map view is changed to the best view for the layer:

var veLayerSpec = new VEShapeSourceSpecification(VEDataType.GeoRSS, url, slGeoRSS);
map.ImportShapeLayerData(veLayerSpec, null, false);

In fact the whole process is even simpler than the one which we presented in the previous chapter. Basically, we will attach events to the map which will fire whenever we pan or zoom the map. If this happens, we determine the latitudes and longitudes of the upper left and lower right corner of the current map view and add them as URL parameters. The URL points to a new HTTP handler rather than a static GeoRSS-feed and this handler in turn will generate the GeoRSS-feed dynamically:

Let’s start with the code. First we add a new accordion pane to our web site and in it we will have a HTML-element of type checkbox:

<cc1:AccordionPane ID="paneGeoRSS" runat="server">
  <Header>GeoRSS-Feed</Header>
  <Content>
    <input id="cbGeoRSSAddressPoint" type="checkbox" onclick="AddGeoRSSAddressPoint('cbGeoRSSAddressPoint')" />GeoRSS AddressPoint<br />
  </Content>
</cc1:AccordionPane>

If we click on the checkbox we execute a JavaScript function, AddGeoRSSAddressPoint. As in the previous chapter, this function checks if the control has been checked or unchecked and if checked it will attach 2 Virtual Earth events which fire when we pan or zoom the map. When that happens, it will execute a function LoadGeoRSSAddressPoint. Finally we call this function for the first time:

//GeoRSS for AddressPoint
function AddGeoRSSAddressPoint(control)
{
    if (document.getElementById(control).checked==false)
    {
        slGeoRSSAddressPoint.DeleteAllShapes();
        map.DetachEvent("onendpan", LoadGeoRSSAddressPoint);
        map.DetachEvent("onendzoom", LoadGeoRSSAddressPoint);
    }
    else
    {
        map.AttachEvent("onendpan", LoadGeoRSSAddressPoint);
        map.AttachEvent("onendzoom", LoadGeoRSSAddressPoint);
        LoadGeoRSSAddressPoint();
    }  
}

The function LoadGeoRSSAddressPoint will determine the bounding box of the map and add the latitudes and longitudes of the upper left and lower right corner as URL-parameters to the URL which points to our HTTP handler. Next we define the VEShapeSourceSpecification and then we already import the data. In the ImportShapeLayerData we define a callback function and we will see, in a second, what we have to do there:

function LoadGeoRSSAddressPoint()
{
    map.DeleteAllShapes();

    //Retrieve the boundaries of the mapview
    var ulPixel  = new VEPixel(0, 0);
    var brPixel  = new VEPixel(mapWidth, mapHeight);
    var ulLatLong = map.PixelToLatLong(ulPixel);
    var ulLat = ulLatLong.Latitude;
    var ulLong = ulLatLong.Longitude;
    var brLatLong = map.PixelToLatLong(brPixel);
    var brLat = brLatLong.Latitude;
    var brLong = brLatLong.Longitude;
            
    //Build URL to call the server
    var url="./GeoRSSAddressPoint.ashx?";
    url += "&ulLat=" + ulLat;
    url += "&ulLong=" + ulLong;
    url += "&brLat=" + brLat;
    url += "&brLong=" + brLong;

    var veLayerSpec = new VEShapeSourceSpecification(VEDataType.GeoRSS, url, slGeoRSSAddressPoint);
    map.ImportShapeLayerData(veLayerSpec, onGeoRSSslGeoRSSAddressPointLoad, false);
}

By default Virtual Earth uses a somewhat unattractive symbol for pushpins but using a callback function we can easily change these symbols. We basically loop through all the VShape-objects in the layer and set a custom icon:

function onGeoRSSslGeoRSSAddressPointLoad()
{
    var numShapes = slGeoRSSAddressPoint.GetShapeCount();
    for(var i=0; i < numShapes; ++i)
    {
        var s = slGeoRSSAddressPoint.GetShapeByIndex(i);
        s.SetCustomIcon("IMG/poi_search1.gif");
    }
}

That was the pretty straight forward Virtual Earth part of the equation so the magic must be elsewhere. Let’s see what our HTTP-handler does. We fetch the URL parameters and set up the database connection before we open a StringBuilder and create the header of the GeoRSS-feed:

'Fetch URL-parameter
Dim ulLat As String = context.Request.Params("ulLat")
Dim ulLong As String = context.Request.Params("ulLong")
Dim brLat As String = context.Request.Params("brLat")
Dim brLong As String = context.Request.Params("brLong")

'Retrieve Database Setting from web.config
Dim settings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("SpatialDB")

context.Response.ContentType = "text/xml"

'Build the GeoRSS feed
Dim rssOutput As New System.Text.StringBuilder("<?xml version='1.0' encoding='utf-8'?>")
rssOutput.AppendLine("<feed xmlns='http://www.w3.org/2005/Atom'")
rssOutput.AppendLine("xmlns:georss='http://www.georss.org/georss'")
rssOutput.AppendLine("xmlns:gml='http://www.opengis.net/gml'>")
rssOutput.AppendLine("<title>OS MasterMap</title>")
rssOutput.AppendLine("<subtitle>AddressPoint</subtitle>")
rssOutput.AppendLine("<link href='http://www.ordnancesurvey.co.uk/'/>")
rssOutput.AppendLine("<updated>" + System.DateTime.Now + "</updated>")
rssOutput.AppendLine("<author>")
rssOutput.AppendLine("<name>SQL Server 2008</name>")
rssOutput.AppendLine("</author>")

Try
    Using myConn As New SqlConnection(settings.ConnectionString)

        'Open a connection to the database
        myConn.Open()

The URL parameters, we fetched previously, will be used as SQL-parameters for a call to our stored procedure. Why do we use a stored procedure rather than executing a SQL query directly? Well, that is the biggest advantage of spatial enabled databases - you can analyse the data where they are. You don’t need to transport huge amounts of data from the database to the middleware to analyse it:

        Using cmd As New SqlCommand()
        'Set SQL Parameters
        cmd.Connection = myConn
        cmd.CommandType = Data.CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter("ulLat", ulLat))
        cmd.Parameters.Add(New SqlParameter("ulLong", ulLong))
        cmd.Parameters.Add(New SqlParameter("brLat", brLat))
        cmd.Parameters.Add(New SqlParameter("brLong", brLong))

        'Specify the stored procedure name as the command text
        cmd.CommandText = "GetAddressGML"
        Using geomRdr As SqlDataReader = cmd.ExecuteReader() 'Read the DataReader to process each row

While we loop through the results of our stored procedure, we create the entries in our GeoRSS-feed:

                While (geomRdr.Read())
                    'Create an <entry> element for this row
                    rssOutput.AppendLine("<entry>")

                    'Set title and description
                    rssOutput.AppendLine(String.Format("<title>{0}</title>", geomRdr.GetValue(0) + " " + geomRdr.GetValue(1)))
                    rssOutput.AppendLine(String.Format("<description>{0}</description>", "<![CDATA[" + "<br>" + geomRdr.GetValue(2) + "<br>" + geomRdr.GetValue(3) + "]]>"))

                    'Add a <georss:where> element
                    rssOutput.AppendLine("<georss:where>")
                    
                    'Get the geography instance GML from column 2
                    Dim gml As String
                    gml = geomRdr.GetValue(4).ToString()
                    
                    'Append the gml: prefix to all the elements due to VE parsing behavior
                    gml = gml.Replace("<", "<gml:")
                    gml = gml.Replace("gml:/", "/gml:")

                    'Add the <gml:> elements to the output XML
                    rssOutput.AppendLine(gml)
                    
                    'Close <georss:where> and <entry> elements
                    rssOutput.AppendLine("</georss:where>")
                    rssOutput.AppendLine("</entry>")
                End While
            End Using
        End Using
    End Using

    'Close the <feed> document and send it as the response
    rssOutput.Append("</feed>")
    context.Response.Write(rssOutput.ToString())
Catch e As Exception
    OutputError(e.ToString(), context)
End Try

Let’s see what the stored procedure does for us. We fetch the SQL parameters and use them to create a geography of type POLYGON. This polygon will be used in a spatial-relationship query to find out which other geometries intersect it. In this case we are intersecting with the AddressPoint-table which contains the rooftop locations for houses in the UK and of course the intersection between a polygon and a point will always be a point.

CREATE PROCEDURE GetAddressGML @ulLat nvarchar(10), @ulLong nvarchar(10), @brLat nvarchar(10), @brLong nvarchar(10)
AS
-- Create a rectangle geography instance based on bounding box of the Virtual Earth map
DECLARE @SearchRectangleString VARCHAR(MAX);
SET @SearchRectangleString = 'POLYGON((' + @ulLat + ' ' + @ulLong + ',' + @brLat + ' ' + @ulLong + ',' + @brLat + ' ' + @brLong + ',' + @ulLat + ' ' + @brLong + ',' + @ulLat + ' ' + @ulLong + '))';

DECLARE @SearchRectangle geography;
SET @SearchRectangle = geography::STPolyFromText(@SearchRectangleString, 4326)

--Return all addresses in the search rectangle
SELECT    RTRIM([PostalAddress.Thoroughfare]),
        RTRIM([PostalAddress.BuildingNumber]),
        RTRIM([PostalAddress.PostTown]),
        RTRIM([PostalAddress.PostCode]),
        Geom.AsGml() As AddressGML
FROM AddressPoint
WHERE @SearchRectangle.STIntersects(Geom) = 1

That’s it. When we compile the code we will retrieve the point information from our database:

We can use the same methodology to retrieve different types of content. In fact we only need to change the stored procedure to retrieve additional data. In the example below, we use the SQL Server function STArea to determine the size of the buildings – again based on the data of the Ordnance Survey UK MasterMap:

CREATE PROCEDURE [dbo].[GetAreaGML] @ulLat nvarchar(10), @ulLong nvarchar(10), @brLat nvarchar(10), @brLong nvarchar(10)
AS
-- Create a rectangle geography instance based on bounding box of the Virtual Earth map
DECLARE @SearchRectangleString VARCHAR(MAX);
SET @SearchRectangleString = 'POLYGON((' + @ulLat + ' ' + @ulLong + ',' + @brLat + ' ' + @ulLong + ',' + @brLat + ' ' + @brLong + ',' + @ulLat + ' ' + @brLong + ',' + @ulLat + ' ' + @ulLong + '))';

DECLARE @SearchRectangle geography;
SET @SearchRectangle = geography::STPolyFromText(@SearchRectangleString, 4326)

--Return all addresses in the search rectangle
SELECT    Geom.STArea(), Geom.AsGml()
FROM TopographicArea
WHERE @SearchRectangle.STIntersects(Geom) = 1 AND Theme LIKE 'Building%'

With regards to the accuracy of the polygons there is one thing to keep in mind. By default Virtual Earth generalizes the polygons, i.e. it removes points from the polygons and polylines to enhance performance. If you need to increase the performance you can set a parameter EnableShapeDisplayThreshold to false.

(to be continued)