Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (6/6) [Archive]

Johannes Kebeck's Blog

03-22-2008, 08:13 AM

<h2>Inserting data into SQL Server 2008</h2> In the previous parts we have first been looking on the database side and how we can use various mechanisms to load and extract data and we even implemented a geocoder in the database. Then we moved on and investigated various ways to retrieve data from SQL Server 2008 and visualize them in Virtual Earth. In this final part we will now focus on using Virtual Earth as a simple data editor which allows us to write data back to SQL Server 2008. We will implement the data editor in form of a query builder. In the user interface we will have some drawing tools which allow us to draw points, lines, polygons and circles on the map and to store them temporarily in our browser window. Each of the temporarily stored geographies will have a checkbox next to it and when we check these boxes we will dynamically create a SQL statement which automatically determines the appropriate feature-type. The SQL-statement will be displayed in a textarea and we will be able to manually edit it. Once we click on the image with the SQL Server logo we will create an AJAX-call which sends the SQL-statement via HTTP-Post request to a HTTP handler and this handler will actually execute the statement. <a href="http://blufiles.storage.msn.com/y1pLfDzppuxasgPm72cNO2kzgN4VB-a1kD5MP7P-jn2AhFNx999zGQ14ccgnmyVtCu8gvGSFMTb4Fg?PARTNER=WRI TER">

http://blufiles.storage.msn.com/y1pLfDzppuxasgWQquQZMiRZNaAbTz5BrThUjhwyS5sFtPv9oE OsbKX3Ww9jrR2yqAh912IvTcBZto?PARTNER=WRITER
</a> Let’s start with a fresh table in our SQL Server. We create a table with a column of type geography. <pre>CREATE TABLE [FeatureDemo]
(
[id] [int] IDENTITY(1,1) NOT NULL,
[Geom] geography NULL,
CONSTRAINT [PK_FeatureDemo] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]</pre><a href="http://11011.net/software/vspaste"></a>
In our ASP.NET page we first add a new accordion pane. This pane first displays a notice which reminds you that drawings which shall be added as a geography data type to the database must be drawn counter-clockwise. It also hosts buttons to draw various types of geographies and textboxes which will display the latitude and longitude under the mouse-cursor.
In the “Geometry”-section it provides 2 links: delete all drawings and reset the SQL statement in the textarea. Finally there is an empty DIV-element in which we will temporarily store our drawing objects with a checkbox next to them:<pre><cc1:AccordionPane ID="paneQueryBuilder" runat="server">
<Header>Query Builder</Header>
<Content>
<b>Drawing Tools</b><br /><br />
<i>Please make sure, you draw the polygon counter-clockise</i><br /><br />
<input id="btnPoint" type="button" value="Point" onclick="Draw('point')" style="width: 125px;" />
<input id="btnPolyline" type="button" value="Polyline" onclick="Draw('polyline')" style="width: 125px;" /><br />
<input id="btnPolygon" type="button" value="Polygon" onclick="Draw('polygon')" style="width: 125px;" />
<input id="btnCircle" type="button" value="Circle" onclick="Draw('circle')" style="width: 125px" /><br /><br />
<a style="font-family:Courier New">Lat: </a><input id="txtLat" type="text" disabled="disabled" style="width: 210px" class="TxtBox" /><br />
<a style="font-family:Courier New">Lon: </a><input id="txtLon" type="text" disabled="disabled" style="width: 210px" class="TxtBox" /><br /><br />
<b>Geometries</b><br />
<a href='javascript:DeleteAll()'>Delete All </a>
<a href='javascript:ResetQuery()'>Reset SQL-Query</a><br />
<div id="divGeom"></div>
</Content>
</cc1:AccordionPane></pre><a href="http://11011.net/software/vspaste"></a>
We will also need some more DIV-elements for the interaction with our geographies as well as the visualization and the editing of our SQL statement. Below you find the ASP.NET sources for a textarea where the SQL statement will be displayed, a clickable image which allows us to execute the AJAX-call as well as 2 elements which display the length of polylines and polygons while we draw as well as a dialog which allows us to enter the title and description of a geography.<pre><textarea id="txtSQL" rows="2" style="position:absolute; left:300px; bottom:5px; width:300px; height:94px" ></textarea>
<img src="IMG/Katmai.png" alt="Execute SQL-Statement" style="position:absolute; bottom:5px; right:5px; cursor:pointer;" onclick="SqlInsert();"/>
<div id='divDistance' style="position:absolute; left:10px; top:800px; visibility:hidden;" class="TxtBox";></div>
<div id='divShapeInfo' style="position:absolute; left:10px; top:850px; visibility:hidden;" class="TxtBox";>
<b><u>Please enter the details:</u></b><br /><br />
<a>ID         : </a><input id="txtShapeID" type="text" disabled="disabled" style="width: 150px" /><br />
<a>Title      : </a><input id="txtShapeTitle" type="text" style="width: 150px" /><br />
<a>Description: </a><input id="txtShapeDetails" type="text" style="width: 150px" /><br /><br />
<input id="btnShapeInfo" type="button" value="Set" onclick="SetInfo()" style="width: 260px"/><br />
</div></pre>
If we click a button to draw a geography we change the cursor to a crosshair and attach an onclick- and an onmousemove-event to the map. While we move the mouse we continuously update the latitude and longitude in the textboxes on the accordion pane. When we click with the left mouse-button we add a point to our temporary geography. If the geography is a polyline or polygon we will also determine the length of the line. For a circle the value in the divDistance will be the radius.
<a href="http://gkxu3a.blu.livefilestore.com/y1psLOdC392akruH7qetJzPF1V1ebyR5sVrEspjAMPh4Bkb91Z 5edfitN0gNu48KbeFs0-74BuaQotQK7HhTLLP9A?PARTNER=WRITER">

