Virtual Earth & SQL Server 2008: Better Together

Introduction

Now that SQL Server 2008 has been released to manufacturing it is time again to go a bit more into the details about integrating Virtual Earth and SQL Server 2008. SQL Server 2008 provides 2 spatial data types, spatial indices and more than 70 spatial-functions. It is a wonderful tool to manage huge amounts of spatial data but it doesn’t have a visualization component and even if it had, wouldn’t it be great to see your data on a nice background map? On the other side Virtual Earth has great visuals but sometimes you would like to add additional spatial data – for example land parcels.

Well, of course Virtual Earth has its ways to overlay your own data but:

  • If you overlay these data as vector data you can only use simple points, lines and polygons. Multigeometries would need to be disaggregated, polygons with holes would be a real challenge and if the total amount of vertices exceeds a few thousand then the performance goes really down since everything will be rendered in the browser through JavaScript-methods.
  • If you overlay the data as rasters, you can avoid all the challenges mentioned above but on the other side you loose the meta-data.

Further down below you find a number of previous postings which describes basic methods of integrating Virtual Earth and SQL Server 2008 but now we want to take it a step further.

The Concept

The idea is to display a large data set of land-parcels and since the total amount of vertices in all polygons can be quite high depending on the zoom-level we want to overlay the data as rasters. However, since we don’t want to loose the metadata we want to be able to click on a particular parcel and display the corresponding details. That sounds simple enough but since Virtual Earth overlays rasters as a tile layer each image can contain – depending on the zoom-level – more than one parcel or just a part of it. So we have to create a service which queries the database and retrieves only the information of the parcel that contains the clicked location. Additionally we have to consider that the amount of tiles may become quite big and if you want to store hundreds of thousands or even millions of tiles from a file-system the performance may be not as good. Fortunately we already have our database in place and we can leverage it as a tile store as well.

image

Getting your data into SQL Server 2008

That is the easy part since there are already a number of community and professional tools available. Out of the box SQL Server 2008 allows you to insert data from Well Known Text (WKT), Well Known Binary (WKB) or the Geography Markuo Language (GML). If your data is available in ESRI-Shape files you can use Morten Nielsen’s great ‘SQL Server 2008 Spatial Tools‘. For many other data formats and a huge variety of transformations you may want to have a look at Safe FME. This is really my personal favourite when it comes to spatial ETL. For my example I needed to convert the data from NAD83 into the WGS84 coordinate system – an easy task for FME. Just set the coordinate system for the destination to EPSG:4326. The next decision you’ll have to make is which data type you want to use in SQL Server 2008. You can choose between GEOMETRY – a planar data type – and GEOGRAPHY – a geodetic data type. One would think that since Virtual Earth uses a Spherical Mercator projection it would always be a good idea to start with the GEOGRAPHY data type and that is certainly true if your coordinates are for example in WGS84 and you need for example to calculate areas or distances or you want to create a buffer but the GEOGRAPHY data type:

  • accepts only valid geometries, i.e. geometries may not have any self-intersections or overlaps. You can work around this limitation by loading the data as GEOMETRY and then run the MakeValid() statement.
  • expects that geometries follow the left-hand rule, i.e. coordinates need to be in a counter-clockwise order.
  • does not have the same function-set as the GEOMETRY data type.

We will load the data as GEOMETRY data type

image

Creating a Spatial Index

For performance enhancements we will create a spatial index:

alter table Anchorage add id int identity;
ALTER TABLE Anchorage ADD CONSTRAINT
    PK_Anchorage PRIMARY KEY CLUSTERED 
    (
      ID
    );
CREATE SPATIAL INDEX SPATIAL_Anchorage ON Anchorage(GEOM) USING GEOMETRY_GRID
     WITH( GRIDS  = ( LEVEL_1  = MEDIUM, 
LEVEL_2 = MEDIUM,
LEVEL_3 = MEDIUM,
LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

Creating the Virtual Earth Tiles

Safe FME provides a VirtualEarthTiler and we will run through the process in more detail but before we do that we have to do some preliminary work. While FME does have an AreaFillColorSetter which could be used to set the fill colour of a polygon and a PenColorSetter which could be used to set the outline-colour this is unfortunately not usable when you create rasters. Until this problem is solved we have to create a temporary table which contains just the boundaries of the polygons which represent the parcels. Let’s use some SQL Server methods to do that:

select * into temp from Anchorage;
alter table temp add boundary geometry;
update temp set boundary=geom1.STBoundary();
alter table temp drop column geom;

Now let’s fire up FME and build our process:

image

As data sources we choose the table with the parcel polygons and the the temporary table with the boundaries. We use the PencColorSetter to define the fill-colour and a different outline-colour. Next we add AttributeCreator-factories to create a new attribute _order followed by a Sorter-factory to make sure that the polygons are in the background and the boundaries are in the foreground.

The next factory is a Reprojector but hold on why do we need that? We loaded our data in WGS84 coordinates and does Virtual Earth not use the WGS84 coordinate system? Yes, it does but when it comes to raster data we also have to consider the projection and Virtual Earth uses a Spherical Mercator projection whereas EPSG:4326 specifies an equirectangular projection. At large scales, the two will line up reasonably well, because they’re both rectangular.

clip_image001

However, as the scale becomes smaller, the two projections become more dissimilar.

clip_image001[7]

To compensate for this distortion we reproject to EPSG:3785.

Now we add an Rasterizer-factory and make sure that we create a transparent background by setting the background-colour to 1,1,1 and filling the background with Nodata.

image

The challenge here is to figure out how many rows and columns you need in your image and that certainly depends on the Virtual Earth zoom-level you want to use. If the number is too low you will have a lack of accuracy:

image

And if it is too high the rasterizing and tiling will take longer. So what is the appropriate number of columns and rows? There is an article on MSDN that helps Understanding Scale & Resolution in Virtual Earth. The Virtual Earth scale in meter/pixel varies with the latitude and the zoom-level:

Map resolution = 156,543.04 meters/pixel * cos(latitude*PI/180) / (2 ^ zoomlevel)

Now you need to find out what the maximum and minimum latitudes are and how big the area with your spatial data is. The little SQL script below helps you to determine that. First we calculate the accumulated box of all parcels, then we determine the maximum and minimum latitude of the area and at the end we calculate the distances. To make sure that the result comes back in meter we convert the GEOMETRY into a GEOGRAPHY data type in between.

DECLARE @Parcel geometry;
DECLARE @BBOX geometry;
DECLARE @i INT = 1;

--Calculate the Bounding Box
DECLARE GeomCursor CURSOR FOR SELECT GEOM1 FROM Anchorage WITH (NOLOCK);
OPEN GeomCursor;
FETCH NEXT FROM GeomCursor INTO @Parcel;
WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@i=1) BEGIN
            SET @BBOX = @Parcel;
            SET @i=@i+1;
        END;
        ELSE BEGIN
            SET @BBOX = (SELECT @BBOX.STUnion(@Parcel).STEnvelope());
        END;
        FETCH NEXT FROM GeomCursor INTO @Parcel;
    END;
CLOSE GeomCursor;
DEALLOCATE GeomCursor;

--Determine max and min Latitude
SELECT @BBOX.STPointN(1).STY AS [Min Latitude];
SELECT @BBOX.STPointN(3).STY AS [Max Latitude];

--Create GEOGRAPHY from GEOMETRY to calculate distances in meters
DECLARE @BBOX_GEOG geography;
SET @BBOX_GEOG = (geography::STGeomFromWKB(@BBOX.STAsBinary(), 4326));

--Calculate Distance
SELECT ROUND(@BBOX_GEOG.STPointN(1).STDistance(@BBOX_GEOG.STPointN(2)),2) AS [X-Dist at Min-Lat];
SELECT ROUND(@BBOX_GEOG.STPointN(3).STDistance(@BBOX_GEOG.STPointN(4)),2) AS [X-Dist at Max-Lat];
SELECT ROUND(@BBOX_GEOG.STPointN(2).STDistance(@BBOX_GEOG.STPointN(3)),2) AS [Y-Dist];

Now you have all you need to determine the amount of pixels that Virtual Earth provides at a given zoom-level for this area. To make that calculation a bit easier, I have created this spreadsheet. Just enter the maximum and minimum latitude as well as the distance in X- and Y-direction and the spreadsheet will calculate the number of pixels and estimate the total number of tiles.

image

Next we add a RasterCheckPointer- and the VirtualEarthTiler-factories. The RasterCheckPointer is for performance reasons and doesn’t require customization. For the VirtualEarthTiler set the minimum and maximum zoom-levels.

image

Finally set the destination as PNG and select the Fanout Attribute _quadkey to make sure that the image-names will follow the same quadkey tree structure as the one Virtual Earth uses.

image 

Also make sure that you don’t create World or Tab files. We don’t need them and it would create a lot of unnecessary files.

image

Once this is done run your workspace to create the tile layer.

Load the tiles into SQL Server

This is an optional step but not only because I’m a big a fan of databases this might make sense. If you have hundreds of thousands or even millions of image files in the file system the performance might go down. Thus I created a little tool to upload the files to my database. First create a table in your tile-database like this…

CREATE TABLE [US_Parcels_AK](
    [quadkey] [varchar](30) NOT NULL,
    [image] [varbinary](max) NOT NULL,
 CONSTRAINT [PK_US_Parcels_AK] PRIMARY KEY CLUSTERED 
 ([quadkey] ASC))

…and then run the tool:

image

You can download the source code here.

Fetching Virtual Earth Tiles from SQL Server

To add a tile layer to Virtual Earth you use the method VEMap.AddTileLayer(). By default this function ingests a parameter which points to a virtual directory on a web server. The Virtual Earth Interactive SDK provides a nice sample for this default-procedure. Now that we have the data in SQL Server we need to create a web service or generic handler which fetches the tiles and point the URL to this web handler:

The we handler is pretty simple. I call it SQLTileServer.ashx and it looks like this:

<%@ WebHandler Language="VB" Class="SQLTileServer" %>

Imports System
Imports System.Web
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.IO

Public Class SQLTileServer : Implements IHttpHandler
    
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        'Fetch URL-parameter
        Dim requestParam As String = context.Request.Params("ID")

        'Query Database
        Dim settings As ConnectionStringSettings
        settings = ConfigurationManager.ConnectionStrings("TileDB")
        Dim myConn As New SqlConnection(settings.ConnectionString)
        Dim myQ As String = "SELECT image FROM US_Parcels_AK WHERE quadkey='" & requestParam & "'"
        Dim myC As New SqlCommand(myQ, myConn)
        myConn.Open()
        Dim myReader As SqlDataReader = myC.ExecuteReader
        While myReader.Read
            Dim imageData As Byte() = myReader.Item(0)
            Dim ms As New IO.MemoryStream(imageData)
            Dim myImage As New Bitmap(System.Drawing.Image.FromStream(ms))
            WritePngToStream(myImage, context.Response.OutputStream)
        End While
        myReader.Close()
        myConn.Close()
    End Sub
 
    Private Sub WritePngToStream(ByVal image As Bitmap, ByVal outStream As Stream)
        Dim writeStream As New MemoryStream()
        Image.Save(writeStream, Imaging.ImageFormat.Png)
        writeStream.WriteTo(outStream)
        Image.Dispose()
    End Sub

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class

In my web page I have a checkbox which when clicked calls a JavaScript-function AddTileLayer() and hands over a couple of parameters including the URL to the web handler.

<input id="cbParcels" type="checkbox" 
onclick="AddTileLayer('cbParcels',
'Parcels',
61.4229958,
-150.063109,
60.7419368,
-149.4277879,
'SQLTileServer.ashx?ID=%4',
16,17,0.5,100)" /><
a>Parcels (16-17)</a><br /><br />

