Monday 24 November 2008

Adventures in Spatial Data - Part 2

In my previous entry, I described how I created a SQL Server 2008 database that contains spatial data for the BeanieTracker Web application. In this article, I'll examine the Web application itself and discuss how to combine spatial data from SQL Server with the Microsoft Virtual Earth map control. The code for this article is available here.

The aim of the Beanie Tracker application is to use Virtual Earth map control to visualize the spatial data in the database in two ways:
  • Use push-pins to show the locations that Beanie has visited, and include a photograph and the distance from home in the tooltip displayed for each push-pin.
  • Overlay countries Beanie has visited with a colored shape - the more often Beanie has visited the country, the darker the color of the shape.

The first step is to display the Virtual Earth map control in the Web page. The map control is freely available, and provides a Javascript-based API that you can use from pretty much any Web application to add mapping capabilities to your Web site. To include the control in your Web page, you need to do four things:

In the body of the Web page, create a <div> element where you want the map to be displayed:

<div id='mapDiv' style="position:relative; width:600px; height:600px;"/>

In the head of the page, add a reference to the Virtual Earth map control script library:

<script type="text/javascript" src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6">

</script>

In the head of the page, add a script to display the map in the <div> area you created in step 1:

<script type="text/javascript">

var map = null;

function GetMap()

{

// Display the map control

map = new VEMap('mapDiv');

map.LoadMap();

map.SetCenterAndZoom(new VELatLong(0, 0), 1);

}

</script>

In the <body> tag, assign your script to the onload event:

<body onload="GetMap();">

Using only this code, you can include a fully-functional Virtual Earth map control in your Web page. The control provides the ability for users to click and drag to move around the map, zoom by double-clicking or using a mouse scroll wheel, and change the view from a basic map, to aerial view (based on satellite imagary) or even to birds-eye view (low level aerial photography) where available. The map control is shown here:

On its own, the map control provides some interesting functionality, but for the Beanie Tracker application I want to use the control's API to visualize the data from the database. The key challenge here is to get the data from the database (which is on the server) to the Javascript used to render it on the map (which is in the browser). There are a number of ways to do this, including creating a Web Service or implementing a .aspx page that returns a text/xml document instead of HTML; but one of the most common is to implement a custom HTTP handler that returns the data as a GeoRSS feed, and consume that feed from the Javascript in the Web page. GeoRSS is an XML-based feed format for exchanging Geographic Markup Language (GML) elements across the Internet. Here's an example of a GeoRSS feed:

<?xml version="1.0" encoding="utf-8" ?>

<feed xmlns="http://www.w3.org/2005/Atom"
xmlns:georss="http://www.georss.org/georss"
xmlns:gml="http://www.opengis.net/gml">

<title>BeanieTracker</title>

<subtitle>Beanie Locations</subtitle>

<link href=http://www.graemesplace.com/beanietracker.htm/ />

<updated>10/11/2008 09:28:09</updated>

<author>

<name>Graeme Malcolm</name>

</author>

<entry>

<title>Beanie diving in the Red Sea</title>

<description><img src='Photo.aspx?PhotoID=1' height='100'/><p>4329.09 km from
home</p></description>

<georss:where>

<gml:Point xmlns="http://www.opengis.net/gml">

<gml:pos>27.8487 34.2865</gml:pos>

</gml:Point>

</georss:where>

</entry>

<entry>

<title>Beanie at Edinburgh Castle</title>

<description><img src='Photo.aspx?PhotoID=2' height='100'/><p>18.05 km from
home</p></description>

<georss:where>

<gml:Point xmlns="http://www.opengis.net/gml">

<gml:pos>55.9486 -3.2005</gml:pos>

</gml:Point>

</georss:where>

</entry>

</feed>

To get this feed to the browser, I created an HTTP handler that builds the XML for the feed, inserting the GML representations of the spatial data in the database; which are retrieved through some stored procedures in the database itself. Since there are two options that the user can use to retrieve a GeoRSS feed (one for photos of locations Beanie has visited, and one for a list of countries and a count of visits Beanie has made to them), I added some logic in the HTTP handler to look for a parameter, and create the appropriate feed with data from the corresponding stored procedure. The main sections of code (in VB .NET) from the handler are shown here:

Public Sub ProcessRequest(ByVal context As HttpContext) Implements
IHttpHandler.ProcessRequest

context.Response.ContentType = "text/xml"

Dim rssOutput As New System.Text.StringBuilder("<?xml version='1.0' encoding='utf-8'?>")

Try

'Build the GeoRSS feed

rssOutput.AppendLine("<feed xmlns='http://www.w3.org/2005/Atom'")

rssOutput.AppendLine("xmlns:georss='http://www.georss.org/georss'")

rssOutput.AppendLine("xmlns:gml='http://www.opengis.net/gml'>")

rssOutput.AppendLine("<title>BeanieTracker</title>")

rssOutput.AppendLine("<subtitle>Beanie Locations</subtitle>")

rssOutput.AppendLine("<link href='http://localhost/beanietracker/'/>")

rssOutput.AppendLine("<updated>" + System.DateTime.Now + "</updated>")

rssOutput.AppendLine("<author>")

rssOutput.AppendLine("<name>Graeme Malcolm</name>")

rssOutput.AppendLine("</author>")

Dim dataParam As String = context.Request.QueryString("data")

If Not dataParam Is Nothing Then

If dataParam = "locations" Then

GetPhotos(rssOutput)

ElseIf dataParam = "countries" Then

GetCountries(rssOutput)

Else

Throw New Exception("Invalid parameter")

End If

End If

'Close the <feed> document and send it as the response

rssOutput.Append("</feed>")

context.Response.Write(rssOutput.ToString())

Catch e As Exception

OutputError(e.ToString(), context)

End Try

End Sub



Private Sub GetPhotos(ByRef rssOutput As System.Text.StringBuilder)

Using sqlConn As New SqlConnection(connStr)

'Open a connection to the database

sqlConn.Open()

Dim spName As String

'Use the GetPhotosGML stored proc to get all stores by default

spName = "GetPhotosGML"

Using cmd As New SqlCommand()

cmd.Connection = sqlConn

cmd.CommandType = Data.CommandType.StoredProcedure

'Specify the stored procedure name as the command text

cmd.CommandText = spName

Using geomRdr As SqlDataReader = cmd.ExecuteReader()

'Read the DataReader to process each row

While (geomRdr.Read())

'Create an <entry> element for this row

rssOutput.AppendLine("<entry>")

rssOutput.AppendLine(String.Format("<title>{0}</title>", geomRdr.GetValue(1)))

rssOutput.AppendLine(String.Format("<description>&lt;img
src='Photo.aspx?PhotoID={0}' height='100'/&gt;&lt;p&gt;{1} km from
home&lt;/p&gt;</description>", _

geomRdr.GetValue(0), geomRdr.GetValue(3)))

'Add a <georss:where> element

rssOutput.AppendLine("<georss:where>")

Dim gml As String

'Get the geography instance GML from column 2

gml = geomRdr.GetValue(2).ToString()

'Append the gml: prefix to all the elements due to VE parsing behavior

gml = gml.Replace("<", "<gml:")

gml = gml.Replace("gml:/", "/gml:")

'Add the <gml:> elements to the output XML

rssOutput.AppendLine(gml)

'Close <georss:where> and <entry> elements

rssOutput.AppendLine("</georss:where>")

rssOutput.AppendLine("</entry>")

End While

End Using

End Using

End Using

End Sub



Private Sub GetCountries(ByRef rssOutput As System.Text.StringBuilder)

Using sqlConn As New SqlConnection(connStr)

'Open a connection to the database

sqlConn.Open()

Dim spName As String

'Use the GetPhotosGML stored proc to get all stores by default

spName = "GetCountriesGML"

Using cmd As New SqlCommand()

cmd.Connection = sqlConn

cmd.CommandType = Data.CommandType.StoredProcedure

'Specify the stored procedure name as the command text

cmd.CommandText = spName

Using geomRdr As SqlDataReader = cmd.ExecuteReader()

'Read the DataReader to process each row

