newbie question: SOLAP, connecting VE to SQL SERVER 2005 for GeoRSS data?

Virtual Earth: Map Control Development

briansss


Hi,

I'm new to using VE and would like to see how people are accessing spatial data in SQL SERVER. Looking for ideas and examples.

I'm guessing that through a webservice one could access SQL SERVER (or SQL SERVER with Spatial extensions) and generate a quick GeoRSS file that could be usable by VE to populate a layer. I have not seen any examples of this being done.

Or perhaps VE is going to better interface to SQL SERVER in the near future (than just GeoRSS and MS.Live formats)

Thanks for any pointers!




Top

Re: newbie question: SOLAP, connecting VE to SQL SERVER 2005 for GeoRSS data?

Earthware


Hello

A few of us are using MS ajax library to call webservices that return data from sql server. We are all (I believe) using custom objects to return the data from the webservice into javascript on the client. The advantage of using the MS Ajax libraries is that its very easy to get a webservice talking to javascript passing the same custom objects between the two. We then in js loop the results and create the shapes/pushpins that we need. The reason we do this rather than creating georss is that georss is not as flexible as calling the map functions directly.

For an example of how to setup a connection between a webservice and js see:

http://ajax.asp.net/docs/tutorials/ConsumingWebServicesWithAJAXTutorial.aspx

I hope this helps you.

Brian Norman

www.earthware.co.uk



Top

Re: newbie question: SOLAP, connecting VE to SQL SERVER 2005 for GeoRSS data?

Jared H

Hi Brian, the way I'm doing this now is as follows (you could just as easily do the same thing with an asmx)

a) Layer source specification URL is an HttpHandler (.ashx) with a return content type of text/xml

b) In the querystring, pass parameters so the handler knows what to fetch

c) Handler calls a SQL stored procedure with FOR XML AUTO, ELEMENTS

d) using ExecuteXmlReader(), read the results into an XPathDocument

e) Depending on your usage scenario, cache the document as needed to help with further db calls for the same data

f) Send the XPath doc on to an XSLT where the .xsl style sheet is determined by what storedProc value was passed to the original call. You could further extend / parameterize this step to give your users a high degree of flexibility in terms of personalizing the display based on the results of the underlying data (i.e., they could adjust at what threshold in the data certain icons are displayed as Red dots or whatnot)

g) execute the .Transform(), sending the results to the underlying output stream (context.Response.output) of the HttpHandler and that's it

Code Snippet

public class VE_SQL_GeoRSS_Xslt : IHttpHandler {

public void ProcessRequest (HttpContext context

{

context.Response.ContentType = "text/xml";

SqlDataAccessLayer dal = new SqlDataAccessLayer();

dal.GetGeoRssData(context.Request.QueryString["serviceToCall"]);

}

public bool IsReusable {

get {

return false;

}

}

}

(other code omitted...I am passing storedProc as a variable for the procedure to be called)

using (SqlConnection oConn = new SqlConnection(YourConnectionString))

{

using (SqlCommand oCmd = new SqlCommand())

{

oCmd.Connection = oConn;

oCmd.CommandType = CommandType.StoredProcedure;

oCmd.CommandText = storedProc;

oConn.Open();

using (XmlReader xr = oCmd.ExecuteXmlReader())

{

XPathDocument xpd = new XPathDocument(xr);

context.Cache.Insert(storedProc, xpd, null, DateTime.Now.AddMinutes(120), System.Web.Caching.Cache.NoSlidingExpiration);

TransformSqlXmlResults(ref xpd, storedProc);

}

}

}

public void TransformSqlXmlResults(ref XPathDocument outputXPathDoc, string procedureCalled)

{

XslCompiledTransform xslt = new XslCompiledTransform();

switch (procedureCalled)

{

case "stored_proc1":

xslt.Load(context.Server.MapPath("../XslStyleSheets/StyleSheet1.xsl"));

break;

default:

break;

}

xslt.Transform(outputXPathDoc, null, context.Response.Output);

}





Top