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