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.

Advertisement

A Wind power prototype – using Google maps, Google charts and ExtJS

U.S.INSTALLED WIND CAPACITY EXPLORER

I just finished up the above prototype application that displays the installed wind power capacity by state in the US over the years. The application is built on top of the ExtJS JavaScript framework to provide layout functionality with collapsible left and bottom panels. I was going for a close-to-ArcMAP kind of look for the application. So, the left-side panels contains the legend information and some related links on another sub-tab. The attribute table data appears in a grid that is docked to the bottom. The grid allows the user to sort and hide columns as required. Both the left and the bottom panels can be completely collapsed to allow for maximum screen real-estate for the map. The possibilities with the ExtJS framework can be much more powerful and even more user-friendly.

Google maps provides the mapping for the prototype. The state polygons are overlayed on the maps and are symboloized based on the year selected by the user and the installed MW capacity for the selected state. The user can also click on a state on the map or on the grid to see a bar chart (generated by Google charts) that displays the year over year growth in the installed MW capacity for the selected state. The current year’s value is highlighted in red on the bar chart.

The state polygon and the attribute data is delivered by the server in a GeoJSON format making it easy to interpret. Please take a look at the GeoJSON.NET library if you are interested in generating GeoJSON yourselves. I will also soon be posting about a GIS data framework that I have been working on and that will make generating GeoJSON even easier for various other data formats like FeatureClasses and SQL Server 2008 spatial.

Over all the development of the prototype was pretty smooth. The task that took some time was the reducing the state boundaries shapefile from National Atlas into shapes with a reasonable number of points so that it can be displayed in Google maps without too much of a performance hit (to download and to draw) and still looks good in a cartographer’s eyes. I tried ESRI’s “Generalize” tool on the state shapes, only for it to pop-up a message box saying that the geometry is empty. SQL Server 2008’s “Reduce” function also didn’t get the job done on the shapes. I tried MapShaper.org‘s map reduction functionality and it got the job done on the shapefile. But not everyone was happy with the cartographic quality of the generated shapes. Finally, one of my colleague took the pains to edit the polygons manually to get a better cartographic prototype (kudos for Chris French for doing that).

I also hope to posting about “Google Maps vs VirtualEarth” from purely a developer’s perspective pretty soon.