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
POLYGON(Well Known Text syntax)
SPATIAL INDEXon a column of type [geography]
This query over John's data:
SELECT Id, Postcode, Title, Location.STAsText()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;Which returns the 31 postcodes in the Cook electorate, and
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
WHERE [Location].STIntersects(@g) = 1
SELECT @g.STArea() -- square metreswhich 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- as explained above, you must ensure the POLYGON is described in counter-clockwise order!
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.
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeometryData g)
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