Aside from just loading or hiding the tile layer. The function AddTileLayer will also attach an event which we leverage when we want to fetch the metadata.

function AddTileLayer(control, layer, maxlat, maxlon, minlat, minlon, url, minlvl, maxlvl, opac, zindex) {
    if (document.getElementById(control).checked == false) {
        map.DeleteTileLayer(layer);
        map.DetachEvent("onclick", RightClick);
        slParcel.DeleteAllShapes();
    }
    else {
        var bounds = [new VELatLongRectangle(new VELatLong(maxlat, maxlon), new VELatLong(minlat, minlon))];
        var tileSourceSpec = new VETileSourceSpecification(layer, url);
        tileSourceSpec.Bounds = bounds;
        tileSourceSpec.MinZoomLevel = minlvl;
        tileSourceSpec.MaxZoomLevel = maxlvl;
        tileSourceSpec.Opacity = opac;
        tileSourceSpec.ZIndex = zindex;
        map.AddTileLayer(tileSourceSpec);
        map.AttachEvent("onclick", RightClick);
    }
}

Retrieving the Metadata from SQL Server 2008

To retrieve the metadata we use

  • the Virtual Earth event system to capture a right-click on the map and determine the latitude and longitude of the clicked location
  • a stored procedure in SQL Server 2008 to determine which land-parcel contains this clicked location
  • a web service or generic handler to execute the stored procedure and parse the result into a GeoRSS-feed

