SQL Server 2008 (Katmai) Spatial/Geography Functions

Decided to take the 'night off' from SilverlightEarth.com to play with the new Geography types in the latest SQL Server 2008 CTP.

There is surprisingly little around in terms of demos and samples so far, John O'Brien's First Impression being the best by far (and uses Aussie data too!). Only took a few minute to get his postcode-data demo going in C# & WebDev Express 2008.

His code covers:

• [geography] SQL Data Type
• geography::STPointFromText(@WKT, 4326)
• POLYGON (Well Known Text syntax)
• geography::STPolyFromText(@WKTBounds, 4326)
• STIntersects 'function'
• SPATIAL INDEX on a column of type [geography]

and discusses the interesting 'side effect' of the datatype enforcing polygons not spanning greater than a hemisphere of area AND "Must be supplied in counter-clockwise order"! (the 4326 SRID is explained here)

This query over John's data:

SELECT Id, Postcode, Title, Location.STAsText()
FROM postcodes

returns the list of all the postcodes with 'human readable' co-ordinates, but that's about the only "interesting" thing you can do without adding other data...

So I visited the Google Election '07 site which has a link to http://voteaustralia.googlepages.com/aus.kmz that contains a 'collection' of polygons defining the electorates in Australia.

It was trivial to extract the data for just one electorate (Cook) using Excel to re-order (see counter-clockwise note above, and error below) and reformat the <coordinates> to fit in the following query:

DECLARE @g geography;
SET @g = geography::STGeomFromText('POLYGON((
-34.041 151.166,-34.036 151.178,-34.035 151.187,-34.037 151.195,
-- coordinates removed for clarity
-34.052 151.156,-34.044 151.162,-34.041 151.166))', 4326);

SELECT [Postcode] as Title
,      [Title] as Description
,      [Location].STAsText()
FROM   [dbo].[Postcodes]
WHERE  [Location].STIntersects(@g) = 1

Which returns the 31 postcodes in the Cook electorate, and

SELECT @g.STArea() -- square metres

which returns 195631.754900467 (square metres).

It shouldn't be hard to programmatically import the electorial boundaries to do more spatial queries; could also mash it up with aec polling place and voting population data (by postcode). Using the area and population we could do some density visualisation, and maybe some other useless calculations...

This is the error I got when I DIDN'T reverse the order of the coordinates from the KML file: localhost(MYPC\Administrator): Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user defined routine or aggregate 'geography':
Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeometryData g) - as explained above, you must ensure the POLYGON is described in counter-clockwise order!


Link drop:
geography Data Type Reference (Books Online)

Demystifying Spatial Support in SQL Server 2008 is an EXCELLENT 'real world' example.

SQL Server 2008 Katmai will Include Spatial Support is also good reading

And finally, this is a rather old (May) 'introduction' but still worth a look.


WOT: TODO in SilverlightEarth.com - FlashEarth mash-up