{"id":575,"date":"2010-12-29T10:29:00","date_gmt":"2010-12-29T10:29:00","guid":{"rendered":"\/blogs\/bobb\/post\/Measuring-the-earth-with-SQL-Server-Denali.aspx"},"modified":"2010-12-29T10:29:00","modified_gmt":"2010-12-29T10:29:00","slug":"measuring-the-earth-with-sql-server-denali","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/","title":{"rendered":"Measuring the earth with SQL Server Denali"},"content":{"rendered":"

\nSo what's the area of the earth? Well…depends on who's measurement you want to use.\n<\/p>\n

\nThere an interesting SQL Server Denali spatial feature known as fullglobe. Actually there's two features with that name, more later; I was using the FULLGLOBE geometric type (geometric type is the term used by OGC<\/a>, this one only works with SQL Server's GEOGRAPHY data type). You create one like this:\n<\/p>\n

\nDECLARE @g geography = ('FULLGLOBE', 4326) — where 4326 is your SRID\n<\/p>\n

\nIt's a special extension to the OGC (that doesn't have a specific geography type as such either) that specifies the entire earth. So you can get the area of the earth by using @g.STArea(). After doing the "figure out the water area by subtracting the union of the countries' area" thing with it, I thought this might be a cool way to demonstrate one facet of SRIDs (that is spatial reference system identifiers<\/a>).\n<\/p>\n

\nThere's a table called sys.spatial_reference_systems that lists all of the SRIDs supported by SQL Server's geography type (they use these in the computations). The also includes the OCG well-known text that describes them. One parameter describes the ellipsoid used by this SRID. Ellipsoids' measurements differ for two main reasons; different surveys and the fact that the earth changes shape slightly over time.\n<\/p>\n

\nSo you could probably guess (and you'd be right) that different SRIDs would produce different areas for the earth. Here's the code. Note that as a special bonus for using it, I found two new SRIDs in Denali, Unit Sphere (SRID 101) and Spherical Earth (SRID 126). Nice.\n<\/p>\n

\nSELECT spatial_reference_id AS SRID,
\n       dbo.measure_globe(spatial_reference_id) AS Area,
\n       unit_of_measure,
\n       well_known_text,
\n       dbo.get_ellipsoid(well_known_text) AS Ellipsoid
\nFROM sys.spatial_reference_systems
\nORDER BY area, ellipsoid, spatial_reference_id – whatever you want\n<\/p>\n

\nCREATE FUNCTION dbo.measure_globe(@srid INT)
\nRETURNS FLOAT
\nAS
\nBEGIN
\nDECLARE @g geography = geography::STGeomFromText('FULLGLOBE',@srid);
\nRETURN @g.STArea()
\nEND\n<\/p>\n

\nCREATE FUNCTION dbo.get_ellipsoid(@wkt NVARCHAR(4000))
\nRETURNS NVARCHAR(4000)
\nAS
\nBEGIN
\n— grungy string-handling alert
\nDECLARE @begin INT = PATINDEX('%ELLIPSOID%',@wkt) + 11
\nDECLARE @rest NVARCHAR(4000) = SUBSTRING(@wkt, @begin, 4000)
\nDECLARE @end INT = CHARINDEX('"', @rest)
\nRETURN SUBSTRING(@rest, 1, @end-1)
\nEND
\nGO\n<\/p>\n

\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"

So what's the area of the earth? Well…depends on who's measurement you want to use. There an interesting SQL Server Denali spatial feature known as fullglobe. Actually there's two features with that name, more later; I was using the FULLGLOBE geometric type (geometric type is the term used by OGC, this one only works with SQL […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,36],"tags":[],"class_list":["post-575","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-sql-server-spatial"],"yoast_head":"\nMeasuring the earth with SQL Server Denali - 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\/measuring-the-earth-with-sql-server-denali\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Measuring the earth with SQL Server Denali - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"So what's the area of the earth? Well…depends on who's measurement you want to use. There an interesting SQL Server Denali spatial feature known as fullglobe. Actually there's two features with that name, more later; I was using the FULLGLOBE geometric type (geometric type is the term used by OGC, this one only works with SQL […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2010-12-29T10:29:00+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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/\",\"name\":\"Measuring the earth with SQL Server Denali - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2010-12-29T10:29:00+00:00\",\"dateModified\":\"2010-12-29T10:29:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2012\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2012\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Measuring the earth with SQL Server Denali\"}]},{\"@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":"Measuring the earth with SQL Server Denali - 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\/measuring-the-earth-with-sql-server-denali\/","og_locale":"en_US","og_type":"article","og_title":"Measuring the earth with SQL Server Denali - Bob Beauchemin","og_description":"So what's the area of the earth? Well…depends on who's measurement you want to use. There an interesting SQL Server Denali spatial feature known as fullglobe. Actually there's two features with that name, more later; I was using the FULLGLOBE geometric type (geometric type is the term used by OGC, this one only works with SQL […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/","og_site_name":"Bob Beauchemin","article_published_time":"2010-12-29T10:29:00+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/","name":"Measuring the earth with SQL Server Denali - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2010-12-29T10:29:00+00:00","dateModified":"2010-12-29T10:29:00+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/measuring-the-earth-with-sql-server-denali\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2012","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2012\/"},{"@type":"ListItem","position":3,"name":"Measuring the earth with SQL Server Denali"}]},{"@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\/575","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=575"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/575\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=575"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=575"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=575"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}