February 26, 2013

Tricky stuff with Sql Server Spatial part 1

Dear reader(s),

Last week I held an internal lecture about SQL Server Spatial and the basics of GIS here at knowit.
I really like talking about GIS as it is about the world and big amounts of data and I do like the world and big  amounts of data.

Well... where I am? who am I?

Oh, yeah. While preparing I noticed a few things that can be a bit tricky with SQL Server Spatial.

This is the first one.

Preparations:

I use a table called test which basically holds one geography column named spatial and another varchar() called name.

Inserts:


INSERT INTO Test (Name, Spatial)
VALUES(
    'Polygon',
    geography::STGeomFromText(
         'POLYGON((-1 -1, 8 -1 , 8 8 ,-1 8, -1 -1))',4326));

So this basically inserts a polygon into the world. As you can see it is square and crosses the equator. The number 4326 is called the SRID and basically means that the coordinates are calculated according to the projection WGS84. (projections basically means that you get different sets of coordinates depending on how you try to put the round globe on to a square piece of paper (a map). Look here for more info.).

Well, besides the 4326 this is no sweat... now try doing it this way:


INSERT INTO Test (Name, Spatial)
VALUES(
    'Polygon',
    geography::STGeomFromText(
         'POLYGON((-1 -1, -1 8 , 8 8 ,8 -1, -1 -1))',4326))

Exactly the same call, but we put the coordinates the opposite way. Now it is no longer a square polygon crossing the equator but instead it is all but a square polygon crossing the equator. It is covering the rest of the world.

Depending on if you add point clockwise or counter clockwise gives you two totally different polygons.

Why?

Well the world IS actually round meaning that any set of points that makes up a polygon on the surface of the world has two meanings. An interior version and an exterior version. Counter clockwise gives the interior version, clockwise the exterior. Easy to forget...

More about it here.


No comments:

Post a Comment