Thursday 27 December 2007

SQL Server 2008 Geography: STExteriorRing, STInteriorRingN

MSDN describes STExteriorRing() with this query
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g.STExteriorRing().ToString();

which returns
LINESTRING (0 0, 3 0, 3 3, 0 0, 0 0)

Here it is visually -- the Green line is the original geometry (one square inside another), the thicker Blue line shows that STExteriorRing and the Orange line shows STInteriorRing(1):
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g.STExteriorRing(), 0.3 as thickness, 'Blue' as color
SELECT @g.STInteriorRingN(1), 0.2 as thickness, 'Orange' as color
SELECT @g, 0.1 as thickness, 'Green' as color

Interestingly, this query exposed a bug in Geoquery where the Exterior and Interior rings were being 'joined' (ie there was no "pen up" occuring as the shapes were generated) -- causing the dodgy looking lines on the world map in the last post. Here's the "fixed" map drawing...

SQL Server 2008 Geometry: STUnion Aggregate

There doesn't seem to be a lot of information about Geometry Aggregates, except for these posts on MSDN Forums, including How to calculate table all geometry extent?

Why might we want aggregate functions on the GEOMETRY spatial datatype? Here's an example of 'how to use' the STUnion() from MSDN:
DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STUnion(@h) -- Can only STUnion two Geometries
On the left are the two GEOMETRYs, on the right is the STUnion() result.

The problem with these simple examples is they don't scale well to 'real' spatial database queries where you need to use these functions across result-sets. For example, it would be much nicer if we could insert data as follows

INSERT INTO TestShapes VALUES (geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0))
INSERT INTO TestShapes VALUES (geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0))
-- Now 'Union' all the shapes
SELECT dbo.UnionAggregate(Shape) FROM TestShapes

On the left is the result of this new UnionAggregate query, on the left is the result after added another shape;
INSERT INTO TestShapes VALUES (geometry::STGeomFromText('POLYGON((3 3, 5 3, 5 5, 3 5, 3 3))', 0))
SELECT dbo.UnionAggregate(Shape) FROM TestShapes

DOWNLOAD the source (8k) to try out the two aggregates posted on MSDN, or follow these steps:

1. Download the code from MSDN, or my copy. NOTE: You must do all these steps on a PC with SQL Server 2008 (Katmai) installed to get the Microsoft.SqlServer.Types.dll assembly. I copied this assembly into C:\assemblies\.

2. Compile the C# SQLCLR functions in C:\assemblies\ via the Command Prompt
c:\windows\\framework\v3.5\csc /t:library /r:Microsoft.SqlServer.Types.dll C:\assemblies\aggregate.cs

3. Execute T-SQL to register the aggregate functions in SQL Server 2008
CREATE ASSEMBLY SpatialAggregate FROM 'c:\assemblies\Aggregate.dll'
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.EnvelopeAggregate]
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.UnionAggregate]

sp_configure 'clr enabled', 1;

Here's another example of using the STUnion Aggregate, this time to outline continents on a GEOMETRY map. Note the thickness and color columns are a feature of Geoquery.

select continent, dbo.UnionAggregate(Location), 0.5 as thickness, 'Brown' as color
from countries2 where continent is not null and Location.STArea() > 40
group by continent
union all
select name, Location, 0.1 as thickness, 'Green' as color
from countries2

(Not sure why weird lines appear through some of the STUnion-ed geometries... something to investigate another day)

Wednesday 19 December 2007

SQL Server 2008 Geometry: Reduce

I once had a go at using the Douglas-Peucker Algorithm in C# (for, so it was interesting to see it "pop-up" in SQL Server 2008 as the Reduce() function.

To understand what it does, you could try these queries (using country polygon data from the Google Earth community):

-- Tasmania from kmz
SET @a = geometry::STGeomFromText('POLYGON((146.5861 41.18666, 147.9718 40.74479, 148.2733 40.90111, 148.3638 42.22243, 148.1953 41.94545, 148.0791 42.11722, 147.8429 42.87292, 147.9997 42.90708, 147.9955 43.22759, 147.7897 43.24695, 147.6316 43.06555, 147.7065 42.93833, 147.8994 43.02687, 147.8258 42.93195, 147.5575 42.83056, 147.4271 43.04174, 147.3175 42.84666, 147.2475 43.26917, 146.9913 43.11243, 147.0953 43.28872, 146.8336 43.64806, 146.0383 43.49805, 145.9327 43.37632, 146.2345 43.32514, 145.8369 43.29723, 145.4597 42.90444, 145.2052 42.25695, 145.4694 42.52306, 145.552 42.35111, 144.8586 41.54445, 144.6372 41.03194, 144.7014 40.75917000000001, 146.5861 41.18666))',0)

SELECT @a.STNumPoints() AS [PointsInRawPolygon]
, @a.Reduce(0.2).STNumPoints() AS [PointsReduce1]
, @a.Reduce(1).STNumPoints() AS [PointsReduce5]

But it would be even better if we could visualize the effects that Reduce() has on the polygon data... introducing Geoquery Analyzer

Polygon data without Reduce()

Polygon data with moderate Reduce() tolerance

Polygon data with agressive Reduce() tolerance

Polygon data with overly agressive Reduce() tolerance!

And of course, if you raise the tolerance too high,
System.AgumentException: 24127: Using Reduce with the specified tolerance will yield an instance that is not valid. Using a smaller tolerance may achieve a valid result.

A few additional examples - some countries missing purely because the KML import had some errors...

NOTE 1: Geoquery Analyzer is currently in PRE alpha development status (ie. it's pretty dodgy). If/when the code gets to a fairly stable/usable point, it'll be uploaded here.

NOTE 2: if you looked really closely at the coordinates, you'll see I've changed the latitude values to be positive... the reason is NOTE 1.

NOTE 3: on the world maps, the 0,0 point is top/left which makes it appear upsidedown

Saturday 15 December 2007

Battleship 2008 - GEOMETRY datatype sample

Here's a quick example of the new coordinate datatypes in SQL Server 2008, in the form of a game!

Battleship 2008 - GEOMETRY datatype and spatial functions in SQL Server (and on CodeProject) uses the basics - POINT and LINESTRING - to build a simple, single-player version of the game Battleship

This is what a game "looks like" (13 is the GameId, 4 is my PlayerId... the other numbers are the co-ordinates I'm shooting at)

it's played in Management Studio, like a 'command line', so you'll need to keep your own copy of the board on paper (like this?)


Thursday 13 December 2007

SQL Server 2008 Geography vs Geometry

Really basic comparison of GEOGRAPHY and GEOMETRY data types in SQL Server 2008.

First, some queries:
DECLARE @ll geography;
DECLARE @g geometry;

PRINT 'LatLong polygon length'
SET @ll = geography::STGeomFromText(
'POLYGON((1 1,1 5,5 5,5 1,1 1))', 4326);
SELECT @ll.STLength() -- 1773426.60585491

PRINT 'Geometry polygon length'
SET @g = geometry::STGeomFromText(
'POLYGON((1 1,1 5,5 5,5 1,1 1))', 4326);
SELECT @g.STLength() -- 16

PRINT 'LatLong polygon area'
SET @ll = geography::STGeomFromText(
'POLYGON((3 3,6 4,6 2,3 3))', 4326);
SELECT @ll.STArea() -- 36749145501.1338

PRINT 'Geometry polygon area'
SET @g = geometry::STGeomFromText(
'POLYGON((3 3,6 4,6 2,3 3))', 4326);
SELECT @g.STArea() -- 3

You might want to read this excellent explanation of the 'Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.' requirement before looking at the next query.

PRINT 'Ring-orientation: counterclockwise'
DECLARE @ll geography;
SET @ll = geography::STGeomFromText('POLYGON((3 3,6 4,6 2,3 3))', 4326);
-- Command(s) completed successfully.
SET @ll = geography::STGeomFromText('POLYGON((3 3,6 2,6 4,3 3))', 4326);
-- The specified input does not represent a valid geography instance because it exceeds a single hemisphere.

Make sense? Maybe this diagram will help (green, anti-clockwise arrows good; red, clockwise arrows bad)

The first attempt at MUTLIPOLYGON didn't work, and at first I couldn't understand why. I got this example WKT from Wikipedia:
DECLARE @ll geography;
SET @ll = geography::STGeomFromText(
'MULTIPOLYGON(((1 1,5 1,5 5,1 5,1 1),(2 2, 3 2, 3 3, 2 3,2 2)),((3 3,6 2,6 4,3 3)))'
, 4326);
-- The specified input does not represent a valid geography instance.

Here it is 'on paper'

you'll notice they overlap and are all described clockwise... no problem, right? After all, the example came from Wikipedia! Hmmm, the Reduce doco just happens to mention for example, an invalid MultiPolygon instance is created if Reduce() is applied to simplify each ring in the instance and the resulting rings overlap..

Ah ha! So SQL Server doesn't like overlapping elements within a MULTIPOLYGON? Let's try removing the 'overlaps' (making the green polygon smaller) like this:
DECLARE @l1 geography;
SET @l1 = geography::STGeomFromText(
'MULTIPOLYGON(((2 2, 2 3, 3 3, 3 2, 2 2)
,(3 3,6 4,6 2,3 3))
,((1 1,2 1,2 2,1 2,1 1)))'
, 4326);
-- The specified input does not represent a valid geography instance

Still not working - hmm, maybe the ring-orientation is also a problem?

DECLARE @l1 geography;
SET @l1 = geography::STGeomFromText(
'MULTIPOLYGON(((2 2, 2 3, 3 3, 3 2, 2 2)
,(3 3,6 4,6 2,3 3))
,((1 1,1 2,2 2,2 1,1 1)))'

, 4326); -- IT WORKS!

Interestingly, as long one of the POLYGONs is anti-clockwise, the query seems to work! Also interestingly, the previous query works using the GEOMETRY type (just to prove that the 'hemisphere problem' only applies to GEOGRAPHY!).

DECLARE @l1 geometry;
SET @l1 = geometry::STGeomFromText(
'MULTIPOLYGON(((2 2, 2 3, 3 3, 3 2, 2 2)
,(3 3,6 4,6 2,3 3))
,((1 1,2 1,2 2,1 2,1 1)))'
, 4326);

Finally, here's another MULITPOLYGON example
DECLARE @ll geography;
SET @ll = geography::STGeomFromText(
(35.003075386599093 136.49999931072208,
35.004100499758216 136.00000001975212,
35.000000005283141 136.00000001974058,
35.003075386599093 136.49999931072208)),
((34.999999973649494 138.00000000826108,
36.5 136,
35.761206832024406 136.00000002190484,
35.004100371491738 136.99469190018391,
34.999999973649494 138.00000000826108)))'

, 4326);

SELECT @ll.STLength() -- 634673.083368519
SELECT @ll.STArea() -- 11370068204.2658
SELECT @ll.STIntersects(geography::STGeomFromText('POINT(-30.041 153.166)',4326)) -- 0
SELECT @ll.STIntersects(geography::STGeomFromText('POINT(36.5 136)',4326)) -- 1

Here's the query that confirms they don't overlap and therefore make a valid MULTIPOLYGON:
DECLARE @l1 geography;
DECLARE @l2 geography;
SET @l1 = geography::STGeomFromText('POLYGON ((
35.003075386599093 136.49999931072208,
35.004100499758216 136.00000001975212,
35.000000005283141 136.00000001974058,
35.003075386599093 136.49999931072208))'
, 4326);
SET @l2 = geography::STGeomFromText('POLYGON ((
34.999999973649494 138.00000000826108,
36.5 136,
35.761206832024406 136.00000002190484,
35.004100371491738 136.99469190018391,
34.999999973649494 138.00000000826108))'

, 4326);
SELECT @l1.STIntersects(@l2) -- 0 -- doesn't intersect!

That last example is from this post (in Japanese) on these Microsoft.SqlServer.Types. There is also a couple of excellent images that demonstrate the difference between GEOGRAPHY and GEOMETRY (remember to imagine the 2nd image is on a sphere!).

