Creating Bing Maps Tile-Layers from SQL Server 2008 on the Fly

Many ways lead to Rome or in this case to a connection between your spatial data in SQL Server 2008 and the visualization in Bing Maps.

  1. You can overlay the data as vectors in a VEShapeLayer by retrieving spatial data from SQL Server 2008 as GML (see for example this blog post). This is quite straight forward but since we have to render the data in the client the user experience will not be great when we have loads of geometries or geometries with many points. Using SQL Server’s Reduce-function we could increase the performance by generalizing the polygons, i.e. adjusting the distance between points to the map-resolution in Bing Maps dynamically for each zoom-level but even then we can’t really cope with Polygons with Holes in the Bing Maps API;
  2. For data that doesn’t change very frequently you can use my favourite spatial ETL tool Safe FME to generate static tile layers (see for example this blog post);
  3. For more dynamic data you could render the tiles on the fly using 3rd party tools such as the MapServer (see for example this blog post)
  4. In SQL Server 2008 R2 you will also be able to use the SQL Server Reporting Services (see for example this blog post) but unfortunately this will not come with a lot of interactivity.
  5. Recently Ricky Brundritt had blogged about his dynamic tile layer that he presented on a Bing Maps Developer Event. In the meantime I have heard from quite a few who have implemented Ricky’s code and have run into 2 issues:
    1. In the sample Ricky processes only exterior rings of polygons which means that he wouldn’t draw the holes in polygons with holes. Such a “Donut” is not as rare as you might think; for example: if we look at thematic maps on the country level, Italy is represented by a polygon with 2 holes for Vatican City and San Marino.
      Keep in mind that this was just a quick sample meant to be demonstrated during his speech in front of developers. Of course it is relatively simple to extend the code and process the interior rings as well. If you do so you might want to change the method from Graphics.DrawPolygon to Graphics.DrawRegion as well. Why? Well, the DrawPolygon-method draws a path. By setting the property Fillmode.Winding you can draw holes but it still remains a path and that might create weird-looking side-effects:
      image

A Region on the other side allows you to combine or exclude other regions, e.g.

Dim extRing As New GraphicsPath extRing.AddPolygon({ _ New Point(10, 10), _
   New Point(10, 490), _
   New Point(490, 490), _
   New Point(490, 10), _
   New Point(10, 10)})
Dim myRegion As New Region(extRing)

Dim intRings As New GraphicsPath intRings.AddPolygon({ _ New Point(50, 50), _
   New Point(100, 50), _
   New Point(100, 100), _
   New Point(50, 100), _
   New Point(50, 50)})
intRings.AddPolygon({ _
    New Point(150, 150), _
    New Point(200, 150), _
    New Point(200, 200), _
    New Point(150, 200), _
    New Point(150, 150)})
intRings.AddPolygon({ _
    New Point(50, 250), _
    New Point(50, 200), _
    New Point(100, 200), _
    New Point(100, 250), _
    New Point(50, 250)}) myRegion.Exclude(intRings) 
Dim img As Image = New Bitmap(PictureBox1.Width, PictureBox1.Height)
Dim g As Graphics = Graphics.FromImage(img) g.FillRegion(Brushes.Blue, myRegion)
image

  • The second problem and that might actually be more of an issue if you want to use this in a production environment is that Ricky uses a 3rd party library that does not come with source code so you have little control over it. The library is actually from Morten Nielsen’s SQL Server 2008 Spatial Tools and Morten specifically mentions: “These tools are NOT meant for use in a production environment”.
  • Based on Ricky’s concept I have now created a dynamic tile rendering service that is using the SQL Server 2008 spatial library in the middleware. This library is installed along with SQL Server 2008 but can also be downloaded separately from the Microsoft SQL Server System CLR Types in the Feature Pack. There is certainly room for improvement but all libraries are fully supported and the code is just a start 🙂

    You will find a sample database here

    …and the sample code here

    Advertisements
    This entry was posted in Bing Maps. Bookmark the permalink.

    2 Responses to Creating Bing Maps Tile-Layers from SQL Server 2008 on the Fly

    1. Ahmet Apaydin says:

      Hi,

      I am working on heatmaps by using the data from SQL 2008. Your website has great information on how to do that. Unfortunately the links/url’s on your website became obsolute therefore I cannot access to the links to get things work. Probably you are very busy with lots of other things but I was wondering if you will have time to fix the links. Or do you have any other website that you also published your articles.

      Thanks,
      Ahmet

      • Hi Ahmet, unfortunately I made a stupid mistake which lead to the broken images. My blog has been migrated from Windows Live Spaces to WordPress and after the migration I deleted all image files from Windows Live. Unfortunately I noticed only then that the older blog postings were refrencing the original locations on Windows Live. Even more unfortunate is that the URLs were using some cryptic tokens so it won’t be just as simple as uploading the files to Windows Live again. I will fix the problem in the comming days but it might take a while. Sorry for the inconvenience. For this particular post I have fixed it.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s