SQL Server 2008 + VirtualEarth made easier and building a REST API

I can guess what everybody is thinking after reading the title, how can SQL Server 2008 + VirtualEarth be made any easier? Well, follow along and you might change your opinion. I have started work on a GIS data framework at Assembla hoping to make working with GIS data in general easier. Currently, I have added support for reading GIS data from FeatureClasses and SQL Server 2008 (SqlGeometry and SqlGeography). I will be added data editing support for the above mentioned GIS data stores and also be adding providers to support OSGEO’s FDO Data Access Technology.

If you have been following Hannes’s blog, you might have noticed that the data from SQL Server 2008 is sent to the client-side in a convenient format and not in the standard GeoJSON format which is meant for such uses. This is where the GIS data framework comes into play and enables generating GeoJSON data from SQL Server 2008 a breeze while still remaining flexible. The code snippet below shows how easy generating GeoJSON to send to the client-side can be.

StringWriter writer = new StringWriter();

JsonTextWriter jwriter = new JsonTextWriter(writer);

 

using (SqlConnection sqlConn = new SqlConnection(_connString))

{

    sqlConn.Open();

    using (SqlCommand cmd = new SqlCommand(“Select * from USStatesGeog”, sqlConn))

    {

        //Parameters

        //1) SqlCommand representing the data to fetch

        //2) Geography field name

        //3) A name to be given to the polygon layer

        //4) Primary key field name

        SQL2008PolygonLayer target = new SQL2008PolygonLayer(cmd, “geog”, “USStates”, “ID”);

        target.Search(null);

        target.ToJSON(jwriter);

    }

}

In the above code sample, the SqlCommand can be any complex attribute or spatial query as desired. The results generated by the command will be outputted by the ToJSON(… method. The SqlCommand provides the SqlConnection information necessary to connect to the SQL Server 2008 database. To get all the records returned by the SqlCommand call the Search(…) with the NULL argument or you can pass in a string that will be applied to the CommandText property of the SqlCommand before execution.

The GeoJSON output contains both the geometry and the attributes returned by the query. This data can now be sent to the client-side where the geometry can be added to VirtualEarth.

The GIS data framework can also be used to generated GeoJSON and XML output from FeatureClasses. The code sample below illustrates how.

IWorkspace workspace = GeodatabaseUtil.GetShapefileWorkspace(shapefileDirectory);

IFeatureWorkspace featureWorkspace = workspace as IFeatureWorkspace;

IFeatureClass featureClass = featureWorkspace.OpenFeatureClass(shapefile);

//Parameters

//1) FeatureClass reference

//2) A name to be given to the polygon layer

//3) Primary key field name

target = new AoPolygonFCLayer(featureClass, “USStates”, “FID”);

IQueryFilter queryFilter = new QueryFilterClass();

queryFilter.WhereClause = “FID < 10”;

target.Search(queryFilter);

 

StringBuilder text = new StringBuilder();

StringWriter writer = new StringWriter(text);

JsonTextWriter jwriter = new JsonTextWriter(writer);

target.ToJSON(jwriter);

string result = text.ToString();

The client side code to add the geometries from the GeoJSON is simple enough. The code samples below show you how. The code sample uses the jQuery framework.

 

 

 

I had cooked up the JavaScript code sample real quick, but you can still see the use of the module pattern (from Douglas Crockford) in the VMAP function. It is always a good practice to follow the module pattern to keep your JavaScript code organized and make it more user-friendly. So, if you aren’t aware of the module pattern, I would recommend that you read about as soon as possible to bring sanity to your JavaScript applications.

 

Now, that we have come so far, why stop here? I am going to go ahead and create a WCF based REST service so that the client-side JavaScript can make simple HTTP GET request to get the GeoJSON data. The query for the data that you want to retrieve from the SQL Server 2008 database can be represented in the URL to fetch only the desired records. The code samples below illustrates how to fetch all records from the database and to fetch only the desired records by the primary key ID. Representing spatial constraints in the URL can follow thereon by extending the example.

 

Here is the service contract for the WCF REST service

[ServiceContract]

public interface IStatesData

{

    [OperationContract]

    [WebGet(UriTemplate = “States”)]

    Stream GetAllStates();

 

    [OperationContract]

    [WebGet(UriTemplate = “States/{id}”)]

    Stream GetStateByID(string id);

}

Here is the sample implementation

using System;

using System.Collections.Generic;

using System.Linq;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.Text;

using System.IO;

using System.ServiceModel.Web;

using System.Data;

using System.Data.SqlClient;

using GIS.Framework;

using GIS.Framework.SQL2008;

using GIS.Framework.SQL2008.SQLGeography.Layers;

using Jayrock.Json;

 

// NOTE: If you change the class name “StatesData” here, you must also update the reference to “StatesData” in Web.config.

public class StatesData : IStatesData

{

    string _connString = @”Data Source=vish-pc;Initial Catalog=VishDB;Connection Timeout=180;User ID=sa;Password=vish@home”;

 

    #region IStatesData Members

 

    public System.IO.Stream GetAllStates()

    {

        WebOperationContext.Current.OutgoingResponse.ContentType = “text/json”;

        MemoryStream ms = new MemoryStream();

        StreamWriter sw = new StreamWriter(ms);

        StringWriter writer = new StringWriter();

        JsonTextWriter jwriter = new JsonTextWriter(writer);

 

        using (SqlConnection sqlConn = new SqlConnection(_connString))

        {

            sqlConn.Open();

            using (SqlCommand cmd = new SqlCommand(“Select * from USStatesGeog”, sqlConn))

            {

                //Parameters

                //1) SqlCommand representing the data to fetch

                //2) Geography field name

                //3) A name to be given to the polygon layer

                //4) Primary key field name

                SQL2008PolygonLayer target = new SQL2008PolygonLayer(cmd, “geog”, “USStates”, “ID”);

                target.Search(null);

                target.ToJSON(jwriter);

            }

        }

        sw.Write(writer.ToString());

 

        sw.Flush();

        ms.Position = 0;

        return ms;

    }

 

    public System.IO.Stream GetStateByID(string id)

    {

        WebOperationContext.Current.OutgoingResponse.ContentType = “text/json”;

        MemoryStream ms = new MemoryStream();

        StreamWriter sw = new StreamWriter(ms);

        StringWriter writer = new StringWriter();

        JsonTextWriter jwriter = new JsonTextWriter(writer);

 

        using (SqlConnection sqlConn = new SqlConnection(_connString))

        {

            sqlConn.Open();

            using (SqlCommand cmd = new SqlCommand(“Select * from USStatesGeog Where ID = “ + id, sqlConn))

            {

                SQL2008PolygonLayer target = new SQL2008PolygonLayer(cmd, “geog”, “USStates”, “ID”);

                target.Search(null);

                target.ToJSON(jwriter);

            }

        }

        sw.Write(writer.ToString());

 

        sw.Flush();

        ms.Position = 0;

        return ms;

    }

 

    #endregion

}

You can test out the above REST services using Fiddler as shown in the images below.

The image below shows the URL to return all the states(Polygon database table) data as GeoJSON.

/USStates/StatesData.svc/States

 

The image below shows the URL to return only the desired state (Polygon database table) data as GeoJSON by representing the feature’s ID as a part of the URL.

/USStates/StatesData.svc/States/3

 

If you want to get your feet wet with WCF REST and get the above sample working, you can download the above WCF REST Service sample here. Keep in mind that it is only a quick and dirty sample. Well all the above leads to your SQL Server 2008 spatial data in VirtualEarth, only easier.