Geography type - Polygons

Question

Hi,

We're currently with ongoing spatial data project. We will use SQL SERVER 2014 to store spatial data in WGS84 SRS. We are having problems importing polygon's to database because of ring orientation defined. The restriction that existed on SQL SERVER 2008 of a polygon not being imported because it surpassed one hemisfere has been removed on this version so that is not problem. The problem we have is when calculating distances, areas and relations between polygons because of the ring orientation. We have no control over the data that is sent to us so we cannot force the clients to give the right ring orientation.

We've checked if SQL SERVER 2014 as a solution for this, meaning, reorient spatial object if the points are on the wrong orientation and it doesn't. There is a method on the geography type that reorients the ring orientation but, we only want that, when the ring orientation is wrong. Is there a good way to check if the polygon ring orientation is wrong and then reorient it? We discovered some solutions but they are straight saying that could fail on some cases.

If we use a geometry type column we could overpass the relations problems between spatial objets but the distances and areas are given on decimal degrees which is not what we want.

Best regards,

Bruno da Fonseca

Answer 1

Hi Bruno,

If you're certain that none of your polygons comes close to 1/2 the size of the earth, you can use the FULLGLOBE spatial type, check the polygon's area against half the area of a full globe and reorient if it's more than half. Ed Katibah also suggested using EnvelopeAngle instead, like this: http://www.sqlskills.com/blogs/bobb/loading-spatial-data-and-cleansing-converting-with-sql-server-2012/ Remember to also use MakeValid if you need it.

Having said that, there is the possibility that your data may actually be more than half the size of the earth (e.g. habitat of certain whales). If you have data like this, you'll need to collect the outliers and have a content-expert check them one at a time, or use some other semantic that can be programmed (e.g. it's pretty easy to observe the object in SQL Server's spatial results tab, knowing that whales don't live on land :-). Depending on the number of vertices of your object, you may have to Reduce it temporarily for the purpose of being able to use the spatial results tab.

If neither one of these methods is acceptable, you can use geometry type and code in formulas for area or distance when you need those. That's what folks that use databases without a geography type often do.

Hope this helps, Bob

Answer 2

Hi Bob,

None of the polygons that we receive come close to 1/2 of the size of the earth. So we will use one of the options, check if the area is bigger then 1/2 of the earth or use the EnvelopeAngle.

Thanks,

BdaF