Share via

Spatial Aggregates in SQL Server Denali

  • Article

 

SQL Server Code-named “Denali” is currently available as a Community Technology Preview (CTP).  Denali introduces 4 new aggregates to the the suite of spatial operators in SQL Server:

  • Union Aggregate
  • Envelope Aggregate
  • Collection Aggregate
  • Convex Hull Aggregate

All aggregates are implemented as static methods which work for either the Geography or the Geometry data types. As an example, consider the following geography aggregate:

Geography::UnionAggregate(geog)

and its counterpart for geometry:

Geometry::UnionAggregate(geom)

The following introduces each aggregate with a short definition, a code sample and a visual illustrating what goes into the aggregate and what is returned. While aggregates are applicable to all classes of spatial data (points, linestrings, etc.) they are most effectively described and illustrated with polygons, which are used in the following discussion.

Union Aggregate

Definition: Combines multiple spatial objects into a single spatial object, removing interior boundaries, where applicable.

SELECT Geography::UnionAggregate(geog)
  FROM Counties
    WHERE name_1 = 'Washington';

image

Envelope Aggregate

The unlike other aggregates, the Envelope Aggregate exhibits different behavior depending on whether is is applied to a geography or a geometry type.  For the geometry type, a “traditional” rectangular polygon is calculated which closely bounds the selected input objects.  For the geography type, a circular object is calculated which loosely bounds the selected input objects.  Furthermore, the circular object is defined using the new CurvePolygon feature.

Envelope Aggregate for the Geometry Type

Definition: Returns a bounding rectangular polygon object, which encloses one or more spatial objects.

SELECT Geometry::EnvelopeAggregate(geom)
  FROM Counties
    WHERE name_1 = 'Washington';

image

Envelope Aggregate for the Geography Type

Definition: Returns a bounding circular object as a CurvePolygon which encloses one or more spatial objects.

SELECT Geography::EnvelopeAggregate(geog).STCurveToLine()
  FROM Counties
    WHERE name_1 = 'Washington';

Note the STCurveToLine() method in the sample code, above. This new method is used to output spatial objects in a manner which older visualization systems can digest.

image

Collection Aggregate

Definition: Returns a geometry collection with one geometry part for each spatial object(s) in the selection set.

SELECT Geography::CollectionAggregate(geog)
  FROM Counties
    WHERE name_1 = 'Washington';

image

 

Convex Hull Aggregate

Definition: Returns a convex hull polygon which encloses one or more spatial objects.

SELECT Geography::ConvexHullAggregate(geog)
  FROM Counties
    WHERE name_1 = 'Washington';

image

 

Technorati Tags: SQL Server,Denali,spatial,aggregates,union,envelope,collection,convex hull

Comments

  • Anonymous
    April 27, 2011
    Yah! Finally these are getting into SqlServer instead of using the codeplex tools. Love the CollectionAggregate also - very kewl! Lets hope the hemisphere issue is now resolved and we can get some better performance when doing lots of things like Point in Poly (STIntersects()) or STIntersection() calculations. Bring on Denali!! -PK (using Sql Spatial since the beta's..)

  • Anonymous
    April 27, 2011
    The comment has been removed

  • Anonymous
    April 28, 2011
    The comment has been removed

  • Anonymous
    July 14, 2011
    It might not be obvious from the screenshot above, but the CollectionAggregate() always returns a Geometry Collection. If you call CollectionAggregate() on a column containing only Points, for example, you'll get a Geometry Collection of Points, not a MultiPoint. Likewise, if you call it on a table containing only one LineString, say, you'll get a Geometry Collection with one LineString in it, not the LineString itself. This surprised me, as it does not match the behaviour of some other methods that seek to return the simplest geometry type capable of representing a given point set.

  • Anonymous
    October 09, 2013
    Are there any other places someone can find documentation/explanations of these aggregates? I have never worked with spatial data ever, and for the 70-461 certification tests there are several questions on this exact topic, but I can find no mention of these anywhere in BOL! I can find zero mention in all the exam preps I've looked at. Some explanations somewhere in just "plain old English" would be great...maybe some examples? Thank you!

  • Anonymous
    May 17, 2015
    I found this article extremely useful while studying for MCSA sql server exams. Thanks!!!