Vishful thinking…

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

Posted in Uncategorized by viswaug on June 2, 2008

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.

 

CropperCapture[8]

 

CropperCapture[7]

 

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

CropperCapture[9] 

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

CropperCapture[10]

 

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.

CropperCapture[6]

About these ads

9 Responses

Subscribe to comments with RSS.

  1. […] http://viswaug.wordpress.com/2008/06/02/sql-server-2008-virtualearth-made-easier-and-building-a-rest… Technorati Tags: .net framework,sql server 2008,virtual earth,wcf Published 21 August 08 11:49 by marcsc Filed under: WCF, .NET Framework 3.5, Virtual Earth, SQL Server […]

  2. Peter Kellner said, on August 25, 2008 at 8:32 am

    Nice examples of integration! The javascript looks cleaner than the C#!

  3. Bennie Haelen said, on September 26, 2008 at 5:41 am

    Hi Vish, this is a great sample. Hey, I have one question: Where can I download the database that you are using in your demo?
    Thanks,

    Bennie

  4. Christian said, on October 20, 2008 at 7:21 pm

    Hi,

    Thank you for this very good sample. I keep getting the following error: “Invalid attempt to read when no data is present” at the line, SQL2008PolygonLayer target = new SQL2008PolygonLayer(cmd, “geog”, “USStates”, “ID”);

    I have a sql geography type column with a stored polygon. I am actually trying to map zipcode boundaries. If I just return a simple datareader before that call I see the results being returned, so I know the query is working. I also am making sure the params are being set correctly. Any pointers on where to look? I am using the RTM version of SQL 2008.

    Thanks

  5. Christian said, on October 20, 2008 at 7:52 pm

    I have traced the error to the following method in the source:

    protected SqlGeography GetShape()
    {
    return _dataReader[_shapeFieldName] as SqlGeography;
    }

    This is the call that fails with the above error.

    Thanks again.

  6. viswaug said, on October 20, 2008 at 8:01 pm

    Hi Christian,

    Sounds like the DataReader has not been ‘Read’ yet. Meaning it has not been advanced to the first row in the results. Please do make sure that you make a call to ‘Search(null)’ before doing anything.

    Thank You,
    Vish

  7. Christian said, on October 20, 2008 at 8:14 pm

    Thank you for the very quick reply. I am using your example code “as-is” except for changing the sql query and geo column name.
    If I break the code after the Search(null) method and I trace all the way down I get the following error:

    “Method may only be called on a type for which Type.IsGenericParamater is True”

    Thanks again.

  8. Andy WIlde said, on August 15, 2011 at 12:47 pm

    This could be just what I’m looking for.

    Where do I get the binaries that contain the GIS.Framework namespaces?


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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: