{"id":679,"date":"2008-08-21T05:25:00","date_gmt":"2008-08-21T05:25:00","guid":{"rendered":"\/blogs\/bobb\/post\/A-helper-function-for-ring-orientation-in-the-SQL-Server-2008-geography-data-type.aspx"},"modified":"2013-01-03T23:59:48","modified_gmt":"2013-01-04T07:59:48","slug":"a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/","title":{"rendered":"A helper function for ring-orientation in the SQL Server 2008 geography data type"},"content":{"rendered":"<p>\nFolks have always had trouble with the fact that ring orientation is required with&nbsp;spatial instances&nbsp;if you&#39;re using SQL Server 2008&#39;s geography data type, but not with the geometry data type. For an explanation of the need for this, reference Isaac Kunen&#39;s <a href=\"http:\/\/blogs.msdn.com\/b\/isaac\/archive\/2008\/04\/06\/the-unexpected-too-large-polygon.aspx\" class=\"broken_link\">blog entry here<\/a> and Ed Katibah&#39;s blog entry (link in next paragraph).\n<\/p>\n<p>\nIn Ed&#39;s blog entry, he provides a neat way to fix&nbsp;spatial instances&nbsp;that have the wrong ring orientation for geography&nbsp;by using the geometry type and calling a method that forces the required ring orientation for geography. You should read his <a href=\"http:\/\/blogs.msdn.com\/b\/edkatibah\/archive\/2008\/08\/19\/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx\" class=\"broken_link\">entry from yesterday<\/a>&nbsp;for background.\n<\/p>\n<p>\nI thought this was such a&nbsp;cool mechanism that I coded up&nbsp;a SQLCLR UDF that encapsulates this behavior. It accepts almost any WKT and produces a &quot;proper&quot; geography type, regardless of the ring orientation in the WKT. The code is included with this post. The function is called GeographyFromAnyWKT. Well, almost any. There are certain edge conditions (such as a ring that actually would exceed a single hemisphere) that will produce and error, but at least you shouldn&#39;t have to worry about ring orientation. Thanks Ed!\n<\/p>\n<p>\nAs an aside, although this is a Visual Studio SQLCLR autodeploy database project, the function can&#39;t be autodeployed because it returns a SqlGeograhy type and this is not covered by the autodeployer code. So I&#39;ve included a deploy script and a couple of test cases&nbsp;in the project. Although I define the UDF as RETURNS NULL ON NULL INPUT, I&#39;ve also included (redundant) null checking in the function itself, just in case you want to change the function a bit. Enjoy!\n<\/p>\n<p><a href=\"http:\/\/3.209.169.194\/blogs\/bobb\/content\/binary\/geographyvalidator.zip\" class=\"broken_link\">GeographyValidator.zip (96.73 KB)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Folks have always had trouble with the fact that ring orientation is required with&nbsp;spatial instances&nbsp;if you&#39;re using SQL Server 2008&#39;s geography data type, but not with the geometry data type. For an explanation of the need for this, reference Isaac Kunen&#39;s blog entry here and Ed Katibah&#39;s blog entry (link in next paragraph). In Ed&#39;s [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,36,38],"tags":[],"class_list":["post-679","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sql-server-spatial","category-sqlclr"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>A helper function for ring-orientation in the SQL Server 2008 geography data type - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A helper function for ring-orientation in the SQL Server 2008 geography data type - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"Folks have always had trouble with the fact that ring orientation is required with&nbsp;spatial instances&nbsp;if you&#039;re using SQL Server 2008&#039;s geography data type, but not with the geometry data type. For an explanation of the need for this, reference Isaac Kunen&#039;s blog entry here and Ed Katibah&#039;s blog entry (link in next paragraph). In Ed&#039;s [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2008-08-21T05:25:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:48+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/\",\"name\":\"A helper function for ring-orientation in the SQL Server 2008 geography data type - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2008-08-21T05:25:00+00:00\",\"dateModified\":\"2013-01-04T07:59:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2008\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2008\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"A helper function for ring-orientation in the SQL Server 2008 geography data type\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"A helper function for ring-orientation in the SQL Server 2008 geography data type - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/","og_locale":"en_US","og_type":"article","og_title":"A helper function for ring-orientation in the SQL Server 2008 geography data type - Bob Beauchemin","og_description":"Folks have always had trouble with the fact that ring orientation is required with&nbsp;spatial instances&nbsp;if you&#39;re using SQL Server 2008&#39;s geography data type, but not with the geometry data type. For an explanation of the need for this, reference Isaac Kunen&#39;s blog entry here and Ed Katibah&#39;s blog entry (link in next paragraph). In Ed&#39;s [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/","og_site_name":"Bob Beauchemin","article_published_time":"2008-08-21T05:25:00+00:00","article_modified_time":"2013-01-04T07:59:48+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/","name":"A helper function for ring-orientation in the SQL Server 2008 geography data type - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2008-08-21T05:25:00+00:00","dateModified":"2013-01-04T07:59:48+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/a-helper-function-for-ring-orientation-in-the-sql-server-2008-geography-data-type\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2008","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2008\/"},{"@type":"ListItem","position":3,"name":"A helper function for ring-orientation in the SQL Server 2008 geography data type"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/679","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=679"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/679\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=679"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=679"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=679"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}