Let’s start with the stored procedure. We receive the latitude and longitude from the web handler and build a geometry of type point. Then we select the relevant data of the parcel which contains this point.

CREATE PROCEDURE [GetFeatureGML] @Lat VARCHAR(MAX), @Lon VARCHAR(MAX)
AS
DECLARE @clickString VARCHAR(MAX);
SET @clickString = 'POINT(' + @Lon + ' ' + @Lat + ')';
DECLARE @click GEOMETRY;
SET @click = GEOMETRY::STPointFromText(@clickString, 4326);
SELECT NT_ADDR, NT_ZIP, CITY, STATE_, PARCEL_ID, Geom.AsGml() FROM Anchorage WHERE (GEOM1.STContains(@click) = 1);

The web handler – I call it GetFeature.ashx – triggers this stored procedure and parses the result into a GeoRSS-feed.

<%@ WebHandler Language="VB" Class="GetFeature" %>

Imports System
Imports System.Web
Imports System.Data.SqlClient

Public Class GetFeature : Implements IHttpHandler
    
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        'Fetch URL-parameter
        Dim Lat As String = context.Request.Params("Lat")
        Dim Lon As String = context.Request.Params("Lon")

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

        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>Navteq</title>")
        rssOutput.AppendLine("<subtitle>Parcels</subtitle>")
        rssOutput.AppendLine("<link href='http://www.navteq.com/'/>")
        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()

                Using cmd As New SqlCommand()
                    'Set SQL Parameters
                    cmd.Connection = myConn
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add(New SqlParameter("Lat", Lat))
                    cmd.Parameters.Add(New SqlParameter("Lon", Lon))

                    'Specify the stored procedure name as the command text
                    cmd.CommandText = "GetFeatureGML"
                    Using geomRdr As SqlDataReader = cmd.ExecuteReader()
                        'Read the DataReader to process each row
                        While (geomRdr.Read())
                            'Create an <entry> element for this row
                            rssOutput.AppendLine("<entry>")

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

                            'Add a <georss:where> element
                            rssOutput.AppendLine("<georss:where>")
                            
                            'Get the geography instance GML from column 2
                            Dim gml As String
                            gml = geomRdr.GetValue(5).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
    End Sub
 
    Public Sub OutputError(ByVal errorMsg As String, ByVal context As HttpContext)
        'Return a feed containing info if an error occurs
        Dim emptyOutput As String = String.Format("<?xml version='1.0' encoding='utf-8'?><feed xmlns='http://www.w3.org/2005/Atom' xmlns:georss='http://www.georss.org/georss' xmlns:gml='http://www.opengis.net/gml'><title>An Error Occurred</title><subtitle>{0}</subtitle><link href='http://localhost/storefindersite/'/><updated>2007-12-06T18:30:02Z</updated><author><name>SQL Server 2008</name></author><id>urn:uuid:60a76c80-d399-11d9-b93C-0003939e0af6</id></feed>", errorMsg)
        context.Response.Write(emptyOutput)
    End Sub

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class