Wednesday 12 December 2007

SQL Server 2008 Geography: STIntersects, STArea

Having read (and tried out) John's first impression of SQL 2008 Spatial, I went about extending the dataset to try some of the other capabilities of SQL Server 2008's geography datatypes and functions.

The following tables were added:

and the data inserted. The KMZ file was parsed with the XSD generator and C# (similar to John's example);

the CSV files were opened in Excel

and INSERT statements generated.

It's worthwhile noting that the data import didn't go 100% according to plan. Some electorates consist of more than one polygon (to be unioned together, I suppose) - I've only imported the first Polygon for each electorate in the KMZ file. Other electorates failed with SQL errors - still to determine why the coordinates listed cause SQL Server to fail (no, it's not the ring-orientation...).

The following queries were then possible...

select state
, electorate
, ROUND(Location.STArea()/1000000,0) As [AreaSqKm]
from electorates

select state
, electorate
, COUNT(p.Title) AS [NumberOfPostcodes]
, ROUND(e.Location.STArea()/1000000,0) AS [AreaSqKms]
, ROUND(e.Location.STArea()/1000000 /COUNT(p.Title),0) AS [AreaPerPostcodeSqKm]
from electorates e
inner join Postcodes p on (p.Location.STIntersects(e.Location) = 1)
group by State, electorate, e.Location.STArea()/1000000

select state
, e.electorate
, p.Title as [Suburb]
, COUNT(pp.PollingPlaceID) AS NumberPollingPlaces
from electorates e
inner join Postcodes p on (p.Location.STIntersects(e.Location) = 1)
inner join PollingPlaces pp on (pp.Electorate = e.Electorate AND pp.Postcode = p.Postcode)
group by state, e.electorate, p.Title

select state
, e.electorate
, ROUND(e.Location.STArea(),0) AS [AreaSqMs]
, SUM(ppv.TotalVotes) AS [TotalVotes]
, ROUND(e.Location.STArea() /(1+SUM(ppv.TotalVotes)),0) AS [AreaPerVoterM]
from electorates e
inner join Postcodes p
on (p.Location.STIntersects(e.Location) = 1)
inner join PollingPlaces pp
inner join PollingPlaceVotes ppv on ppv.PollingPlaceId = pp.PollingPlaceId
on (pp.Electorate = e.Electorate AND pp.Postcode = p.Postcode)
group by State, e.electorate, e.Location.STArea()

It is this query I've attempted to map at the top of the post - hopefully demonstrating finding points inside a polygon to group postcode data together for couting and summation; and using the area function to determine 'density'.

The next step is to get the POLYGON data into a map (as well as the POINTS)...

select electorate, Location.STAsText() as PolygonWKT
from electorates order by electorate

Tuesday 11 December 2007

SQL Server 2008 (Katmai) Diagram Tool

The Database Diagram Tool in SQL Server 2008 (Katmai) Management Studio has (once again) not changed much from the previous version.

Some notes:

Scripted Diagram export still works
This script to 'export' diagrams to SQL file (designed for SQL Server 2005) works in 2008. Implies there hasn't been any change to the storage format, and probably diagrams scripted in 2005 can be 'rehydrated' in 2008.

Diagram context menu unchanged
Nothing new in this menu - although I had forgotten the Copy to Clipboard item existed...

Table context menu adds Spatial Indexes... item
New spatial types can have their indexes setup just like other index types

Scroll down to Spatial Indexes on this post and these explanations of spatial indexes for a brief explanation of 'how they work'

(and no, this dialog doesn't actually resize in Management Studio - it's been Photoshopped for clarity)

SQL Server 2008 (Katmai) Spatial/Geography Functions

Decided to take the 'night off' from 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 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.
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 - FlashEarth mash-up

Saturday 1 December 2007

Introducing 'Gaml' - Geographic Application Markup Language

Adding a few geographic tags to existing Xaml provides infinite flexibility for map "markup". This is a simple example of a flickr photo 'balloon' on Silverlight Earth

