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
I was inspired today by 
Earth.jpg)