http://gkxu3a.blu.livefilestore.com/y1psLOdC392akqCy22_Uqtlr3G8xf_zALGeihHJeWBft4s_q45 WlR2uTaRFnM9wHfbtdVTLKdDRnMsX95EEMqmDjA?PARTNER=WR ITER
</a>
To finish the drawing we will hit the right mouse-button. This will detach the map-events and also bring up the divShapeInfo so that we can enter a title and a description. The distance will automatically be added to the shape.
<a href="http://gkxu3a.blu.livefilestore.com/y1pYZrCEXzy6WGZmw0QomwlkcrMzsM_YTVnQ0WTB7tO2oZPc2p EOgBCSzT4jo6o0zL3uqy7aUar1DwybLqc4oCRQXMU2pMAgXZd? PARTNER=WRITER">

http://gkxu3a.blu.livefilestore.com/y1pYZrCEXzy6WEU0RdHWSRkYYBTSHd3F7hwvzCLDeVZY8Pv14o dmR96lm04hucCXk7QdICg5xQKdndC2nLsBnFgMV3EVbKP-WWU?PARTNER=WRITER
</a>
When we click on “Set” in the dialogue above we will add the geography to our divGeom in the accordion pane. Next to the geography we have a checkbox and a JavaScript-function will update the textarea txtSQL with a statement appropriate for the selected geometries. For example if we have a polygon the statement will reflect that, if we have 2 polygons the statement will show a MULTIPOLYGON and if we have 2 polygons and a point the statement will show a GEOMETRYCOLLECTION.
<a href="http://gkxu3a.blu.livefilestore.com/y1psLOdC392akre99boSLnpFTx6Qw3tTuX7t3BQPRIu1Wp2csO Mt9oU0vM6YuMg2Vr6K_GSi9bM2JjTa1TL7a4aHw?PARTNER=WR ITER">
http://gkxu3a.blu.livefilestore.com/y1pYZrCEXzy6WEuKU8TWPHLlv8nkY74BOd2TbbjCaBD_wxtk3G C2h_sWoc0eI7WADsZ9RXt3vQt1yQQjZTBLGwnGwNXS5LS35r3? PARTNER=WRITER
</a>
The drawing part on top of Virtual Earth leverages a couple of events which can be attached to the Virtual Earth Map and I published the complete code on <a href="http://www.viavirtualearth.com/Wiki/Drawing-Tool+for+VE+v5.ashx">Via Virtual Earth</a>. So I don’t want to go into detail for this part.
[I just noticed that this sample has been removed so now you'll find it <a href="http://cid-42e1f70205ec8a96.skydrive.live.com/self.aspx/Public/VE-Drawing.zip">here</a>].
The major difference between the code on “Via Virtual Earth” and the code in this sample application is that we create an additional checkbox with an onclick-event attached. Once we activate the checkbox we call a function to update the SQL statement in the textarea:<pre>function SetInfo()
{
myCurrentShape.SetTitle(document.getElementById("txtShapeTitle").value);
myCurrentShape.SetDescription(document.getElementB yId("txtShapeDetails").value + " <a href='javascript:Delete(\"" + myCurrentShape.GetID() + "\")'>Delete</a>");

document.getElementById("divGeom").innerHTML = document.getElementById("divGeom").innerHTML + "<input id='" + myCurrentShape.GetID() + "' type='checkbox' onclick='UpdateSQLQuery(" + myCurrentShape.GetID() + ")' />" + document.getElementById("txtShapeTitle").value + " ";

myPoints = new Array();
tempPoints = null;
myDistance = 0;
tempDistance = 0;

document.getElementById("divShapeInfo").style.visibility = "hidden";
document.getElementById("txtShapeTitle").value = "";
document.getElementById("txtShapeDetails").value = "";
}</pre><a href="http://11011.net/software/vspaste"></a>
Updating the SQL-statement in the textarea requires first to determine the types of the VEShape-objects which have been activated.<pre>function UpdateSQLQuery(id)
{
var currentShape = slDrawing.GetShapeByID(id.id);
switch (currentShape.GetType())
{
case "Point":
pointCount++;
var tempArray = currentShape.GetPoints();
for(var j = 0; j < (tempArray.length); j++)
{
pointArray.push(tempArray[j].Latitude + " " + tempArray[j].Longitude);
}
break;
case "Polyline":
polylineCount++;
var tempArray = currentShape.GetPoints();
var dummy = "";
for(var j = 0; j < (tempArray.length); j++)
{
dummy = dummy + tempArray[j].Latitude + " " + tempArray[j].Longitude + ", ";
}
dummy = dummy.substr(0, dummy.length - 2);
polylineArray.push(dummy);
break;
case "Polygon":
polygonCount++;
var tempArray = currentShape.GetPoints();
var dummy = "";
for(var j = 0; j < (tempArray.length); j++)
{
dummy = dummy + tempArray[j].Latitude + " " + tempArray[j].Longitude + ", ";
}
dummy = dummy.substr(0, dummy.length - 2);
polygonArray.push(dummy);
break;
}</pre>
Now we determine which type of geography in SQL Server 2008 matches our selection.<pre>if (pointCount == 1 && polylineCount == 0 && polygonCount == 0) geomType = "POINT";
else if (pointCount > 1 && polylineCount == 0 && polygonCount == 0) geomType = "MULTIPOINT";
else if (pointCount == 0 && polylineCount == 1 && polygonCount == 0) geomType = "LINESTRING";
else if (pointCount == 0 && polylineCount > 1 && polygonCount == 0) geomType = "MULTILINESTRING";
else if (pointCount == 0 && polylineCount == 0 && polygonCount == 1) geomType = "POLYGON";
else if (pointCount == 0 && polylineCount == 0 && polygonCount > 1) geomType = "MULTIPOLYGON";
else geomType = "GEOMETRYCOLLECTION";</pre><a href="http://11011.net/software/vspaste"></a><a href="http://11011.net/software/vspaste"></a>
Finally we create our SQL-statement.<pre> switch (geomType)
{
case "POINT":
sqlBuilder = "INSERT INTO FeatureDemo (Geom) VALUES (geography::STGeomFromText('POINT (" + pointArray[0] + ")', 4326))";
document.getElementById("txtSQL").value = sqlBuilder;
break;
case "MULTIPOINT":
var dummy = "";
for(var j = 0; j < (pointArray.length); j++)
{
dummy = dummy + pointArray[j] + ", ";
}
dummy = dummy.substr(0, dummy.length - 2);
sqlBuilder = "INSERT INTO FeatureDemo (Geom) VALUES (geography::STGeomFromText('MULTIPOINT (" + dummy + ")', 4326))";
document.getElementById("txtSQL").value = sqlBuilder;
break;
case "LINESTRING":
sqlBuilder = "INSERT INTO FeatureDemo (Geom) VALUES (geography::STGeomFromText('LINESTRING (" + polylineArray[0] + ")', 4326))";
document.getElementById("txtSQL").value = sqlBuilder;
break;
case "MULTILINESTRING":
var dummy = "";
for(var j = 0; j < (polylineArray.length); j++)
{
dummy = dummy + "(" + polylineArray[j] + "), ";
}
dummy = dummy.substr(0, dummy.length - 2);
sqlBuilder = "INSERT INTO FeatureDemo (Geom) VALUES (geography::STGeomFromText('MULTILINESTRING (" + dummy + ")', 4326))";
document.getElementById("txtSQL").value = sqlBuilder;
break;
case "POLYGON":
sqlBuilder = "INSERT INTO FeatureDemo (Geom) VALUES (geography::STGeomFromText('POLYGON ((" + polygonArray[0] + "))', 4326))";
document.getElementById("txtSQL").value = sqlBuilder;
break;
case "MULTIPOLYGON":
var dummy = "";
for(var j = 0; j < (polygonArray.length); j++)
{
dummy = dummy + "((" + polygonArray[j] + ")), ";
}
dummy = dummy.substr(0, dummy.length - 2);
sqlBuilder = "INSERT INTO FeatureDemo (Geom) VALUES (geography::STGeomFromText('MULTIPOLYGON (" + dummy + ")', 4326))";
document.getElementById("txtSQL").value = sqlBuilder;
break;
case "GEOMETRYCOLLECTION":
var dummy = "";
if (pointArray.length > 0)
{
for(var j = 0; j < (pointArray.length); j++)
{
dummy = dummy + "POINT(" + pointArray[j] + "), ";
}
}
if (polylineArray.length > 0)
{
for(var j = 0; j < (polylineArray.length); j++)
{
dummy = dummy + "LINESTRING(" + polylineArray[j] + "), ";
}
}
if (polygonArray.length > 0)
{
for(var j = 0; j < (polygonArray.length); j++)
{
dummy = dummy + "POLYGON((" + polygonArray[j] + ")), ";
}
}
dummy = dummy.substr(0, dummy.length - 2);
sqlBuilder = "INSERT INTO FeatureDemo (Geom) VALUES (geography::STGeomFromText('GEOMETRYCOLLECTION (" + dummy + ")', 4326))";
document.getElementById("txtSQL").value = sqlBuilder;
break;
}
}</pre><a href="http://11011.net/software/vspaste"></a>
Now that we have drawn our geographies and dynamically created our SQL-statement we only need to execute it. As in previous examples we will use an AJAX-call but unlike in the previous examples we will use an HTTP-Post request since the SQL-statements with all the coordinates can become quite long.<pre>function SqlInsert()
{
//Get the appropriate XMLHTTP object for the browser
var xmlhttp = GetXmlHttp();

//if we have a valid XMLHTTP object
if (xmlhttp)
{
xmlhttp.open("POST", "./Insert.ashx", false);
xmlhttp.setRequestHeader("Content-Type","application/x-www-form-urlencoded");
//set the callback
xmlhttp.onreadystatechange = function()
{
if (xmlhttp.readystate ==4) //4 is a success
{
//server code creates JavaScript "on the fly" for us to
//execute using eval()
var result = xmlhttp.responseText
eval(result);
}
}
xmlhttp.send("sql=" + document.getElementById("txtSQL").value);
}
}</pre><a href="http://11011.net/software/vspaste"></a>
The HTTP-handler which is being called by the AJAX-call is even simpler than anything we had before. We just fetch the SQL-statement from the, set up the database connection and execute the statement. A message will be returned to the AJAX-call to indicate success or possible problems with the statement.<pre>'Fetch URL-parameter
Dim mySQL1 As String = context.Request.Params("sql")

'Prepare database
Dim settings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("SpatialDB")

Dim myConn1 As New SqlConnection(settings.ConnectionString)
myConn1.Open()
Dim myCMD1 As New SqlCommand(mySQL1, myConn1)

Try
myCMD1.ExecuteNonQuery()
context.Response.Write("alert('SQL-Statement Executed');")
Catch ex As Exception
context.Response.Write("alert(" + """" + ex.Message + """" + ");")
End Try</pre><a href="http://11011.net/software/vspaste"></a>
All right, we’re done. Run your page and test it.
<a href="http://gkxu3a.blu.livefilestore.com/y1psLOdC392akqeyrUGBe4HPu4FFP6awMvq9wQs74dGAURtUCa X5lfm8X6TJojpfVfS0PI_FPgAc3YES4skSFICwA?PARTNER=WR ITER">
http://gkxu3a.blu.livefilestore.com/y1psLOdC392akq9xcC0dbyy0Nz1Uwha-INGGlBjL6r4OZ_wnraqJwEi-P1_1E-vfdDliOdVnyhm2-bR7I7xZTxxkA?PARTNER=WRITER</a>
You can also retrieve the results within SQL Server Management Studio... <pre>SELECT Geom.STAsText() FROM FeatureDemo
---------------------------------------
POINT (51.461166621237787 -0.9271699190139665)
MULTIPOINT ((51.461149909867657 -0.9271699190139665), (51...))
POLYGON ((51.461380526235004 -0.92682123184203891, 51...))
MULTIPOLYGON (((51.461380526235004 -0...))((...)))</pre>
…and do further analysis like determining the size of the area which is covered by a polygon.<pre>SELECT Geom.STArea() FROM FeatureDemo WHERE ID = 4
--------------------------------------------------
2532.6 square meter</pre><a href="http://11011.net/software/vspaste"></a>
Note: Polygons must be drawn counter clockwise and each polygon figure must be within a single, logical hemisphere. Individual spatial objects do not need to be within the same logical hemisphere in order to run spatial operations between them.
*
<div style="padding-right:0px;display:inline;padding-left:0px;padding-bottom:0px;margin:0px;padding-top:0px">Technorati Tags: <a href="http://technorati.com/tags/Virtual Earth" rel=tag>Virtual Earth</a>,<a href="http://technorati.com/tags/SQL Server 2008" rel=tag>SQL Server 2008</a></div><img src="http://c.services.spaces.live.com/CollectionWebService/c.gif?cid=4819404664324524694&page=RSS%3a+Virtual+Earth+%26+SQL+Server+2008+-+Part+4%3a+Integrating+SQL+Server+2008+and+Virtual +Earth+(6%2f6)&referrer=" width="1px" height="1px" border="0" alt=""><img style="position:absolute" alt="" width="0px" height="0px" src="http://c.live.com/c.gif?NC=31263&NA=1149&PI=73329&RF=&DI=3919&PS=85545&TP=johanneskebeck.spaces.live.com&GT1=JohannesKebeck">

Click here to view the article. (http://JohannesKebeck.spaces.live.com/Blog/cns!42E1F70205EC8A96!3704.entry)