While (geomRdr.Read())

'Create an <entry> element for this row

rssOutput.AppendLine("<entry>")

'Use columns 0 and 1 for the title and description

rssOutput.AppendLine(String.Format("<title>{0}</title>", geomRdr.GetValue(1)))

rssOutput.AppendLine(String.Format("<description>{0}</description>", _

geomRdr.GetValue(3)))

'Add a <georss:where> element

rssOutput.AppendLine("<georss:where>")

Dim gml As String

'Get the geography instance GML from column 2

gml = geomRdr.GetValue(2).ToString()

'Append the gml: prefix to all the elements due to VE parsing behavior

gml = gml.Replace("<", "<gml:")

gml = gml.Replace("gml:/", "/gml:")

'Add the <gml:> elements to the output XML

rssOutput.AppendLine(gml)

'Close <georss:where> and <entry> elements

rssOutput.AppendLine("</georss:where>")

rssOutput.AppendLine("</entry>")

End While

End Using

End Using

End Using

End Sub

The code merges the GML results from the stored procedures into an XML feed, which is built up using simple string concatenation. You could use an XmlDocument or XmlWriter for this (or you could even use SQL Server's native XML support to generate the complete XML in the stored procedures), but this approach makes it easier to see what's going on. Note that SQL Server includes a gml:http://www.opengis.net/gml namespace declaration in the query results. However, the Virtual Earth map contol appears to require that all namespaces be declared in the root element of the feed, so I had to add code to strip out the namespace in the query results.

Next I needed to register the HTTP handler so that any requests for URLs with the extension .georss are handled by the custom handler, and not treated as regular ASP.NET pages. There's nothing special about the extension .georss by the way, I just chose to use that for my handler. To register a custom handler, you need to edit the Web.Config of your ASP.NET application. The specific entry you need to create depends on whether you are using IIS 6 or IIS 7 - see this KB article for more information. The configuration I used is shown here:


<httpHandlers>

<!--Register the GeoRSSHandler for .georss requests -->

<add verb="*" path="*.georss" type="GeoRSSHandler" validate="false"/>

</httpHandlers>

Finally, I needed to create some client-side Javascript to retrieve the feed and import it into the Virtual Earth map control. Here are the functions to do this (one to retrieve the photo locations feed, and one to retrieve the countries feed):

function ShowBeanieLocations()
{
try
{
map.DeleteAllShapes();
// Import GeoRSS feed of location data, and call onBeanieLocationsLoad function
when the data is loaded
var veLayerSpec = new VEShapeSourceSpecification(VEDataType.GeoRSS,"./Beanie.georss?data=locations");
map.ImportShapeLayerData(veLayerSpec, onBeanieLocationsLoad, true);
}
catch(e)
{
document.getElementById("Info").innerHTML = e.Message;
}
}

function ShowCountries()
{
try
{
map.DeleteAllShapes();
// Import GeoRSS feed of country data, and call onCountriesLoad function when the data is loaded
var veLayerSpec = new VEShapeSourceSpecification(VEDataType.GeoRSS,"./Beanie.georss?data=countries");
map.ImportShapeLayerData(veLayerSpec, onCountriesLoad, true);
}
catch (e)
{
document.getElementById("Info").innerHTML = e.Message;
}
}

Both of these functions follow the same basic procedure:

  1. Clear all existing shapes from the map control
  2. Retrieve the feed by requesting a .georss file wth the appropriate parameter (note that the actual file name is unimportant, the custom handler handles all .georss requests)
  3. Import the feed into the map control as a shape layer by using the ImportShapeLayerData method

The ImportShapeLayerData method is asynchronous, so a callback function to be run when the impoprt has completed is specified. Here are the callback functions in the Beanie Tracker application:

function onBeanieLocationsLoad(feed)
{
// Count the shapes returned
var locationcount = feed.GetShapeCount();
document.getElementById("Info").innerHTML = 'Beanie has visited ' +
locationcount + ' places.';
}

function onCountriesLoad(feed)
{
/ / Count the shapes returned
var locationcount = feed.GetShapeCount();
for (i=0;i<locationcount;i++)
{
var s = feed.GetShapeByIndex(i);
s.SetLineColor(new VEColor(0, 0, 0, 0));
var numVisits = s.GetDescription();
s.SetDescription("Beanie has visited this country " + numVisits + " times.");
var transparency = (numVisits % 9) / 10;
s.SetFillColor(new VEColor(255, 50, 100, transparency));
s.SetCustomIcon("<img src='images/transparentIcon.png'>" + numVisits +
"</img>");
}
document.getElementById("Info").innerHTML = 'The darker the country, the more times Beanie has visited it.';
}

The callback function for the photo location data feed just counts the number of shapes (which will be rendered as push pins on the map by default since they are all points defined by a single lat/long pair) and displays the total. The callback for the countries data is a little more complicated - The elements returned are polygons, and so will be rendered as shapes of countries on the map; the code changes the transparency property of each shape to reflect the number of visits Beanie has made to the country, so that the more visits Beanie has made, the darker the country will appear. Note that by default a pushpin is displayed in the center of the shape, but I've used the SetCustomIcon method to specify that a transparent image should be used instead and included the number of visits as text, so that's what appears on the map.

The last step is to hook the methods that retrieve the feeds up to the buttons on the Web page:

<input id="Button1" type="button" value="Show Beanie Locations"
onclick="javascript:ShowBeanieLocations()" />

<input id="Button2" type="button" value="Count Locations by Country:"
onclick="javascript:ShowCountries()"/>

The resulting page is shown here:



One minor issue I had to deal with is that the ImportShapeLayerData method of the Virtual Earth map control does not seem to handle MultiSurface GML elements in the GeoRSS feed. I had to change the spatial data for countries with multiple land masses to a single polygon in the database by adding a very thin line between each land mass. If anyone knows of a workaround for this, I'd love to hear about it!

Monday 17 November 2008

Adventures in Spatial Data - Part 1

As part of my job at Content Master, I get to play around with some pretty cool new technologies. Unfortunately, I usually need to do this to a fairly tight deadline; and often at the end of a project I'm left feeling that I'd like to have a bit more time to dig a little deeper. Recently I completed some marketing collateral and Hands-On Labs on Spatial Data in SQL Server 2008 for the SQL Server Marketing team at Microsoft, and of all the technologies and products I've looked at recently, I find myself fascinated with this one. I've always been impressed with technology that's actually useful, so I've decided going to play around with spatial data to see what I can come up with. I should warn you, I'm not a GIS specialist, but I love that I can now incorporate geographical data into SQL Server databases - even with SQL Server Express Edition.

As a basic context for learning about this stuff, I've decided to create a simple Web-based application that combines spatial data in SQL Server 2008 with Virtual Earth. Specifically, it will track the travels of Beanie - a small white bear that we've had in our family for a number of years and who gets taken with us wherever we go (it's a long story!). Here's Beanie:

The Web application is available at http://www.graemesplace.com/beanietracker.htm, and I'll spend the next couple of blog articles dissecting it (and probably making changes to it as I go).

OK, so let's cover a few basics first. Spatial data support is new in SQL Server 2008, and takes the form of two new data types: geometry and geography. The first of these is designed to handle planar spatial data (i.e. points, lines and shapes on a flat surface), while the second is for geodetic spatial data (i.e. points, lines, and shapes on the surface of a spheroid - specifically the planet Earth). The data types ar quite similar to one another, and expose many of the same methods and properties - key differences between them include:
  • The geometry type uses arbitrary x and y coordinates to define spatial elements within a bounded flat area, while the geography type uses longitude and latitude coordinates to define spatial elements on the surface of the Earth.
  • The geometry type uses basic pythagorean mathematics to calculate distances between points, while the geography type accounts for the curvature of the Earth when calculating distances.
Since I want to integrate my data with Virtual Earth, it makes most sense to use the geography type for Beanie's travel data.

The first thing I need to do is to define the database objects for my application. For this aplication, I want to store the locations that Beanie has visited as a longitude/latitude point (along with information about the location and a photograph of Beanie there - just in case you thought I was making this up!)
So, here's the definition for the Photos table:
CREATE TABLE Photos
([PhotoID] int IDENTITY PRIMARY KEY,
[Description] nvarchar(200),
[Photo] varbinary(max),
[Location] geography)
GO


Note the Location column, which is defined as geography. All of the locations in this table will be simple long/lat points rather than lines or shapes, but the geography type will store any valid geography instance.

To insert a location, I can choose from a number of ways to parse the spatial data - depending on the format I have it in. For example, here's an INSERT statement that inserts a geography instance from a text-based decription of a point:
INSERT INTO Photos
([Description], Photo, Location)
VALUES
('Beanie in Las Vegas',
(SELECT * FROM OPENROWSET(BULK N'C:\BeanieTracker\3.JPG', SINGLE_BLOB) As [Photo]),
geography::STPointFromText('POINT (-115.1765 36.1132)', 4326))

Note the use of the STPointFromText static method. This parses the spatial text description, which must specify coordinates in longitude - latitude order. Note also the number 4326 specified as the Spatial Reference ID (SRID) - this is a code that determines the specific geodetic model that the coordinates are based on (in this case, 4326 indicates the WGS84 spheroid model used by GPS systems. There are a few different models, largely because it's difficult to be exact about what shape the Earth actually is! There's a useful article on this
here that might help if you're interested in learning more about ellipsoid models.)

Spatial data is commonly available in Geographic Markup Language (GML) format - an XML syntax for describing geospatial elements, and you can also insert data in this format as shown here:

INSERT INTO Photos
([Description], Photo, Location)
VALUES
('Beanie at Edinburgh Castle',
(SELECT * FROM OPENROWSET(BULK N'C:\BeanieTracker\2.JPG', SINGLE_BLOB) As [Photo]),
geography::GeomFromGml
('<Point xmlns="http://www.opengis.net/gml">
<pos>55.9486 -3.2005</pos>
</Point>'
, 4326))


Note the use of the GeomFromGml static method in this code, and also note that this time the coordinates are in latitude - longitude order. The reason for the inconsistancy stems from a late-breaking change in spatial data support in SQL Server 2008 (see
Isaac Kunen's blog article about this).

The next thing I need in the application is another table in which to store details about countries. The reason I need this is that I want to be able to find out how many locations Beanie has visited within each country, and show them visually on a map. I'll store the shapes of the countries as polygons - in terms of the database schema, I can use another geography column, since the data type can handle polygons and lines as well as points. here's the table definition:
CREATE TABLE Countries
(CountryID INT IDENTITY PRIMARY KEY,
CountryName nvarchar(255),
CountryShape geography)


To insert the polygon data, I can use the same basic approaches I used for the long/lat points in the Photos table. All I need to do is to describe the polygon as a sequence of points, as shown in this example:
INSERT INTO Countries
(CountryName, CountryShape)
VALUES
('France',
geography::STPolyFromText
('POLYGON
((
2.109375 51.01375465718818,
0.615234375 50.17689812200107,
-0.439453125 49.439556958940855,
-1.2744140625 49.837982453084834,
-2.1533203125 49.696061819115634,
-2.28515625 48.893615361480194,
-4.9658203125 48.83579746243093,
-4.5703125 47.635783590864854,
-3.1640625 47.487513008956554,
-1.6259765625 46.07323062540835,
-1.3623046875 44.809121700077355,
-1.845703125 43.32517767999296,
0.615234375 42.682435398386204,
3.0322265625 42.42345651793833,
4.2626953125 43.421008829947254,
6.2841796875 42.74701217318067,
7.6025390625 43.7393520791547,
6.943359375 44.37098696297173,
6.6357421875 45.182036837015886,
6.5478515625 46.49839225859763,
5.9765625 46.13417004624326,
6.328125 46.6795944656402,
7.119140625 47.57652571374621,
8.2177734375 48.951366470947725,
6.5478515625 49.32512199104001,
4.833984375 50.064191736659104,
4.21875 49.89463439573421,
4.1748046875 50.3734961443035,
2.98828125 50.62507306341437,
2.109375 51.01375465718818
))', 4326)
)

As before, I can also import the data in GML format:

INSERT INTO Countries
(CountryName, CountryShape)
VALUES
('Egypt',
geography::GeomFromGml
(
'<Polygon xmlns="http://www.opengis.net/gml">
<exterior>
<LinearRing>
<posList>
31.57853542647338 24.9609375
31.128199299111959 24.697265625
30.524413269923986 24.9609375
30.221101852485983 24.609375
29.152161283318915 24.9609375
21.94304553343818 24.9609375
21.983801417384697 34.1015625
23.140359987886114 35.6396484375
27.9361805667694 34.43115234375
29.535229562948469 34.892578125
31.278550858946531 34.21142578125
31.222197032103196 32.32177734375
1.597252561706661 31.1572265625
30.939924331023441 28.76220703125
31.57853542647338 24.9609375
</posList>
</LinearRing>
</exterior>
</Polygon>'
, 4326)
)



Again, note the difference in the ordering of the longitude and latitude coordinates between the two methods. Also note that you must describe the polygon as a sequence of coordinates that enclose the shape you want to store in a counter-clockwise direction. The reason for this requirement is to avoid ambiguity about the part of the Earth's surface you want to include in the shape and the rest of the Earth's surface. For example, look at the following picture:



Now, does this show a small red circle on a gray sphere, or does it show a large gray area that covers most of a red sphere? By using the convention of describing polygons by enclosing them in a counter-clockwise direction, you can be unambiguous about which shape you're describing. Additionally, if you try to define a shape that is larger than a hemisphere, the geography type will raise an exception - and this is often happens when you describe polygon in the wrong direction by mistake. See this article in Ed Katibah's blog for an interesting discussion about this.
OK, back to our database - and an interesting problem. Some countries consist of more than one landmass. In other words, they include multiple polygons. Fortunately, the geography data type can handle this scenario as shown in the following code, which creates a geography instance for the United States that includes the mainland US, Alaska, and Hawaii:
INSERTINTO Countries
(CountryName, CountryShape)
VALUES
('United States of America',
geography::STMPolyFromText
('MULTIPOLYGON(
((-123.07402588363084 37.767367310117017,
-121.94097917688177 36.490937923858858,
-120.37149732826121 34.045890710439785,
-117.16155430516534 32.715680633728539,
-115.13671875 32.54681317351514,
-110.390625 31.203404950917395,
-108.6328125 31.653381399664,
-106.47549795243778 31.733844355633792,
-103.7109375 29.075375179558346,
-101.56221503336745 29.807382670042525,
-100.89817721206964 29.366207091173063,
-99.501983817857962 27.530904416002304,
-97.505157798460743 25.866221792519891,
-97.149468867243158 25.943457571654395,
-97.176960378652467 27.675151290420871,
-93.894849576786243 29.732981419413576,
-90.506037559706073 29.06303206938912,
-89.12109375 30.353916372297035,
-86.1328125 30.27804437780013,
-85.2099609375 29.649868677972303,
-83.95751953125 29.99300228455108,
-81.780293310096368 24.5583430193,
-80.134852769446283 25.792769876534258,
-80.101951906484146 26.931995972872219,
-80.606873856214719 28.079387728938791,
-80.537540582081022 28.470839206510636,
-80.749057521104547 32.189103634193479,
-75.984925610664732 35.373785353022406,
-71.591597672350787 41.189858770922925,
-70.3056301459883 41.801225588436182,
-66.967327818020152 44.812878222881466,
-69.154285648166763 47.454989805995105,
-71.538670536472367 44.99637100669522,
-74.735724992604418 45.028448990132183,
-77.250621135570626 43.995140406452222,
-79.064274837925424 43.10662863009243,
-78.878439231461826 42.88544317671419,
-82.652583599187864 41.76926713330969,
-84.347939004022578 46.518294248124974,
-89.245498827623535 48.381706457487851,
-102.54842574981625 49.00235529594778,
-122.7588943299908 48.976728632317268,
-123.14601579176878 48.165524140067781,
-123.43134011415688 48.118720268780265,
-124.73518224557731 48.391678533676071,
-123.07402588363084 37.767367310117017)),
((-141.15234375 59.88893689676585,
-141.50390625 70.08056215839737,
-156.68701171875 71.43417616304582,
-168.3984375 68.84766505841037,
-164.8828125 66.99884379185184,
-169.1015625 65.58572002329472,
-164.70703125 63.704722429433225,
-169.1015625 60.23981116999892,
-162.59765625 58.17070248348609,
-171.5625 51.83577752045248,
-153.6328125 56.072035471800866,
-148.0078125 59.44507509904714,
-141.15234375 59.88893689676585)),
((-159.169921875 22.411028521558702,
-159.85107421875 22.380555501421533,
-160.400390625 21.80030805097259,
-158.26904296875 21.238182425982312,
-157.08251953125 20.80747157680652,
-156.741943359375 20.478481600090564,
-156.02783203125 20.262197124246533,
-156.2255859375 19.673625561844392,
-155.819091796875 18.781516724349703,
-154.632568359375 19.383704634148017,
-155.8740234375 20.848545148787234,
-157.8955078125 21.749295836732088,
-159.2138671875 22.27893059841188,
-159.169921875 22.411028521558702))
)', 4326)
)
Note the use of the STMPolyFromText method, which includes a text description of three polygons. Of course, you can also use GML for this, as shown in the following code to define two polygons for the United Kingdom of Great Britain and Northern Ireland:

INSERT INTO Countries
(CountryName, CountryShape)
VALUES
('United Kingdom',
geography::GeomFromGml
('<MultiSurface xmlns="http://www.opengis.net/gml">
<surfaceMembers>
<Polygon>
<exterior>
<LinearRing>
<posList>
58.813741715707813 -5.09765625
58.539594766640484 -6.6796875
57.704147234341931 -7.998046875
56.022948079627454 -6.767578125
55.379110448010472 -6.15234375
55.07836723201514 -5.361328125
54.162433968067809 -3.779296875
53.540307391500221 -3.251953125
53.44880683542759 -4.7021484375
52.696361078274485 -4.98779296875
52.749593726741139 -4.24072265625
52.308478623663355 -4.2626953125
51.944264879028765 -5.38330078125
51.440312757160115 -5.07568359375
51.508742458803319 -3.955078125
51.358061573190916 -3.5595703125
51.303145259199056 -3.1640625
51.206883394865621 -4.482421875
50.007739014636869 -5.99853515625
49.908787000867136 -5.0537109375
50.247204901392671 -4.24072265625
50.12057809796007 -3.69140625
50.471490851399558 -2.13134765625
50.583236614805884 -0.72509765625
50.861444110589233 1.12060546875
51.23440735163458 1.64794921875
51.577069537225668 1.0546875
52.1874047455997 1.77978515625
52.816043191549333 1.82373046875
53.054421865461023 0.615234375
52.948637884883205 0.3076171875
53.278353017531821 0.41748046875
54.610254981579146 -0.615234375
54.711928848406139 -1.12060546875
55.615589025267482 -1.51611328125
56.084297562061408 -2.63671875
55.986091533808384 -3.2080078125
56.022948079627454 -3.69140625
56.059769479106571 -3.22998046875
56.279960831728459 -2.548828125
56.389583525613055 -2.79052734375
57.112385007934009 -2.021484375
57.680660029772348 -1.73583984375
57.715885127745032 -3.8232421875
57.879816455278409 -3.7353515625
57.879816455278409 -3.955078125
58.562522728537338 -2.8125
58.813741715707813 -5.09765625
</posList>
</LinearRing>
</exterior>
</Polygon>
<Polygon>
<exterior>
<LinearRing>
<posList>
55.354135310210573 -7.31689453125
55.028022112992517 -7.4267578125
54.762670400254947 -7.646484375
54.711928848406139 -7.91015625
54.635697306063854 -7.93212890625
54.610254981579146 -7.7783203125
54.482804559582554 -8.173828125
54.1109429427243 -7.53662109375
54.188155481071512 -7.20703125
54.380557368630654 -7.03125
54.213861000644926 -6.85546875
54.188155481071512 -6.7236328125
54.046489113355761 -6.70166015625
54.033586335210849 -6.08642578125
54.393352223845881 -5.38330078125
54.800684867322332 -5.55908203125
54.863962939854758 -5.82275390625
55.216490131689788 -6.1083984375
55.241552035652518 -6.87744140625
55.354135310210573 -7.31689453125
</posList>
</LinearRing>
</exterior>
</Polygon>
</surfaceMembers>
</MultiSurface>
'
,4326)
)

After you've inserted your spatial data, you can query it in Transact-SQL. SQL Server Management Studio includes a pretty cool visualizer that lets you see your spatial data projected as a map:

You can retrieve spatial data in Binary, Text, or GML format as shown by the following queries and results:

SELECT [Description], [location]
FROM Photos

Results:
Beanie at Edinburgh Castle 0xE6100000010C287E8CB96BF94B40B4C876BE9F9A09C0
Beanie in Las Vegas 0xE6100000010C1FF46C567D0E42409EEFA7C64BCB5CC0


SELECT [Description], [location].ToString()
FROM Photos

Results:
Beanie at Edinburgh Castle POINT (-3.2005 55.9486)
Beanie in Las Vegas POINT (-115.1765 36.1132


SELECT [Description], [location].AsGml()
FROM Photos

Partial Results:
Beanie at Edinburgh Castle <Point xmlns="http://www.opengis.net/gml"><pos>55.9486 -3.2005</pos></Point>
Beanie in Las Vegas <Point xmlns="http://www.opengis.net/gml"><pos>-115.1765 36.1132<pos></Point>

Of course, if the spatial support in SQL Server 2008 only enabled you to store and retrieve spatial objects, it would be of limited use. After all, you could just as easily store this data as text or XML. The real advantage of spatial data support is the ability to perform spatial operations on the data, and ask location-related questions such as "How many times has Beanie been in each country?" (or "What are the sales volumes for each sales region?" if you prefer) and "How far is this place from Beanie's home?"(or "Where's the nearest store to a customer's current location?"). Here are a couple of code samples that shpow how these kinds of query can be performed by using the instance methods of the geography data type.
-- Function to get the distance of any point from Beanie's home in Dunfermline, Scotland
CREATE FUNCTION GetDistanceFromHome (@location geography)
RETURNS float
AS
BEGIN
DECLARE @home geography;
SET @home = geography::STPointFromText('POINT(-3.408304452896118 56.06143472072096)', 4326)
DECLARE @dist float;
SET @dist = ROUND(@home.STDistance(@location)/1000, 2)
RETURN @dist
END
GO
-- Stored procedure to return all photos and their locations
CREATE PROCEDURE GetPhotosGML
AS
-- Return the location geography data as GML
SELECT [PhotoID], [Description], [Location].AsGml() As PhotoGML,
(SELECT dbo.GetDistanceFromHome(Location)) AS DistanceFromHome
FROM Photos
GO

EXEC GetPhotosGML

Partial Results:
PhotoID Description PhotoGML DistanceFromHome
2 Beanie at Edinburgh Castle <Point xmlns="http://www.opengis.net/gml"><pos>55.9486 -3.2005</pos></Point> 18.05
3 Beanie in Las Vegas <Point xmlns="http://www.opengis.net/gml"><pos>36.1132 -115.1765</pos></Point> 7940.86

SELECT c.CountryName, Count(p.PhotoID) Visits
FROMPhotos p, Countries c
WHERE p.Location.STIntersects(c.CountryShape) = 1
GROUP BY c.CountryName
Partial Results:
CountryName Visits
Egypt 2
France 1


So, hopefully you've enjoyed this little exploration of spatial data in SQL Server 2008. The database code for the Beanie Tracker application is available from
http://cid-ddad9079cff45619.skydrive.live.com/self.aspx/Public/BeanieTracker.zip if you want to play with it yourself. You'll need SQL Server 2008 (you can get the Express Edition for free from http://www.microsoft.com/sqlserver/2008/en/us/express.aspx).