<Canvas xmlns=""
<Path Stroke="Black" Fill="Gray"
Data="m 30,20 v -20 h 200 v 85 h -200 v -45 l -30,-10 l 30,-10" />
<Image Canvas.Top="5" Canvas.Left="35" Width="75" Height="75"
Source="" />
<TextBlock Canvas.Left="114" Canvas.Top="8"
Text="New York - East River"
FontSize="10" FontFamily="Verdana" Foreground="#FFFFFFFF" />

The sample currently works, but due to a hosting issue (not serving .gaml files), the other demo's are not currently online. When this is resolved, the above example will be available on

There's more information on Gaml here, but there's a little more work to be done on the implementation... if you're wondering why some of the samples are flag-markers, they were the first examples of Xaml clipart that I could find!

Friday 30 November 2007

Silverlight Google Maps (inc new Topo view) now has 11 map options, including Yahoo's map tiles and the new Google Topo view. mapstraction had some helpful pointers on the Yahoo tile-names (so thanks to them).

The 'hybrid' layers don't "overlay" anything yet - that's still to come...

And finally, the design has been tweaked a bit - hopefully it looks a bit nicer?

p.s. yes, jerky panning fix to come...

p.p.s. another 'before & after' shot - check out the baseball diamond on the east side before (google) and after (virtual earth)... here it is in October.

Tuesday 27 November 2007

Silverlight Virtual Earth - now with (jumpy) drag-panning

Visit Auckland on and try out drag-panning across the map.

The dragging 'jumps' because it's currently aligning the "tile grid" to the nearest tile-boundary (256 pixels)... just a few more tweaks to get it behaving properly.

The "Link to this map" feature now includes the map type, so you can view the London Eye via (Virtual Earth) satellite, London (Virtual Earth) road map or even the London Eye on Google Maps.

And yes, it works on the Mac (Safari 3 and Firefox 2)!

Thursday 22 November 2007

Silverlight (Virtual) Earth with Ink

Thanks to Gavin's pointer to an MSDN sample, now has Ink! Click [Draw on this map] at top-right to try it out.

Sorry, I haven't figured out saving the Ink yet (although got some ideas from search sample and converting to xaml), but it's kinda fun to play with.

Still to do: drag-panning and bookmarking map-type Virtual Earth or Google Maps... so yes, it's still in beta.

Wednesday 14 November 2007

Virtual Earth 6 - new map 'shading'

Microsoft Virtual Earth 6 has been released - along with various different improvements to the "UI" (javascript and the controls that drive it), one noticable difference is the addition of 'topographical shading' in Road view. Here's the Golden Gate Recreation Area 'before' and 'after'...

Of course, now uses the new tiles - visit San Francisco and switch between (road) and (roadshade) views.

Seems to be available "down under", but only to zoom '13' and no closer...

(thanks to via windows live and Darren Neimke for the heads-up)

Monday 12 November 2007

Silverlight 'map tile client' finds a home -

While the map tile client isn't quite yet for release, I'd already paid for the domain so decided to post it anyway... which means

is now LIVE! Granted the dragging still doesn't work; but keyboard (arrows, page up, page down to zoom and pan) does, and the mouse-wheel zooms too. The search box 'searches' and the 'link to this page' does just that.

What's still to come? Well centering the search location for a start, then proper drag-panning on mouse-still-down. After that - maybe uploading some form of geo-coded Xaml to overlay directly on the map... and finally animation a-la

While I had already started working on the 'tile client' before coming across FlashEarth, that is basically the kind of result I'd like to get from Silverlight. Of course, since the Silverlight 1.0 version is (will be) Javascript-based, the code will be easily available for all to see...

Friday 9 November 2007

Add recipes to (for iPhone/iPod Touch)

Following these instructions for adding data to the Notes application for iPhone, you could add your favourite iPhone/iPod Recipes for easy reference. provides preformatted SQL to use with those instructions -- here is an example link for Sugar-grilled Banana Bagels, and how the resulting Note looks:

To 'import' any recipe as a Note, find it's ID by browing with Firefox or Safari, taking note of the URL when you find a recipe link you like

Then simply visit the link below, substituting the ID and follow the instructions.