The JavaScript function that creates the call to our web handler is being executed whenever a mouseclick-event is fired. The result from the web handler is a GeoRSS-feed and will be imported into a VEShapeLayer. Upon completion we execute a callback-function to style the feed.

function RightClick(e) {
    if (e.rightMouseButton == true){
        var x = e.mapX;
        var y = e.mapY;
        pixel = new VEPixel(x, y);
        var LL = map.PixelToLatLong(pixel);
        var Lat = LL.Latitude;
        var Lon = LL.Longitude;
        LoadGeoRSSParcel(Lat, Lon);
    }
}

function LoadGeoRSSParcel(Lat, Lon) {
    slParcel.DeleteAllShapes();

    //Build URL to call the server
    var url = "./GetFeature.ashx?";
    url += "Lat=" + Lat;
    url += "&Lon=" + Lon;

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

function onGeoRSSParcelLoad() {
    var numShapes = slParcel.GetShapeCount();
    for (var i = 0; i < numShapes; ++i) {
        var s = slParcel.GetShapeByIndex(i);
        s.SetLineColor(new VEColor(255,0,0,1));
        s.SetFillColor(new VEColor(255,0,0,0.5));
        s.SetCustomIcon("IMG/red.png");
    }
}

Note: Virtual Earth uses a generalization to enhance the performance when it draws polygons. While more performance is usually a good thing it reduces the accuracy in this case by removing some points based on proximity. To maintain all of the original points add this line to your code before you load the map:

map.EnableShapeDisplayThreshold(false);

Well and that’s it. You find the complete source code (without the database) here.

image

 

Previous Postings on Virtual Earth and SQL Server 2008

Advertisements
This entry was posted in SQL Server 2008. Bookmark the permalink.

4 Responses to Virtual Earth & SQL Server 2008: Better Together

  1. Robert says:

    Hi ,
     
    cheers for the information, I love these kind of deep dives into Virtual earth.
    On another quick question, I am using WordPress and so are a few of my friends, the problem we have is that formatting from pasted in code in Visual Studio is lost, I am wondering if you have the same problem, or is there anything you use to "prep" the code for publishing to the blog ?
     
    I know its a little off topic, but the code presented here is pristine and I would love to know how you do it ?
    Rob

  2. Johannes says:

    Hi Rob,
     
    I’m using the Windows Live Writer and there are plenty of plugins for it available. The one I use to copy code from Visual Studio is Douglas Stockwell’s Paste from Visual Studio.
     
    Kind regards
    Johannes

  3. Hugh says:

    Great post Johannes – except for one thing…You’re giving away all the GIS secrets!!!I’m going to be out of a job and busking on the street soon :-)

  4. Morten says:

    I simply don’t get this:"If you
    have hundreds of thousands or even millions of image files in the file
    system the performance might go down. Thus I created a little tool to
    upload the files to my database."Why would the overhead of putting your tiles in a database and constantly streaming them out give you better performance? Having the webserver serve up the tiles directly from file would have the least impact, and no need to put any type of handlers in-between….Not to mention that you never set the expires header in your tile handler, so the browser will not use its cache but request the same tiles over and over again, putting even more load on your server and making your app seem A LOT slower.

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