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);
-- 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!).



2 comments:

  1. Good article, but I think there's a mistake in one of your examples. If you look at it:

    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);

    You'll notice that the order of points are the opposite orientation of your diagram. They are all counter-clockwise. Also bear in mind that the Geometry data type uses (lat, long) coordinate pair order. That means the coordinates are expressed as (y, x), which might be why you drew some of them as clockwise when they're really counter-clockwise. Just wanted to point that out.

    Thanks

    ReplyDelete
  2. Dear Sir,

    I hope you are doing well. I got this email address from one of your contribution web site. I have launched a web site www.codegain.com and it is basically aimed C#,JAVA,VB.NET,ASP.NET,AJAX,Sql Server,Oracle,WPF,WCF and etc resources, programming help, articles, code snippet, video demonstrations and problems solving support. I would like to invite you as an author and a supporter.
    Looking forward to hearing from you and hope you will join with us soon.

    ReplyDelete