A New Frontier

Tiengemeten is a small island located in part of the Maas delta about 20km south of Rotterdam. It's a nature reserve these days, the last inhabitant having left the island about two years ago. It's a beautiful place, wild and open, big enough to spend a day exploring and small enough to get around on foot. It's well worth a visit if you're in the neighbourhood.

It also makes for a suitable case study in presenting some of the spatial features of SQL Server 2008. I'm planning a series of posts on this topic, so this won't be the last mention of Tiengemeten on this blog.

Geographic features can be represented in several different ways using the spatial data types in SQL 2008; as points (longitude & latitude coordinates), lines (a joined series of coordinates with a begin and end) or polygons (two-dimensional closed areas bound by a set of coordinates). Polygons, broadly speaking, can be any shape and size. You can define Asia as a polygon, for instance, but also go to the other extreme: a mapping vendor I spoke to last week has polygons defined for every group of trees in the Netherlands!

I've got a SQL 2008 database table with 27 rows, each with one column of datatype geography which in turn contains one polygon per record. There's one large polygon with over 1100 data points representing the Dutch mainland:

..and 26 more records containing the polygons for each of the Dutch islands of any size:

Putting them all together, a simple SELECT [GeoPolygon] FROM NLIslands; gives us a very recognizable set of shapes in the Management Studio:

Tiengemeten is the polygon that looks like this:

Let's examine the record more closely. The data for the polygon is stored as a very unwelcoming looking hexadecimal binary number, 0xE610000001040C000000000068... and so on for another 400 characters or so. We can produce a more friendly-looking representation of this data by using the ToString() method of the geography datatype, however.

SELECT [GeoPolygon].ToString() FROM NLIslands;

returns

POLYGON ((4.3239999990910292 51.725999999791384, 4.3379999995231628 51.7269999999553, 4.3540000002831221 51.731000000145286, 4.3560000006109476 51.731999999843538, 4.3560000006109476 51.734999999869615, 4.2760000005364418 51.754999999888241, 4.2689999993890524 51.75400000018999, 4.26799999922514 51.751000000163913, 4.2760000005364418 51.74599999981001, 4.296000000089407 51.736000000033528, 4.3220000006258488 51.728000000119209, 4.3239999990910292 51.725999999791384))

...which is instantly recognizable as a set of longitude and latitude co-ordinates. You'll notice that the first and last x-y pairs are identical; this is obligatory for a valid polygon definition.

You can produce the exact same representation yourself by reversing the process.

declare @v_Tiengemeten  geography = 'POLYGON ((4.3239999990910292 51.725999999791384, 4.3379999995231628 51.7269999999553, 4.3540000002831221 51.731000000145286, 4.3560000006109476 51.731999999843538, 4.3560000006109476 51.734999999869615, 4.2760000005364418 51.754999999888241, 4.2689999993890524 51.75400000018999, 4.26799999922514 51.751000000163913, 4.2760000005364418 51.74599999981001, 4.296000000089407 51.736000000033528, 4.3220000006258488 51.728000000119209, 4.3239999990910292 51.725999999791384))';

select @v_Tiengemeten;

Not very exciting so far for sure, but we're only getting started here. If you'll bear with me for a couple of months, I should be able to demonstrate some of the rich geospatial functionality of SQL Server 2008 and in particular how spatial data can be combined with "normal" relational datasets to produce surprising and incredibly useful queries and results.

If you'll permit a pun, watch this space.