{"id":564,"date":"2011-01-25T04:52:00","date_gmt":"2011-01-25T04:52:00","guid":{"rendered":"\/blogs\/bobb\/post\/Using-the-Denali-spatial-aggregates-on-the-client.aspx"},"modified":"2014-01-20T12:20:28","modified_gmt":"2014-01-20T20:20:28","slug":"using-the-denali-spatial-aggregates-on-the-client","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-denali-spatial-aggregates-on-the-client\/","title":{"rendered":"Using the Denali spatial aggregates on the client"},"content":{"rendered":"<p>\nOne of the interesting spatial features in SQL Server Denali is the inclusion of spatial aggregates, namely CollectionAggregate, ConvexHullAggregate, EnvelopeAggregate, and UnionAggregate. Here&#39;s an example of unioning two squares together to make a rectangle.\n<\/p>\n<p>\ncreate table t1 (g geometry)\n<\/p>\n<p>\ninsert t1 values(&#39;POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))&#39;)<br \/>\ninsert t1 values(&#39;POLYGON((1 0, 2 0, 2 1, 1 1, 1 0))&#39;)\n<\/p>\n<p>\nselect geometry::UnionAggregate(g) from t1\n<\/p>\n<p>\nBeside being useful, they are interesting because its the first use of <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms131057.aspx\">SQLCLR-based aggregates<\/a> (that I&#39;m aware of)&nbsp;as part of the SQL Server database engine code&nbsp;itself.\n<\/p>\n<p>\nThe <a href=\"http:\/\/sqlcat.com\/sqlcat\/b\/whitepapers\/archive\/2010\/11\/09\/new-spatial-features-in-sql-server-code-named-denali-community-technology-preview-1.aspx\" class=\"broken_link\">what&#39;s new spatial whitepaper<\/a> has an interesting comment about the spatial aggregates: &quot;The new aggregates are exposed in SQL Server only and are not exposed in the underlying spatial library&quot;. I asked (spatial)&nbsp;<a href=\"http:\/\/blogs.msdn.com\/b\/edkatibah\/\" class=\"broken_link\">Ed<\/a> about this and pointed out that you can use the spatial aggregates&nbsp;(or any .NET-based user-defined aggregate, for that matter)&nbsp;on the client as well as the server. You just need to find the correct class in the library in the spatial library, which is public (and hopefully it will stay public in the released version). Here&#39;s the same aggregation in client-side code.\n<\/p>\n<p>\n\/\/collection of SqlGeometry<br \/>\nList&lt;SqlGeometry&gt; glist = new List&lt;SqlGeometry&gt;();<br \/>\nglist.Add(SqlGeometry.Parse(&quot;POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))&quot;));<br \/>\nglist.Add(SqlGeometry.Parse(&quot;POLYGON((1 0, 2 0, 2 1, 1 1, 1 0))&quot;));\n<\/p>\n<p>\nGeometryUnionAggregate agg = new GeometryUnionAggregate();<br \/>\n\/\/ call the appropriate methods on the aggregate<br \/>\nagg.Init();<br \/>\nforeach (SqlGeometry geom in glist)<br \/>\n&nbsp;&nbsp;&nbsp; agg.Accumulate(geom);<br \/>\nSqlGeometry theanswer = agg.Terminate();<br \/>\nConsole.WriteLine(&quot;answer is {0}&quot;, theanswer);\n<\/p>\n<p>\nThe only strange thing is that, in T-SQL, the aggregate appears to be a static property on the geometry class (you use geometry::UnionAggregate to invoke it in T-SQL). But using reflection on the SqlGeometry class in the library reveals no such public (or private) property. But that&#39;s fine; you CAN use the spatial aggregates directly; you just need to know the name of the aggregate class. Conceivably, you could even parallelize the client-side&nbsp;aggregation and call Merge() at the appropriate time.\n<\/p>\n<p>\n@bobbeauch\n<\/p>\n<p>\n&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the interesting spatial features in SQL Server Denali is the inclusion of spatial aggregates, namely CollectionAggregate, ConvexHullAggregate, EnvelopeAggregate, and UnionAggregate. Here&#39;s an example of unioning two squares together to make a rectangle. create table t1 (g geometry) insert t1 values(&#39;POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))&#39;) insert t1 values(&#39;POLYGON((1 0, [&hellip;]<\/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-564","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-sql-server-spatial"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Using the Denali spatial aggregates on the client - 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\/using-the-denali-spatial-aggregates-on-the-client\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using the Denali spatial aggregates on the client - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"One of the interesting spatial features in SQL Server Denali is the inclusion of spatial aggregates, namely CollectionAggregate, ConvexHullAggregate, EnvelopeAggregate, and UnionAggregate. Here&#039;s an example of unioning two squares together to make a rectangle. create table t1 (g geometry) insert t1 values(&#039;POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))&#039;) insert t1 values(&#039;POLYGON((1 0, [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-denali-spatial-aggregates-on-the-client\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2011-01-25T04:52:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-20T20:20:28+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\/using-the-denali-spatial-aggregates-on-the-client\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-denali-spatial-aggregates-on-the-client\/\",\"name\":\"Using the Denali spatial aggregates on the client - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2011-01-25T04:52:00+00:00\",\"dateModified\":\"2014-01-20T20:20:28+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-denali-spatial-aggregates-on-the-client\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-denali-spatial-aggregates-on-the-client\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-denali-spatial-aggregates-on-the-client\/#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\":\"Using the Denali spatial aggregates on the client\"}]},{\"@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":"Using the Denali spatial aggregates on the client - 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\/using-the-denali-spatial-aggregates-on-the-client\/","og_locale":"en_US","og_type":"article","og_title":"Using the Denali spatial aggregates on the client - Bob Beauchemin","og_description":"One of the interesting spatial features in SQL Server Denali is the inclusion of spatial aggregates, namely CollectionAggregate, ConvexHullAggregate, EnvelopeAggregate, and UnionAggregate. Here&#39;s an example of unioning two squares together to make a rectangle. create table t1 (g geometry) insert t1 values(&#39;POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))&#39;) insert t1 values(&#39;POLYGON((1 0, [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-denali-spatial-aggregates-on-the-client\/","og_site_name":"Bob Beauchemin","article_published_time":"2011-01-25T04:52:00+00:00","article_modified_time":"2014-01-20T20:20:28+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\/using-the-denali-spatial-aggregates-on-the-client\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-denali-spatial-aggregates-on-the-client\/","name":"Using the Denali spatial aggregates on the client - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2011-01-25T04:52:00+00:00","dateModified":"2014-01-20T20:20:28+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-denali-spatial-aggregates-on-the-client\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-denali-spatial-aggregates-on-the-client\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-denali-spatial-aggregates-on-the-client\/#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":"Using the Denali spatial aggregates on the client"}]},{"@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\/564","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=564"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/564\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=564"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=564"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=564"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}