Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 1 of 1

Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (4/6)

This is a discussion on Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (4/6) within the Bing Maps Blogs & Tweets forums, part of the Blogs category; Importing GeoRSS-Layer In the previous parts we have seen how to create geospatial information ourselves using the geocoder in the ...

  1. #1
    Johannes Kebeck's Blog is offline Senior Member Green Belt
    Join Date
    Sep 2007
    Posts
    154

    Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (4/6)

    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 3rd 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-FeedHeader>
    <
    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("")
    rssOutput.AppendLine(")
    rssOutput.AppendLine("xmlns:georss='http://www.georss.org/georss'")
    rssOutput.AppendLine("xmlns:gml='http://www.opengis.net/gml'>")
    rssOutput.AppendLine("OS MasterMap")
    rssOutput.AppendLine("AddressPoint")
    rssOutput.AppendLine("")
    rssOutput.AppendLine("" + System.DateTime.Now + "")
    rssOutput.AppendLine("")
    rssOutput.AppendLine("SQL Server 2008")
    rssOutput.AppendLine("")

    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 element for this row
    rssOutput.AppendLine("")

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

    'Add a element
    rssOutput.AppendLine("")

    '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.Replace("gml:/", "/gml:")

    'Add the elements to the output XML
    rssOutput.AppendLine(gml)

    'Close and elements
    rssOutput.AppendLine("")
    rssOutput.AppendLine("")
    End While
    End Using
    End Using
    End Using

    'Close the document and send it as the response
    rssOutput.Append("")
    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)

    *



    Click here to view the article.
    Last edited by Eric Frost; 03-03-2008 at 12:59 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (3/6)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 03:52 PM
  2. Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (2/6)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 03:52 PM
  3. Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (1/6)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 03:52 PM
  4. Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (3/3)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 01:40 PM
  5. Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (2/3)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 01:40 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132