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);
-- WORKS FOR GEOMETRY

Finally, here's another MULITPOLYGON example
DECLARE @ll geography;
SET @ll = geography::STGeomFromText(
'MULTIPOLYGON ((
(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!).